Partition projection

Partitioning allows you to prompt Yandex Query which rules should be used for storing data in Yandex Object Storage.

Let's assume the data in Yandex Object Storage is stored using the following folder structure:

year=2021
    month=01
    month=02
    month=03
year=2022
    month=01

When running the query below, Yandex Query does the following:

1. Gets a full list of subfolders within '/'.
2. For each subfolder, tries to handle a subfolder name in "year=<DIGITS>" format.
3. For each subfolder, "year=<DIGITS>" will get a list of all subfolders formatted as "month=<DIGITS>".
4. Processes the data read.

SELECT
    *
FROM
    objectstorage.'/'
    WITH
    (
        Schema =
        (
            data String,
            year Int,
            month Int
        ),
        partitioned_by =
        (
            year,
            month
        )
    )
WHERE
    year=2021
    AND month=02

This means that when working with partitioned data, a full listing of the Yandex Object Storage contents is performed, which may take a long time in large buckets.

To optimize operations with large amounts of data, use extended partitioning. This mode does not involve Yandex Object Storage folder scanning. Instead, all paths are determined in advance and data is only accessed using them.

To enable extended partitioning, set the rules for operations via a special parameter called "projection". This parameter describes the rules for storing data in Yandex Object Storage folders.

SyntaxSyntax

Extended partitioning is called "partition projection" and set using the projection parameter.

Example of setting partition projection:

$projection =
@@
{
    "projection.enabled" : true,

    "projection.year.type" : "integer",
    "projection.year.min" : 2010,
    "projection.year.max" : 2022,
    "projection.year.interval" : 1,

    "projection.month.type" : "integer",
    "projection.month.min" : 1,
    "projection.month.max" : 12,
    "projection.month.interval" : 1,
    "projection.month.digits" : 2,

    "storage.location.template" : "${year}/${month}"
}
@@;

SELECT
    *
FROM
    <connection>.`/`
WITH
(
    schema=
    (
        data String,
        year Int,
        month Int
    ),
    partitioned_by=(year, month),
    projection=$projection
)

The example above indicates that there is data for each year and each month from 2010 to 2022 and that bucket data is stored in folders like 2022/12.

In general, partition projection setup is as follows:

$projection =
@@
{
    "projection.enabled" : <partition_projection_availability>,

    "projection.<field_1_name>.type" : "<type>",
    "projection.<field_1_name>...." : "<properties>",

    "projection.<field_2_name>.type" : "<type>",
    "projection.<field_2_name>...." : "<properties>",

    "storage.location.template" : ".../${<field_2_name>}/${<field_1_name>}/..."
}
@@;

SELECT
    *
FROM
    <connection>.<path>
WITH
(
    schema=(<fields>, <field_1>, <field_2>),
    partitioned_by=(<field_1_name>, <field_2_name>),
    projection=$projection
)

Description of fieldsDescription of fields

Field of the integer typeField of the integer type

Used for columns whose values can be represented as integers in the range from -2⁶³ to 2⁶³-1.

Field of the enum typeField of the enum type

Used for columns whose values can be represented as an enumeration.

Field of the date typeField of the date type

Used for columns whose values can be represented as a date.

Path templatesPath templates

Yandex Object Storage bucket data can be stored in folders with any name. Using the storage.location.template setting, you can specify the rules for naming folders with data.