Партиции таблиц

Что такое партиции таблиц в ClickHouse?

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

PARTITION BY

Партиционирование можно включить при создании таблицы с помощью конструкции PARTITION BY. В ней может быть указано SQL-выражение по любым столбцам, результат которого определяет, к какой партиции относится строка.

Чтобы продемонстрировать это, мы расширим пример таблицы из раздела What are table parts, добавив конструкцию PARTITION BY toStartOfMonth(date), которая группирует части данных таблицы по месяцам продаж недвижимости:

CREATE TABLE uk.uk_price_paid_simple_partitioned
(
    date Date,
    town LowCardinality(String),
    street LowCardinality(String),
    price UInt32
)
ENGINE = MergeTree
ORDER BY (town, street)
PARTITION BY toStartOfMonth(date);

Вы можете выполнить запрос к этой таблице в нашей песочнице ClickHouse SQL.

Структура на диске

Каждый раз, когда в таблицу вставляют набор строк, вместо создания (как минимум) одного единственного фрагмента данных, содержащего все вставленные строки (как описано здесь), ClickHouse создает новый фрагмент данных для каждого уникального значения ключа партиционирования среди вставленных строк:

Сервер ClickHouse сначала разбивает строки из примерной операции INSERT с 4 строками, показанной на схеме выше, по значению ключа партиционирования toStartOfMonth(date). Затем для каждой найденной партиции строки обрабатываются обычным образом, проходя несколько последовательных шагов (① Сортировка, ② Разделение на столбцы, ③ Сжатие, ④ Запись на диск).

Обратите внимание, что при включённом партиционировании ClickHouse автоматически создает индексы MinMax для каждого фрагмента данных. Это просто файлы для каждого столбца таблицы, используемого в выражении ключа партиционирования, содержащие минимальное и максимальное значения этого столбца внутри данного фрагмента данных.

Слияния по разделам

При включённом партиционировании ClickHouse сливает части данных только внутри разделов, но не между ними. Ниже это показано на примере нашей таблицы из предыдущего раздела:

Как показано на схеме выше, части, относящиеся к разным разделам, никогда не сливаются. Если выбран ключ партиционирования с высокой кардинальностью, части, распределённые по тысячам разделов, никогда не станут кандидатами на слияние — будут превышены предустановленные лимиты, что приведёт к печально известной ошибке Too many ^^parts^^. Решение этой проблемы простое: выберите разумный ключ партиционирования с кардинальностью в пределах 1000–10000.

Мониторинг партиций

Вы можете получить список всех уникальных партиций таблицы из нашего примера, используя виртуальный столбец _partition_value:

SELECT DISTINCT _partition_value AS partition
FROM uk.uk_price_paid_simple_partitioned
ORDER BY partition ASC;

Кроме того, ClickHouse отслеживает все парты и партиции всех таблиц в системной таблице system.parts, и следующий запрос возвращает для приведённой выше примерной таблицы список всех партиций, а также текущее количество активных партов и сумму строк в этих партах по каждой партиции:

SELECT
    partition,
    count() AS parts,
    sum(rows) AS rows
FROM system.parts
WHERE (database = 'uk') AND (`table` = 'uk_price_paid_simple_partitioned') AND active
GROUP BY partition
ORDER BY partition ASC;

Для чего используются разделы таблиц?

Управление данными

В ClickHouse партиционирование в первую очередь является механизмом управления данными. Логически организуя данные на основе выражения партиционирования, можно управлять каждой партицией независимо. Например, схема партиционирования в приведённой выше таблице позволяет реализовать сценарий, когда в основной таблице хранятся только данные за последние 12 месяцев, а более старые данные автоматически удаляются с помощью правила TTL (см. добавленную последнюю строку DDL-запроса):

CREATE TABLE uk.uk_price_paid_simple_partitioned
(
    date Date,
    town LowCardinality(String),
    street LowCardinality(String),
    price UInt32
)
ENGINE = MergeTree
PARTITION BY toStartOfMonth(date)
ORDER BY (town, street)
TTL date + INTERVAL 12 MONTH DELETE;

Поскольку таблица партиционирована по toStartOfMonth(date), целые партиции (наборы частей таблицы), удовлетворяющие условию TTL, будут удалены, что делает операцию очистки более эффективной — без необходимости перезаписывать части.

Аналогично, вместо удаления старых данных их можно автоматически и эффективно перемещать на более экономичный уровень хранилища:

CREATE TABLE uk.uk_price_paid_simple_partitioned
(
    date Date,
    town LowCardinality(String),
    street LowCardinality(String),
    price UInt32
)
ENGINE = MergeTree
PARTITION BY toStartOfMonth(date)
ORDER BY (town, street)
TTL date + INTERVAL 12 MONTH TO VOLUME 'slow_but_cheap';

Оптимизация запросов

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

SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple_partitioned
WHERE date >= '2020-12-01'
  AND date <= '2020-12-31'
  AND town = 'LONDON';

Запрос выполняется над приведённой выше примерной таблицей и вычисляет наивысшую цену среди всех проданных объектов недвижимости в Лондоне в декабре 2020 года, фильтруя по столбцу (date), используемому в ключе партиционирования таблицы, и по столбцу (town), используемому в первичном ключе таблицы (при этом date не является частью первичного ключа).

ClickHouse обрабатывает этот запрос, последовательно применяя методы отсечения данных, чтобы избежать обработки нерелевантных данных:

① Отсечение партиций (partition pruning): MinMax-индексы используются для игнорирования целых партиций (наборов частей), которые логически не могут удовлетворять фильтру запроса по столбцам, используемым в ключе партиционирования таблицы.

② Отсечение гранул (granule pruning): для оставшихся после шага ① частей данных их первичный индекс используется для игнорирования всех гранул (блоков строк), которые логически не могут удовлетворять фильтру запроса по столбцам, используемым в первичном ключе таблицы.

Мы можем наблюдать эти шаги отсечения данных, изучая физический план выполнения запроса для нашего примерного запроса выше с помощью оператора EXPLAIN:

EXPLAIN indexes = 1
SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple_partitioned
WHERE date >= '2020-12-01'
  AND date <= '2020-12-31'
  AND town = 'LONDON';

    ┌─explain──────────────────────────────────────────────────────────────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))                                                                    │
 2. │   Aggregating                                                                                                │
 3. │     Expression (Before GROUP BY)                                                                             │
 4. │       Expression                                                                                             │
 5. │         ReadFromMergeTree (uk.uk_price_paid_simple_partitioned)                                              │
 6. │         Indexes:                                                                                             │
 7. │           MinMax                                                                                             │
 8. │             Keys:                                                                                            │
 9. │               date                                                                                           │
10. │             Condition: and((date in (-Inf, 18627]), (date in [18597, +Inf)))                                 │
11. │             Parts: 1/436                                                                                     │
12. │             Granules: 11/3257                                                                                │
13. │           Partition                                                                                          │
14. │             Keys:                                                                                            │
15. │               toStartOfMonth(date)                                                                           │
16. │             Condition: and((toStartOfMonth(date) in (-Inf, 18597]), (toStartOfMonth(date) in [18597, +Inf))) │
17. │             Parts: 1/1                                                                                       │
18. │             Granules: 11/11                                                                                  │
19. │           PrimaryKey                                                                                         │
20. │             Keys:                                                                                            │
21. │               town                                                                                           │
22. │             Condition: (town in ['LONDON', 'LONDON'])                                                        │
23. │             Parts: 1/1                                                                                       │
24. │             Granules: 1/11                                                                                   │
    └──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Вывод выше показывает:

① Отсечение партиций (partition pruning): строки с 7 по 18 вывода EXPLAIN, показанного выше, показывают, что ClickHouse сначала использует MinMax-индекс поля date, чтобы найти 11 из 3257 существующих гранул (блоков строк), которые хранятся в 1 из 436 существующих активных частей данных (data parts) и содержат строки, удовлетворяющие date‑фильтру запроса.

② Отсечение гранул (granule pruning): строки с 19 по 24 вывода EXPLAIN, показанного выше, указывают, что ClickHouse затем использует первичный индекс (созданный по полю town) части данных, определённой на шаге ①, чтобы далее сократить число гранул (которые содержат строки, потенциально также удовлетворяющие town‑фильтру запроса) с 11 до 1. Это также отражено в выводе ClickHouse-client, который мы привели выше для выполненного запроса:

... Затрачено: 0.006 сек. Обработано 8.19 тыс. строк, 57.34 КБ (1.36 млн строк/с., 9.49 МБ/с.)
Пиковое потребление памяти: 2.73 МиБ.

Это означает, что ClickHouse просканировал и обработал одну гранулу (блок из 8192 строк) за 6 миллисекунд при вычислении результата запроса.

Партиционирование — в первую очередь механизм управления данными

Имейте в виду, что выполнение запроса по всем партициям обычно медленнее, чем выполнение того же запроса по непартиционированной таблице.

При партиционировании данные, как правило, распределяются по большему числу партиций и частей данных, что часто приводит к тому, что ClickHouse сканирует и обрабатывает больший объём данных.

Мы можем продемонстрировать это, запустив один и тот же запрос как для примерной таблицы из раздела What are table parts (без включённого партиционирования), так и для нашей текущей примерной таблицы выше (с включённым партиционированием). Обе таблицы содержат одни и те же данные и одинаковое количество строк:

SELECT
    table,
    sum(rows) AS rows
FROM system.parts
WHERE (database = 'uk') AND (table IN ['uk_price_paid_simple', 'uk_price_paid_simple_partitioned']) AND active
GROUP BY table;

Однако таблица с включёнными партициями имеет больше активных частей данных, потому что, как уже упоминалось выше, ClickHouse сливает части данных только внутри одной партиции, но не между партициями:

SELECT
    table,
    count() AS parts
FROM system.parts
WHERE (database = 'uk') AND (table IN ['uk_price_paid_simple', 'uk_price_paid_simple_partitioned']) AND active
GROUP BY table;

Как показано выше, секционированная таблица uk_price_paid_simple_partitioned имеет более 600 секций, то есть 600 306 активных частей данных. Тогда как для нашей несекционированной таблицы uk_price_paid_simple все исходные части данных могли быть объединены фоновыми слияниями в одну активную часть.

Если мы проверим физический план выполнения запроса с предложением EXPLAIN для нашего приведённого выше примера запроса без фильтра по секции, выполняемого над секционированной таблицей, мы видим в строках 19 и 20 приведённого ниже вывода, что ClickHouse определил 671 из 3257 существующих гранул (блоков строк), распределённых по 431 из 436 существующих активных частей данных, которые потенциально содержат строки, удовлетворяющие фильтру запроса, и, следовательно, будут просканированы и обработаны движком запросов:

EXPLAIN indexes = 1
SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple_partitioned
WHERE town = 'LONDON';

    ┌─explain─────────────────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))                       │
 2. │   Aggregating                                                   │
 3. │     Expression (Before GROUP BY)                                │
 4. │       Expression                                                │
 5. │         ReadFromMergeTree (uk.uk_price_paid_simple_partitioned) │
 6. │         Indexes:                                                │
 7. │           MinMax                                                │
 8. │             Condition: true                                     │
 9. │             Parts: 436/436                                      │
10. │             Granules: 3257/3257                                 │
11. │           Partition                                             │
12. │             Condition: true                                     │
13. │             Parts: 436/436                                      │
14. │             Granules: 3257/3257                                 │
15. │           PrimaryKey                                            │
16. │             Keys:                                               │
17. │               town                                              │
18. │             Condition: (town in ['LONDON', 'LONDON'])           │
19. │             Parts: 431/436                                      │
20. │             Granules: 671/3257                                  │
    └─────────────────────────────────────────────────────────────────┘

Физический план выполнения запроса для того же примерного запроса, выполняемого по таблице без секционирования, показывает в строках 11 и 12 приведённого ниже вывода, что ClickHouse определил 241 из 3083 существующих блоков строк в единственной активной части данных таблицы, которые потенциально содержат строки, удовлетворяющие фильтру запроса:

EXPLAIN indexes = 1
SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple
WHERE town = 'LONDON';

    ┌─explain───────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))             │
 2. │   Aggregating                                         │
 3. │     Expression (Before GROUP BY)                      │
 4. │       Expression                                      │
 5. │         ReadFromMergeTree (uk.uk_price_paid_simple)   │
 6. │         Indexes:                                      │
 7. │           PrimaryKey                                  │
 8. │             Keys:                                     │
 9. │               town                                    │
10. │             Condition: (town in ['LONDON', 'LONDON']) │
11. │             Parts: 1/1                                │
12. │             Granules: 241/3083                        │
    └───────────────────────────────────────────────────────┘

При выполнении запроса для секционированной версии таблицы ClickHouse сканирует и обрабатывает 671 блок строк (~ 5,5 миллиона строк) за 90 миллисекунд:

SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple_partitioned
WHERE town = 'LONDON';

┌─highest_price─┐
│     594300000 │ -- 594,30 млн
└───────────────┘

Получена 1 строка. Затрачено: 0,090 сек. Обработано 5,48 млн строк, 27,95 МБ (60,66 млн строк/сек., 309,51 МБ/сек.)
Пиковое использование памяти: 163,44 МиБ.

А при выполнении запроса по таблице без партиционирования ClickHouse сканирует и обрабатывает 241 блок (~ 2 миллиона строк) за 12 миллисекунд:

SELECT MAX(price) AS highest_price
FROM uk.uk_price_paid_simple
WHERE town = 'LONDON';

┌─highest_price─┐
│     594300000 │ -- 594,30 млн
└───────────────┘

Получена 1 строка. Затрачено: 0,012 сек. Обработано 1,97 млн строк, 9,87 МБ (162,23 млн строк/сек., 811,17 МБ/сек.)
Пиковое использование памяти: 62,02 МиБ.