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 CSV-based and keeps your data in comma-separated columns. The first line in the file contains the 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 TSV-based and keeps your data in tab-separated columns (the tab char code is 0x9). The first line in the file contains the 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 JSON-based. Each file must contain a list of objects in a valid JSON representation.

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, with objects not wrapped 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 JSON-based. Each line in the file must contain an object in a valid JSON representation. These objects are not wrapped into a JSON 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 you to read the contents of files without any conversion. 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 JSON-based. Instead of splitting the input JSON document into fields, it treats each line in the file as a single JSON object. Use this format when the list of fields 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.

Within Parquet files, the following compression algorithms are supported:

• 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

Query example 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

ReadingReading

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.