ClickHouse и колоночные СУБД: урок по Data Engineering

ClickHouse это колоночная аналитическая СУБД, родом из Яндекс.Метрики. В российских компаниях это самое частое хранилище для событий и дашбордов, поэтому на собесах дата-инженера о нём спрашивают почти везде.

MergeTree: как лежат данные

Основное семейство движков таблиц: MergeTree.

CREATE TABLE events (
  event_date Date,
  event_time DateTime,
  user_id UInt64,
  event_type LowCardinality(String),
  amount Decimal(12, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, user_id, event_time);

Каждая вставка создаёт новую неизменяемую часть (part): каталог с файлами колонок, отсортированными по ключу ORDER BY.
В фоне ClickHouse сливает (merge) мелкие части в крупные, как LSM-дерево.
PARTITION BY группирует части по значению выражения (обычно месяц или день). Партиции нужны для управления данными (удалить или перезалить месяц целиком, TTL), а не для скорости запросов. Слишком мелкое партиционирование создаёт тысячи частей и замедляет всё.

Сортировочный ключ и разреженный индекс

ORDER BY определяет физический порядок строк внутри части. Первичный ключ (по умолчанию равен ORDER BY) не уникален и разреженный: индекс хранит значение ключа для каждой гранулы, по умолчанию 8192 строки (index_granularity). При запросе ClickHouse по индексу находит диапазоны гранул, где могут быть нужные строки, и читает только их.

Отсюда правила выбора ключа:

В начало ключа колонки, по которым чаще всего фильтруют, обычно от низкой кардинальности к высокой: (event_type, user_id, event_time).
Фильтр по колонке не из начала ключа индекс использует плохо: условие только на event_time при ключе (event_type, user_id, event_time) почти не сужает чтение.
Сортировка также улучшает сжатие: похожие значения рядом.
Для дополнительных фильтров есть индексы пропуска данных (skip indexes: minmax, set, bloom_filter) и проекции.

Ключ уникальности ClickHouse не обеспечивает: вставить две строки с одинаковым ключом можно.

Вставки

ClickHouse любит крупные редкие вставки: тысячи-сотни тысяч строк за раз, порядка одной вставки в секунду на таблицу. Тысячи вставок по одной строке создают тысячи частей, слияния не успевают, и вставки начинают отклоняться с ошибкой о слишком большом числе частей.

Решения: буферизация на стороне приложения, async_insert (сервер копит мелкие вставки сам), чтение из Kafka встроенным движком таблицы Kafka плюс материализованное представление.

Обновления и удаления

Строки не обновляются на месте. ALTER TABLE ... UPDATE/DELETE это мутации: асинхронная перезапись затронутых частей, тяжёлая операция для редкого использования. Есть облегчённое удаление DELETE FROM (помечает строки), но для частых изменений используют специальные движки.

Специальные движки

ReplacingMergeTree(ver): при слиянии оставляет одну строку на значение ключа ORDER BY (с максимальной версией). Подходит для «последнего состояния» сущности. Важно: дедупликация происходит при слияниях, когда-нибудь, а не при вставке. В запросах до слияния дубли видны. Решения: SELECT ... FINAL (дороже), агрегация argMax(value, version) по ключу.
SummingMergeTree: при слиянии суммирует числовые колонки строк с одинаковым ключом.
AggregatingMergeTree: хранит промежуточные состояния агрегатных функций (uniqState, sumState), которые можно доагрегировать функциями -Merge. Используется с материализованными представлениями для предагрегатов.
ReplicatedMergeTree: репликация через ClickHouse Keeper (или ZooKeeper). Distributed: таблица-фасад над шардами.

Материализованные представления

В ClickHouse материализованное представление работает как триггер на вставку: каждый блок, вставленный в исходную таблицу, преобразуется запросом представления и записывается в целевую таблицу.

CREATE MATERIALIZED VIEW mv_daily TO daily_stats AS
SELECT event_date, event_type, count() AS cnt, uniqState(user_id) AS users
FROM events
GROUP BY event_date, event_type;

Следствия:

Представление видит только новые вставки, существующие данные в него не попадут без отдельной заливки.
Агрегат считается по каждому блоку вставки отдельно, поэтому целевая таблица должна уметь доагрегировать: SummingMergeTree или AggregatingMergeTree, а запросы к ней используют sum или -Merge с GROUP BY.

Джойны

ClickHouse умеет JOIN, но по умолчанию строит хеш-таблицу по правой таблице в памяти: правую сторону делают меньшей. Для справочников есть словари (dictionaries) с функциями dictGet. Часто данные заранее денормализуют в широкую таблицу.

Частые ошибки

Вставки по одной строке из каждого запроса приложения.
Партиционирование по дню на маленькой таблице или по user_id.
Ожидать уникальности по ORDER BY или мгновенной дедупликации в ReplacingMergeTree.
Материализованное представление с count() в целевой MergeTree без доагрегации.
Частые ALTER TABLE UPDATE вместо правильного движка.

Что спросят на собесе

Как устроен MergeTree: части, слияния, сортировочный ключ, гранулы.
Как выбрать ORDER BY и PARTITION BY.
Почему нельзя вставлять по одной строке.
ReplacingMergeTree, FINAL и argMax.
Как работают материализованные представления и AggregatingMergeTree.

ClickHouse: MergeTree и как его не сломать