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✔✔
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​

Apache Parquet is a columnar storage format widespread in the Hadoop ecosystem. ClickHouse supports read and write operations for this format.

Data Types Matching​

The table below shows supported data types and how they match ClickHouse data types in INSERT and SELECT queries.

Parquet data type (INSERT)ClickHouse data typeParquet data type (SELECT)
BOOLBoolBOOL
UINT8, BOOLUInt8UINT8
INT8Int8/Enum8INT8
UINT16UInt16UINT16
INT16Int16/Enum16INT16
UINT32UInt32UINT32
INT32Int32INT32
UINT64UInt64UINT64
INT64Int64INT64
FLOATFloat32FLOAT
DOUBLEFloat64DOUBLE
DATEDate32DATE
TIME (ms)DateTimeUINT32
TIMESTAMP, TIME (us, ns)DateTime64TIMESTAMP
STRING, BINARYStringBINARY
STRING, BINARY, FIXED_LENGTH_BYTE_ARRAYFixedStringFIXED_LENGTH_BYTE_ARRAY
DECIMALDecimalDECIMAL
LISTArrayLIST
STRUCTTupleSTRUCT
MAPMapMAP
UINT32IPv4UINT32
FIXED_LENGTH_BYTE_ARRAY, BINARYIPv6FIXED_LENGTH_BYTE_ARRAY
FIXED_LENGTH_BYTE_ARRAY, BINARYInt128/UInt128/Int256/UInt256FIXED_LENGTH_BYTE_ARRAY

Arrays can be nested and can have a value of the Nullable type as an argument. Tuple and Map types also can be nested.

Unsupported Parquet data types: FIXED_SIZE_BINARY, JSON, UUID, ENUM.

Data types of ClickHouse table columns can differ from the corresponding fields of the Parquet data inserted. When inserting data, ClickHouse interprets data types according to the table above and then cast the data to that data type which is set for the ClickHouse table column.

Inserting and Selecting Data​

You can insert Parquet data from a file into ClickHouse table by the following command:

$ cat {filename} | clickhouse-client --query="INSERT INTO {some_table} FORMAT Parquet"

You can select data from a ClickHouse table and save them into some file in the Parquet format by the following command:

$ clickhouse-client --query="SELECT * FROM {some_table} FORMAT Parquet" > {some_file.pq}

To exchange data with Hadoop, you can use HDFS table engine.

Parquet format settings​

ParquetMetadata​

Special format for reading Parquet file metadata (https://parquet.apache.org/docs/file-format/metadata/). It always outputs one row with the next structure/content:

  • num_columns - the number of columns
  • num_rows - the total number of rows
  • num_row_groups - the total number of row groups
  • format_version - parquet format version, always 1.0 or 2.6
  • total_uncompressed_size - total uncompressed bytes size of the data, calculated as the sum of total_byte_size from all row groups
  • total_compressed_size - total compressed bytes size of the data, calculated as the sum of total_compressed_size from all row groups
  • columns - the list of columns metadata with the next structure:
    • name - column name
    • path - column path (differs from name for nested column)
    • max_definition_level - maximum definition level
    • max_repetition_level - maximum repetition level
    • physical_type - column physical type
    • logical_type - column logical type
    • compression - compression used for this column
    • total_uncompressed_size - total uncompressed bytes size of the column, calculated as the sum of total_uncompressed_size of the column from all row groups
    • total_compressed_size - total compressed bytes size of the column, calculated as the sum of total_compressed_size of the column from all row groups
    • space_saved - percent of space saved by compression, calculated as (1 - total_compressed_size/total_uncompressed_size).
    • encodings - the list of encodings used for this column
  • row_groups - the list of row groups metadata with the next structure:
    • num_columns - the number of columns in the row group
    • num_rows - the number of rows in the row group
    • total_uncompressed_size - total uncompressed bytes size of the row group
    • total_compressed_size - total compressed bytes size of the row group
    • columns - the list of column chunks metadata with the next structure:
      • name - column name
      • path - column path
      • total_compressed_size - total compressed bytes size of the column
      • total_uncompressed_size - total uncompressed bytes size of the row group
      • have_statistics - boolean flag that indicates if column chunk metadata contains column statistics
      • statistics - column chunk statistics (all fields are NULL if have_statistics = false) with the next structure:
        • num_values - the number of non-null values in the column chunk
        • null_count - the number of NULL values in the column chunk
        • distinct_count - the number of distinct values in the column chunk
        • min - the minimum value of the column chunk
        • max - the maximum column of the column chunk

Example:

SELECT * FROM file(data.parquet, ParquetMetadata) format PrettyJSONEachRow
{
    "num_columns": "2",
    "num_rows": "100000",
    "num_row_groups": "2",
    "format_version": "2.6",
    "metadata_size": "577",
    "total_uncompressed_size": "282436",
    "total_compressed_size": "26633",
    "columns": [
        {
            "name": "number",
            "path": "number",
            "max_definition_level": "0",
            "max_repetition_level": "0",
            "physical_type": "INT32",
            "logical_type": "Int(bitWidth=16, isSigned=false)",
            "compression": "LZ4",
            "total_uncompressed_size": "133321",
            "total_compressed_size": "13293",
            "space_saved": "90.03%",
            "encodings": [
                "RLE_DICTIONARY",
                "PLAIN",
                "RLE"
            ]
        },
        {
            "name": "concat('Hello', toString(modulo(number, 1000)))",
            "path": "concat('Hello', toString(modulo(number, 1000)))",
            "max_definition_level": "0",
            "max_repetition_level": "0",
            "physical_type": "BYTE_ARRAY",
            "logical_type": "None",
            "compression": "LZ4",
            "total_uncompressed_size": "149115",
            "total_compressed_size": "13340",
            "space_saved": "91.05%",
            "encodings": [
                "RLE_DICTIONARY",
                "PLAIN",
                "RLE"
            ]
        }
    ],
    "row_groups": [
        {
            "num_columns": "2",
            "num_rows": "65409",
            "total_uncompressed_size": "179809",
            "total_compressed_size": "14163",
            "columns": [
                {
                    "name": "number",
                    "path": "number",
                    "total_compressed_size": "7070",
                    "total_uncompressed_size": "85956",
                    "have_statistics": true,
                    "statistics": {
                        "num_values": "65409",
                        "null_count": "0",
                        "distinct_count": null,
                        "min": "0",
                        "max": "999"
                    }
                },
                {
                    "name": "concat('Hello', toString(modulo(number, 1000)))",
                    "path": "concat('Hello', toString(modulo(number, 1000)))",
                    "total_compressed_size": "7093",
                    "total_uncompressed_size": "93853",
                    "have_statistics": true,
                    "statistics": {
                        "num_values": "65409",
                        "null_count": "0",
                        "distinct_count": null,
                        "min": "Hello0",
                        "max": "Hello999"
                    }
                }
            ]
        },
        ...
    ]
}

Arrow​

See Arrow

ArrowStream​

See ArrowStream

ORC​

Apache ORC is a columnar storage format widespread in the Hadoop ecosystem.

Data Types Matching​

The table below shows supported data types and how they match ClickHouse data types in INSERT and SELECT queries.

ORC data type (INSERT)ClickHouse data typeORC data type (SELECT)
BooleanUInt8Boolean
TinyintInt8/UInt8/Enum8Tinyint
SmallintInt16/UInt16/Enum16Smallint
IntInt32/UInt32Int
BigintInt64/UInt32Bigint
FloatFloat32Float
DoubleFloat64Double
DecimalDecimalDecimal
DateDate32Date
TimestampDateTime64Timestamp
String, Char, Varchar, BinaryStringBinary
ListArrayList
StructTupleStruct
MapMapMap
IntIPv4Int
BinaryIPv6Binary
BinaryInt128/UInt128/Int256/UInt256Binary
BinaryDecimal256Binary

Other types are not supported.

Arrays can be nested and can have a value of the Nullable type as an argument. Tuple and Map types also can be nested.

The data types of ClickHouse table columns do not have to match the corresponding ORC data fields. When inserting data, ClickHouse interprets data types according to the table above and then casts the data to the data type set for the ClickHouse table column.

Inserting Data​

You can insert ORC data from a file into ClickHouse table by the following command:

$ cat filename.orc | clickhouse-client --query="INSERT INTO some_table FORMAT ORC"

Selecting Data​

You can select data from a ClickHouse table and save them into some file in the ORC format by the following command:

$ clickhouse-client --query="SELECT * FROM {some_table} FORMAT ORC" > {filename.orc}

Arrow format settings​

To exchange data with Hadoop, you can use HDFS table engine.

One​

Special input format that doesn't read any data from file and returns only one row with column of type UInt8, name dummy and value 0 (like system.one table). Can be used with virtual columns _file/_path to list all files without reading actual data.

Example:

Query:

SELECT _file FROM file('path/to/files/data*', One);

Result:

┌─_file────┐
│ data.csv │
└──────────┘
┌─_file──────┐
│ data.jsonl │
└────────────┘
┌─_file────┐
│ data.tsv │
└──────────┘
┌─_file────────┐
│ data.parquet │
└──────────────┘

Npy​

This function is designed to load a NumPy array from a .npy file into ClickHouse. The NumPy file format is a binary format used for efficiently storing arrays of numerical data. During import, ClickHouse treats top level dimension as an array of rows with single column. Supported Npy data types and their corresponding type in ClickHouse:

Npy data type (INSERT)ClickHouse data typeNpy data type (SELECT)
i1Int8i1
i2Int16i2
i4Int32i4
i8Int64i8
u1, b1UInt8u1
u2UInt16u2
u4UInt32u4
u8UInt64u8
f2, f4Float32f4
f8Float64f8
S, UStringS
FixedStringS

Example of saving an array in .npy format using Python

import numpy as np
arr = np.array([[[1],[2],[3]],[[4],[5],[6]]])
np.save('example_array.npy', arr)

Example of reading a NumPy file in ClickHouse

Query:

SELECT *
FROM file('example_array.npy', Npy)

Result:

┌─array─────────┐
│ [[1],[2],[3]] │
│ [[4],[5],[6]] │
└───────────────┘

Selecting Data

You can select data from a ClickHouse table and save them into some file in the Npy format by the following command:

$ clickhouse-client --query="SELECT {column} FROM {some_table} FORMAT Npy" > {filename.npy}

LineAsString​

In this format, every line of input data is interpreted as a single string value. This format can only be parsed for table with a single field of type String. The remaining columns must be set to DEFAULT or MATERIALIZED, or omitted.

Example

Query:

DROP TABLE IF EXISTS line_as_string;
CREATE TABLE line_as_string (field String) ENGINE = Memory;
INSERT INTO line_as_string FORMAT LineAsString "I love apple", "I love banana", "I love orange";
SELECT * FROM line_as_string;

Result:

┌─field─────────────────────────────────────────────┐
│ "I love apple", "I love banana", "I love orange"; │
└───────────────────────────────────────────────────┘

Regexp​

Each line of imported data is parsed according to the regular expression.

When working with the Regexp format, you can use the following settings:

  • format_regexp — String. Contains regular expression in the re2 format.

  • format_regexp_escaping_rule — String. The following escaping rules are supported:

    • CSV (similarly to CSV)
    • JSON (similarly to JSONEachRow)
    • Escaped (similarly to TSV)
    • Quoted (similarly to Values)
    • Raw (extracts subpatterns as a whole, no escaping rules, similarly to TSVRaw)

  • format_regexp_skip_unmatched — UInt8. Defines the need to throw an exception in case the format_regexp expression does not match the imported data. Can be set to 0 or 1.

Usage

The regular expression from format_regexp setting is applied to every line of imported data. The number of subpatterns in the regular expression must be equal to the number of columns in imported dataset.

Lines of the imported data must be separated by newline character '\n' or DOS-style newline "\r\n".

The content of every matched subpattern is parsed with the method of corresponding data type, according to format_regexp_escaping_rule setting.

If the regular expression does not match the line and format_regexp_skip_unmatched is set to 1, the line is silently skipped. Otherwise, exception is thrown.

Example

Consider the file data.tsv:

id: 1 array: [1,2,3] string: str1 date: 2020-01-01
id: 2 array: [1,2,3] string: str2 date: 2020-01-02
id: 3 array: [1,2,3] string: str3 date: 2020-01-03

and the table:

CREATE TABLE imp_regex_table (id UInt32, array Array(UInt32), string String, date Date) ENGINE = Memory;

Import command:

$ cat data.tsv | clickhouse-client  --query "INSERT INTO imp_regex_table SETTINGS format_regexp='id: (.+?) array: (.+?) string: (.+?) date: (.+?)', format_regexp_escaping_rule='Escaped', format_regexp_skip_unmatched=0 FORMAT Regexp;"

Query:

SELECT * FROM imp_regex_table;

Result:

┌─id─┬─array───┬─string─┬───────date─┐
│  1 │ [1,2,3] │ str1   │ 2020-01-01 │
│  2 │ [1,2,3] │ str2   │ 2020-01-02 │
│  3 │ [1,2,3] │ str3   │ 2020-01-03 │
└────┴─────────┴────────┴────────────┘

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​

In this format, all input data is read to a single value. It is possible to parse only a table with a single field of type String or similar. The result is output in binary format without delimiters and escaping. If more than one value is output, the format is ambiguous, and it will be impossible to read the data back.

Below is a comparison of the formats RawBLOB and TabSeparatedRaw.

RawBLOB:

  • data is output in binary format, no escaping;
  • there are no delimiters between values;
  • no newline at the end of each value.

TabSeparatedRaw:

  • data is output without escaping;
  • the rows contain values separated by tabs;
  • there is a line feed after the last value in every row.

The following is a comparison of the RawBLOB and RowBinary formats.

RawBLOB:

  • String fields are output without being prefixed by length.

RowBinary:

When an empty data is passed to the RawBLOB input, ClickHouse throws an exception:

Code: 108. DB::Exception: No data to insert

Example

$ clickhouse-client --query "CREATE TABLE {some_table} (a String) ENGINE = Memory;"
$ cat {filename} | clickhouse-client --query="INSERT INTO {some_table} FORMAT RawBLOB"
$ clickhouse-client --query "SELECT * FROM {some_table} FORMAT RawBLOB" | md5sum

Result:

f9725a22f9191e064120d718e26862a9  -

MsgPack​

ClickHouse supports reading and writing MessagePack data files.

Data Types Matching​

MessagePack data type (INSERT)ClickHouse data typeMessagePack data type (SELECT)
uint N, positive fixintUIntNuint N
int N, negative fixintIntNint N
boolUInt8uint 8
fixstr, str 8, str 16, str 32, bin 8, bin 16, bin 32Stringbin 8, bin 16, bin 32
fixstr, str 8, str 16, str 32, bin 8, bin 16, bin 32FixedStringbin 8, bin 16, bin 32
float 32Float32float 32
float 64Float64float 64
uint 16Dateuint 16
int 32Date32int 32
uint 32DateTimeuint 32
uint 64DateTime64uint 64
fixarray, array 16, array 32Array/Tuplefixarray, array 16, array 32
fixmap, map 16, map 32Mapfixmap, map 16, map 32
uint 32IPv4uint 32
bin 8Stringbin 8
int 8Enum8int 8
bin 8(U)Int128/(U)Int256bin 8
int 32Decimal32int 32
int 64Decimal64int 64
bin 8Decimal128/Decimal256bin 8

Example:

Writing to a file ".msgpk":

$ clickhouse-client --query="CREATE TABLE msgpack (array Array(UInt8)) ENGINE = Memory;"
$ clickhouse-client --query="INSERT INTO msgpack VALUES ([0, 1, 2, 3, 42, 253, 254, 255]), ([255, 254, 253, 42, 3, 2, 1, 0])";
$ clickhouse-client --query="SELECT * FROM msgpack FORMAT MsgPack" > tmp_msgpack.msgpk;

MsgPack format settings​

MySQLDump​

ClickHouse supports reading MySQL dumps. It reads all data from INSERT queries belonging to one table in dump. If there are more than one table, by default it reads data from the first one. You can specify the name of the table from which to read data from using input_format_mysql_dump_table_name settings. If setting input_format_mysql_dump_map_columns is set to 1 and dump contains CREATE query for specified table or column names in INSERT query the columns from input data will be mapped to the columns from the table by their names, columns with unknown names will be skipped if setting input_format_skip_unknown_fields is set to 1. This format supports schema inference: if the dump contains CREATE query for the specified table, the structure is extracted from it, otherwise schema is inferred from the data of INSERT queries.

Examples:

File dump.sql:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test` (
  `x` int DEFAULT NULL,
  `y` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,NULL),(2,NULL),(3,NULL),(3,NULL),(4,NULL),(5,NULL),(6,7);
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test 3` (
  `y` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test 3` VALUES (1);
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test2` (
  `x` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test2` VALUES (1),(2),(3);

Queries:

DESCRIBE TABLE file(dump.sql, MySQLDump) SETTINGS input_format_mysql_dump_table_name = 'test2'
┌─name─┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ x    │ Nullable(Int32) │              │                    │         │                  │                │
└──────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
SELECT *
FROM file(dump.sql, MySQLDump)
         SETTINGS input_format_mysql_dump_table_name = 'test2'
┌─x─┐
│ 1 │
│ 2 │
│ 3 │
└───┘

DWARF​

Parses DWARF debug symbols from an ELF file (executable, library, or object file). Similar to dwarfdump, but much faster (hundreds of MB/s) and with SQL. Produces one row for each Debug Information Entry (DIE) in the .debug_info section. Includes "null" entries that the DWARF encoding uses to terminate lists of children in the tree.

Quick background: .debug_info consists of units, corresponding to compilation units. Each unit is a tree of DIEs, with a compile_unit DIE as its root. Each DIE has a tag and a list of attributes. Each attribute has a name and a value (and also a form, which specifies how the value is encoded). The DIEs represent things from the source code, and their tag tells what kind of thing it is. E.g. there are functions (tag = subprogram), classes/structs/enums (class_type/structure_type/enumeration_type), variables (variable), function arguments (formal_parameter). The tree structure mirrors the corresponding source code. E.g. a class_type DIE can contain subprogram DIEs representing methods of the class.

Outputs the following columns:

  • offset - position of the DIE in the .debug_info section
  • size - number of bytes in the encoded DIE (including attributes)
  • tag - type of the DIE; the conventional "DW_TAG_" prefix is omitted
  • unit_name - name of the compilation unit containing this DIE
  • unit_offset - position of the compilation unit containing this DIE in the .debug_info section
  • ancestor_tags - array of tags of the ancestors of the current DIE in the tree, in order from innermost to outermost
  • ancestor_offsets - offsets of ancestors, parallel to ancestor_tags
  • a few common attributes duplicated from the attributes array for convenience:
    • name
    • linkage_name - mangled fully-qualified name; typically only functions have it (but not all functions)
    • decl_file - name of the source code file where this entity was declared
    • decl_line - line number in the source code where this entity was declared
  • parallel arrays describing attributes:
    • attr_name - name of the attribute; the conventional "DW_AT_" prefix is omitted
    • attr_form - how the attribute is encoded and interpreted; the conventional DW_FORM_ prefix is omitted
    • attr_int - integer value of the attribute; 0 if the attribute doesn't have a numeric value
    • attr_str - string value of the attribute; empty if the attribute doesn't have a string value

Example: find compilation units that have the most function definitions (including template instantiations and functions from included header files):

SELECT
    unit_name,
    count() AS c
FROM file('programs/clickhouse', DWARF)
WHERE tag = 'subprogram' AND NOT has(attr_name, 'declaration')
GROUP BY unit_name
ORDER BY c DESC
LIMIT 3
┌─unit_name──────────────────────────────────────────────────┬─────c─┐
│ ./src/Core/Settings.cpp                                    │ 28939 │
│ ./src/AggregateFunctions/AggregateFunctionSumMap.cpp       │ 23327 │
│ ./src/AggregateFunctions/AggregateFunctionUniqCombined.cpp │ 22649 │
└────────────────────────────────────────────────────────────┴───────┘

3 rows in set. Elapsed: 1.487 sec. Processed 139.76 million rows, 1.12 GB (93.97 million rows/s., 752.77 MB/s.)
Peak memory usage: 271.92 MiB.

Markdown​

You can export results using Markdown format to generate output ready to be pasted into your .md files:

SELECT
    number,
    number * 2
FROM numbers(5)
FORMAT Markdown
| number | multiply(number, 2) |
|-:|-:|
| 0 | 0 |
| 1 | 2 |
| 2 | 4 |
| 3 | 6 |
| 4 | 8 |

Markdown table will be generated automatically and can be used on markdown-enabled platforms, like Github. This format is used only for output.

Form​

The Form format can be used to read or write a single record in the application/x-www-form-urlencoded format in which data is formatted key1=value1&key2=value2

Examples:

Given a file data.tmp placed in the user_files path with some URL encoded data:

t_page=116&c.e=ls7xfkpm&c.tti.m=raf&rt.start=navigation&rt.bmr=390%2C11%2C10
SELECT * FROM file(data.tmp, Form) FORMAT vertical;

Result:

Row 1:
──────
t_page:   116
c.e:      ls7xfkpm
c.tti.m:  raf
rt.start: navigation
rt.bmr:   390,11,10