Модуль 4. Проектирование дашбордов и запросов под ClickHouse

Вы уже имеете слой витрин (Модуль 2) и семантику/метрики (Модуль 1). Теперь цель — заставить отчёты летать, при этом цифры должны быть едиными и объяснимыми. Мы разберём:

типы дашбордов и требования по свежести/гранулярности;
шаблоны запросов и анти-шаблоны;
как проектировать rollup-слой (минуты/часы/дни) и когда его использовать;
как оптимизировать чтение из ClickHouse (партиции, ORDER BY, индексы, PREWHERE);
как устранять «медленные» плитки и расхождения;
кейсы: eCom-воронка, SaaS-NRR, веб-производительность (p95), финансы (сверки).

Тип дашборда → требования к данным

Тип	Цель	Окно данных	Свежесть (SLA)	Гранулярность	Что приготовить заранее
Оперативный (NRT)	Реакция «здесь и сейчас»	от 1 часа до 1–7 дней	1–5 минут	минута/час	минутные/часовые агрегаты, rolling-окна
Управленческий (ежедневный)	План/факт, тренды	недели/месяцы/кварталы	15–60 минут	день	дневные агрегаты, YoY/WoW
Аналитический (ad-hoc)	Исследования/срезы	месяцы-годы	час/сутки	день/накопительно	широкие витрины, недельные/месячные агрегаты

Правило: под каждый тип — свой слой чтения (VIEW/агрегаты). Не пытайтесь «одной» таблицей закрыть NRT и годовые срезы.

Из чего собирать дашборд (и как не запутаться)

Метрики — только из семантических VIEW (vw_*) с паспортами (Модуль 1).
Разрезы — те, что реально нужны (3–5 основных). Всё остальное — в отдельные отчёты/доп. страницы.
Дефолтные фильтры: всегда ставьте период (например, «последние 28 дней») и ключевой разрез (регион/магазин/продукт-линия).
Гранулярность: выбирается под SLA. Оператив — минута/час; управленческий — день.
Запреты: без SELECT *, без FINAL, без «весь год без фильтра» в NRT-дашбордах.

Быстрые и устойчивые запросы: 12 правил

Ниже — шаблоны «как писать запрос», каждый снабжён пояснением/риском.

Фильтр по партиции и ключу

SELECT day, shop_id, sum(net_sales) AS s
FROM vw_net_sales_daily
WHERE day BETWEEN today()-28 AND today()-1     -- партиция (месяц/день)
  AND shop_id IN (101, 205, 309)               -- префикс ORDER BY
GROUP BY day, shop_id
ORDER BY day;

Зачем: ClickHouse читает только нужные партиции и «узкие» ключевые блоки.
Риск: фильтр не соотвествует ORDER BY → лишнее чтение.
Фикс: в витрине проектируйте ORDER BY под реальные WHERE (Модуль 2).

Используйте PREWHERE для «узких» фильтров

SELECT *
FROM mart_sales_wide
PREWHERE day >= today()-14 AND day <= today()-1
WHERE shop_id = 101 AND category_id = 55;

Зачем: PREWHERE отработает раньше и сократит чтение колонок. CH часто сам «поднимет» условия в PREWHERE, но явное указание помогает на широких таблицах.
Риск: PREWHERE по низкоселективным полям почти не помогает.

«Лёгкие» колонны

SELECT day, shop_id, sum(net_sales)
-- (не тянем sku_name, customer_id и прочие «тяжёлые» колонки)

Зачем: колоночное хранение — читаем только нужные колонки.
Антипаттерн: SELECT * — всегда дороже и опаснее.

Агрегаты-состояния вместо «тяжёлых» метрик на лету

-- вместо COUNT(DISTINCT) и перцентилей на лету:
SELECT day,
       uniqCombinedMerge(users_state) AS dau,
       quantileTDigestMerge(0.95)(latency_state) AS p95
FROM agg_events_daily_state
GROUP BY day;
Зачем: состояние (…State) агрегируется ассоциативно и быстро; устойчиво к повторной заливке.

Риск: читать состояния напрямую без …Merge → увидите «бинарники».
Фикс: заворачивайте чтение в VIEW.

Проценты и доли: агрегируйте через числитель/знаменатель

SELECT day,
  sum(ok_calls) / NULLIF(sum(all_calls), 0) AS success_rate
FROM agg_calls_hourly
GROUP BY day;

Зачем: «среднее средних» искажает доли.
Риск: считать % «по группам» и потом усреднять — некорректно.

Оконные и скользящие

-- 28-дневный суммарный «роллинг»
SELECT day, shop_id,
  sum(net_sales) OVER (PARTITION BY shop_id ORDER BY day
                       ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) AS roll28
FROM vw_net_sales_daily
WHERE day >= today()-90;

Зачем: быстро считать rolling без самосоединений.
Риск: «дырявый» календарь → окно «сжимается».
Фикс: денсить матрицу дат × ключ (Модуль 1, §22.1).

Бакетирование времени без функций в WHERE

-- сначала сузили по дате (WHERE), потом бакет
SELECT toStartOfHour(event_time) AS hour, sum(bytes)
FROM events
WHERE event_time >= now()-INTERVAL 24 HOUR  -- важнее
GROUP BY hour
ORDER BY hour;

Зачем: любая функция в условии по времени мешает «пропустить» партиции.
Риск: WHERE toStartOfDay(event_time) = ... — дорого.

Статусы и домены — нормализованные

-- только канонические статусы
WHERE status_canon IN ('PAID','REFUND','CANCELLED')

Зачем: единые правила фильтрации (семантический слой).
Риск: «сырые» статусы приведут к расхождениям с коллегами.

Ограничения в BI по умолчанию

Период: «последние 28/90 дней».
Лимит строк (TopN/сортировка).
Разрешённые разрезы для NRT-плиток.
Зачем: защита от «случайно снял год поминутно».
Риск: взрывные запросы от одной ошибочной плитки.

JOIN — только по делу

Малые измерения → держать как словарь (dictionary) и дергать dictGet*() в запросе.
SCD/«на дату факта» → pre-join в витрине.
«Толстые» джойны в BI — избегать (вынести в витрину/VIEW).

LIMIT BY и Top-K

-- Top-10 товаров по магазину за день
SELECT day, shop_id, sku_id, sum(net_sales) s
FROM vw_sales_wide
WHERE day = today()-1
GROUP BY day, shop_id, sku_id
ORDER BY shop_id, s DESC
LIMIT 10 BY shop_id;

Зачем: быстро и без временных таблиц получить Top-N в группах.

Никакого FINAL в продуктивных дашбордах

FINAL заставляет читать/сливать версии на лету → это планомерно «убивает» SLA.
Фикс: обеспечьте консистентность на записи (Replacing с версией, Aggregating-состояния, ретро-окно пересчёта ночью).

Rollup-слой (минуты/часы/дни): когда он нужен и как его делать

Когда нужен

Метрики переиспользуются в десятках плиток (DAU, p95, NetSalesDay).
Оперативные дашборды с требованием < 5–10 сек на запрос.
Периодические сравнения (WoW/MoM/YoY) и rolling-окна.

Как устроить (шаблон)

Сырые факты (*_wide).
Агрегаты-состояния (минутные/часовые/дневные): sumState/uniq*State/quantile*State.
VIEW со сборкой (…Merge) и презентационной логикой (календарь, валюты, %).

Пример (часовой KPI сети):

CREATE TABLE agg_cell_hour_state
(
  hour DateTime,
  cell_id UInt64,
  ok_state   AggregateFunction(sum, UInt64),
  all_state  AggregateFunction(sum, UInt64),
  p95_state  AggregateFunction(quantileTDigest(0.95), Float64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMMDD(hour)
ORDER BY (hour, cell_id);
-- чтение
CREATE OR REPLACE VIEW vw_cell_hour AS
SELECT hour, cell_id,
       sumMerge(ok_state)  AS ok,
       sumMerge(all_state) AS all_calls,
       quantileTDigestMerge(0.95)(p95_state) AS p95_latency,
       ok / NULLIF(all_calls, 0) AS success_rate
FROM agg_cell_hour_state
GROUP BY hour, cell_id;

Риски: двойной учёт событий при повторной заливке.
Митигация: …State (идемпотентно), чёткое зерно и ключи.

«Лечим» медленные плитки: маршруты диагностики и фиксы

Действуем по чек-листу

Сузить период (в BI дефолт «последние 28/90 дней»).
Проверить, что запрос соотносится с ORDER BY/партицией.
Убрать SELECT *, оставить нужные колонки.
Поменять COUNT(DISTINCT)/перцентили на чтение готовых состояний.
Убрать «толстые» JOIN — сделать pre-join/VIEW.
Для Top-N — применить LIMIT BY и «топ-разрез».
На витрине — добавить skip-индекс (bloom/set), если фильтр по длинным строкам/IN-спискам.
Если NRT-плитка — перенести расчёт в hour/minute rollup.

Где смотреть факты

system.query_log: read_rows, read_bytes, query_duration_ms — топ-10 тяжёлых запросов; какие поля чаще в WHERE.
system.parts/system.part_log: нет ли part-explosion (слишком много мелких частей).
Доля запросов с FINAL. Если >0 — это почти всегда корень проблемы.

Частые кейсы: как спроектировать плитки и запросы

eCom-воронка (оперативный)

Плитки: Sessions → AddToCart → Checkout → Purchase; CR, AOV; top-каналы; p95 API.
Что готовим:

agg_events_minute_state: uniqCombinedState(user_id), sumState(order_amount), quantileTDigestState(0.95)(latency).
VIEW: vw_funnel_minute с …Merge, rolling-окна (60/180 минут), дефолтный период 24 часа.
Риски: бурсты (части), «сырой» канал.
Митигации: микробатчи ingress, маппинг каналов (канонические), алерты parts/merges.

SaaS-NRR (управленческий)

Плитки: MRR start/end, Expansion/Churn/Contraction, NRR, когорты логинов.
Что готовим:

subs_snapshot (дневные/месячные снимки), vw_mrr_month, vw_nrr.
Для когорт — vw_first_activation, vw_activity_with_cohort.
Риски: смешать one-time и recurring; TZ календаря.
Митигации: чётко разделить, календарь и TZ зафиксировать в паспорте.

Веб-производительность (p95/p99)

Плитки: p95 по endpoint/регион/релиз, error rate, throughput.
Что готовим:

agg_perf_minute_state: quantileTDigestState(0.95/0.99), sumState(errors), sumState(total).
VIEW: vw_perf_hour (rollup к часу), Top-N эндпоинтов LIMIT BY.
Риски: перцентили на лету; редкие «длинные хвосты».
Митигации: состояния, верхние капы и медианы для sanity-check.

Финансы: сверка поступлений

Плитки: NetSales vs CORE, отклонения, дюжины/категории.
Что готовим:

vw_net_sales_daily + nightly тест «баланс с CORE», плитка «расхождение (в %)».
Риски: смешанные курсы, возвраты задним числом.
Митигации: две вьюхи (на дату операции / на дату отчёта), ретро-окно 14–30 дней.

Индексы и «физика» под BI (точечно)

Data-skipping индексы

minmax — по умолчанию на сортировочных полях;
bloom_filter — поиск по LIKE/IN для длинных строк/идентификаторов;
set — компактные домены.

Пример:

ALTER TABLE mart_sales_wide
ADD INDEX idx_sku_bloom sku_id TYPE bloom_filter GRANULARITY 4;

Риск: индекс без селективности только занимает место.
Критерий: реальная выгода на ваших запросах (замер до/после).

Проекции (projections)

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

Безопасность и «чистота» семантики в BI

BI ходит только к vw_* — там маскирование PII и RLS (если нужно).
Вьюхи разделяйте по календарю/валюте — не смешивайте логики.
В паспорте метрики укажите лимиты и дефолтные фильтры (BI-гвардейлы).
Линтер SQL (в CI) на запреты: FINAL, SELECT *, «без WHERE по дате» для NRT-источников.

Риски проектирования дашбордов (и как их гасить)

Риск	Проявление	Профилактика/фикс
Смешанные календари/валюты	«Неделя к неделе» не бьётся, суммы «пляшут»	Разные VIEW на разные правила; календарь/TZ/валюта в паспорте
COUNT(DISTINCT)/перцентили «на лету»	Минуты ожидания/таймауты	Состояния в AggregatingMergeTree + …Merge
FINAL в прод-плитках	Внезапные провалы по SLA	Убрать FINAL, обеспечить чистую запись/ретро-окна
Нет дефолтных фильтров	Случайные «полные сканы»	Период по умолчанию, лимиты строк, Top-N
«Толстые» JOIN в BI	Память, падения	Pre-join в витрине, словари для малых справочников
Part-explosion	Мерджи «задыхаются», свежесть падает	Микробатчи, буферные таблицы, укрупнение партиций
Плохой ORDER BY	Читаем «пол-таблицы»	Перепроектировать под реальные WHERE (v2-таблица)
PII в отчётах	Риски утечек	Только vw_* с маскированием и RLS

Чек-лист автора дашборда (до публикации)

Метрики берутся из семантических VIEW (версия/паспорт актуальны).
В запросах есть фильтр по периоду и он соответствует партиции.
Нет SELECT *, тянем только нужные поля.
Нет FINAL.
У тяжёлых метрик (uniq/quantile/%) — чтение состояний.
Для Top-N — LIMIT BY/ограничения.
Дашборд открывается < N секунд на «боевом» пользователе.
Плитка «здоровье витрины»: свежесть < SLA, DQ-баланс ок.
Документация: ссылка на паспорта метрик и описание фильтров.

Эксплуатация дашбордов: мониторинг и алерты

Freshness каждой ключевой VIEW (sem_meta.updated_at);
Топ-запросы из system.query_log (по байтам/строкам/времени);
Доля запросов с FINAL;
DQ-плитка: расхождения с CORE, «новые статусы».

Runbook «плитка стала медленной» (коротко):

Посмотреть запрос в логах (байты/строки/время).
Проверить WHERE/партиции/ORDER BY соответствие.
Заменить «тяжёлые» метрики на чтение из rollup-состояний.
Убрать JOIN → pre-join/VIEW.
Если NRT — сделать минутный/часовой агрегат.

Приложение: cookbook запросов

YoY/МоМ/WoW (правильные сравнения)

-- Дни: YoY
WITH base AS (
  SELECT day, sum(net_sales) AS s
  FROM vw_net_sales_daily
  WHERE day BETWEEN addYears(today(), -1)-28 AND today()
  GROUP BY day
)
SELECT b.day,
       b.s AS cur,
       b_prev.s AS prev,
       (b.s - b_prev.s) AS delta,
       100.0 * (b.s - b_prev.s) / NULLIF(b_prev.s,0) AS delta_pct
FROM base b
LEFT JOIN base b_prev ON b_prev.day = addYears(b.day, -1)
ORDER BY b.day;

Расклад по квантилям

SELECT day,
       quantilesTDigest(0.5,0.9,0.95,0.99)(check_amount) AS quants
FROM mart_orders_wide
WHERE day BETWEEN today()-30 AND today()-1
GROUP BY day;

В прод — лучше хранить состояния quantileTDigestState и читать …Merge.

Сегментация RFM (упрощённо)

WITH base AS (
  SELECT customer_id,
         dateDiff('day', max(toDate(tx_datetime)), today()) AS recency,
         countIf(status_canon='PAID') AS freq,
         sumIf(amount_base, status_canon='PAID') AS monetary
  FROM mart_orders_wide
  GROUP BY customer_id
)
SELECT *,
  multiIf(recency<=7,'R1',recency<=30,'R2','R3') AS R,
  multiIf(freq>=10,'F1',freq>=3,'F2','F3')       AS F,
  multiIf(monetary>=100000,'M1',monetary>=20000,'M2','M3') AS M
FROM base;

Top-K с массивом

SELECT day, topK(10)(sku_id) AS top10_sku
FROM mart_sales_wide
WHERE day BETWEEN today()-7 AND today()-1
GROUP BY day;

Для стабильности — храните topKState и читайте topKMerge.

Итоги

Проектирование дашбордов под ClickHouse — это комбинация правильных входов (семантические VIEW, нормализованные статусы/календари/валюты), готовых агрегатов-состояний (uniq/quantile/%/Top-K) и аккуратных запросов (фильтры по партициям/ключам, PREWHERE, без FINAL, без «лишних» колонок). Добавьте rollup-слой под NRT и «тяжёлые» метрики, держите мониторинг (query_log, свежесть, DQ), и ваши дашборды будут быстрыми, стабильными и сопоставимыми между командами.

Arenadata QuickMarts (ADQM) — корпоративная платформа на базе ClickHouse для быстрого слоя витрин и near-real-time аналитики. Решает задачи «быстрых» дашбордов и API с низкой латентностью и высокой конкуррентностью, работает поверх вашего DWH/лейкхауса как serving-уровень. Даёт предсказуемую производительность на терабайтно-петабайтных объёмах за счёт колоночного хранения, компрессии и предагрегатов (Materialized Views, AggregatingMergeTree), подключается к Kafka/S3 и стандартным BI-инструментам по SQL/HTTP. Для корпоративных ИТ ADQM предлагает поддержку и SLA, отказоустойчивые кластеры (HA/DR), безопасность (RBAC, LDAP/OIDC, шифрование трафика и данных), мониторинг и резервное копирование. Платформа хорошо ложится на методологию курса: семантика vw_*, роллап-слои, NRT-ингест, SLO/наблюдаемость и «гвардейки» для BI/API. Итог — быстрый запуск витрин за недели, снижённые риски в проде и предсказуемая стоимость владения.

← Предыдущая статья

Модуль 3. Потоки данных, эксплуатация и надёжность ClickHouse-витрин

Следующая статья →

Модуль 5. Data Quality, тестирование, управление изменениями и надёжность витрин ClickHouse