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

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

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

PARTITION BY

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

Чтобы проиллюстрировать это, мы расширяем пример таблицы Что такое части таблиц, добавляя оператор 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 Playground.

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

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

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

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

Слияния по партициям

С включенным партиционированием ClickHouse только сливает части данных внутри, но не между партициями. Мы схематически изображаем это для нашего примера таблицы выше:

Как изображено на диаграмме выше, части, принадлежащие различным партициям, никогда не сливаются. Если выбран ключ партиции с высокой кардинальностью, то части, распространившиеся по тысячам партиций, никогда не будут кандидатами на слияние - превышая предварительно настроенные лимиты и вызывая dreaded Слишком много ^^частей^^ ошибку. Решить эту проблему просто: выберите разумный ключ партиционирования с кардинальностью менее 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 обрабатывает этот запрос, применяя последовательность методов обрезки, чтобы избежать оценки нерелевантных данных:

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

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

Мы можем наблюдать эти шаги обрезки данных, инспектируя физический план выполнения запроса для нашего примерного запроса выше через 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                                                                                   │
    └──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

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

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

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

... Elapsed: 0.006 sec. Processed 8.19 thousand rows, 57.34 KB (1.36 million rows/s., 9.49 MB/s.)
Peak memory usage: 2.73 MiB.

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

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

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

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

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

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 million
└───────────────┘

1 row in set. Elapsed: 0.090 sec. Processed 5.48 million rows, 27.95 MB (60.66 million rows/s., 309.51 MB/s.)
Peak memory usage: 163.44 MiB.

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

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

┌─highest_price─┐
│     594300000 │ -- 594.30 million
└───────────────┘

1 row in set. Elapsed: 0.012 sec. Processed 1.97 million rows, 9.87 MB (162.23 million rows/s., 811.17 MB/s.)
Peak memory usage: 62.02 MiB.

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

PARTITION BY​

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

Слияния по партициям​

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

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

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

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

Партиционирование в первую очередь является функцией управления данными​