Модуль 0. ClickHouse как слой витрин: зачем, где он силён, как проектировать, какие риски учесть

Что такое слой витрин и почему здесь ClickHouse

Витрина данных — это «готовый стол» для отчётов: уже подчищенные, согласованные, часто заранее агрегированные данные «как нужно бизнесу».
ClickHouse чаще всего используется именно здесь, потому что:

очень быстро считает срезы и суммы по большим объёмам;
выдерживает много одновременных читателей (дашборды, ad-hoc);
дешёв в эксплуатации по сравнению с классическими СУБД для таких нагрузок.

Важно: витрина — не «источник правды». Истина хранится в CORE (нормализованные таблицы, где «правильно» оформлены сущности, статусы, связи). Витрина — это быстрый, «читабельный» слой сверху.

Поток в целом: Источники → Landing/RAW → STAGE → CORE → MARTS (ClickHouse) → BI/отчёты.
Вы, как аналитик, работаете с MARTS, где всё уже «по-деловому» и быстро.

Когда витрина на ClickHouse — уместна, а когда лучше не надо

Подходит, если:

основная работа — чтение, группировки, фильтры, а не правки строчек;
нужны быстрые отчёты по дням/часам/минутам;
у вас много пользователей и параллельных запросов;
есть смысл материализовать (заранее посчитать) типовые агрегаты.

Осторожно, если:

вы ожидаете жёсткие транзакционные операции (много «правок задним числом» каждую минуту);
витрина должна заменять собой всю правду (MDM, сложные связи);
основной профиль — OLTP (короткие апдейты каждой строки).
В этих случаях разместите «сложность» в CORE, а в ClickHouse — готовые срезы.

Договоренности на берегу: контракт данных CORE → MARTS

Чтобы отчёты были стабильными, мы фиксируем data contract между слоями. В нём есть:

Список полей и их смыслы (связь с «паспортом метрики»).
Ключ и уникальность (как определяем «одну и ту же» запись).
SLA свежести: например, «витрина не старше 15 минут днём, 1 часа ночью».
Окно корректировок: «последние 14 дней могут пересчитываться».
Версии: как меняется схема и формулы (v1 → v2), что это значит для отчётов.
Кто отвечает: бизнес-владелец и тех-владелец.

Аналитику это помогает: вы знаете какая метрика, где и с какой задержкой обновится, и кого спрашивать при расхождениях.

Как устроены витрины (понятно и по-деловому)

Есть два привычных «стиля»:

Широкая витрина (wide): факты + нужные атрибуты прямо в одной таблице.
Плюс: очень быстро строятся отчёты, мало соединений.
Минус: атрибуты повторяются, таблица «толстеет».
«Звезда»: факты отдельно, справочники (магазин, товар, клиент) отдельно.
Плюс: гибкость, меньше дублирования атрибутов.
Минус: отчёты могут быть тяжелее из-за соединений.

Обычно вам дают готовые представления (VIEW) поверх того, что выбрали инженеры, — вы работаете с ними одинаково удобно в обоих случаях.

Про «материализацию» простыми словами

Материализация — это когда типовой расчёт (например, «продажи по дням × магазинам») мы считаем заранее и храним в отдельной таблице/представлении.
Зачем это вам:

отчёт открывается быстрее (суммы уже посчитаны);
цифры стабильнее (меньше зависимости от «настроения» запроса в BI).

Важно знать две вещи:

такие агрегаты обычно пересчитываются кусками (например, «каждые 15 минут за последние 2 часа» + «ночью за последние 30 дней»);
если вы смотрите «сегодняшний день», лёгкие колебания возможны из-за опоздавших событий — это нормально, об этом заранее предупреждает окно корректировок.

Время, календарь и валюты — главные источники «разъездов»

Календарь. Мы либо живём на обычном календаре (месяцы/кварталы), либо на финансовом (например, 4-5-4 в ритейле). Сравнения «неделя к неделе» делаем в одном и том же календаре.
Валюты. Обычно есть две согласованные логики:

в базовой валюте на момент операции (управленка и сверка с «кассой»);
пересчёт на дату отчёта (для сравнений между датами).
Не смешиваем эти подходы: если нужен другой — просим другое представление.

Практика для аналитика: в «паспорте метрики» всегда смотрите, какой календарь и какая валюта используются. Если не сходится — первый вопрос именно туда.

Что на вашей стороне: семантические представления и паспорта метрик

Вам дают VIEW (представления) с понятными колонками: там уже зашиты формулы метрик, правильные статусы, календарь, валюта, и часто — правила доступа (например, «видишь только свой регион»).
К каждой ключевой метрике есть паспорт: что считаем, что исключаем, какое «зерно», какая свежесть, кто владелец.

Правило №1 для аналитика: строить отчёты только на этих представлениях. Так вы гарантируете, что ваша «Net Sales» = «Net Sales» коллег.

Стабильность и качество (что мы проверяем автоматом)

Мы регулярно мониторим:

Свежесть витрин (не старше SLA).
Баланс с источником (расхождение за вчера ≤ согласованного порога).
Дубли по ключам (зерну) — не должны появляться.
«Дыры» в плотности данных (дни, где внезапно всё пропало).
Инварианты: например, GM не может быть больше Net Sales.

Если что-то не так — появляются «сигналы здоровья». Это первый экран, куда имеет смысл заглянуть, если цифры кажутся странными.

Что происходит при изменении формул: версии v1 → v2

Метрики живут: поменялись правила, скорректировали статусы — цифры изменились. Это нормально, если:

появляется v2 (вторая версия) — и какое-то время обе версии есть параллельно;
в паспорте метрики видно, что изменилось и с какой даты;
BI переключают на v2 осознанно, а v1 держат для проверок ещё 30–60 дней.

Ваша польза: вы заранее знаете, чего ожидать («GM% вырастет на 1–2% из-за исключения тестовых транзакций») и не теряете историю.

Доступ и безопасность: почему вам дают «только VIEW»

В представлениях уже замаскированы PII (персональные поля), настроены политики видимости (например, «регион видит только себя»).
На «сырые» таблицы витрин и тем более CORE BI обычно не имеет прав — это сделано, чтобы избежать случайных утечек и «самодельных» формул.

Если вам нужен дополнительный срез/показатель — просите инженеров добавить его в семантическое представление. Так это станет «правильной» частью общего слоя.

Стоимость и хранение (почему иногда «старое» открывается медленнее)

Данные делятся на:

«горячие» (последние дни/недели) — хранятся на быстрых дисках, отчёты летают;
«холодные» (архив) — могут лежать в более дешёвом хранилище (объектное/облако). Там скорость ниже, но это экономит бюджет.

Если отчёт на год «тяжелее» — это не баг, это так задумано. Для регулярной отчётности чаще используют готовые агрегаты по периодам, которые тоже быстро открываются.

Типовые сценарии и на что смотреть

Розница / eCom

Отчёт: «продажи по дням × магазин × категория».
На что смотреть: календарь (финансовый/обычный), статусы «оплачено/возврат», окно корректировок (возвраты часто приходят «задним числом»).

Веб/приложение

Отчёт: «события, конверсия, топ каналов, p95 задержки».
На что смотреть: уникальные пользователи (как считаем «активного»), атрибуция каналов (last/first, U-shape и т. п.).

Финансы/финтех

Отчёт: «остатки/балансы по дням, выручка, комиссии».
На что смотреть: валюта (на дату операции или отчёта), окно ретро-пересчётов (чарджбеки, уточнения), согласование с «главной книгой».

Типовые «почему не сходится» и что делать

Цифры «вчера» сегодня изменились.
Скорее всего, сработало окно корректировок (дозалили возвраты/опоздавшие события). Посмотрите SLA и окно; для «официальной» выгрузки берите дату, вне окна.
Разъехались с отчётом коллег из другой команды.
Проверьте: одинаковый ли календарь (финансовая неделя ≠ обычная), одна ли валюта, одна ли версия метрики.
Дашборд стал медленным.
Возможно, выбран слишком длинный период или «тяжёлый» срез. Проверьте рекомендуемые фильтры/лимиты в паспорте метрики. Если это новый кейс — попросите команду витрин сделать отдельную агрегированную витрину.
Появился новый статус/категория и всё «в UNKNOWN».
Это нормально на пару дней: статус попал в отчёт «на разбор». Сообщите владельцу метрики — добавят в маппинг.

Шпаргалка аналитика (коротко)

Перед тем как строить отчёт:

Откройте паспорт метрики: формула, календарь, валюта, зерно, окно корректировок.
Убедитесь, что вы используете правильное представление (VIEW).
Проверьте свежесть витрины (SLA) и версию метрики (v1/v2).
Держите в голове: «последние N дней» могут меняться, потому что данные дозаливаются.
Если «не сходится» — сначала календарь/валюта/версия, потом — сигнал «здоровья» витрины (freshness, баланс).

Что даёт такой подход

Единые цифры для всех отчётов.
Прозрачность (всегда понятно, что и откуда посчитано).
Предсказуемость (изменения через версии, а не «тихо ночью»).
Скорость (тяжёлое посчитано заранее, отчёты открываются быстро).
Безопасность (без «лишних» доступов и утечек PII).

Подробнее обо всем:

Введение без маркетинга: зачем вообще выделять слой витрин

В классическом хранилище (DWH) мы делим ответственность по слоям: RAW/LANDING (как прилетело), STAGE (очистка/приведение типов), CORE/DDS (нормализованная, «правдивая» модель), MARTS/Serving (готовые под запросы бизнеса представления и агрегаты).
ClickHouse чаще всего попадает именно в последний слой — слой витрин. Причины просты:

Скорость: колонночное хранение, векторизированное исполнение, сжатие — дешёвые сканы и агрегации на миллиардах строк.
Стоимость: на «железе» средней ценовой категории ClickHouse даёт SLA интерактивной аналитики, который в реляционных OLTP-СУБД потребовал бы несоразмерных ресурсов.
Простая материализация агрегатов: Materialized View, AggregatingMergeTree, SummingMergeTree, Projection (в ряде случаев) — быстро готовим «отчётные» таблицы.
Хорошо уживается с потоками: Kafka/S3/JDBC источники плюс MVs — и у нас near real-time витрины.

Важно: ClickHouse почти никогда не является единственным источником правды. Исторически он слабее в части «тяжёлых» транзакций, строгих ACID, сложной ссылочной целостности и тонкой работы с SCD на уровне концептуальной модели (хотя SCD-паттерны внедряются). Поэтому практическая архитектура: истина хранится в CORE (Postgres/Greenplum/Iceberg/Delta/… или даже другой ClickHouse-кластер как DDS), а ClickHouse — быстрый слой витрин.

Архитектурная роль ClickHouse как слоя витрин

Как вписывается в общую картину

Типичный поток:
Sources (OLTP/Apps/Logs) → Landing/RAW → STAGE → CORE(DDS) → MARTS(ClickHouse) → BI/Приложения.

CORE обеспечивает единые определения сущностей и метрик на уровне логики бизнеса (нормализованные модели, MDM, проверки качества).
MARTS на ClickHouse — это денормализованные, предагрегированные, подогнанные под запросы таблицы, которые:
- гарантируют низкую латентность (секунды) для отчётов/дашбордов,
- разгружают CORE от тяжёлых аналитических запросов,
- дают стабильные SLA: время ответа, свежесть данных (freshness), целостность и полноту.

Контракты между CORE и MARTS

Перед тем как строить витрину из CH, фиксируем data contract (минимум):

Список полей и их смысл (с обязательной ссылкой на «паспорт метрики»).
Ключи и уникальность (какой surrogate/business key, где dedup).
Границы латентности: «в MARTS попадает не старше N минут/часов от факта».
Стратегия изменений схемы: backward/forward compatibility, версия в имени или в колонке.
Ответственность за DQ: что тестируется в CORE, а что — на входе MARTS.
Сценарии отката и ретро-пересчёта: side-by-side таблицы, переключение BI.

Где ClickHouse уместен (и где нет)

ClickHouse уместен, если:

Преобладают чтения и агрегации; нужна низкая латентность на больших объёмах.
Запросы сканируют много строк и сводят их (SUM/COUNT/AVG, percentiles, top-k).
Есть много одновременных читателей (аналитики/дашборды/ад-hoc).
Витрины можно материализовать и обновлять инкрементально (CDC/микробатчи).
Приемлема eventual consistency внутри короткого окна (секунды-минуты).

Осторожнее/неуместно, если:

Нужны жёсткие транзакции с множеством взаимосвязанных апдейтов/удалений.
Высокая интенсивность point updates с жёсткой консистентностью.
Модели требуют строгой ссылочной целостности и сложных каскадных операций.
Основной профиль — OLTP (короткие point-select/insert/update).
Витрина должна быть единственным источником правды без альтернативной «золотоносной» базы — рискованно.

База ClickHouse, важная для витрин

Колонночное хранение, партиции, части

Данные хранятся колонками — выигрываем на сканах/агрегациях.
Таблица разбита на партиции (обычно по дате/месяцу/неделе) и части (parts).
Фоновый процесс merge объединяет маленькие части в крупные.
Риск part explosion (тысячи маленьких частей) — боль для диска и планировщика.

MergeTree-семейство (базовый выбор для витрин)

MergeTree: базовый движок (без встроенной логики «сворачивания» изменений).
ReplacingMergeTree(version): хранит записи с одинаковым ключом, «побеждает» с максимальной version.
SummingMergeTree: суммирует числовые колонки по ключу (осторожно с повторной агрегацией!).
AggregatingMergeTree: хранит состояния агрегатных функций (avgState, uniqState), а при чтении делаем …Merge/…Final.
CollapsingMergeTree(sign): схлопывает пары +(insert)/–(delete) по ключу (аккуратно с порядком).
VersionedCollapsingMergeTree: расширенный вариант collapsing с version.

ORDER BY vs PRIMARY KEY

В ClickHouse PRIMARY KEY = префикс ORDER BY.
ORDER BY определяет физический порядок данных в частях — ключевая настройка для диапазонных фильтров и пропуска чтения (skip indices). Проектирование ORDER BY — половина успеха витрины.

Индексы-скип-лист и Bloom

Skip indices (minmax, set, bloom_filter) позволяют пропускать чтение гранул, где условие заведомо ложно.
Bloom-фильтр полезен для поиска подстрок/LIKE/IN больших множеств (но не панацея).

Materialized Views и Projections

MV — потоковая/батчевая материализация: читаем из источника → пишем в витрину/агрегат.
Projections — альтернативные локальные «представления» с другим ORDER BY/агрегацией (хитрый инструмент, требует аккуратной эксплуатации и понимания планировщика).

Базовые паттерны проектирования витрин

Широкая денормализованная витрина (Wide Table)

Под BI-приложения, которым не хочется много JOIN’ов:

Храним факты + дескриптивные атрибуты измерений прямо в строке факта (на дату среза).
Проще запросы, меньше JOIN — быстрее ответы.
Нужно уметь поддерживать актуальность атрибутов (SCD1/SCD2 snapshot).

Пример (продажи по чекам, денормализация на дату транзакции):

CREATE TABLE mart_sales_wide
(
    tx_date Date,
    shop_id UInt32,
    shop_name LowCardinality(String),
    region_id UInt16,
    region_name LowCardinality(String),
    sku_id UInt32,
    sku_name String,
    sku_brand LowCardinality(String),
    qty Int32,
    amount Decimal(12,2),
    currency FixedString(3),
    customer_id UInt64,
    segment LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(tx_date)
ORDER BY (tx_date, shop_id, sku_id);

Почему так ORDER BY: почти все отчёты в рознице фильтруют по дате, затем по магазину/товару.

Звезда (star schema) для устойчивых JOIN

Если BI-инструмент активно комбинирует измерения, а denormalize = слишком толстые строки:

Факт (grain: чек-позиция, транзакция, день) + измерения (магазин, товар, клиент, календарь).
Стороны медальки:
- Плюс — гибкость, меньше дублей атрибутов;
- Минус — JOIN в ClickHouse стоит памяти/времени; важно проектировать ключи и порядок.

Пример (упрощённо):

CREATE TABLE d_shop (
    shop_id UInt32,
    shop_name String,
    region_id UInt16,
    region_name String,
    scd_from DateTime,
    scd_to   DateTime,
    is_current UInt8
) ENGINE = MergeTree
ORDER BY (shop_id, scd_from);
CREATE TABLE f_sales (
    tx_datetime DateTime,
    shop_id UInt32,
    sku_id UInt32,
    qty Int32,
    amount Decimal(12,2)
) ENGINE = MergeTree
PARTITION BY toYYYYMM(tx_datetime)
ORDER BY (tx_datetime, shop_id, sku_id);

Далее строим витрины с pre-join (см. ниже) либо выполняем JOIN в запросе (см. модуль 5).

Pre-join и pre-aggregate

Часто выгоднее раз в минуту/час собрать предагрегат, чем каждый раз гонять ад-hoc.
Пример: ежедневные продажи на уровне магазина и бренда:

CREATE TABLE agg_sales_daily
(
    sales_date Date,
    shop_id UInt32,
    brand LowCardinality(String),
    qty_sum Int64,
    amount_sum Decimal(14,2)
)
ENGINE = SummingMergeTree((qty_sum, amount_sum))
PARTITION BY toYYYYMM(sales_date)
ORDER BY (sales_date, shop_id, brand);

И заполняем либо INSERT INTO … SELECT … по расписанию, либо через MV:

CREATE MATERIALIZED VIEW mv_sales_to_daily
TO agg_sales_daily
AS
SELECT
    toDate(tx_datetime) AS sales_date,
    shop_id,
    sku_brand AS brand,
    sum(qty) AS qty_sum,
    sum(amount) AS amount_sum
FROM mart_sales_wide
GROUP BY sales_date, shop_id, brand;

Замечания:

Для устойчивого Summing — следи за повторной агрегацией (перезалив дублей приведёт к удвоению).
Если возможны корректировки задним числом — лучше AggregatingMergeTree + sumState/sumMerge, либо Replacing + версия.

Семантический слой метрик: «паспорт метрики» как контракт

Самая частая причина «разъезжания» отчётов — разные формулы/фильтры в разных витринах. Решение — паспорт метрики:

Идентификатор и название (GM, NetSales, ARPU).
Определение: формула, агрегаты, где считать (факт/агрегат), по каким колонкам.
Фильтры/исключения: «не учитывать возвраты SPIKE», «только оплаченные чек-позиции».
Границы: единицы измерения, валюты, курс пересчёта.
Срок годности (окно событий): «пересчитываем последнюю неделю каждые 15 мин».
Слой материализации: MV в CH, batch overlay, пост-обработка.
Версионирование: как меняется формула во времени.

Пример (кратко, для NetSales):

Формула: SUM(amount) - SUM(returns_amount) на зерне tx_date, shop, sku.
Фильтр: status IN ('paid','captured').
Валюта: локальная; пересчёт в базовую валюту делается в отдельной витрине *_fx.
Окно: «скользящая неделя + ежедневный ретро-пересчёт 30 дней».
Материализация: mv_sales_to_daily (см. выше), ретро-пересчёт через overlay.

Инкрементальные обновления витрин (без полного пересчёта)

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

Idempotent upsert (вставка «поверх» старого с логикой выбора версии): ReplacingMergeTree(version)
Схлопывание сигналов: CollapsingMergeTree(sign) для потоков c +/–.
Агрегат-состояния: AggregatingMergeTree + …State/…Merge для устойчивого накопления.
Side-by-side стратегия при больших ретро-пересчётах: строим table_v2, переключаем BI.

Пример: денормализованная витрина продаж с заменой по версии:

CREATE TABLE mart_sales_wide_v
(
    tx_id UInt64,
    tx_date Date,
    shop_id UInt32,
    sku_id UInt32,
    qty Int32,
    amount Decimal(12,2),
    version UInt64,            -- монотонно растущая версия события/правки
    updated_at DateTime
)
ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(tx_date)
ORDER BY (tx_date, shop_id, sku_id, tx_id);

Вставляем новые/исправленные строки из CORE — в чтении используем FINAL (или гарантируем отсутствие конкурирующих версий на срезе). Для больших таблиц FINAL дорог — поэтому на отчётных витринах стараемся обеспечивать консистентность upstream, чтобы FINAL не требовался.

Практический кейс №1: розница (ежедневные витрины продаж)

Задача: дашборд по продажам за день с разрезами по магазину, бренду, категории, с метриками NetSales, Qty, Margin, конверсия по акциям.

Шаги:

Контракт CORE→MARTS: формат фактов чеков/позиций, ключи, статусы.
Витрина mart_sales_wide: денормализация атрибутов магазина/товара на дату транзакции (snapshot атрибутов).
Материализация:
- Ежечасно инкремент за последние 2 дня (вдруг опоздавшие события/возвраты).
- Ежедневный ретро-пересчёт за последние 30 дней (агрегаты пересчитались с учётом исправлений).
Агрегаты: agg_sales_daily (shop × brand), agg_sales_daily_cat (shop × category).
DQ-контроли: баланс сумм с CORE (±0.1%), дублей по tx_id, плотность данных по магазинам.

Типичные риски и как их избежать:

Часы «тишины» в кассах → «нулевые» продажи. Решение: строить полную матрицу дат × магазинов и явно проставлять нули (и это отличное место для materialized view c join на календарь/справочник магазинов).
Опоздавшие события/возвраты → разъезды ежедневных агрегатов. Решение: вести overlay-таблицу корректировок и применять их в «окне» X дней; либо AggregatingMergeTree с дневными state и ретро-…Merge при чтении для окна.
Удвоение сумм в SummingMergeTree при переигрывании загрузки. Решение: работать через таблицу-источник и пересобирать целевой Summing из чистого INSERT … SELECT … WHERE date BETWEEN … (или использовать AggregatingMergeTree).

Практический кейс №2: веб-аналитика (события)

Задача: near real-time витрины по событиям сайта/приложения (просмотры, клики, транзакции), отчёт по воронке и retention.

Паттерн:

Источник — Kafka (Debezium/логгер). В CH таблица с Kafka Engine + MV в MergeTree.
Широкая витрина mart_events_wide с нормализованными полями: event_time, user_id, session_id, event_type, channel, campaign, device, referrer, is_purchase, order_id, amount.
Агрегаты agg_events_minute/agg_events_hour по каналам/кампаниям.
Согласование таймзон, дробление по event_time (партиции toYYYYMM(event_time)).

SQL-набросок (упрощённо):

CREATE TABLE events_kafka
(
    event_time DateTime,
    user_id UInt64,
    session_id UUID,
    event_type LowCardinality(String),
    channel LowCardinality(String),
    campaign LowCardinality(String),
    is_purchase UInt8,
    amount Decimal(12,2)
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'kafka:9092',
    kafka_topic_list = 'events',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 4;
CREATE TABLE mart_events_wide
(
    event_time DateTime,
    user_id UInt64,
    session_id UUID,
    event_type LowCardinality(String),
    channel LowCardinality(String),
    campaign LowCardinality(String),
    is_purchase UInt8,
    amount Decimal(12,2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id, session_id);
CREATE MATERIALIZED VIEW mv_events_ingest
TO mart_events_wide
AS
SELECT * FROM events_kafka;
Дальше — агрегаты:
sql
КопироватьРедактировать
CREATE TABLE agg_events_minute
(
    ts_minute DateTime,
    channel LowCardinality(String),
    campaign LowCardinality(String),
    views UInt64,
    clicks UInt64,
    purchases UInt64,
    revenue Decimal(14,2)
)
ENGINE = SummingMergeTree((views, clicks, purchases, revenue))
PARTITION BY toYYYYMM(ts_minute)
ORDER BY (ts_minute, channel, campaign);
CREATE MATERIALIZED VIEW mv_events_to_minute
TO agg_events_minute
AS
SELECT
    toStartOfMinute(event_time) AS ts_minute,
    channel,
    campaign,
    sum(event_type = 'view') AS views,
    sum(event_type = 'click') AS clicks,
    sum(is_purchase) AS purchases,
    sumIf(amount, is_purchase = 1) AS revenue
FROM mart_events_wide
GROUP BY ts_minute, channel, campaign;

Риски:

Мелкие блоки из Kafka, часть взрывается, мерджи зашиваются. Решение: настроить kafka_max_block_size, партиции по времени, микробатчи через буферную таблицу.
Дубликаты событий после рестартов консюмера. Решение: в mart_events_wide завести уникальный event_id и использовать ReplacingMergeTree(version) или явную дедупликацию на STAGE.
Скачки нагрузки в прайм-тайм. Решение: увеличить kafka_num_consumers, горизонтально масштабировать шарды и распределённые таблицы, готовить предагрегаты.

Производительность витрин: с чего начать

Короткий «первый набор» техник, который окупается:

Спроектируйте правильный ORDER BY под реальные фильтры. Если 90% запросов фильтруют по date, затем shop_id, делайте ORDER BY (date, shop_id, …).
Выберите корректный движок под обновление:
- «Вставляем и забываем» — MergeTree/SummingMergeTree.
- «Исправляем задним числом» — ReplacingMergeTree(version) или AggregatingMergeTree.
- «Сигналы +/–» — CollapsingMergeTree(sign) (но с дисциплиной порядка).
Избегайте лишнего FINAL на чтении — обеспечивайте консистентность upstream.
Собирайте разумные партиции — месячные для продаж, дневные для событий высокого трафика.
Контролируйте число частей — укрупняйте партиции, ограничивайте частоту вставок, используйте буферные таблицы.
Профилируйте запросы (EXPLAIN PLAN/PIPELINE), смотрите query_log (прочитано строк/байт, время, стадии).

Антипаттерны слоя витрин (и быстрые фиксы)

«Витрина = копия CORE»: без денормализации и агрегатов смысла мало.
Фикс: делайте pre-join и pre-aggregate, убирайте тяжелые JOIN из BI.
ORDER BY не отражает фильтры: читаем лишние данные, тормоза.
Фикс: переопределите порядок, переложите партиции, сделайте миграцию через v2-таблицу.
Повсюду FINAL: красиво, но больно дорого.
Фикс: добивайтесь уникальности и стабильности на записи.
Summing без дисциплины: удвоение сумм при повторных заливках.
Фикс: rebuild из чистого источника или переход на AggregatingMergeTree.
Мелкие партиции/части: перегрев мерджей.
Фикс: буферизация вставок, настройка размеров блоков, укрупнение партиций.

Мини-чек-лист «Готовим первую витрину в ClickHouse»

Опишите метрику (паспорт) и зафиксируйте контракт CORE→MARTS.
Выберите модель витрины (wide vs star), зерно и ключи.
Спроектируйте партиционирование и ORDER BY под реальные фильтры.
Решите, что материализовать (MV/agg) и в каком окне ретро-пересчётов жить.
Запланируйте DQ-контроли (балансы, дубли, плотность).
Подготовьте runbook: «что делаем, если задержка/дубли/расхождения».
Заложите наблюдаемость (query_log, part_log, метрики merges/parts, лаги).

Набросок CI/CD для витрин (пригодится уже сейчас)

Git: SQL-скрипты схем (v1/v2), код MVs, DQ-тесты.
PR-pipeline: прогон lint/SQL-тестов, smoke-прогоны на стейдже, деплой миграций.
Миграции без простоя: side-by-side таблицы *_v2, двойная запись (при необходимости), переключение BI.
Мониторинг релизов: сравнение агрегатов до/после, контроль латентности.

Коротко об оценке стоимости (TCO) слоя витрин

Хранилище: NVMe для «горячих» партиций (N=30–90 дней), S3/объектные диски для холодных, TTL для ротации.
CPU: учитывайте пики BI-нагрузки; горизонтально масштабируйте Distributed-слой.
Сеть: внимание к S3 latency (кэширование), перекладывайте «тяжёлые» расчёты в ночные окна.
Оптимизация: чем больше pre-aggregate — тем меньше «живых» тяжёлых запросов.

Паттерны материализации: выбор движка и стратегии обновления

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

MergeTree (базовый, «insert and forget»)

Когда применять: факты стабильно не меняются после записи; витрина — «широкая» таблица, собираем её batch-ом или через MV, но без апдейтов.
Плюсы: предсказуемая производительность, простая эксплуатация.
Минусы: любые исправления требуют overlays (накладок) или пересборки партиций.

Шаблон:

CREATE TABLE mart_orders
(
    order_date Date,
    order_id   UInt64,
    customer_id UInt64,
    shop_id    UInt32,
    amount     Decimal(12,2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, shop_id, order_id);

Риск: приходят «поздние» корректировки (chargeback, отмены).
Митигация: отдельная таблица корректировок + nightly overlay INSERT … SELECT … на окно N дней.

ReplacingMergeTree(version) (upsert по версии)

Когда применять: приходит несколько версий одной записи (по business_key), нужна идемпотентность и выбор «последней» версии.
Как работает: хранит все версии; фоновый merge оставляет запись с максимальной version. На чтении FINAL обеспечивает консистентный срез (дорогая операция).
Важно: старайтесь не использовать FINAL на частых BI-запросах — обеспечьте консистентность upstream или читайте только «свежие» партиции, где нет конкурирующих версий.

Шаблон:

CREATE TABLE mart_sales_upsert

(

tx_id UInt64,

tx_date Date,

shop_id UInt32,

sku_id UInt32,

qty Int32,

amount Decimal(12,2),

version UInt64, -- монотонно растущая версия

updated_at DateTime

)

ENGINE = ReplacingMergeTree(version)

PARTITION BY toYYYYMM(tx_date)

ORDER BY (tx_date, shop_id, sku_id, tx_id);

Риск: неконтролируемые многократные версии → FINAL становится «обязательным».
Митигация: на входе дедуп по ключу+версии, дисциплина источника, короткое окно «переигрываний».

CollapsingMergeTree(sign) и VersionedCollapsing

Когда применять: поток сигналов типа +1/-1 для одной сущности (вставки/удаления), которые потом схлопываются.
Collapsing: пары одинаковых ключей с sign=+1 и sign=-1 взаимно уничтожаются при merge.
VersionedCollapsing: добавляет version, упрощает логику при нескольких апдейтах.

Шаблон:

CREATE TABLE mart_positions_collapse
(
    id       UInt64,
    sign     Int8,  -- +1/-1
    qty      Int32,
    amount   Decimal(12,2)
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY (id);

Риски: нарушение порядка сигналов, «непарные» записи, тяжёлые FINAL.
Митигация: применять аккуратно; чаще ReplacingMergeTree(version) проще сопровождать.

SummingMergeTree (устойчивые суммы)

Когда применять: нужна накопительная сумма по ключу, а строки однозначно «добавочные» (без переигрываний).
Плюсы: дешёвые агрегаты; great для «счётчиков».
Минусы: повторная загрузка удвоит сумму.

Шаблон:

CREATE TABLE agg_daily
(
    day Date,
    shop_id UInt32,
    amount_sum Decimal(14,2),
    qty_sum    Int64
)
ENGINE = SummingMergeTree((amount_sum, qty_sum))
PARTITION BY toYYYYMM(day)
ORDER BY (day, shop_id);

Риск: переиграли загрузку за день → удвоение.
Митигация: rebuild «с нуля» из источника (INSERT SELECT с фильтром по дате), либо переход на AggregatingMergeTree.

AggregatingMergeTree (состояния агрегатов)

Когда применять: нужно устойчивое накопление агрегатов (в т. ч. uniq*, quantile*, avg без искажения при повторной заливке).
Как работает: в таблицу пишем state: sumState(x), uniqCombinedState(y), а на чтении делаем sumMerge/uniqCombinedMerge.
Плюсы: отсутствие «удвоения» при повторном прогоне; гибкие агрегаты (перцентили, uniq).
Минусы: чуть сложнее в запросах, нужен дисциплинированный слой чтения (…Merge).

Шаблон:

CREATE TABLE agg_sales_state
(
    day Date,
    shop_id UInt32,
    amount_state AggregateFunction(sum, Decimal(14,2)),
    customers_state AggregateFunction(uniqCombined, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, shop_id);
-- Заполнение:
INSERT INTO agg_sales_state
SELECT
  toDate(tx_datetime) AS day,
  shop_id,
  sumState(amount),
  uniqCombinedState(customer_id)
FROM mart_sales_wide
GROUP BY day, shop_id;
-- Чтение:
SELECT
  day, shop_id,
  sumMerge(amount_state) AS amount_sum,
  uniqCombinedMerge(customers_state) AS customers_uniq
FROM agg_sales_state
GROUP BY day, shop_id;

Риск: забыли …Merge → получите бинарные состояния, а не числа.
Митигация: оборачивайте чтение в вьюху или параметризованный шаблон.

Materialized Views и буферизация

Stream MV: из Kafka/логов в витрины. Настраивайте размер блоков на входе, чтобы не плодить мелкие части.
Batch MV: из промежуточной таблицы «сырья» в агрегаты. Используйте буферные таблицы и короткие окна.
Антипаттерн: MV, которая при каждом сообщении пересчитывает огромный агрегат. Делите по времени и ключам.

Projections (в помощь, но осторожно)

Идея: альтернативная физическая организация внутри той же таблицы — «предагрегированная/переупорядоченная» копия.
Когда помогaют: когда большинство запросов — однотипные и совпадают с проекцией.
Риски: усложнение эксплуатации, неочевидный выбор плана запроса планировщиком.
Рекомендация: начинайте без projections; вводите точечно, измеряя до/после.

Пример:

CREATE TABLE f_tx
(
  dt Date,
  shop_id UInt32,
  sku_id UInt32,
  amount Decimal(12,2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(dt)
ORDER BY (dt, shop_id, sku_id)
SETTINGS allow_experimental_projection_optimization = 1;
ALTER TABLE f_tx
ADD PROJECTION p_daily_shop
(
  SELECT dt, shop_id, sum(amount) AS amount_sum
  GROUP BY dt, shop_id
);

ORDER BY, индексы и физическая организация данных

Как выбрать ORDER BY

Оперируйте реальными фильтрами BI: если 90% запросов начинаются с WHERE day BETWEEN … AND … AND shop_id IN (…), то логика ORDER BY (day, shop_id, …) — рациональна.
Сразу заложите селективные поля в префикс: дата/временная гранулярность, гео/магазин/канал, сегмент.
Не переусердствуйте: слишком длинный ключ → избыточная кардинальность и накладные расходы.

Skip-индексы (secondary data-skipping indexes)

minmax: по числам/датам; надежный «молоток».
bloom_filter: для подстрок/IN по большим множествам; экспериментируйте с настройками granularity и bloom_filter_size.
set: для небольших наборов значений.

Пример:

CREATE TABLE mart_orders_idx
(
    day Date,
    shop_id UInt32,
    customer_id UInt64,
    amount Decimal(12,2),
    status LowCardinality(String),
    INDEX idx_status status TYPE set(1000) GRANULARITY 4
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, shop_id, customer_id);

Принцип: индекс ускорит пропуск гранул, но не заменит продуманное ORDER BY.

Партиционирование и «part explosion»

Партиции — логическая группировка (обычно по месяцу/неделе/дню).
Слишком мелкие партиции → много частей → перегретые merges.
Буферизуйте вставки: вставляйте крупными блоками (десятки-сотни тысяч строк).

Полезные настройки и приёмы:

Вставляйте через INSERT SELECT с батчами.
Сведите мелкие партии в промежуточную таблицу и периодически переливайте в целевую.
Контролируйте метрики: число parts на партицию, merge backlog.

Миграции схем и side-by-side стратегии

Изменения в витринах неизбежны (новые атрибуты, уточнение формул, смена ключей). Цель — без простоя BI.

Side-by-side (v1 → v2)

Создайте новую таблицу *_v2 с нужной схемой, ORDER BY, движком.
Двойная запись (если возможно): всё новое пишем и в v1, и в v2.
Ретро-пересчёт v2 за историю (окно N месяцев/лет).
Сравнение агрегатов (контрольные суммы, «серебряный» датасет).
Переключение BI (view/alias) на v2.
Заморозка v1 и план удаления.

SQL-набросок:

-- 1. v2
CREATE TABLE mart_sales_v2 ( ... )
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, shop_id);
-- 2-3. Перекладка истории:
INSERT INTO mart_sales_v2
SELECT ... FROM source
WHERE day BETWEEN '2024-01-01' AND today();
-- 4. Сравнение:
SELECT sum1, sum2, sum1 - sum2 AS diff FROM (
  SELECT sum(amount) AS sum1 FROM mart_sales_v1 WHERE day>=today()-30
), (
  SELECT sumMerge(amount_state) AS sum2 FROM mart_sales_v2 WHERE day>=today()-30
);
-- 5. VIEW/замена
CREATE OR REPLACE VIEW mart_sales AS SELECT * FROM mart_sales_v2;

Миграции колонок

Добавление — безопасно (backward-compatible).
Удаление/rename — делайте через v2.
Смена типа — v2 + перелив.

Пересборка партиций

Если требуется массовая перекладка:

Снимайте бэкап партиций.
Пересборка по окну (месяц/неделя) с контролем агрегатов.
В ночные окна/low-traffic периоды.

Observability: логи, метрики, алерты

Хорошая витрина — это не только SQL. Это наблюдаемость: чтоб заранее знать о проблемах.

Системные логи ClickHouse

system.query_log — факт выполнения запросов (включайте логирование завершённых и, по возможности, выбрасывайте на долгоживущие SELECT).
system.part_log — операции с частями (создание, мердж, удаление).
system.trace_log — низкоуровневые профилировки (опционально).
system.metric_log / system.asynchronous_metrics — счётчики и «асинхронные» метрики.

Первые запросы:

-- Топ тяжёлых запросов за сутки
SELECT
  query_kind, query,
  sum(read_rows) AS rows, sum(read_bytes) AS bytes,
  avg(query_duration_ms) AS ms
FROM system.query_log
WHERE event_time >= now() - INTERVAL 1 DAY
  AND type = 'QueryFinish'
GROUP BY query_kind, query
ORDER BY bytes DESC
LIMIT 50;
-- Динамика merges/parts
SELECT event_type, count()
FROM system.part_log
WHERE event_time >= now() - INTERVAL 1 DAY
GROUP BY event_type;

Метрики Prometheus/Grafana (или аналоги)

Отслеживайте:

Число партиций/частей на таблицу и их динамику.
Merge backlog и время мерджей.
Репликационный лаг (если реплицируете).
Латентность обновления витрин (SLA freshness).
Ошибки в MV/ingestion (например, лаг чтения из Kafka, отвал консьюмеров).
Объём диска по хранилищам/политикам (NVMe/S3), TTL-переносы.

Алерты:

parts > порога (например, >20k) по таблице/партиции.
merges backlog растёт > X минут.
freshness витрины > SLA (например, >15 мин).
репликация отстаёт > N секунд/минут.

Безопасность и доступ

RBAC (роли и права)

Создавайте роли: reader_bi, developer_marts, admin_dwh.
Гранты уровня БД/таблиц/вьюх.
Привязывайте пользователей к ролям, не раздавайте права напрямую.

CREATE ROLE reader_bi;
GRANT SELECT ON db_marts.* TO reader_bi;
CREATE USER analyst1 IDENTIFIED BY '***';
GRANT reader_bi TO analyst1;

Row-Level Security (политики строк)

Ограничивайте доступ по регионам/подразделениям.

-- Дать пользователю видеть только строки его региона
CREATE ROW POLICY rp_sales_region
ON db_marts.mart_sales_wide
FOR SELECT
USING region_id = currentSetting('region_id');  -- или через mapping-таблицу + JOIN во VIEW
ALTER USER analyst1 SETTINGS region_id = 77;

Практика: для сложных фильтров удобнее создавать VIEW с фильтрами и раздавать доступ к ним.

Маскирование колонок

В ClickHouse нет «универсальной» встроенной сложной маскировки как в некоторых СУБД, поэтому:
- Используйте VIEW, где PII/секреты трансформируются функциями (SHA256, substring, if) или зануляются.
- Гранты только на VIEW, не на исходную таблицу.
- В сложных случаях — вынос PII в отдельные хранилища/таблицы и join-по-требованию.

Прикладной кейс №3: FinTech (книга транзакций и витрины балансов)

Контекст: транзакции клиентов (дебет/кредит), комиссии, возвраты; нужны витрины остатков по дням и отчёты по действительной выручке (после уточнений).

Модель и слои

CORE (вне CH): нормализованные таблицы t_transaction (ledger), справочники клиентов, продуктов, тарифов.
MARTS (CH):
- mart_tx_wide — денормализация транзакций (snapshot тарифов на дату).
- agg_balance_daily_state — AggregatingMergeTree с состояниями баланса.
- agg_revenue_daily_state — агрегаты выручки/комиссий.

Таблицы

CREATE TABLE mart_tx_wide
(
  tx_dttm DateTime,
  account_id UInt64,
  product_id UInt32,
  tx_type LowCardinality(String), -- debit/credit/fee/refund
  amount Decimal(18,2),
  currency FixedString(3),
  exchange_rate Decimal(12,6),    -- snapshot на момент tx
  amount_base Decimal(18,2)       -- amount * rate
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(tx_dttm)
ORDER BY (tx_dttm, account_id, product_id);

Ежедневные балансы (sum по знаку):

CREATE TABLE agg_balance_daily_state
(
  day Date,
  account_id UInt64,
  balance_state AggregateFunction(sum, Decimal(18,2))
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, account_id);
INSERT INTO agg_balance_daily_state
SELECT
  toDate(tx_dttm) AS day,
  account_id,
  sumState(
    if(tx_type IN ('credit','refund'), amount_base, -amount_base)
  ) AS balance_state
FROM mart_tx_wide
GROUP BY day, account_id;

Чтение баланса за период с накоплением (running total на стороне BI/SQL):

SELECT
  day, account_id,
  sumMerge(balance_state) AS delta_day
FROM agg_balance_daily_state
WHERE day BETWEEN '2025-06-01' AND '2025-06-30'
GROUP BY day, account_id
ORDER BY account_id, day;

Далее считаем кумулятивно в BI или SQL-окном.

Выручка (комиссии/fee) по дням/продуктам:

CREATE TABLE agg_revenue_daily_state
(
  day Date,
  product_id UInt32,
  revenue_state AggregateFunction(sum, Decimal(18,2))
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, product_id);
INSERT INTO agg_revenue_daily_state
SELECT
  toDate(tx_dttm) AS day,
  product_id,
  sumState(if(tx_type='fee', amount_base, 0))
FROM mart_tx_wide
GROUP BY day, product_id;

Риски и митигации

Корректировки задним числом (chargeback/refund через дни/недели).
→ Митигация: AggregatingMergeTree + ночной ретро-прогон последних N дней; side-by-side для больших правок.
Валютные пересчёты (курсы меняются).
→ Митигация: фиксируйте snapshot курса в mart_tx_wide и отдельно делайте витрину «пересчёт в базовую валюту» при отчётах на нужную дату.
Согласование с GL (главной книгой).
→ Митигация: DQ-тесты балансов (дебет=кредит), контрольные суммы по дням/продуктам.

DQ-контроли (минимум)

Баланс входа/выхода (сверка с GL).
Наличие «дней тишины» для активных аккаунтов.
Наличие отрицательных/аномальных сумм вне допустимых бизнес-правил.
Согласованность количества транзакций с источником (± допуск).

Прикладной кейс №4: Telecom (CDR/события сети)

Контекст: миллиарды CDR (Call Detail Records), KPI по качеству сети, загрузке сот. Витрины — почасовые/поминутные агрегаты по сотам/тарифам/регионам.

Модель и ingestion

Источник: Kafka (сырые CDR) → Kafka Engine + MV → cdr_wide.
Витрины: agg_cdr_minute, agg_cdr_hour, перцентили задержек/длительностей.

CREATE TABLE cdr_kafka
(
  event_time DateTime,
  cell_id UInt64,
  msisdn UInt64,
  call_duration_ms UInt32,
  latency_ms UInt32,
  result LowCardinality(String)   -- success/fail
)
ENGINE = Kafka
SETTINGS kafka_broker_list='kafka:9092', kafka_topic_list='cdr', kafka_format='JSONEachRow', kafka_num_consumers=6;
CREATE TABLE cdr_wide
(
  event_time DateTime,
  cell_id UInt64,
  msisdn UInt64,
  call_duration_ms UInt32,
  latency_ms UInt32,
  result LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, cell_id, msisdn);
CREATE MATERIALIZED VIEW mv_cdr_ingest TO cdr_wide AS
SELECT * FROM cdr_kafka;

Поминутные агрегаты (состояния):

CREATE TABLE agg_cdr_minute_state
(
  ts_minute DateTime,
  cell_id UInt64,
  calls_state AggregateFunction(sum, UInt64),
  ok_state    AggregateFunction(sum, UInt64),
  latency_q95_state AggregateFunction(quantileTDigest(0.95), UInt32),
  duration_avg_state AggregateFunction(avg, UInt32)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(ts_minute)
ORDER BY (ts_minute, cell_id);
CREATE MATERIALIZED VIEW mv_cdr_to_minute TO agg_cdr_minute_state AS
SELECT
  toStartOfMinute(event_time) AS ts_minute,
  cell_id,
  sumState(1) AS calls_state,
  sumState(result='success') AS ok_state,
  quantileTDigestState(0.95)(latency_ms) AS latency_q95_state,
  avgState(call_duration_ms) AS duration_avg_state
FROM cdr_wide
GROUP BY ts_minute, cell_id;

Чтение:

SELECT
  ts_minute, cell_id,
  sumMerge(calls_state) AS calls,
  sumMerge(ok_state) AS ok,
  round(100.0 * ok / calls, 2) AS success_rate,
  quantileTDigestMerge(0.95)(latency_q95_state) AS p95_latency,
  avgMerge(duration_avg_state) AS avg_duration
FROM agg_cdr_minute_state
WHERE ts_minute >= now() - INTERVAL 1 DAY
GROUP BY ts_minute, cell_id
ORDER BY ts_minute, cell_id;

Риски и митигации

Бурстовая нагрузка вечером → тысячи маленьких вставок.
→ Митигация: настроить kafka_max_block_size, микробатчи, буферные таблицы, контроль parts.
Дубликаты CDR после рестартов консьюмера.
→ Митигация: event_id + ReplacingMergeTree на этапе cdr_wide или upstream-дедуп.
Нестабильная латентность S3/объектного диска (если храните «холодные» партиции там).
→ Митигация: кэширование, вынесение холодных периодов, отделение hot-window (N часов/дней) на NVMe.

BI-отчётность

Дашборды по успешности (success_rate) в разрезе сот/регионов, p95-латентность, средняя длительность, alarm-панель (алерты по SLA).
Drill-down: регион → город → соты → «спайки» по минутам.

Практики «устойчивых» витрин

Стабилизация схемы: изменения через v2, view-перекидку, «медленные» миграции в окна.
Стабилизация метрик: паспорт, версионирование формул, changelog.
Стабилизация запросов: вьюхи с …Merge, чтобы BI не ошибался.
Стабилизация обновления: договорённость с CORE — «в окне последних N дней возможны корректировки», всё старше — только overlays.
Стабилизация стоимости: TTL в холод, S3-policy, «горячее окно» на NVMe.

Мини-runbooks (что делать, если…)

A) Витрина стала обновляться с лагом

Проверить lag ingestion (Kafka/S3), отставание MV.
Проверить merges backlog и число parts; при необходимости — OPTIMIZE TABLE … FINAL по конкретным партициям (точечно!).
Временно сузить окно ретро-пересчёта, отключить тяжёлые overlay.
Алерты: свежесть > SLA → уведомление + полуавтоматические шаги.

B) Дубли/расхождения в агрегатах

Снять снимки контрольных сумм (SUM(amount)) по окну, сравнить CORE↔MARTS.
Найти источник дублей (повторные заливки, offsets Kafka).
Для Summing — пересобрать целевые партиции из чистого источника; для Aggregating — дописать состояния и перечитать …Merge.

C) «Память закончилась» на BI-JOIN

Проверить план (EXPLAIN PIPELINE), уменьшить ширину результата/колонок.
Разбить запрос на два (pre-aggregate), использовать wide-витрину.
Настроить лимиты и распределённые стадии (если кластер).

Практические советы по настройкам (точечно, как чек-лист)

Вставки: старайтесь в один INSERT подавать крупные блоки (сотни тысяч/миллионы строк), избегайте «дроби».
MVs: разбивайте агрегаты по времени (минуты/часы/дни), не пересчитывайте «всё» на каждую запись.
Партиции: для high-traffic событий — дневные; для продаж — месячные (часто достаточно).
ORDER BY: отражает частые фильтры. «Дата → ключ разреза → доп. поля».
Индексы: ставьте только те, что дают измеримый эффект.
SLA: фиксация freshness, completeness, и «алгоритм деградации» (что отключаем при перегрузе).
DQ: балансные проверки, дубликаты, плотность данных; не блокировать прод, а сигналить и лечить.

Часто задаваемые вопросы

Нужно ли делать звезду, если у нас wide-витрины быстрые?
— Делайте то, что простое и предсказуемое. Если wide-таблица закрывает 90% сценариев и не бьёт по объёму — это нормальный выбор. Введите «тематические» витрины (by product, by customer) вместо универсальной «гигантской».

Когда FINAL допустим?
— Точечно: ад-hoc диагностика, маленькие партиции, редкие отчёты. Для постоянных BI-дашбордов добивайтесь консистентности без FINAL.

Projections — спасут?
— Иногда да, но это инструмент точечной оптимизации, а не серебряная пуля. Начинайте без них.

Где хранить истину по метрикам?
— В паспортe метрик (артефакт) + вьюхи/слой semantic. Согласуйте с бизнесом и BI.

В этой части мы разложили ключевые техники ClickHouse-витрин: выбор движков (Replacing/Collapsing/Summing/Aggregating), грамотный ORDER BY, индексы, миграции без простоя, наблюдаемость, безопасность, и две отраслевые практики с SQL и планами митигаций. Это «рабочий чемодан» архитектора/инженера витрин.

Cookbook: типовые симптомы, как диагностировать и что делать

Ниже сгруппированы реальные ситуации. Формат: Симптом → Диагностика → Фикс → Профилактика. Команды даны коротко, отталкивайтесь от своих схем/таблиц.

Вставки, части, мерджи

A1. Много мелких частей (part explosion), мерджи «задыхаются».

Диагностика
- Проверить число частей по таблице/партициям:

SELECT table, partition, count() AS parts
FROM system.parts
WHERE active AND database='db_marts' AND table='mart_sales_wide'
GROUP BY table, partition
ORDER BY parts DESC;

Лог мерджей:

SELECT event_type, count()
FROM system.part_log
WHERE event_time >= now()-INTERVAL 1 HOUR
GROUP BY event_type;

Фикс
- Временно буферизуйте вставки: пишите в промежуточную таблицу батчами (INSERT SELECT раз в N минут).
- Точечно укрупните партиции: OPTIMIZE TABLE ... PARTITION ... FINAL (не злоупотреблять).
Профилактика
Увеличить размер блоков на входе (Kafka/JDBC), переход на микробатчи.
Пересмотреть партиционирование (дни вместо часов/минут при продажах).

A2. Мерджи «висят» часами, растёт merge backlog.

Диагностика
- Метрики merges/threads, background_pool_size, активные мерджи:

SELECT * FROM system.merges WHERE elapsed > 60;
SELECT name, value FROM system.asynchronous_metrics
WHERE name LIKE '%Merge%';

Фикс
- Снизить скорость генерации новых частей (замедлить ingestion или объединять upstream).
- Увеличить background_pool_size, при необходимости вертикально усилить CPU/IO.
Профилактика
Планировать «тяжёлые» пересчёты в ночные окна.
Свести частоту вставок к разумной (десятки/сотни тысяч строк за батч).

A3. «Память закончилась» во время массового OPTIMIZE … FINAL.

Диагностика
- По логу запросов и системным ошибкам (OOM, memory limit exceeded).
Фикс
Делать OPTIMIZE по одной партиции, уменьшить параллелизм.
Временно увеличить max_memory_usage (осторожно).
Предотвращать part explosion, проводить профилактические OPTIMIZE малых партиций заранее.
Профилактика

Запросы и производительность BI

B1. Запросы BI стали в 5–10 раз медленнее «со вчера».

Диагностика
- Сравнить планы: EXPLAIN PIPELINE/PLAN.
- Топ-запросы по байтам/строкам/времени:

SELECT query, sum(read_rows) r, sum(read_bytes) b, avg(query_duration_ms) ms
FROM system.query_log
WHERE event_time >= now()-INTERVAL 1 DAY AND type='QueryFinish'
GROUP BY query ORDER BY b DESC LIMIT 20;

Фикс
- Проверить селективность WHERE и соответствие ORDER BY; добавить/подправить skip-индексы.
- Пересобрать «проблемные» партиции (сильно фрагментированные) точечным OPTIMIZE.
Профилактика
Контролировать изменения схем/ORDER BY через PR-процессы и нагрузочные тесты.
Договориться с BI о лимитах/фильтрах по умолчанию (не «SELECT * за год»).

B2. JOIN «съедает» память, падает по лимиту.

Диагностика
- План запроса, какая таблица «раздувает» хэш-таблицу.
Фикс
Разбить запрос на два (pre-aggregate → join агрегатов).
Перейти на wide витрину с нужными атрибутами.
Ограничить набор колонок и кардинальность.
Для CH-витрин оптимальнее минимизировать JOIN на чтении: делать pre-join при материализации.
Профилактика

B3. FINAL в витрине стал обязательным — всё тормозит.

Диагностика
- Проверить количество конкурирующих версий/«грязных» записей.
Фикс
Навести порядок upstream: дедуп на источнике или на STAGE, ограничить окно версий.
Перейти на AggregatingMergeTree/batch пересчёт.
Жёсткий data contract на уникальность ключей + версионирование событий.
Профилактика

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

C1. MV «жует» CPU — пересчитывает слишком много.

Диагностика
- Посмотреть SQL MV, есть ли группировка без ограничения по времени/партиции.
Фикс
Делить MV по времени (минуты/часы/дни), агрегировать только свежие окна.
Вынести тяжёлые пересчёты в batch (INSERT SELECT по расписанию).
Не делать «одну MV, которая считает весь мир». Мелкие, целевые, по окнам.
Профилактика

C2. SummingMergeTree удвоил суммы после «повторной» загрузки.

Диагностика
- Сверить контрольные суммы по окну, найти дубли в источнике.
Фикс
Пересобрать целевой период из чистого источника (перезапись партиций).
Или перейти на AggregatingMergeTree (…State/…Merge).
Жёсткая дисциплина загрузок: «повторная заливка» только через пересборку.
Профилактика

Репликация, Keeper, Distributed

D1. Реплика «отстаёт», lag растёт.

Диагностика
- Метрики replication queue, system.replication_queue.
Фикс
Увеличить фоновые потоки, проверить IO/сеть, починить «битые» задания в очереди.
Равномерный шард/реплік layout, не перегружать одну ноду.
Профилактика

D2. Keeper (ZooKeeper/CH Keeper) «шалит»: висят DDL, рассинхрон.

Диагностика
- Логи keeper, сессии, состояния нод.
Фикс
Починить кластер keeper (кворум), переразвернуть, переиграть DDL через Distributed DDL.
Выделенные ресурсы под keeper, мониторинг сессий/лагов, не хранить «всё» в ZK.
Профилактика

D3. Distributed-таблица «тормозит», локальные быстрые.

Диагностика
- Проверить preferred_block_size_bytes, балансировку, сеть.
Фикс
Сужать запросы (пушдаун WHERE к шардам), настраивать локальные агрегации.
Правильный sharding key (обычно по дате/идентификатору разреза).
Профилактика

S3/объектные диски и TTL

E1. SELECT по «холодным» партициям на S3 внезапно медленные.

Диагностика
- Проверить кэш, сеть, облачный endpoint.
Фикс
Включить локальный кэш, подогрев горячих участков, разнести «горячее окно» на NVMe.
Чёткий tiering: горячий горизонт (N дней/недель) — локально, остальное — S3.
Профилактика

E2. TTL «вынес» не те партиции/колонки.

Диагностика
- Проверить правила TTL и фактические действия в part_log.
Фикс
Исправить TTL, вернуть партиции из бэкапа, временно отключить правила.
Тестировать TTL на стейдже, не накатывать сразу на весь «прод».
Профилактика

DQ, консистентность, опоздавшие события

F1. BI видит расхождения с CORE (±1–3%).

Диагностика
- Балансы по окну, сравнение контрольных сумм:

-- MARTS
SELECT toDate(tx_datetime) d, sum(amount) s FROM mart_sales_wide
WHERE tx_datetime >= today()-7 GROUP BY d;
-- CORE (подставьте источник)

Фикс
- Выявить окно опоздавших, доиграть корректировки (overlay).
- Устранить дубли на пути ingestion.
Профилактика
Договориться об окне ретро-пересчёта (напр., «последние 14 дней»), автоматический nightly reconcile.

F2. Много дублей событий после рестарта Kafka-консьюмера.

Диагностика
- Сравнить offsets, посмотреть повторяющиеся ключи.
Фикс
Дедуп по event_id на STAGE или ReplacingMergeTree(version) в витрине.
Exactly-once недостижим — стройте идемпотентность на ключах.
Профилактика

Релизы, миграции, CI/CD

G1. Изменили схему — BI «упал».

Диагностика
- Что изменилось: rename/drop/тип?
Фикс
Вернуть обратно или срочно переключить BI на v2-view, где совместимая схема.
Только additive изменения в проде; breaking-changes через v2 + alias/view.
Профилактика

G2. Пересборка витрины заняла всю ночь, отчёты не готовы.

Диагностика
- Объём пересчёта, IO/CPU, конкуренция с мерджами.
Фикс
Делить пересборку по партициям, параллелить по окнам, переносить «глубокую историю» заранее.
Side-by-side миграции, «прокладка» v2 в фоне, переключение BI в нужный момент.
Профилактика

Безопасность и доступ

H1. Случайно раскрыли PII в широких витринах.

Диагностика
- Проверить колонки, кому был дан прямой SELECT.
Фикс
Срочно ограничить доступ, создать VIEW с маскировкой, раздать гранты только на VIEW.
Политика: PII никогда не отдаём напрямую из витрин; всегда через «обезличивающие» представления.
Профилактика

Шаблоны артефактов (копируй и используй)

27.1 Паспорт метрики (шаблон)

Идентификатор: NET_SALES

Название: Чистые продажи (Net Sales)

Бизнес-определение:

Сумма оплаченных продаж без возвратов и отмен,

пересчитанная в базовую валюту по курсу на момент транзакции.

Формула (SQL/псевдо):

SUM(amount_base) - SUM(returns_amount_base)

Гранулярность:

День × Магазин × Категория SKU

Фильтры/исключения:

status IN ('paid', 'captured') AND source NOT IN ('test', 'fraud')

Единицы и валюты:

amount_base в валюте 'RUB'; для мультивалютных витрин — отдельная витрина *_fx.

Окно ретро-пересчёта:

Скользящая неделя (7 дней) каждые 15 минут + ночной пересчёт 30 дней.

Слой материализации:

agg_sales_daily_state (AggregatingMergeTree) + VIEW для чтения (…Merge).

Версионирование:

v1 от 2025-07-01; v2 (меняем исключения) планируется 2025-09-01.

Тесты DQ:

- Баланс с CORE ±0.1% в окне 7 дней

- Отсутствие дублей по ключу (day, shop_id, category_id)

- Нулевые значения только при явной «тишине»

Контакты/ответственность:

Владелец метрики: Head of FP&A

Технический владелец: DWH Architect

Канал изменений: PR в репозитории marts-metrics

SLA витрины (шаблон)

Витрина: agg_sales_daily_state

Назначение: Дашборды продаж (оперативные и управленческие)

Показатели SLA:

- Freshness: не старше 15 минут от факта (08:00–23:00), не старше 1 часа (ночью)

- Availability: 99.5% в месяц

- Consistency: расхождение с CORE ≤ 0.2% на окне 7 дней

Окна обслуживания:

Ночной ретро-пересчёт 00:30–02:00; тяжёлые миграции — воскресенье 02:00–04:00

Алерты:

- Freshness > 15 мин → PagerDuty: BI-OnCall

- parts > 20k/партиция → уведомление в #dwh-alerts

Degradation policy:

При перегрузе отключаем minute-level агрегаты, оставляем hourly-level.

Чек-лист DQ для витрины

Баланс суммы с CORE на окне N дней в допуске.
Дубликаты ключей (grain) отсутствуют.
Плотность данных (нет «дыр» там, где не должно быть).
Домены значений (статусы/категории) валидны.
Неотрицательные суммы/количества, если бизнес-правило таково.
Единицы измерения и валюты соответствуют контракту.
Регрессионные тесты метрик (после изменений) проходят.

Регламент релизов/миграций (в конспекте)

Любые breaking-changes — через v2 + alias/view.
PR: схемы, MV, тесты DQ, миграции.
Стейдж: нагрузочные прогоны, сравнение агрегатов v1 vs v2 на окне.
Прод: двойная запись (если нужно), ретро-пересчёт истории, переключение BI.
Пост-мониторинг (freshness, ошибки запросов, parts/merges).

Runbook (шаблон «что делать, если…»)

Сценарий: Витрина отстала по свежести > SLA

Шаги:

1. Проверить lag ingestion (Kafka/S3), состояние MV

2. Посмотреть merges backlog, число parts

3. При необходимости временно:

- сузить окно ретро-пересчёта

- отключить тяжелые overlay-процессы

4. Точечный OPTIMIZE проблемной партиции (если фрагментация)

5. Сообщить бизнесу о деградации (если длительно) + ETA

Критерии восстановления:

Freshness < 15 мин; merges backlog < порога; parts стабилизированы

Ответственный:

DWH OnCall (смена); эскалация: DWH Lead

Маршрут запуска витрины «с нуля за 2–3 дня»

День 0 (подготовка)

Уточнить use-case, метрики, источники, окно свежести.
Зафиксировать паспорт метрики и data contract CORE→MARTS.
Проверить доступы, учётки, хостинг (кластер CH, Kafka/S3 при необходимости).

День 1 (схема и базовые таблицы)

База и роли:

CREATE DATABASE db_marts;
CREATE ROLE reader_bi; GRANT SELECT ON db_marts.* TO reader_bi;
CREATE USER bi_ro IDENTIFIED BY '***'; GRANT reader_bi TO bi_ro;

Витрина (wide) и агрегаты-состояния:

CREATE TABLE db_marts.mart_sales_wide
(
  tx_datetime DateTime,
  day Date MATERIALIZED toDate(tx_datetime),
  shop_id UInt32, shop_name LowCardinality(String),
  sku_id UInt32,  category_id UInt32,
  qty Int32, amount Decimal(12,2),
  currency FixedString(3),
  customer_id UInt64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, shop_id, sku_id);

Агрегат-состояния (устойчивые):

CREATE TABLE db_marts.agg_sales_daily_state
(
  day Date, shop_id UInt32, category_id UInt32,
  amount_state AggregateFunction(sum, Decimal(14,2)),
  qty_state    AggregateFunction(sum, Int64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, shop_id, category_id);

Первичная загрузка (история):

INSERT INTO db_marts.mart_sales_wide
SELECT ... FROM core.sales
WHERE tx_datetime >= now()-INTERVAL 90 DAY;

Первичное наполнение агрегатов:

INSERT INTO db_marts.agg_sales_daily_state
SELECT
  toDate(tx_datetime) day, shop_id, category_id,
  sumState(amount) AS amount_state,
  sumState(qty)    AS qty_state
FROM db_marts.mart_sales_wide
GROUP BY day, shop_id, category_id;

День 2 (материализация, DQ, OBS)

VIEW для удобного чтения (…Merge):

CREATE OR REPLACE VIEW db_marts.vw_agg_sales_daily AS
SELECT
  day, shop_id, category_id,
  sumMerge(amount_state) AS amount_sum,
  sumMerge(qty_state)    AS qty_sum
FROM db_marts.agg_sales_daily_state
GROUP BY day, shop_id, category_id;

Инкремент (каждые 15 минут) — cron/Airflow:

-- псевдо: инкремент за последние 2 часа
INSERT INTO db_marts.mart_sales_wide
SELECT ... FROM core.sales
WHERE tx_datetime >= now()-INTERVAL 2 HOUR;
INSERT INTO db_marts.agg_sales_daily_state
SELECT
  toDate(tx_datetime), shop_id, category_id,
  sumState(amount), sumState(qty)
FROM db_marts.mart_sales_wide
WHERE tx_datetime >= now()-INTERVAL 2 HOUR
GROUP BY toDate(tx_datetime), shop_id, category_id;

DQ-контроли (ночью и на инкремент):

-- Баланс vs CORE за 1 день
SELECT
  (SELECT sum(amount) FROM db_marts.vw_agg_sales_daily WHERE day= yesterday()) AS marts_sum,
  (SELECT sum(amount) FROM core.sales WHERE toDate(tx_datetime)=yesterday()) AS core_sum,
  abs(marts_sum-core_sum)/NULLIF(core_sum,0) AS diff_rel;
Алерт, если diff_rel > 0.002.

Observability: включить query_log/part_log, настроить экспорт в Prometheus/Grafana (метрики merges/parts, freshness, ошибки MV/джобов).

День 3 (безопасность, BI, ретро-окно, релизы)

Безопасность:

Доступ BI — только к vw_* вьюхам; PII маскируется в VIEW.
RLS (если нужно) по регионам/подразделениям.

Подключение BI:

Тест отчётов, фильтры по умолчанию, лимиты.
SLA-монитор: плитка «свежесть витрины», «health» мерджей/частей.

Ретро-пересчёт (окно 30 дней):

Ночной джоб INSERT SELECT по дням, где были корректировки.
Для Summing — rebuild; для Aggregating — дописываем состояния.

Регламент релизов:

Внести в репозиторий схемы, VIEW, джобы, DQ-тесты.
PR-проверки, стейдж-прогон, потом — прод.

Результат к концу Дня 3: рабочая витрина с SLA, DQ, Observability, безопасностью и подключенным BI.

Быстрые рекомендации по сайзингу слоя витрин (на старте)

CPU: 16–32 vCPU на ноду (3+ ноды) — для начала интерактива; масштабировать горизонтально.
RAM: 64–128 ГБ на ноду (зависит от JOIN/агрегатов и concurrency).
Диск: NVMe для «горячего окна» (N дней/недель), остальное — S3/object storage + кэш.
Сеть: 10–25 Gbit/s внутренняя; выделенная полоса к S3/объектному хранилищу.
Шардинг: по дате/разрезу (shop/region), чтобы запросы «сужались» на шард.
Репликация: 2 реплики на шард для HA, Keeper — выделенные ресурсы.

Частые вопросы (коротко)

Q: Можно ли жить только на SummingMergeTree?
A: Можно, если никогда не переигрываете окна. На практике — используйте AggregatingMergeTree для устойчивости.

Q: Когда FINAL безболезнен?
A: На малых партициях/разово. Для прод-дашбордов — лучше без FINAL.

Q: Нужны ли projections?
A: Только после профилирования и там, где дают заметный прирост. Это не «обязательный» инструмент.

Q: Что хранить в CH, а что — нет?
A: В CH — витрины/агрегаты под чтение. Истина/MDM/тонкие транзакции — в CORE.

Итоги модуля

ClickHouse — идеальный слой витрин: быстрые агрегации, дешёвые сканы, near real-time.
Ключ к успеху — контракты CORE→MARTS, правильный ORDER BY/партиции, устойчивые материализации (Aggregating/Replacing + дисциплина), наблюдаемость и регламент релизов.
Cookbook, шаблоны и маршрут дают «скелет» для быстрого и безопасного старта.

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. Итог — быстрый запуск витрин за недели, снижённые риски в проде и предсказуемая стоимость владения.

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

Модуль 1. Бизнес-метрики и семантический слой витрин для ClickHouse