ClickHouse: моделирование схемы, запросы, materialized views

← назад к разделу

Схема в ClickHouse проектируется от запросов, а не от сущностей. В PostgreSQL нормализуют домен и добавляют индексы под запросы; здесь наоборот — сначала список аналитических вопросов, под них ORDER BY, движок и набор предагрегатов. Ошибка в этих решениях не лечится «ещё одним индексом» — только пересозданием таблицы.

Выбор ORDER BY

Правило: в начало ORDER BY — колонки с низкой кардинальностью, по которым фильтруют всегда; дальше — по убыванию селективности; время — обычно последним.

CREATE TABLE order_events (
    event_time   DateTime,
    event_type   LowCardinality(String),
    region       LowCardinality(String),
    customer_id  UInt64,
    order_id     UUID,
    amount       Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (region, event_type, event_time);

Запросы «по региону за период», «по региону и типу события» лягут в префикс ключа и прочитают минимум гранул. Обратный порядок — ORDER BY (event_time, ...) — типичная ошибка: фильтр по типу события перестаёт отсекать гранулы, потому что внутри каждого временного диапазона лежат все типы вперемешку.

ORDER BY — это ещё и локальность для сжатия: одинаковые значения рядом — кодеки работают лучше. Таблица с удачным ключом и весит меньше, и читается быстрее.

Типы: на чём экономят гранулы

• LowCardinality(String) — словарное кодирование для колонок с сотнями-тысячами уникальных значений (статусы, типы событий, регионы, валюты). Меньше места, быстрее GROUP BY. Для миллионов уникальных значений — вредно.
• Decimal(18, 2) — деньги. Float64 для денег — та же ошибка, что и в любой СУБД.
• DateTime / DateTime64(3) — секундная точность против миллисекундной; DateTime64 вдвое тяжелее, брать по потребности.
• UInt8/16/32/64 — беззнаковые целые там, где PG предложил бы bigint на всё; размер колонки — это скорость её сканирования.
• Enum8('created' = 1, 'paid' = 2, ...) — жёстче и компактнее LowCardinality, но изменение множества значений = ALTER. Для закрытых наборов.
• Nullable(T) — отдельный файл-маска на каждую колонку и запрет на использование в ORDER BY-префиксе без оговорок. По умолчанию — не-Nullable с дефолтом (0, ''), Nullable — только когда «нет значения» семантически отличается от дефолта.
• Array(T), Map(K, V) — легальная денормализация: теги заказа, произвольные атрибуты события. Вместе с функциями arrayJoin, has, mapKeys закрывают большинство случаев «гибкой схемы».

Агрегатные запросы

Хлеб ClickHouse. Несколько идиом, которых нет в PG:

SELECT
    toStartOfMonth(event_time)            AS month,
    count()                               AS events,
    countIf(event_type = 'order_paid')    AS paid_orders,
    sumIf(amount, event_type = 'order_paid') AS revenue,
    uniq(customer_id)                     AS customers,
    quantile(0.95)(amount)                AS p95_check
FROM order_events
WHERE event_time >= '2026-01-01'
GROUP BY month
ORDER BY month;

• countIf / sumIf / avgIf — условные агрегаты вместо CASE WHEN внутри агрегата: читаются лучше, работают быстрее.
• uniq vs uniqExact — uniq считает приближённо (HyperLogLog, погрешность ~1%) и на порядки дешевле; uniqExact — честно и дорого. Для дашбордов почти всегда достаточно uniq.
• quantile / quantileExact — та же пара для перцентилей.
• argMax(col, ts) — значение col в строке с максимальным ts: «последний статус заказа» одним агрегатом, без оконных функций и self-join.

Materialized views: предагрегация

Materialized view в ClickHouse — не «снимок запроса», как в PG, а триггер на вставку: каждый INSERT в исходную таблицу прогоняется через запрос view и дописывается в целевую таблицу. Стандартная связка — с AggregatingMergeTree:

CREATE TABLE revenue_by_region_daily (
    day      Date,
    region   LowCardinality(String),
    revenue  AggregateFunction(sum, Decimal(18, 2)),
    orders   AggregateFunction(uniq, UUID)
)
ENGINE = AggregatingMergeTree
ORDER BY (region, day);

CREATE MATERIALIZED VIEW revenue_by_region_daily_mv
TO revenue_by_region_daily AS
SELECT
    toDate(event_time)        AS day,
    region,
    sumState(amount)          AS revenue,
    uniqState(order_id)       AS orders
FROM order_events
WHERE event_type = 'order_paid'
GROUP BY day, region;

Чтение — через -Merge-функции:

SELECT day, region, sumMerge(revenue) AS revenue, uniqMerge(orders) AS orders
FROM revenue_by_region_daily
GROUP BY day, region;

sumState/uniqState хранят промежуточное состояние агрегата, поэтому предагрегат по дням корректно доагрегируется в месяцы и годы — включая uniq, который из готовых чисел не складывается. Дашборд читает таблицу в тысячи строк вместо сырых миллиардов.

Два предостережения. MV срабатывает только на новые вставки — данные, вставленные до создания view, в агрегат не попадут (засыпку делают INSERT SELECT-ом). И MV разделяет судьбу вставки: ошибка в каскаде view может завалить INSERT в исходную таблицу — каскады глубже одного-двух уровней быстро становятся неотлаживаемыми.

ReplacingMergeTree: «обновляемые» данные

Когда в аналитику нужно класть снимки сущностей («текущее состояние заказа»), а не только события:

CREATE TABLE orders_latest (
    order_id    UUID,
    status      LowCardinality(String),
    amount      Decimal(18, 2),
    updated_at  DateTime64(3)
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY order_id;

Каждое изменение заказа — новая вставка той же order_id; при merge останется строка с максимальным updated_at. Но merge случается «когда-нибудь», поэтому честное чтение — одно из двух:

SELECT * FROM orders_latest FINAL WHERE status = 'paid';

SELECT order_id, argMax(status, updated_at) AS status, argMax(amount, updated_at) AS amount
FROM orders_latest
GROUP BY order_id;

FINAL сливает версии на лету — пишется коротко, на больших таблицах дорого. argMax-вариант многословнее, но предсказуемее по стоимости. Выбор по размеру таблицы и частоте запроса; в обоих случаях это сознательная плата за «обновляемость» в append-only мире.

JOIN и словари

JOIN в ClickHouse есть, но с особенностью: правая таблица целиком загружается в память. JOIN факт-таблицы со справочником регионов — норма; JOIN двух таблиц событий по миллиарду строк — OOM. Приёмы:

• Денормализация при записи — главный приём: название категории, регион, тариф кладутся в строку события на этапе пайплайна. Диск дешевле JOIN-ов.
• Словари (dictionary) — справочники, подгружаемые ClickHouse-ом из PG/файла/HTTP с обновлением по расписанию; обращение — функцией dictGet('regions_dict', 'name', region_id) без JOIN вовсе.
• Если JOIN неизбежен — маленькая таблица справа, фильтры до JOIN, не после.

Антипаттерны

Что почитать дальше

• Fundamentals — почему ORDER BY и parts работают именно так.
• Интеграция из Java/Spring — как наполнять эти таблицы из сервиса.
• Эксплуатация — TTL, репликация, мониторинг merges и мутаций.
• CQRS — ClickHouse-таблицы как read-model с собственной схемой.