Проекции

Елена Дворядкина

Обзор

Проекция (projection) — дополнительная скрытая таблица, которая хранит данные исходной таблицы в преобразованном виде, оптимальном для выполнения какого-либо типа запросов. Проекция может хранить строки данных отсортированными в отличном от исходной таблицы порядке (и, следовательно, иметь другой первичный индекс) или вычислять агрегированные значения по столбцам исходной таблицы. Таким образом, эта функциональность может быть полезна для оптимизации:

  • запросов с фильтрацией по столбцам исходной таблицы, которые не являются частью ее первичного ключа;

  • запросов, агрегирующих значения столбцов исходной таблицы (предварительно выполняемая проекциями агрегация данных может снизить потребление как вычислительных ресурсов, так и ресурсов I/O).

Проекции поддерживаются только таблицами семейства MergeTree (включая Replicated*). Для таблицы можно определить одну или несколько проекций — все они будут автоматически синхронизироваться с исходной таблицей, то есть обновляться при каждой вставке новых данных в исходную таблицу.

Проекции похожи на материализованные представления (которые также позволяют предварительно вычислять агрегаты и использовать иные условия сортировки для данных другой таблицы), но определяются на уровне кусков данных — для каждого куска данных создается проекция данных в нем на основе запроса проекции. Это гарантирует согласованность данных проекций с данными исходной таблицы и позволяет ADQM автоматически выбирать и использовать оптимальные проекции для выполнения пользовательских запросов (проекция считается оптимальной, если она возвращает соответствующий запросу результат, требуя при этом чтения наименьшего объема данных).

В отличие от материализованных представлений проекции не требуют создания вручную отдельных дополнительных таблиц с различными структурами, о которых нужно помнить и каждый раз при формулировке запроса самостоятельно выбирать в какую таблицу его направить, чтобы обеспечить высокую скорость его выполнения. С использованием проекций достаточно направлять все запросы в исходную таблицу, при этом ADQM будет автоматически определять есть ли у таблицы проекция, которая может ускорить выполнение запроса, и если такая проекция есть, перенаправит запрос в нее — то есть пользователю ничего не надо знать о проекциях.

ВНИМАНИЕ

Так как каждая проекция создает новую скрытую таблицу (файлы которой хранятся в подкаталогах <projection_name>.proj внутри каталогов кусков данных исходной таблицы MergeTree), может потребоваться больше ресурсов I/O и места на диске. Например, если проекция создается для изменения ключа сортировки, в нее продублируются все данные из исходной таблицы.

Управление проекциями

Создание/удаление проекций

Проекцию можно указать при создании таблицы MergeTree в секции описания столбцов с помощью выражения PROJECTION. Синтаксис запроса для создания таблицы с проекцией в общем виде:

CREATE TABLE <table_name>
(   <column1> ...,
    <column2> ...,
    ...
    PROJECTION <projection_name> (SELECT <column_list> [ORDER BY <sorting_key>] [GROUP BY <grouping_key>])
)
ENGINE = MergeTree()
...;

После имени проекции в скобках указывается запрос проекции, который определяет как данные исходной таблицы будут преобразовываться для сохранения в таблице проекции. Запрос проекции может включать одно из выражений:

  • ORDER BY — ключ, на основе которого будут сортироваться строки данных и устанавливаться первичный ключ в проекции;

  • GROUP BY — ключ, по которому данные исходной таблицы будут группироваться для вычисления агрегаций в проекции. В этом случае для скрытой таблицы проекции используется движок AggregatingMergeTree, а все агрегатные функции преобразуются в AggregateFunction.

Управлять проекциями существующих таблиц можно с помощью приведенных ниже запросов ALTER TABLE.

  • Добавление проекции в существующую таблицу:

    ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    ADD PROJECTION [IF NOT EXISTS] <projection_name> (SELECT <column_list> [ORDER BY <sorting_key>] [GROUP BY <grouping_key>]);

    Этот запрос добавляет описание проекции в метаданные таблицы MergeTree, но не заполняет проекцию данными (она будет перестраиваться только при вставке новых данных в исходную таблицу или при материализации).

  • Материализация проекции, то есть заполнение проекции текущими данными исходной таблицы (можно выполнить для отдельной партиции):

    ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    MATERIALIZE PROJECTION [IF EXISTS] <projection_name> [IN PARTITION <partition_name>];

  • Удаление файлов проекции с диска без удаления ее описания из метаданных исходной таблицы (можно выполнить для отдельной партиции):

    ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>]
    CLEAR PROJECTION [IF EXISTS] <projection_name> [IN PARTITION <partition_name>];

  • Удаление описания проекции из метаданных исходной таблицы и удаление файлов проекции с диска:

    ALTER TABLE [<db_name.]<table_name> [ON CLUSTER <cluster_name>] DROP PROJECTION [IF EXISTS] <partition_name>;

Команды ADD PROJECTION, CLEAR PROJECTION и DROP PROJECTION являются легковесными, поскольку они только меняют метаданные или удаляют файлы.

Настройки для применения проекций

Чтобы указать ADQM нужно ли использовать проекции при выполнении запросов, используйте следующие параметры (настраиваются на уровне сессии):

  • optimize_use_projections — включает/отключает возможность автоматического использования проекций при выполнении запросов SELECT (значение по умолчанию — 1, то есть проекции могут быть использованы);

  • force_optimize_projection — включает/отключает обязательное использование проекций при выполнении запросов SELECT, если поддержка проекций включена настройкой optimize_use_projections (значение по умолчанию — 0).

Информация о проекциях

Информацию о проекциях можно получить из системных таблиц:

  • system.projection_parts — информация о существующих на данный момент кусках данных проекций всех таблиц MergeTree в кластере (информация о соответствующих кусках данных исходной таблицы содержится в столбцах с префиксом parent_);

  • system.projection_parts_columns — информация о столбцах всех существующих на данный момент кусков данных проекций таблиц MergeTree.

Так как таблицы проекций являются скрытыми, они не включаются в список SHOW TABLES.

Примеры

Создайте таблицу MergeTree, в которой будут храниться данные о просмотрах страниц (в примерах ниже для этой таблицы будут определены проекции):

CREATE TABLE page_views (time DateTime, page_name String, time_on_page UInt64) ENGINE = MergeTree ORDER BY time;

Заполните таблицу тестовыми данными. Например, сгенерируйте случайным образом 1 миллион просмотров для 4 страниц приблизительно за последние 3 года относительного текущего момента:

INSERT INTO page_views SELECT
    now() - toIntervalMinute(rand() % 500000 + number),
    ['landing', 'products', 'docs', 'about'][toInt32(randBinomial(4, 0.01)) + 1],
    rand() % 15
FROM numbers(1000000);

Фильтрация без использования первичного ключа

Если выполнить запрос с фильтрацией по столбцу time, который входит в первичный ключ таблицы page_views, полное сканирование таблицы не требуется:

SELECT count() FROM page_views WHERE toYear(time) = '2022';
   ┌─count()─┐
1. │  388601 │
   └─────────┘

1 row in set. Elapsed: 0.004 sec. Processed 393.22 thousand rows, 1.57 MB (99.18 million rows/s., 396.72 MB/s.)

При фильтрации данных не по ключевому столбцу выполняется полное сканирование таблицы, например:

SELECT count() FROM page_views WHERE page_name = 'products';
   ┌─count()─┐
1. │   38729 │
   └─────────┘

1 row in set. Elapsed: 0.006 sec. Processed 1.00 million rows, 16.04 MB (156.85 million rows/s., 2.52 GB/s.)

Для оптимизации таких запросов в таблицу можно добавить проекцию с другим первичным индексом. Например, создайте проекцию page_name_projection, в которой строки сортируются по столбцу page_name:

ALTER TABLE page_views ADD PROJECTION page_name_projection (SELECT * ORDER BY page_name);

Выполните материализацию проекции, чтобы построить ее на основе текущих данных таблицы page_views:

ALTER TABLE page_views MATERIALIZE PROJECTION page_name_projection;

Повторите запрос с фильтрацией по столбцу page_name:

SELECT count() FROM page_views WHERE page_name = 'products';

Теперь для выполнения запроса считывается 41.54 тысяч строк вместо 1 миллиона:

   ┌─count()─┐
1. │   38729 │
   └─────────┘

1 row in set. Elapsed: 0.003 sec. Processed 41.54 thousand rows, 703.30 KB (12.48 million rows/s., 211.24 MB/s.)

Используйте выражение EXPLAIN, чтобы убедиться, что для выполнения запроса ADQM использует проекцию (ReadFromMergeTree (page_name_projection)):

EXPLAIN SELECT count() FROM page_views WHERE page_name = 'products';
   ┌─explain────────────────────────────────────────┐
1. │ Expression ((Project names + Projection))      │
2. │   Aggregating                                  │
3. │     Filter                                     │
4. │       ReadFromMergeTree (page_name_projection) │
   └────────────────────────────────────────────────┘

Предварительное агрегирование значений

Выполните запрос, возвращающий количество просмотров и среднее время просмотра страниц по месяцам:

SELECT
    page_name,
    toStartOfMonth(time) AS month,
    count() AS page_view_count,
    round(avg(time_on_page), 2) AS avg_time_on_page
FROM page_views
GROUP BY page_name, month
ORDER BY page_name, month;

Для выполнения этого запроса считываются все строки таблицы page_views:

Query id: 303db76e-1e68-4454-91ea-a951c36d170b

     ┌─page_name─┬──────month─┬─page_view_count─┬─avg_time_on_page─┐
  1. │ about     │ 2022-03-01 │               1 │                9 │
  2. │ about     │ 2023-06-01 │               1 │                6 │

      ...

105. │ products  │ 2024-06-01 │             313 │             6.87 │
106. │ products  │ 2024-07-01 │             116 │             6.26 │
107. │ products  │ 2024-08-01 │               7 │                7 │
     └───────────┴────────────┴─────────────────┴──────────────────┘

107 rows in set. Elapsed: 0.018 sec. Processed 1.00 million rows, 28.04 MB (54.22 million rows/s., 1.52 GB/s.)

Создайте и материализуйте проекцию, вычисляющую количество просмотров и среднее время просмотра для каждой страницы в таблице page_views по месяцам:

ALTER TABLE page_views ADD PROJECTION page_views_monthly_projection
    (
        SELECT
            page_name,
            toStartOfMonth(time),
            count(),
            round(avg(time_on_page), 2)
        GROUP BY
            page_name,
            toStartOfMonth(time)
    );
ALTER TABLE page_views MATERIALIZE PROJECTION page_views_monthly_projection;

Повторите запрос:

SELECT
    page_name,
    toStartOfMonth(time) AS month,
    count() AS page_view_count,
    round(avg(time_on_page), 2) AS avg_time_on_page
FROM page_views
GROUP BY page_name, month
ORDER BY page_name, month;

В результате получены те же данные, но производительность стала лучше:

Query id: acb9a600-cbd7-44a4-aa61-ee5f8cdd941d

     ┌─page_name─┬──────month─┬─page_view_count─┬─avg_time_on_page─┐
  1. │ about     │ 2022-03-01 │               1 │                9 │
  2. │ about     │ 2023-06-01 │               1 │                6 │

      ...

105. │ products  │ 2024-06-01 │             313 │             6.87 │
106. │ products  │ 2024-07-01 │             116 │             6.26 │
107. │ products  │ 2024-08-01 │               7 │                7 │
     └───────────┴────────────┴─────────────────┴──────────────────┘

107 rows in set. Elapsed: 0.004 sec.

С помощью EXPLAIN убедитесь, что для выполнения этого запроса используется проекция:

EXPLAIN SELECT
    page_name,
    toStartOfMonth(time) AS month,
    count() AS page_view_count,
    round(avg(time_on_page), 2) AS avg_time_on_page
FROM page_views
GROUP BY page_name, month
ORDER BY page_name, month;
   ┌─explain────────────────────────────────────────────────────────────────────────┐
1. │ Expression ((Project names + (Before ORDER BY + Projection) [lifted up part])) │
2. │   Sorting (Sorting for ORDER BY)                                               │
3. │     Expression ((Before ORDER BY + Projection))                                │
4. │       Aggregating                                                              │
5. │         Expression                                                             │
6. │           ReadFromMergeTree (page_views_monthly_projection)                    │
   └────────────────────────────────────────────────────────────────────────────────┘

Также можно посмотреть лог-таблицу system.query_log, в которой поле projections покажет имя используемой проекции (или будет пустым, если ни одна проекция не использовалась):

SELECT query, formatReadableQuantity(read_rows) AS read_rows, projections FROM system.query_log
WHERE type='QueryFinish' AND query_id='acb9a600-cbd7-44a4-aa61-ee5f8cdd941d';\G
Row 1:
──────
query:       SELECT
                 page_name,
                 toStartOfMonth(time) AS month,
                 count() AS page_view_count,
                 round(avg(time_on_page), 2) AS avg_time_on_page
             FROM page_views
             GROUP BY page_name, month
             ORDER BY page_name, month;
read_rows:   107.00
projections: ['default.page_views.page_views_monthly_projection']

Проекция, агрегирующая значения исходной таблицы, используется не только для запросов, полностью совпадающих с запросом проекции, но и для запросов на выборку части соответствующих агрегатов с группировкой по некоторым столбцам из GROUP BY запроса проекции. Например:

EXPLAIN SELECT page_name, count() AS page_view_count FROM page_views GROUP BY page_name;
   ┌─explain─────────────────────────────────────────────────┐
1. │ Expression ((Project names + Projection))               │
2. │   Aggregating                                           │
3. │     Expression                                          │
4. │       ReadFromMergeTree (page_views_monthly_projection) │
   └─────────────────────────────────────────────────────────┘

Просмотр информации о проекциях

Получите информацию об активных кусках данных исходной таблицы из системной таблицы system.parts:

SELECT name, active, path FROM system.parts WHERE table='page_views';
   ┌─name────────┬─active─┬─path────────────────────────────────────────────────────────────────────────────┐
1. │ all_1_1_0_3 │      1 │ /var/lib/clickhouse/store/7f1/7f1aea44-35a3-4b96-a5c9-07640aec1340/all_1_1_0_3/ │
   └─────────────┴────────┴─────────────────────────────────────────────────────────────────────────────────┘

В каталоге куска данных таблицы page_views присутствуют подкаталоги ее проекций (page_name_projection.proj и page_views_monthly_projection.proj):

$ sudo ls /var/lib/clickhouse/store/7f1/7f1aea44-35a3-4b96-a5c9-07640aec1340/all_1_1_0_3/
checksums.txt  default_compression_codec.txt  page_name.cmrk2                     primary.cidx        time.cmrk2
columns.txt    metadata_version.txt           page_name_projection.proj           serialization.json  time_on_page.bin
count.txt      page_name.bin                  page_views_monthly_projection.proj  time.bin            time_on_page.cmrk2

В подкаталоге каждой проекции содержатся файлы данных столбцов (.bin), файлы засечек столбцов (.cmrk2) и файл первичного индекса (primary.cidx) скрытой таблицы проекции:

$ sudo ls /var/lib/clickhouse/store/7f1/7f1aea44-35a3-4b96-a5c9-07640aec1340/all_1_1_0_3/page_name_projection.proj
checksums.txt  default_compression_codec.txt  page_name.cmrk2     time.bin          time_on_page.cmrk2
columns.txt    metadata_version.txt           primary.cidx        time.cmrk2
count.txt      page_name.bin                  serialization.json  time_on_page.bin

В таблице system.projection_parts посмотрите сколько места на диске занимают существующие проекции:

SELECT
    name,
    parent_name,
    formatReadableSize(bytes_on_disk) AS bytes,
    formatReadableSize(parent_bytes_on_disk) AS parent_bytes,
    bytes_on_disk / parent_bytes_on_disk AS ratio
FROM system.projection_parts;
   ┌─name──────────────────────────┬─parent_name─┬─bytes────┬─parent_bytes─┬──────────────────ratio─┐
1. │ page_name_projection          │ all_1_1_0_3 │ 5.54 MiB │ 11.24 MiB    │    0.49312069529045727 │
2. │ page_views_monthly_projection │ all_1_1_0_3 │ 1.40 KiB │ 11.24 MiB    │ 0.00012159165770921657 │
   └───────────────────────────────┴─────────────┴──────────┴──────────────┴────────────────────────┘

Из таблицы system.projection_parts_columns получите типы столбцов проекций:

SELECT name as projection, column, type as column_type, column_position FROM system.projection_parts_columns;
   ┌─projection────────────────────┬─column───────────────┬─column_type────────────────────┬─column_position─┐
1. │ page_name_projection          │ time                 │ DateTime                       │               1 │
2. │ page_name_projection          │ page_name            │ String                         │               2 │
3. │ page_name_projection          │ time_on_page         │ UInt64                         │               3 │
4. │ page_views_monthly_projection │ page_name            │ String                         │               1 │
5. │ page_views_monthly_projection │ toStartOfMonth(time) │ Date                           │               2 │
6. │ page_views_monthly_projection │ count()              │ AggregateFunction(count)       │               3 │
7. │ page_views_monthly_projection │ avg(time_on_page)    │ AggregateFunction(avg, UInt64) │               4 │
   └───────────────────────────────┴──────────────────────┴────────────────────────────────┴─────────────────┘
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней