ClickHouse для аналитика: гайд по ENGINE и материализациям

Если ты работаешь с миллиардами строк и пишешь GROUP BY, который в PostgreSQL отвечает 40 секунд — пора посмотреть на ClickHouse. Он считает то же самое за 0.3 секунды. Не потому что магия — потому что колоночное хранение и векторизованное выполнение запросов.

Эта статья — практический гайд для аналитика: как понять что внутри, как выбрать ENGINE, что делать с партиционированием, какие функции реально нужны и какие ошибки убьют производительность.

Почему ClickHouse быстрый — column-oriented

Представь таблицу events: user_id, event_name, ts, country, revenue. В PostgreSQL она хранится построчно — на диске лежит {user_id, event_name, ts, country, revenue}, потом следующая строка, потом следующая.

В ClickHouse — поколоночно: сначала все user_id подряд, потом все event_name подряд, потом все ts. Когда ты пишешь SELECT SUM(revenue) FROM events WHERE country='RU', движок:

• Читает с диска ТОЛЬКО колонку country (1 байт на строку, не 200) — фильтрует
• Читает ТОЛЬКО revenue для оставшихся строк
• Никаких других колонок не трогает

Эффект: при чтении 5% колонок таблицы — в 20 раз меньше IO. Плюс данные сжимаются в 5-10 раз (одинаковые значения в одной колонке = LZ4/ZSTD)). Плюс векторизованное выполнение: SUM считается батчами по 64K строк через SIMD-инструкции CPU.

Итого: один и тот же SELECT country, SUM(revenue) FROM events GROUP BY country на 1 млрд строк:

ENGINE — главный выбор при создании таблицы

Это первое о чём спросит интервьюер. ENGINE определяет КАК данные хранятся и что с ними происходит при INSERT.

MergeTree — основа

Базовый и самый частый. Хранит данные отсортированными по ORDER BY (это и primary key одновременно). При INSERT создаёт новый part — фоновый процесс сливает их в более крупные.

CREATE TABLE events (
    ts DateTime,
    user_id UInt64,
    event_name LowCardinality(String),
    revenue Decimal(18, 2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (user_id, ts);

ReplacingMergeTree — дедупликация

Если INSERT'ишь строки которые могут дублироваться (например, retry из Kafka). При мерже оставляет только последнюю по версии.

ENGINE = ReplacingMergeTree(version_column)
ORDER BY (event_id);

AggregatingMergeTree — предагрегация

Хранит не сырые значения, а уже агрегированные состояния (state). Дальше *Merge функции достают финальное значение. Используется в материализованных представлениях.

CREATE TABLE daily_revenue_mv (
    day Date,
    country String,
    revenue_sum AggregateFunction(sum, Decimal(18,2)),
    users_uniq AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (day, country);

SummingMergeTree — суммирование одинаковых ключей

Упрощённая версия: при мерже строк с одинаковым ORDER BY суммирует все числовые колонки. Хорошо для счётчиков.

Партиционирование — ключ к скорости фильтрации

Правила:

• Партиций должно быть не больше 1000 на таблицу — иначе мерж задыхается
• Не партиционируй по user_id или event_name (миллионы партиций)
• Стандарт — по дню или месяцу для time-series

Топ-функции для аналитика

uniq, uniqExact, uniqHLL12 — приближённый count distinct

SELECT uniq(user_id) FROM events;        -- HyperLogLog, ошибка ~0.5%, очень быстро
SELECT uniqExact(user_id) FROM events;   -- точный, медленнее в 10-50 раз на больших данных

На дашбордах используй uniq — никто не заметит 0.5% разницы, но запрос отработает в 50 раз быстрее.

quantile — перцентили без сортировки

SELECT
    quantile(0.50)(latency_ms) AS p50,
    quantile(0.95)(latency_ms) AS p95,
    quantile(0.99)(latency_ms) AS p99
FROM api_calls;

В PostgreSQL для p99 нужна сортировка всех 1 млрд строк. В ClickHouse — приближённый алгоритм, ошибка <1%, секунды.

arrayJoin — раскрутка массивов

В ClickHouse часто хранят массивы внутри ячейки. arrayJoin разворачивает массив в строки.

SELECT arrayJoin(['a','b','c']) AS letter;
-- 3 строки: a, b, c

runningAccumulate — кумулятивная сумма

Эквивалент оконной SUM() OVER (ORDER BY ...), но в синтаксисе CH:

SELECT
    day,
    revenue,
    runningAccumulate(state) AS cumulative_revenue
FROM (
    SELECT day, sum(revenue) AS revenue, sumState(revenue) AS state
    FROM events GROUP BY day ORDER BY day
);

Материализованные представления — секрет ускорения дашбордов

MV в ClickHouse — это триггер на INSERT, который автоматически обновляет агрегированную таблицу. Дашборды читают из неё за миллисекунды вместо секунд.

CREATE MATERIALIZED VIEW daily_revenue_mv
TO daily_revenue
AS SELECT
    toDate(ts) AS day,
    country,
    sumState(revenue) AS revenue_sum,
    uniqState(user_id) AS users_uniq
FROM events
GROUP BY day, country;

-- Запрос с дашборда:
SELECT day, country, sumMerge(revenue_sum), uniqMerge(users_uniq)
FROM daily_revenue
WHERE day >= today() - 7
GROUP BY day, country;

Дашборд за 7 дней теперь читает 7×N строк (по числу стран) вместо миллионов raw events.

Типичные ошибки

JOIN огромных таблиц

ClickHouse не оптимизирован для JOIN — он строит hash-таблицу для правой таблицы целиком в памяти. JOIN двух таблиц по 1 млрд строк положит сервер.

Решение: денормализуй. Записывай нужные поля сразу в events (joined-at-write). Или используй Dictionary для маленьких справочников.

SELECT \*\ или много колонок

Колоночное хранение работает в плюс пока ты читаешь МАЛО колонок. SELECT * читает все — преимущество исчезает. Перечисляй явно нужные поля.

FINAL в продакшене

Удаление через DELETE

ClickHouse — append-only по природе. DELETE работает но медленно (фоновая мутация всех partition'ов). Если нужно удалять часто — используй ALTER TABLE ... DROP PARTITION (мгновенно).

UPDATE строк

То же что DELETE — мутация всех part'ов. Если нужны обновления — посмотри ReplacingMergeTree или CollapsingMergeTree.

Когда НЕ использовать ClickHouse

• OLTP: транзакции, ACID, частые UPDATE — это PostgreSQL/MySQL
• Меньше 100M строк: overhead не оправдан
• Сложные JOIN: лучше Snowflake/BigQuery
• Real-time inserts по 1 строке: ClickHouse любит батчи (1000+ строк за раз)

Идеальный кейс ClickHouse — events/logs/clickstream/metrics. Append-only, миллиарды строк, аналитические агрегации.

Связанные материалы

• SQL-тренажёр — 325 задач с автопроверкой кода, оконные функции, CTE, JOIN
• Оконные функции SQL: полный гайд — про OVER, PARTITION BY, frame clause
• Конспекты SQL — 210 модулей теории по SQL и аналитике
• Продуктовые метрики — 296 метрик с готовыми SQL-запросами
• AI-собеседование — мок-собес с разбором ответов

Открой SQL-тренажёр и попробуй переписать пару своих PostgreSQL-запросов в синтаксис ClickHouse — синтаксис очень похож, разница в нюансах ENGINE и партиционирования.