Skip to main content
Skip to main content
Edit this page

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:

FormatInputOutput
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✔✔
JSONCompactEachRowWithProgress✗✔
JSONCompactStringsEachRow✔✔
JSONCompactStringsEachRowWithNames✔✔
JSONCompactStringsEachRowWithNamesAndTypes✔✔
JSONCompactStringsEachRowWithProgress✗✔
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

JSONCompactEachRowWithProgress​

Similar to JSONEachRowWithProgress but outputs row events in a compact form, like in the JSONCompactEachRow format.

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