Formats for Input and Output Data

ClickHouse can accept and return data in various formats. A format supported for input can be used to parse the data provided to INSERTs, to perform SELECTs from a file-backed table such as File, URL or HDFS, or to read a dictionary. A format supported for output can be used to arrange the results of a SELECT, and to perform INSERTs into a file-backed table. All format names are case-insensitive.

The supported formats are:

Format	Input	Output
TabSeparated	✔	✔
TabSeparatedRaw	✔	✔
TabSeparatedWithNames	✔	✔
TabSeparatedWithNamesAndTypes	✔	✔
TabSeparatedRawWithNames	✔	✔
TabSeparatedRawWithNamesAndTypes	✔	✔
Template	✔	✔
TemplateIgnoreSpaces	✔	✗
CSV	✔	✔
CSVWithNames	✔	✔
CSVWithNamesAndTypes	✔	✔
CustomSeparated	✔	✔
CustomSeparatedWithNames	✔	✔
CustomSeparatedWithNamesAndTypes	✔	✔
SQLInsert	✗	✔
Values	✔	✔
Vertical	✗	✔
JSON	✔	✔
JSONAsString	✔	✗
JSONAsObject	✔	✗
JSONStrings	✔	✔
JSONColumns	✔	✔
JSONColumnsWithMetadata	✔	✔
JSONCompact	✔	✔
JSONCompactStrings	✗	✔
JSONCompactColumns	✔	✔
JSONEachRow	✔	✔
PrettyJSONEachRow	✗	✔
JSONEachRowWithProgress	✗	✔
JSONStringsEachRow	✔	✔
JSONStringsEachRowWithProgress	✗	✔
JSONCompactEachRow	✔	✔
JSONCompactEachRowWithNames	✔	✔
JSONCompactEachRowWithNamesAndTypes	✔	✔
JSONCompactStringsEachRow	✔	✔
JSONCompactStringsEachRowWithNames	✔	✔
JSONCompactStringsEachRowWithNamesAndTypes	✔	✔
JSONObjectEachRow	✔	✔
BSONEachRow	✔	✔
TSKV	✔	✔
Pretty	✗	✔
PrettyNoEscapes	✗	✔
PrettyMonoBlock	✗	✔
PrettyNoEscapesMonoBlock	✗	✔
PrettyCompact	✗	✔
PrettyCompactNoEscapes	✗	✔
PrettyCompactMonoBlock	✗	✔
PrettyCompactNoEscapesMonoBlock	✗	✔
PrettySpace	✗	✔
PrettySpaceNoEscapes	✗	✔
PrettySpaceMonoBlock	✗	✔
PrettySpaceNoEscapesMonoBlock	✗	✔
Prometheus	✗	✔
Protobuf	✔	✔
ProtobufSingle	✔	✔
ProtobufList	✔	✔
Avro	✔	✔
AvroConfluent	✔	✗
Parquet	✔	✔
ParquetMetadata	✔	✗
Arrow	✔	✔
ArrowStream	✔	✔
ORC	✔	✔
One	✔	✗
Npy	✔	✔
RowBinary	✔	✔
RowBinaryWithNames	✔	✔
RowBinaryWithNamesAndTypes	✔	✔
RowBinaryWithDefaults	✔	✗
Native	✔	✔
Null	✗	✔
XML	✗	✔
CapnProto	✔	✔
LineAsString	✔	✔
Regexp	✔	✗
RawBLOB	✔	✔
MsgPack	✔	✔
MySQLDump	✔	✗
DWARF	✔	✗
Markdown	✗	✔
Form	✔	✗

You can control some format processing parameters with the ClickHouse settings. For more information read the Settings section.

TabSeparated

See TabSeparated

TabSeparatedRaw

See TabSeparatedRaw

TabSeparatedWithNames

See TabSeparatedWithNames

TabSeparatedWithNamesAndTypes

See TabSeparatedWithNamesAndTypes

TabSeparatedRawWithNames

See TabSeparatedRawWithNames

TabSeparatedRawWithNamesAndTypes

See TabSeparatedRawWithNamesAndTypes

Template

See Template

TemplateIgnoreSpaces

See TemplateIgnoreSpaces

TSKV

See TSKV

CSV

See CSV

CSVWithNames

See CSVWithNames

CSVWithNamesAndTypes

See CSVWithNamesAndTypes

CustomSeparated

See CustomSeparated

CustomSeparatedWithNames

See CustomSeparatedWithNames

CustomSeparatedWithNamesAndTypes

See CustomSeparatedWithNamesAndTypes

SQLInsert

See SQLInsert

JSON

See JSON

JSONStrings

See JSONStrings

JSONColumns

See JSONColumns

JSONColumnsWithMetadata

See JSONColumnsWithMetadata

JSONAsString

See JSONAsString

JSONAsObject

See JSONAsObject

JSONCompact

See JSONCompact

JSONCompactStrings

See JSONCompactStrings

JSONCompactColumns

See JSONCompactColumns

JSONEachRow

See JSONEachRow

PrettyJSONEachRow

See PrettyJSONEachRow

JSONStringsEachRow

See JSONStringsEachRow

JSONCompactEachRow

See JSONCompactEachRow

JSONCompactStringsEachRow

See JSONCompactStringsEachRow

JSONEachRowWithProgress

See JSONEachRowWithProgress

JSONStringsEachRowWithProgress

See JSONStringsEachRowWithProgress

JSONCompactEachRowWithNames

See JSONCompactEachRowWithNames

JSONCompactEachRowWithNamesAndTypes

See JSONCompactEachRowWithNamesAndTypes

JSONCompactStringsEachRowWithNames

See JSONCompactStringsEachRowWithNames

JSONCompactStringsEachRowWithNamesAndTypes

See JSONCompactStringsEachRowWithNamesAndTypes

JSONObjectEachRow

See JSONObjectEachRow

JSON Formats Settings

See JSON Format Settings

BSONEachRow

See BSONEachRow

Native

See Native

Null

See Null

Pretty

See Pretty

PrettyNoEscapes

See PrettyNoEscapes

PrettyMonoBlock

See PrettyMonoBlock

PrettyNoEscapesMonoBlock

See PrettyNoEscapesMonoBlock

PrettyCompact

See PrettyCompact

PrettyCompactNoEscapes

See PrettyCompactNoEscapes

PrettyCompactMonoBlock

See PrettyCompactMonoBlock

PrettyCompactNoEscapesMonoBlock

See PrettyCompactNoEscapesMonoBlock

PrettySpace

See PrettySpace

PrettySpaceNoEscapes

See PrettySpaceNoEscapes

PrettySpaceMonoBlock

See PrettySpaceMonoBlock

PrettySpaceNoEscapesMonoBlock

See PrettySpaceNoEscapesMonoBlock

RowBinary

See RowBinary

RowBinaryWithNames

See RowBinaryWithNames

RowBinaryWithNamesAndTypes

See RowBinaryWithNamesAndTypes

RowBinaryWithDefaults

See RowBinaryWithDefaults

Values

See Values

Vertical

See Vertical

XML

See XML

CapnProto

See CapnProto

Prometheus

See Prometheus

Protobuf

See Protobuf

ProtobufSingle

See ProtobufSingle

ProtobufList

See ProtobufList

Avro

See Avro

AvroConfluent

See AvroConfluent

Parquet

See Parquet

ParquetMetadata

See ParquetMetadata

Arrow

See Arrow

ArrowStream

See ArrowStream

ORC

See ORC

One

See One

Npy

See Npy

LineAsString

See LineAsString

See also: LineAsStringWithNames, LineAsStringWithNamesAndTypes

Regexp

See Regexp

Format Schema

The file name containing the format schema is set by the setting format_schema. It’s required to set this setting when it is used one of the formats Cap'n Proto and Protobuf. The format schema is a combination of a file name and the name of a message type in this file, delimited by a colon, e.g. schemafile.proto:MessageType. If the file has the standard extension for the format (for example, .proto for Protobuf), it can be omitted and in this case, the format schema looks like schemafile:MessageType.

If you input or output data via the client in interactive mode, the file name specified in the format schema can contain an absolute path or a path relative to the current directory on the client. If you use the client in the batch mode, the path to the schema must be relative due to security reasons.

If you input or output data via the HTTP interface the file name specified in the format schema should be located in the directory specified in format_schema_path in the server configuration.

Skipping Errors

Some formats such as CSV, TabSeparated, TSKV, JSONEachRow, Template, CustomSeparated and Protobuf can skip broken row if parsing error occurred and continue parsing from the beginning of next row. See input_format_allow_errors_num and input_format_allow_errors_ratio settings. Limitations:

• In case of parsing error JSONEachRow skips all data until the new line (or EOF), so rows must be delimited by \n to count errors correctly.
• Template and CustomSeparated use delimiter after the last column and delimiter between rows to find the beginning of next row, so skipping errors works only if at least one of them is not empty.

RawBLOB

See RawBLOB

Markdown

See Markdown

MsgPack

See MsgPack

MySQLDump

See MySQLDump

DWARF

See Dwarf

Form

See Form