Введение субъективно, но основано на действительности.
По нашему скромному мнению, хардкорных ребят из Banner Stat, рынок дата-инженерии сильно меняется. Еще лет пять назад, когда ты имел несколько миллионов строчек, ты хранил их в PSQL — почему? Потому что это супер надёжно. Когда ты заимел десятки миллионов строк, у тебя уже сильно болела голова: ты погружался в индексы, статистику таблиц, в конфиги самой БД и так далее. Возможно, данных стало настолько много, а любовь к PSQL была настолько большой, что ты поставил всё на кон и сразу пульнул pg_ctlcluster. Ну а если у тебя появлялись логи, файлы, возникнувший ML со своими чертовыми векторами или что-то другое, ты обрастал большим пулом решений, так как все это хранить в PSQL уже совсем перебор (даже не смотря на спец решения по типу pg_vector). Не дай бог, данных были триллионы — и ты решил пойти сразу в Hadoop. И тут, о боже, управление всем этим пожирало твое время. Тебе пришлось завезти Airflow или другие оркестраторы. И вот тебе уже нужен Data Engineer, а скорее ты уже сам он.
Какая главная проблема Data Engineer? Ну, во-первых, для средних компаний он дорогой, приходится кому-то соединять эти функции. Во-вторых, это некоторая прослойка между разрабами, которые обычно поставляют данные в БД, и аналитикой, которые их используют, а это удлинение ��епочки, которое, как правило, замедляет процесс. А какая главная цель? Строить витринки для аналитиков. А кто лучше всех знает, что должно быть в витринке? Аналитик, конечно — он же заказчик задачи. Но, блэт, он не знает Hadoop, Spark, а от Scala и Java его начинает подташнивать. Слава богу, хоть PySpark ещё есть. Но когда ты говоришь аналитику "SQL", на его душе сразу растекается мёд.
По-моему, именно так рассуждали ребята из dbt. Именно с его появлением Data Engineer начал свое постепенное угасание: аналитики начали писать свои пайплайны, пусть не всегда инженерно успешные. При этом аналитик чётко понимает природу данных, бизнес-логику, и он быстрый — это вам не медленный разраб, который две недели деплоит сервис. С точки зрения бизнеса все шикарно. Но стойте: dbt сделает жизнь прекрасной в рамках одной БД, пока что про кроссплатформенность можно забыть. Но как вам идея: DWH в одной БД, все туда, и логи, и вектора, и обычные таблички. И вот тут на сцену выходит ClickHouse.
Мы в компании беспощадно сидим на нём уже два года. Все эти дни я лично наблюдаю за развитием проекта. И могу выявить вот такую интересную тенденцию.
На момент нашего входа в этот удивительный мир CH, он имел очень четкую позицию: "Мы топ-1 OLAP хранилище, поэтому нет ACID, хочешь гарантии, мы не для этого, сначала посчитай, все сделай, запиши в меня все денормализовано, и я обеспечу лучшую скорость!". Собственно мы так и делали.
Добровольное страдание - это, когда при подсчете количества уникальных значений с 15 минут в PSQL, я увидел 0.03 мс в CH. Это как поменять матиз на 911-ый.
Хорошо. Но что произошло далее? ClickHouse начал обрастать очень странными штуками для колоночной БД: полная поддержка вью — как инкрементальных, так и обновляемых, шедулинг операций из коробки, словари, транзакционность на движках MergeTree, join'ы типов, так сейчас еще и оконные view завезли, ну только foreign keys осталось добавить. В доке ClickHouse потихоньку начали появляться материалы о построении DWH, например, реализация медальона от датабрикс.
По нашему мнению, ClickHouse сейчас становится полноценным DWH. Туда сыпят всё: количество приложений для хранения сокращается, они всё больше не нужны, всё можно хранить в CH — логи туда, Kafka туда, события туда, эмбеддинги — да тоже засунем. И что? А то, что от классической системы начинаем уходить, да и Hadoop больше не так нужен для тех данных, которые можно хранить в табличном виде, ведь CH идеально ставится на кластеры. А коннекторы ClickHouse, которые есть, блэт, для всего — Kafka, PSQL, Hadoop, S3 и др. При этом появился dbt-clickhouse, который даёт аналитикам ну прям размах, надо будет только привыкнуть к верблюжьему регистру в функциях.
Но, боль все же остается на CH невозможно обустроить Data Vault, привычный всем олдам, а вы тут говорите, пойдемте все в CH, нет, вы конечно, можете попытаться организовать контроль всего этого добра с приложения, но задайте себе вопрос: реально ли вам это надо? Действительно при написании каждого нового сервиса вы хотите думать о консистентности БД? В этом году мы полностью перестроили свое DHW и решили в том числе эту проблему. Этот материал реальный практический пример, как CH может поглотить в себя все, к тому же рассказанное является примером чистого Medallion в CH, конкретных примеров, которых пока что довольно мало в рунете.
Боль, моя боль
Продукт Banner Stat запустился в 2024 году, тогда в нашем стеке был только PSQL, никаких оркестраторов и никаких других хранилищ. Данных было не так много, 22.5 млн строк за весь 2024 год, ситуация немного усугублялась большим количеством текстовой информации (конкретно об этом чуть позже). Примерно, к середине 2024 года, мы начинаем испытывать страдания, а вместе с этим приобретаем четкое понимание, что классика Data Vault (высокая нормализация) нам не подходит. Так как join всех таблиц для составления полного клиентского отчета формируется в среднем 55 минут, если мы не упали с OOM)))). И первой нашей попыткой исправить ситуацию была денормализация основного отчета прямо в PSQL, уже тогда появился Airflow, он просто собирал все данные и вставлял в репортную таблицу ежедневно. Это работало, под капотом у Airflow был polars, что давало не хватать OOM и делать все несколько быстрее. Однако это решало лишь часть проблемы, при этом накладывая на нас новые ограничения:
Как вы думаете? Как PSQL справляется с текстовым поиском в денормализованной таблице на 22.5 млн строк. Ответ: никак. В среднем поиск по URL занимал около 15 минут. Мы решали это триграммами и подобными GIN. Тут также на наших тестах неплохим вариантов был md5.
А с подсчетом уникальных значений по тому же столбцу? Ответ: 15 минут. Причем в обычный индекс вы не влезете по памяти.
К репортной денормализации у нас, как полагается, было огромное кол-во запросов, а значит большое количество индексов. В какой-то момент сама таблица весила 105ГБ, при весе индексов в 102ГБ.
А еще, вам обязательно захочется добавить новое поле. В результате чего вы будете переписывать DDL таблицы и основные DTO в Airflow. Но это пол беды, реальная боль наступит, когда вы будете обновлять все ретроспективные данные. У нас это занимало примерно неделю)
Мы закопались в индексах и в статистиках таблицы. В какой-то момент это помогало оптимизировать часть запросов. Мы начинали выжимать из PSQL последнее, наши усилия давали слишком маленький результат и мы это понимали. Надо было менять парадигму.
Именно в начале 2025 года, сразу после новогодних началось переписывание Airflow, и там появился долгожданный коннектор CH. Теперь все отчетные данные лились в OLAP хранилище. Мы зажили, как никогда. Фильтрация и группировки перестали быть болью, кончилась мультизадачность, из-за того, что ты сидишь и ждешь у компа, пока отработает запрос, перебиваясь другой задачей. Однако "но" все же остались. Во-первых, сырых данных в PSQL становилось все больше, запросы на fetch работали долго, во-вторых, мы динамичны, добавление новой реализации или новой колонки в БД не редкость. И каждый раз это боль, надо обновлять Airflow, новые модели, новые DTO, потом миграция схемы в CH, и потом обновление ретроспективных данных, если это возможно. Реализация такого могла занимать вплоть до недели времени.
Отдельно стоит отметить, что в PSQL все еще содержит часть важнейшей информации, например, это обновляемые справочники, которые нельзя записать в репорт, из-за постоянной модерации, нужен join. Но держать постоянно модерируемый справочник в CH смерть, тогда давайте читать данные на лету с помощью PSQL коннекторов. Кстати реализованы они в CH очень классно, но все же это чтение на лету, что равно замедление join.
Для конкретики давайте сведем основные метрики в табличку:
Показатель | 2024, PSQL Data Vault | 2025, PSQL денормализация | 2025, PSQL + CH |
|---|---|---|---|
Размер отчетной таблицы | 44 ГБ (общий вес всех таблиц) | 105 ГБ - 102 ГБ на индексы | 40 ГБ |
Время подсчёта уникальных значений (count distinct по строковому полю) | 10-25 мин | 15-35 мин | 0.03 мс |
Добавление нового поля в таблицу | 0.5 часа | от 4 часов до 7 дней | от 4 часов до 7 дней |
Время построения денормализованной витрины для клиента | не строили | 55 мин - 1.5 часа или OOM | 55 мин - 1.5 часа или OOM |
join между фактами и 7 справочниками | 55 мин - 1.5 часа или OOM | не было | не было |
Мы можем заметить, что последнее наше решение (хранение денормализованного отчета в CH), решило часть наших проблем, оставив фактически только две:
Долго добавление новой сущности в финальный результат
Долгое построение финальной витрины
И, как кажется, обе этих проблемы можно решить полным переходом на CH, при это не потеряв старые плюшки, естественно, туда мы и пошли.
С чем работаем?
Да, всё очень просто. Banner Stat - продукт, анализирующий рекламную активность брендов с помощью сбора и хранения их рекламных размещений на просторах интернета . Кстати, в прошлой статье можете почитать подробнее об этом удивительном рынке аукционной закупки. В общем что бы что-то мерить, надо это что-то иметь. Ну а как вы будете "иметь" рекламу? Парсить, конечно. Именно этот болезненный и неприятный вид добычи информации, выступает у нас источником сырых данных.
Объемы
Собственно, давайте к делу. Главным источником полезной информации являются парсеры, причём в разных сценариях. Кто-то парсит обычные программатик-баннеры, кто-то парсит поисковую выдачу и собирает контекстную рекламу, кто-то парсит рекламу in-app, кто-то парсит товарную полку на маркетплейсах. И количество таких реализаций только растёт, так как мы хотим спарсить всё, что видим. Сегодня думаем о коптерах, которые будут скринить баннеры наружки на улицах, чтобы точно понимать, кто рекламирует гречку на третьем транспортном. Последнее было шуткой) Хотя кто знает...
На текущий момент все эти парсеры возвращают похожий набор информации, но со своими особенностями. Например, у кого-то есть search-запрос для поиска, а у кого-то нет. Это отдельная проблема, которую мы затронем в пункте про требования. А пока давайте посмотрим на цифры, потому что абстракции — это хорошо, а конкретика в гигабайтах — лучше.
Примерные текущие объёмы (январь 2026):
Программатик-баннеры: 62 млн строк в одной только таблице сырых данных, ~15ГБ
Маркетплейсы (товарные полки): 3 млн строк, ~2GB (JSON'ы с ценами, остатками).
Поисковая выдача (Яндекс/Google): 1 млн строк, ~1GB (тут много текста)
Старая схема: Data Vault и ад джоинов
Теперь про то, как это хранилось раньше. У нас была классическая нормализованная схема ( Data Vault). Выглядело это примерно так (показана лишь часть схемы):
Тут довольно много
-- Хабы (сущности) table hubs_screenshots { screenshot_id bigint pk image_url varchar hash varchar created_at timestamp } table hubs_destination_url { destination_url_id bigint pk destination_url varchar } table hubs_advertiser { advertiser_id bigint pk name varchar } table hubs_brands { brand_id bigint pk name varchar } table hubs_categories { category_id bigint pk name varchar } table hubs_sites { site_id bigint pk site_url text } table hubs_pages { page_id bigint pk page_url text site_id int } table hubs_xpaths { xpath_id bigint pk xpath text site_id int } ... -- Линки (связи) table links_screenshot_url { link_screenshot_url_id bigint pk screenshot_id bigint [ref: > hubs_screenshots.screenshot_id] destination_url_id bigint [ref: > hubs_destination_url.destination_url_id] load_date timestamp record_source varchar } table links_screenshot_advertiser { link_screenshot_advertiser_id bigint pk screenshot_id bigint [ref: > hubs_screenshots.screenshot_id] advertiser_id bigint [ref: > hubs_advertiser.advertiser_id] load_date timestamp record_source varchar } table links_screenshot_brand { link_screenshot_brand_id bigint pk screenshot_id bigint [ref: > hubs_screenshots.screenshot_id] brand_id bigint [ref: > hubs_brands.brand_id] load_date timestamp record_source varchar } table links_screenshot_category { link_screenshot_category_id bigint pk screenshot_id bigint [ref: > hubs_screenshots.screenshot_id] category_id bigint [ref: > hubs_categories.category_id] load_date timestamp record_source varchar } table links_page_site { link_page_site_id bigint pk page_id bigint [ref: > hubs_pages.page_id] site_id bigint [ref: > hubs_sites.site_id] load_date timestamp record_source varchar } table links_xpath_site { link_xpath_site_id bigint pk xpath_id bigint [ref: > hubs_xpaths.xpath_id] site_id bigint [ref: > hubs_sites.site_id] load_date timestamp record_source varchar } table links_advertiser_brand { link_advertiser_brand_id bigint pk advertiser_id bigint [ref: > hubs_advertiser.advertiser_id] brand_id bigint [ref: > hubs_brands.brand_id] load_date timestamp record_source varchar } ... -- Сателлиты table satellites_screenshots { screenshot_id bigint [ref: > hubs_screenshots.screenshot_id] image_width int image_height int file_size bigint mime_type varchar screenshot_date timestamp parsing_status varchar error_message text load_date timestamp record_source varchar } -- Сателлит для целевых URL table satellites_destination_url { destination_url_id bigint [ref: > hubs_destination_url.destination_url_id] protocol varchar domain varchar path text query_params text is_active boolean last_checked timestamp load_date timestamp record_source varchar } ...
Красивая теория, да? Всё нормализовано, ничего не дублируется, историчность сохранена. Но во введении мы уже поняли к чему она нас привела)
Типы данных и их особенности
Вернёмся к тому, что возвращают парсеры. Основной набор полей у всех одинаковый:
image_url— String, прямая ссылка на креативdestination_url— String, куда ведёт баннерredirects— Array (редиректы бывают длиной до 10 уровней)page_url— String, где увиделиlinear_size— JSON (ширина/высота, позиция на экране)banner_type— Enum ('TGB', 'DISPLAY AD', ...)detected_at— DateTime
Но дальше каждый парсер добавляет свою специфику:
Поисковый парсер (Яндекс):
{ "search_query": "купить айфон дешево", "textInfo": "iPhone 15 Pro - 85 000 руб. Доставка бесплатно...", }
Парсер товарных полок:
{ "sku": 1488228, "price": 1299.00, "old_price": 2599.00, "seller": "ООО Рога и Копыта", "delivery_date": "2025-02-02" }
И вот тебе нужно всё это положить в одну таблицу, не ломая существующие парсеры при добавлении нового поля "доставка в пункт выдачи" для маркетплейсов.
В последующем на эти исходные данные навешивается еще несколько задач, которые будут возвращаться свои данные:
Дедупликация - каждое изображение будет дедуплицировано, на выходе будет иметь кластер, которые объединяет дубликаты
Атрибуция - каждое размещение получит бренд, рекламодатель, категорию, рекламную кампанию
Описание изображений и OCR - каждое изображение будет описано и получен с него текст
Определение битых изображений - каждое изображение будет проанализировано на блур, изображения не пригодные к дальнейшему анализу будут отмечены специальной меткой.
Как вы понимаете в уже классическом подходе Data Vault это каждый раз отдельный хаб и линк. И огромный джоин с нашими исходными данными.
Требования к решению
Хорошо, мы поняли, что парсим много и парсим разнообразно. Главный вопрос - как с этим жить? А жить мы хотим вот так:
Zero downtime. Добавляем новое поле в парсер — ни один сервис не останавливается. DDL таблицы не меняется. Не более 30 минут на добавление нового поля в финальную таблицу.
Масштабирование. Новый парсер (маркетплейс, не важно) добавляется без правки схемы БД.
Гарантированная вставка.
Денормализация на выходе.
Runtime. Данные за вчера видим с утра. Максимум 6 часов от сбора до витрины.
Алертинг. Видим, где данные застряли. Если лаг больше 10 минут — пейджер в Telegram.
При всем при этом готовы примириться:
Отказываемся от UPDATE/DELETE в аналитике. Только append-only. Ошиблись в расчётах или неправильно заполнили таблицу, управляемую админом — пересоздаём партицию.
Выбираем один стек и сидим на нём. Если завтра CH умрёт — будем думать тогда, а не расписывать абстракции на все случаи жизни.
🟠⚪️🟡Medalion
Теперь собираем критерии в кучу и идем писать новое решение. Мы осознанно отказываемся от оркестраторов в виде Airflow — это долго, во-первых, в силу extract и load, во-вторых, затраты на разработку. Отказываемся в сторону системы views в CH. Здесь, конечно, можно было бы выбрать dbt, но мы считаем, что для нас он пока излишний, сначала обкатаем механизм на более низком уровне. Вот когда менеджерить все эти views в CH станет тяжело, тогда совершим переход.
В качестве хранения любой витрины выбираем ClickHouse — для нас это отличный инструмент масштабирования, CH будет еще очень долго поддерживать наши объемы, если не всю жизнь.
Не обойдемся, без классического PSQL, там останутся наши хабы, которые менеджерят люди, так поддержим операции UPDATE/DELETE. Плюс с PSQL сильно удобнее работать из сервисов, чем с CH.
Уже здесь зададимся вопросом: данные из PSQL должны оказаться в CH для построения витрины. Как это сделать, если мы отказываемся от оркестраторов? Явно нужен CDC, который прекрасно реализует PeerDB. Компания, которая в 2024 году была куплена CH. Ха, в CH точно что-то знают.
PeerDB поддерживает CDC, Query Replication и Xmin. У нас классический CDC и Query Replication (обычно, когда нужны join) — лаг репликации до 30 секунд в штатном режиме. На текущий момент (январь 2026 года), PeerDB довольно сырое решение со своими багами и особенностями, работа с которыми тянет на отдельную статью, в силу немногословной документации, однако мы верим в развитие, особенно после покупки компании кликхаусом.
Хорошо, стек определили, давайте перейдем к схеме бронзового уровня.
🟠Bronze
Парсеры разных цветов отвечают за разные реализации. Все отдают результаты в Менеджер — координатор системы парсинга. Он знает о каждом парсере, дает задачи и пишет результаты в raw_data. Достигается запись разных реализаций в одну таблицу благодаря JSON.
Менеджер проверяет схему перед записью — если парсер прислал payload без image_url или с 50MB строкой вместо URL, он дропает запись в Dead Letter Queue (отдельная таблица в PSQL), а не ломает всю партицию. Мониторинг следит за структурой: если в detector вдруг появилось новое поле drone_height (от коптеров) — алерт в Telegram, мы добавляем его в JSON Schema валидации за 5 минут, не трогая DDL.
DDL таблицы:
create table bronze_raw_data ( raw_id UUID, detection_type Enum8('USER' = 2, 'ROBOT' = 1), detector JSON, detected_at DateTime, context JSON, payload JSON, created_at DateTime default now64() ) engine = MergeTree() PARTITION BY toYYYYMM(detected_at) ORDER BY (detected_at, raw_id);
Партиции получаются по 800MB–1.2GB, что ок для CH. Сама таблица удивительно проста. Посмотрим на логику размышлений:
Каждый парсер это кто-то, вот он
detector.Каждый парсит в каком-то контексте, вот
context, контекст задается задачей, которую получилdetectorот manager.Каждый парсит что-то, вот
payload.
Пример для programmatic баннеров:
{ "detector": { "profile_id": 1, "profile_name": "Default", "parser_id": 1, "parser_name": "S_1", "parsing_type": "programmatic", "detection_type": "robot", }, "context": { "page_id": 1, "page_url": "https://3dnews.ru", "page_type": "programmatic", "xpath_id": 1, "xpath_type": "IMAGE", "proxy_id": 1, }, "payload": { "target_url_first": "https://3dnews.ru/...", "redirects": { "urls": [ "https://3dnews.ru/..." ] }, "destination_url_id": 1, "destination_url": "https://pro.zamm.ru/catalog/...", "screenshot_url_id": 1, "screenshot_url": "https://2d513dc7-...", }, }
Важный момент: Упрощенная схема на картинке немного врет — DataVault-таблицы живут в PSQL, а PeerDB зеркалит их в CH. Сервисы-дополнители (дедупликация, атрибуция) берут данные из raw_data и пишут в свои таблицы хабы и линки в PSQL.
Прекрасно: достигли записи любого результата в одну таблицу, заведение новой реализации достигается только изменением DTO в менеджер, и никаких миграций. Погнали на Silver.
⚪️Silver
На Silver происходит подготовка данных к финальному Gold-слою. Три важнейших операции: фильтрация, соединение, расчёт или пред расчет. Всё происходит с помощью инкрементальных материализованных представлений. Они позволяют соответствовать требованиям runtime — момент вычисления Silver-таблицы происходит в тот же момент, что и insert в raw_data.
Важное ограничение: с такими условиями система может быть только append-only. Incremental MV в CH не умеют GROUP BY — агрегация внутри них невозможна по архитектуре (представление видит только вставляемый блок строк, не всю таблицу). Так что если вы думаете "а сделаю я тут count() по группам прямо в потоке" — облом. Именно поэтому мы выносим агрегаты в отдельные таблицы (про kutm ниже), а в основном VIEW делаем только фильтрацию и обогащение (join).
В силу этого ограничения также важно отметить, что в случае неправильного ведения таблиц в PSQL, join с ошибочным значением на сильвер уровне будет необратимым, так как сильвер невозможно перестроить, в отличие от голды. Важно иметь механизмы перестройки сильвер уровня, в нашем случае это может быть простой повторный insert всей ретроспективы из бронзового уровня.
Видим, что под каждую реализацию парсинга мы выделяем свои таблицы на Silver. Программатик идёт в silver_programmatic_banners, маркетплейсы в silver_marketplace_banners и т.д.
Почему? Потому что так удобно строить витрину в Gold. Этап разделения мы вынесли с этапа записи (где всё в одной JSON-таблице) на этап трансформации — с разработчика на аналитика, с Java/Rust/Python на обычный SQL. Это было одним из лучших решений: прокидывание нового столбца с raw на report начали занимать не несколько дней, а 1-2 часа в худшем случае.
Не сказали еще про этап расчета, фактически здесь мы говорим о пред расчете, который будет необходим на голде. Смотрим на VIEW для программатика. Здесь мы не только фильтруем мусор (пустые URL, капчи), но и готовим пред расчёты для Gold:
create materialized view banner_stat.bronze_programmatic_to_silver to banner_stat.silver_programmatic_banners as select *, -- Предобработка полей arrayConcat( if(isNotNull(toString(payload.taget_url_first)) AND (lengthUTF8(toString(payload.taget_url_first)) > 0), [toString(payload.taget_url_first)], []), ifNull(JSONExtract(toString(payload.redirects), 'urls', 'Array(String)'), [])) AS redirects, replaceOne(arrayFirst(x -> startsWith(x, 'utm_source='), params), 'utm_source=', '') -- Обогащение данных новыми сущностями domain(destination_url) as destination_domain, -- Обработка некорректных числовых значений toUInt32(greatest(ifNull(JSONExtract(toString(payload.banner_loc), 'top_offset', 'Int32'), 0), 0)) AS top_offset, -- Вот ПОЛЯ для того самого пред расчета cityHash64(concat(concat(splitByChar('.', destination_url)[-2], '.', splitByChar('.', destination_url)[-1]), placement_site, utm_source, utm_campaign, utm_medium)) AS hash_calc_kutm_per_group, cityHash64(concat(splitByChar('.', destination_url)[-2], '.', splitByChar('.', destination_url)[-1])) AS hash_calc_kutm_per_domain, from banner_stat.bronze_raw_data brd -- Джоин с таблицей администрируемой пользователями join banner_stat.silver_peer_xpaths as spx on toUInt32(brd.context.xpath_id) = spx.id -- Необходимые фильтры where (toString(detector.parsing_type) = 'programmatic') and (toString(context.page_type) = 'programmatic') and (length(toString(payload.destination_url)) > 2) and (toString(payload.destination_url) not ilike 'about:blank%%') and (toString(payload.destination_url) not ilike '%ads-captcha%') -- Защита от битого JSON: если не распарсилось поле image_url - не пускаем дальше AND notEmpty(JSONExtractString(payload, 'image_url'))
Здесь важно: мы жёстко валидируем JSON на лету. Если парсер прислал битый UTF-8 или отсутствует
image_url— строка не проходит фильтр и не попадает в Silver. Это better than sorry: лучше потерять 0.01% данных, чем засрать витрину NULL'ами, которые потом ломают JOIN'ы в Gold.
Надеюсь значение операций кроме пред расчета - понятно. Посмотрим внимательно на получившиеся поля для пред расчета это хеш по��я от некоторой группы столбцов. Для чего это. Одной из основным метрик любой нашей реализации является OTS (opportunity to see) - возможность увидеть. Она рассчитывается по разработанной нами методологии с помощью нескольких коэффициентов. Одним из таких коэффициентов является kutm.
kutmпризван описать диверсифицированность размещений каждого рекламодателя на конкретной площадке. Коэффициент вычисляется как отношение количества размещений бренда с одинаковыми utm_source, utm_campaign, utm_medium на конкретной площадке ко всем размещениям данного бренда.
Уже по определению понимаем, что это очень трудоемкая операция для кликхаус. Так как это оконная функция, причем по всей таблице (как минимум по одной месячной партиции), которую к тому же невозможно посчитать на сильвер (в инкрементальных представлениях), так как вью не владеет всей таблицей, а только вставляемой частью. Поэтому мы смело переносим операцию в пред расчет.
Смотрите, коэффициент фактически вычисляется из дроби, где числитель и знаменатель и есть наши хеши, точнее их кол-во. Круто, давайте сделаем еще несколько view:
CREATE MATERIALIZED VIEW banner_stat.silver_programmatic_kutm_per_domain to banner_stat.silver_kutm_per_domain AS SELECT month, hash_calc_kutm_per_domain, 1 AS count FROM banner_stat.silver_programmatic_banners; CREATE MATERIALIZED VIEW banner_stat.silver_programmatic_kutm_per_group TO banner_stat.silver_kutm_per_group AS SELECT month, hash_calc_kutm_per_group, 1 AS count FROM banner_stat.silver_programmatic_banners;
Вне подозрения, что таблицы silver_kutm_per_domain и silver_kutm_per_group должны быть с движком SummarizeMergeTree, чтобы получать то, самое количество.
В итоге, вместо того, чтобы считать огромное окно по всей таблице, нам нужен join с таблицами и просто деление одного показателя на другой
Почему
cityHash64? Для kutm нам нужен быстрый хеш на группировку.sipHash128безопаснее, но медленнее, аMD5— издевательство над CPU. КоллизииcityHashнас не очень волнуют: даже если случится 1 на 10^9, мы просто чуть-чуть скорректируем коэффициент для супер-редкого домена. Зато скорость!
Тут замечу, что можно было воспользоваться новой экспериментальной реализацией CH оконное представление, размышления об этом оставляю читателю на откуп.
Таким образом все операции, сложные для ClickHouse (оконные функции, сложные JOIN'ы с агрегацией), мы разбиваем и рассчитываем на Silver. Где-то больше, где-то меньше, но принцип один: на Gold должно остаться только финальное соединение.
🟡Gold
Тут всё просто — последние JOIN'ы с таблицами сервисов, финальное обогащение данных и расчёт метрик. Делаем это через обновляемые материализованные представления (Refreshable MV). Они пересчитываются по расписанию — раз в день, раз в час, как настроишь.
Благо, тут ClickHouse наконец-то зашёл дальше PostgreSQL — там Refreshable MV вообще нет из коробки (тут я имею в виду обновление по шедулингу), а тут можно сказать REFRESH EVERY 1 DAY и идти пить кофе.
Во время перестроения CH создаёт временную таблицу с новыми данными, а потом делает atomic rename — старая таблица уходит в мусор, новая становится на её место. Это значит, что запросы к витрине не падают — они либо читают старую версию, либо (после rename) новую. Но если в момент swap занимает 10 минут — он отвалится по таймауту. Поэтому лучше выставить
max_execution_timeдля тяжёлых аналитических запросов.
Есть ещё грабли: если у тебя внутри MV используется
FINAL(а он нужен дляSummingMergeTree, чтобы получить агрегированные значения), перестроение жрёт память как не в себя.
Посмотрим, на пример:
CREATE MATERIALIZED VIEW banner_stat.silver_programmatic_to_gold REFRESH EVERY 1 DAY TO banner_stat.gold_programmatic_banners as with -- Много, много view расчета для бизнес-показателей visits as ( select ), first_appearance as ( select ), site_cutoff as ( select ), visits_filter as ( select ), avg_visits_per_site as ( select ), kvisits as ( select ), coeff_data as ( select -- Необходимы в витрине поля *, -- А вот и наш kutm if( domain_kutm.count = 0 OR group_kutm.count = 0, 1, if( (group_kutm.count / domain_kutm.count) >= 0.1, pow(0.1 / (group_kutm.count / domain_kutm.count), 2), 1 ) ) AS kutm, -- Поля из таблиц сервисов clusters_dedup.cluster_id, pa.brand as brand, pa.advertiser as advertiser, pa.category as category from concat_data cd left join banner_stat.silver_peer_aliases as pa on cd.base_url_id = pa.base_url_id left join banner_stat.silver_screenshots_clusters as clusters_dedup on cd.screenshot_url_id = toUInt64(clusters_dedup.screenshot_id) left join (select * from banner_stat.silver_kutm_per_group final) as group_kutm on (group_kutm.month = cd.month) and (group_kutm.hash_calc_kutm_per_group = cd.hash_calc_kutm_per_group) left join (select * from banner_stat.silver_kutm_per_domain final) as domain_kutm on (domain_kutm.month = cd.month) and (domain_kutm.hash_calc_kutm_per_domain = cd.hash_calc_kutm_per_domain) ) select * from coeff_data SETTINGS max_memory_usage = 25000000000;
Здесь сразу же в настройках указываем повышенное значение возможной RAM, так как джоины будут большие учитывая перестройку все таблицы каждый день. Если джоин слишком большой можно указать возможность скидывания результатов джоина на диск:
SETTINGS max_bytes_in_join = ...
В целом, это не гуд практика, слишком много RAM выделено, благо сервер под БД у нас довольно мощный, но все же такие вещи лучше разбивать не на CTE, а на промежуточные View. На текущий момент view обновляется около 5 минут, что нас вполне устраивает. Однако в дальнейшем будем такие большие запросы разбивать.
Важно, что если ваше View завалится, то оно вам об этом не поспешит сообщить. Поэтому важно мониторить логи CH и
system.view_refreshes
Что получаем в итоге — жирную, плоскую, денормализованную таблицу, которую клиенты забирают через API, а мы делаем быстрые агрегации на дашборды:
create table gold_programmatic_banners ( banner_stat_id UUID, image_url String, destination_url String, redirects Array(String), page_url String, top_offset UInt32, square UInt32, width UInt32, height UInt32, xpath_type String, banner_type String, detected_at DateTime, date Date, month Date, brand LowCardinality(String), advertiser LowCardinality(String), category LowCardinality(String), destination_domain String, placement_site String, utm_source String, utm_medium String, utm_campaign String, utm_term String, utm_content String, params Array(String), kvisits Float32, kdepth Float32, ksquare Float32, koffset Float32, kformat Float32, kformat_up Float32, kutm Float32, ots Float32, ots_utm Float32, ots_up Float32, ots_utm_up Float32, screenshot_id UInt64, base_url_id UInt64, cluster_id UInt64, brand_id UInt64, advertiser_id UInt64, category_id UInt64, site_id UInt64, parser_name LowCardinality(String), profile_name LowCardinality(String), ) engine = MergeTree() PARTITION BY month ORDER BY (date, destination_domain, placement_site, base_url_id, screenshot_id, brand_id, advertiser_id, category_id);
Отдельно скажу, что это не конец — из таблиц Gold мы строим ещё десятки специализированных витрин для UI, Excel-выгрузок и ML-датасетов. Но это уже другая история.
Итого по архитектуре сейчас:
31 инкрементальных MV (Bronze → Silver, мгновенные)
25 refreshable MV (Silver → Gold, с расписанием)
1 таблица Bronze (сырые JSON'ы)
8 таблиц Silver (разбиты по типам парсеров)
15 таблиц Gold (финальные витрины)
Что получили в итоге?
Показатель | 2024, PSQL Data Vault | 2025, PSQL денормализация | 2025, PSQL + CH | DWH CH |
|---|---|---|---|---|
Размер отчетной таблицы | 44 ГБ (общий вес всех таблиц) | 105 ГБ - 102 ГБ на индексы | 40 ГБ | 40 ГБ |
Время подсчёта уникальных значений (count distinct по строковому полю) | 10-25 мин | 15-35 мин | 0.03 мс | 0.03 мс |
Добавление нового поля в таблицу | 0.5 часа | от 4 часов до 7 дней | от 4 часов до 7 дней | от 15 минут до 2 часов |
Время построения денормализованной витрины для клиента | не строили | 55 мин - 1.5 часа или OOM | 55 мин - 1.5 часа или OOM | 5 - 10 минут |
join между фактами и 7 справочниками | 55 мин - 1.5 часа или OOM | не было | не было | 5 - 10 минут |
Таким образом мы полностью закрываем требования, зафиксированные в начале статьи. Но, всегда есть НО:
JSON и типизация. Парсер прислал мусор, CH молча вставил - важно иметь валидацию на сервисе и мониторинг всех схем.
PeerDB рискует. Если CH ляжет на пару часов (обновление, например), WAL в PSQL разрастается до терабайта. Вообще PeerDB очень жестко необходимо контролировать по памяти, которую он занимает в PSQL.
МНОГО VIEW. Если оставлять все на нативном CH, рано или поздно это станет неподдерживаемым, фактически dbt призван решить эту проблему.
Silver-layer инкрементален. Ошибка в PSQL таблицах, которые тянет в PeerDB зафиксируется в сильвер слое, важно уметь правильно и безопасно его перестраивать.
Итак, коротко, но факт: ClickHouse уже не только OLAP, он полноценное DWH, которое может вполне обойтись без оркестраторов, без страха ALTER TABLE и потери данных, так еще и по скорости обработки с ним мало кто сравнится.
Но сила — не в скорости. Сила — в том, чтобы не упасть под её весом.
