Как мы построили внутреннее хранилище данных в ClickHouse (и как это повторить)
Ниже — подробный разбор реального кейса ClickHouse: как команда собрала DWH для операционной аналитики ClickHouse Cloud на самом ClickHouse, включая архитектуру слоёв, загрузку из S3, подход к идемпотентности и «перезаливкам», доступы на уровне строк, эксплуатацию Airflow/Superset и эволюцию стека за год (dbt, real-time источники, 50 ТБ сырых данных в день). В конце — риски, чек-лист внедрения и блок Q&A. Основано на официальных постах ClickHouse и документации — ключевые факты помечены источниками.
Контекст кейса и цели
Команда ClickHouse строила DWH, чтобы:
- понимать поведение клиентов и нагрузку сервиса;
- управлять затратами CSP (AWS/GCP), ценами и автоскейлингом;
- давать командам (Product/Eng/Support/Sales/Finance/Marketing) единый источник правды для отчётности и ad-hoc анализа.
По масштабу: десятки источников, сотни ТБ, ~70+ активных пользователей в месяц, ~40 000 запросов в день. Через год эволюции — ~6 млрд строк и около 50 ТБ нежатых данных в день на вход, ~470 ТБ сжатых в хранении.
Архитектура слоёв: Landing (S3) → RAW → MART
Ключевая идея — ELT. Данные сначала «как есть» выгружаются из источников в S3 (hourly/daily), затем ClickHouse читает их функцией s3() и загружает в базу, где происходят преобразования. Это не ETL: трансформации — уже в ClickHouse.
Слои:
- Landing (S3) — промежуточное размещение инкрементов (часы/дни) и полных выгрузок для словарей. Это не «слой RAW в базе», а именно файловая площадка.
- RAW (в ClickHouse) — таблицы с той же схемой, что в источниках; просто реплика фактов/справочников.
- MART (в ClickHouse) — денормализованные витрины/бизнес-сущности под задачи команд и BI. Первоначально пытались обойтись RAW→MART, но быстро добавили промежуточный слой внутренних сущностей (по сути ODS/Core), чтобы стабилизировать модель.
Инструменты: ClickHouse Cloud (кластер из 3 реплик), Airflow как оркестратор, Superset как BI для дашбордов и ad-hoc SQL (позже добавили и SQL-консоль ClickHouse Cloud; ещё позже — dbt для управления трансформациями).
Источники и объёмы
Примеры (из статьи):
- Data Plane (ClickHouse Cloud): системные метрики/статистика запросов и таблиц — ~15 ГБ/час.
- Control Plane (DocumentDB): метаданные сервисов — ~500 МБ/час.
- AWS CUR (S3): ~1 ГБ/час по инфраструктурным cost & usage.
- GCP Billing (BigQuery): ~500 МБ/час.
- Salesforce: ~1 ГБ/час (30+ таблиц).
- Маркетинговые и ценовые справочники, события Galaxy и др.
Через год — 19 raw-источников; большой перекос в «тяжёлые» real-time логи (JSON/вложенные структуры), одиночные таблицы на сотни миллиардов строк и до 4 млрд строк в день в одну таблицу.
Загрузка из S3 в ClickHouse: как устроено
- Источники выгружают hourly/daily инкременты в S3 (или GCS → S3).
- Airflow запускает импорт через s3()/s3Cluster() — это табличные функции: позволяют читать/писать файлы в S3 «как таблицы». Для кластерной распараллеливания — s3Cluster.
Пример (упрощённо):
-- Чтение сырых CSV/Parquet из S3
SELECT *
FROM s3('https://s3.amazonaws.com/bucket/path/hour=2025-08-21/*.parquet',
'AWS_KEY','AWS_SECRET','Parquet');
-- Загрузка инкремента в RAW
INSERT INTO raw.aws_cur /* структура = как в источнике */
SELECT * FROM s3(...);
Идемпотентность, «перезаливки» и отсутствие дублей
Выбор движка: почти везде используют ReplicatedReplacingMergeTree. Он:
- реплицируется на каждую ноду шарда (надёжность);
- удаляет дубликаты по ключу при слияниях (оставляет «последнюю версию» строки — по версии/времени, если такая колонка задана).
Это критично для «перезаливок»: можно многократно вставлять один и тот же час — в таблице останется единая последняя запись на ключ. Для согласованных чтений в трансформациях используют SELECT ... FINAL, чтобы запрос логически «схлопнул» дубли до последней версии (учтите стоимость FINAL).
Плюс: весь пайплайн сделан идемпотентным — DAG из Airflow разрешено запускать повторно за тот же период без риска задвоений.
Согласованность: вставка с кворумом и чтение
По умолчанию ClickHouse — «eventually consistent» между репликами. Для DWH-транзактов ребята включают кворум вставки (например, 3/3), чтобы INSERT считался успешным только если запись попала на все реплики кворума. При падении ноды лучше получить ошибку и повторить, чем читать «полуточные» данные.
Трансформации: из RAW в MART
Трансформации — пакетные (hourly), с временными staging-таблицами: сначала пишут во временную, потом — INSERT ... SELECT в целевую MART. Это позволяет переиспользовать инкремент без полного пересканирования и упростить откаты. Позже в стек добавили dbt, чтобы:
- централизовать SQL-логику и зависимости,
- управлять пересборками, lineage и документацией,
- совместить пакетную отчётность и real-time агрегаты (через dbt + materialized views).
Оркестрация Airflow: структура DAG’ов
- Отдельные DAG’и «Источник → S3» (по каждому источнику).
- Один большой DAG трансформаций, который стартует, когда данные «приехали» в S3. Так достигают явной управляемости зависимостей и изоляции от падений отдельного источника.
Доступы и безопасность: от Google Groups до RLS
- Права пользователей и ролей синхронизируются с Google Groups; в Superset используется DB_CONNECTION_MUTATOR, чтобы пробрасывать уникальные DB-пользователи per-user (аутентификация — Google OAuth).
- Row-level security реализуется штатными Row Policies ClickHouse: политика — это USING-фильтр, который применяется к конкретной таблице и привязан к ролям/пользователям; в результате пользователь видит только «свои» строки (важно: смысл есть для read-only ролей).
Пример RLS:
CREATE ROW POLICY sales_region_policy ON mart_revenue FOR SELECT USING region_id IN (SELECT region_id FROM allowed_regions WHERE user() = username); GRANT SELECT ON mart_revenue TO role_sales_readonly; APPLY ROW POLICY sales_region_policy TO role_sales_readonly;
BI-доступ: Superset + SQL-консоль ClickHouse
На старте — Superset для дашбордов, алёртов и ад-хок запросов; позже добавили SQL-консоль ClickHouse Cloud как более стабильный интерфейс для интерактивного SQL, плюс интеграции (например, GrowthBook для A/B). При необходимости — экспорт данных из CH → S3 → чтение Salesforce.
Эксплуатация и инфраструктура
Внутренняя инфраструктура упростили с помощью Docker: отдельные машины/контейнеры под Airflow Web/Worker, под Superset и т.д. Репозитории DAG’ов и SQL-скриптов синхронизируются каждые 5 сек. Прод/предпрод — отдельные сервисы ClickHouse Cloud (по 3 реплики), суммарно ~200 ГБ RAM.
Эволюция за год: больше real-time и «compute-compute separation»
За год DWH «повзрослел»: подключили 19 источников, dbt для сложных метрик и зависимости по времени, добавили real-time логи (вложенные JSON/массивы) рядом с пакетной отчётностью. В планах — декомпозировать вычисления на несколько сервисов ClickHouse (compute-compute separation): выделить read-only под BI, 24×7 read-write под критичные ETL и отдельный read-write под менее критичные задачи, которые можно масштабировать/останавливать для экономии.
Практические паттерны и мини-рецепты
Моделирование таблиц
- Факты: ReplicatedReplacingMergeTree, ORDER BY — на ключе отчётности (например, (customer_id, ts)), партиционирование по часу/дню. Версионная колонка (например, version_ts) позволит явно управлять «последней строкой».
- Справочники: также Replacing/ReplicatedReplacing; для часто обновляемых — перезаливка полным слепком каждый час (как делает ClickHouse для части таблиц).
Согласованное чтение трансформаций
В шагах трансформаций, где важна консистентность, используйте FINAL (точечно!) или стройте пайплайн так, чтобы читать уже «схлопнутые» результаты (например, после OPTIMIZE/мерджей — но избегайте массового OPTIMIZE FINAL, он дорог).
Кворум вставки
Включайте insert_quorum и, при необходимости, «последовательное чтение» кворумных вставок для отчётных шагов. Это снижает риск частичных чтений в распределённых репликах.
Загрузка из S3
Для объёмистых часов используйте s3Cluster() — распределит файлы по воркерам кластера. Храните «часовые» префиксы, чтобы легко перезаливать конкретный период.
Real-time агрегирование
Для «текущих» метрик — инкрементальные materialized views; для «переигрывания» логики по полному периоду — refreshable MV или пересборка через dbt-модели.
Типовые риски и как их закрыть
-
FINAL везде → дорогие запросы.
Митигировать: применять точечно; проектировать ключ/версию для Replacing, избегать тотальных OPTIMIZE FINAL. -
Полагаться на «само рассосётся» слияниями → дубли в онлайне.
Митигировать: в критичных шагах — SELECT ... FINAL, кворум вставки, staging-паттерн. -
Длинные DAG’и-«комбайны» → хрупкие зависимости.
Митигировать: разделять ingestion и трансформации; позже — вынести трансформации в dbt-граф, хранить SLA/SLI по узлам. -
«Сырые» JSON/вложенные массивы → сложные запросы.
Митигировать: нормализовать «тонкие» извлечения в отдельные колонки/матвью; стандартизировать схемы событий. -
RLS на write-ролях → обход ограничений.
Митигировать: RLS только для read-only; строгий RBAC; аудит грантов (WITH REPLACE). -
Стоимость 24×7 вычислений.
Митигировать: разделение compute-групп (read-only, критичные ETL, «холодные» ETL), автоскейл даун неактивных сервисов.
Чек-лист внедрения (по мотивам кейса)
- Слои и схема именования: договоритесь о RAW/CORE(MODEL)/MART и нейминге до первой загрузки.
- Ingestion в S3 по часам/дням; для справочников — «replace каждый N часов».
- Импорт через s3()/s3Cluster() → RAW (структура источника).
- Движки таблиц: ReplicatedReplacingMergeTree + версия; продумать ORDER BY/партиции.
- Идемпотентность: многократная вставка одного часа безопасна; трансформации терпимы к повторному запуску.
- Согласованность: insert_quorum; отказ от «auto»-поведения, если оно грозит частичными чтениями.
- Оркестрация: отдельные DAG’и на ingestion + один трансформационный DAG; затем — перенос логики в dbt.
- Доступы: RBAC + Row Policies; прокси-аутентификация из BI (например, Superset DB_CONNECTION_MUTATOR) и OAuth.
- BI: Superset для дашбордов; SQL-консоль CH для ad-hoc; при нужде — интеграции (GrowthBook, Salesforce).
- Экономия: разделение compute-групп по классам нагрузок (read-only vs 24×7 ETL vs «холодный» ETL).
Мини-FAQ (по опыту внедрений)
Q: «RAW — это S3 или таблицы?»
A: В кейсе S3 — landing (промежуточка). RAW — в ClickHouse, таблицы 1:1 к источнику. Так проще историзировать и версионировать, а также применять RLS/кворумные вставки и вести lineage.
Q: «Почему Replacing, а не Collapsing?»
A: Replacing проще для «последняя версия записи» (dedup by key/version). Collapsing требует знаков сверки и аккуратной логики событий. Для бухгалтерского «схлопывания» дельт Collapsing ок, но здесь важнее перезаливки и «последняя версия».
Q: «Можно ли обойтись без FINAL?»
A: В онлайне — не всегда: слияния асинхронны. Точка-точечно применяйте FINAL в критичных чтениях/агрегатах, проектируйте ключи/версии правильно, избегайте OPTIMIZE FINAL «в лоб».
Q: «Superset или родная SQL-консоль?»
A: Для дашбордов — Superset ок; для интерактивного SQL многие пользователи предпочли консоль ClickHouse Cloud (стабильнее/удобнее). Комбинируйте.
Q: «Где брать real-time агрегации?»
A: Материализованные представления для инкремента; либо dbt-модели + refreshable MV для периодических пересборок («переигрывания» логики).
Q: «Как экономить?»
A: Разделить вычислительные группы (read-only / 24×7 ETL / «по расписанию»), масштабировать и останавливать неактивные. В ClickHouse Cloud готовят compute-compute separation.
Итоги и что взять в свой проект
- Ставка на ELT: быстрый time-to-value, меньше внешней логики, больше прозрачности (вся трансформация — в SQL/ClickHouse+dbt).
- Идемпотентность и Replacing + FINAL там, где нужно: безопасные перезаливки и консистентные расчёты.
- Простой, но дисциплинированный слойинг: Landing(S3) → RAW → CORE/ODS → MART, явная политика именования.
- Доступы и RLS «по-взрослому»: централизованный RBAC, row policies, прокси-аутентификация из BI.
- Эволюция без «переписывания»: добавили dbt и real-time поверх существующей архитектуры, не ломая основу.
