Data formats and compression algorithms

Below you will find the data formats and compression algorithms supported in Yandex Query.

Supported data formatsSupported data formats

Yandex Query Language supports the following data formats:

• csv_with_names
• tsv_with_names
• json_list
• json_each_row
• raw
• json_as_string
• parquet

Csv_with_namesCsv_with_names

This format is based on the CSV format. The data is arranged in comma-separated columns with the first row containing column names.

Sample data:

Year,Manufacturer,Model,Price
1997,Ford,E350,3000.00
1999,Chevy,"Venture «Extended Edition»",4900.00

SELECT
    *
FROM <connection>.<path>
WITH
(
    format=csv_with_names,
    SCHEMA
    (
        Year int,
        Manufacturer string,
        Model string,
        Price double
    )
)

Tsv_with_namesTsv_with_names

This format is based on the TSV format. The data is arranged in tab(0x9)-separated columns with the first row containing column names.

Sample data:

Year    Manufacturer    Model   Price
1997    Ford    E350    3000.00
1999    Chevy   "Venture «Extended Edition»"    4900.00

SELECT
    *
FROM <connection>.<path>
WITH
(
    format=tsv_with_names,
    SCHEMA
    (
        Year int,
        Manufacturer string,
        Model string,
        Price double
    )
)

Json_listJson_list

This format is based on the JSON format. With this format, each file must contain a valid JSON object.

Example of valid data presented as a list of JSON objects:

[
    { "Year": 1997, "Manufacturer": "Ford", "Model": "E350", "Price": 3000.0 },
    { "Year": 1999, "Manufacturer": "Chevy", "Model": "Venture «Extended Edition»", "Price": 4900.00 }
]

Example of invalid data: each line contains a separate JSON object, but these objects are not combined into a list:

{ "Year": 1997, "Manufacturer": "Ford", "Model": "E350", "Price": 3000.0 }
{ "Year": 1999, "Manufacturer": "Chevy", "Model": "Venture «Extended Edition»", "Price": 4900.00 }

Json_each_rowJson_each_row

This format is based on the JSON format. With this format, each individual line within each file must contain a valid JSON object, without wrapping them into a list. This format is used for data transmission over streaming systems, such as Yandex Data Streams.

Example of valid data: each line contains a separate JSON object, without wrapping them into a list:

{ "Year": 1997, "Manufacturer": "Ford", "Model": "E350", "Price": 3000.0 },
{ "Year": 1999, "Manufacturer": "Chevy", "Model": "Venture «Extended Edition»", "Price": 4900.00 }

SELECT
    *
FROM <connection>.<path>
WITH
(
    format=json_each_row,
    SCHEMA
    (
        Year int,
        Manufacturer string,
        Model string,
        Price double
    )
)

RawRaw

This format allows reading file contents in raw form. This data can then be split into rows and columns and processed via YQL.

Use this format when Yandex Query‘s built-in data parsing capabilities are insufficient.

SELECT
    *
FROM <connection>.<path>
WITH
(
    format=raw,
    SCHEMA
    (
        Data String
    )
)

Year,Manufacturer,Model,Price
1997,Ford,E350,3000.00
1999,Chevy,\"Venture «Extended Edition»\",4900.00

Json_as_stringJson_as_string

This format is based on the JSON format. Instead of splitting the input JSON document into fields, this format treats each line of the file either as a single JSON object or a single string. It is convenient when the list of fields is not fixed and may change across different messages.

With this format, each file must contain:

• Valid JSON object on each individual line of the file.
• Valid JSON objects wrapped in a list.

Example of valid data presented as a list of JSON objects:

{ "Year": 1997, "Manufacturer": "Ford", "Model": "E350", "Price": 3000.0 }
{ "Year": 1999, "Manufacturer": "Chevy", "Model": "Venture «Extended Edition»", "Price": 4900.00 }

SELECT
    *
FROM <connection>.<path>
WITH
(
    format=json_as_string,
    SCHEMA
    (
        Data Json
    )
)

ParquetParquet

This format allows you to read the contents of Apache Parquet files.

Data compression algorithms supported in Parquet files:

• No compression
• SNAPPY
• GZIP
• LZO
• BROTLI
• LZ4
• ZSTD
• LZ4_RAW

SELECT
    *
FROM <connection>.<path>
WITH
(
    format=parquet,
    SCHEMA
    (
        Year int,
        Manufacturer string,
        Model string,
        Price double
    )
)

Data reading exampleData reading example

Sample query for reading data from Yandex Object Storage.

SELECT
        *
FROM
    connection.`folder/filename.csv`
WITH(
    format='csv_with_names',
    SCHEMA
    (
        Year int,
        Manufacturer String,
        Model String,
        Price Double
    )
);

Where:

Supported compression algorithmsSupported compression algorithms

ReadsReads

Yandex Query supports the following compression algorithms for reading data:

While the parquet format supports built-in compression algorithms, Yandex Query also enables you to write parquet data using these:

Writing to Yandex Object StorageWriting to Yandex Object Storage

The service currently supports the following formats for writing data:

Query supports the following compression algorithms for writing data:

While the parquet format supports built-in compression algorithms, Query also enables you to write parquet data using these:

Writing to Yandex Data StreamsWriting to Yandex Data Streams

Data Streams only lets you write data as a byte stream, which is processed by the receiving side.

When writing to Data Streams, file format and compression algorithm settings are not applied.