Все

Как мы храним 20000+ метрик и миллиарды комбинаций разрезов в одной таблице. Часть 2

2025-05-29 12:05 Статьи Big Data
Привет! Меня зовут Влад Божьев, я старший разработчик юнита АБ-тестирования Авито. В нашей команде мы ежедневно работаем с по-настоящему большими объёмами данных – это не просто фигура речи, это наша реальность. Мы помогаем создавать метрики, которые помогают бизнесу не «гадать на кофейной гуще», а принимать взвешенные решения, основанные на данных.
Один из наших ключевых инструментов – M42, сервис для визуализации метрик. Он позволяет быстро проверять гипотезы, анализировать отклонения и оценивать инициативы. M42 – это часть единого аналитического продукта Trisigma, вторая его половина – платформа для A/B-тестирования. Trisigma считает и визуализирует метрики в конкретных A/B-экспериментах, а M42 помогает принимать решения глобально по продукту. Видео с подробным разбором продукта и его возможностей:

This browser does not support the video element.

В этой статье мы с вами погружаемся в самое сердце M42 и разбираем, как же там хранятся отчеты по метрикам. Это не просто рассказ, это почти детективная история о том, как мы искали оптимальное решение.
В нашем семантическом слое данных больше 20 000 метрик, и есть десятки разрезов для каждой из них. В этой статье рассказываю, как мы храним терабайты данных и автоматизируем добавление новых разрезов в отчёт M42.
Первую часть статьи можно прочитать здесь.

Структура таблицы М42

Итак, мы определились, как будем хранить и идентифицировать уникальные комбинации разрезов (слайсы). Теперь нам необходимо продумать структуру самой таблицы с отчетом, где будут лежать фактические значения метрик. Наша цель – получить наилучшую производительность и возможность масштабировать количество обрабатываемых разрезов по метрикам.
Пойдем по шагам. Что нам нужно хранить в каждой строке этой таблицы?
  1. Дата, к которой относится рассчитанное значение метрики: metric_date Date. Это основа для анализа временных рядов.
  2. Идентификатор метрики: metric_id UInt... (тип подбираем по количеству метрик, например, UInt32). Это числовой ID, который ссылается на справочник метрик, где хранится ее название, формула расчета и так далее.
  3. Значение метрики: metric_value Int…
  4. Идентификаторы дименшенов (разрезов): Вместо slice_id, который мы обсуждали для гипотетического JSON-хранилища, мы будем хранить значения каждого дименшена в отдельной колонке, такая денормализация позволит ускорить наши запросы к отчёту ещё сильнее устранив накладные расходы на join.

  6. Например: region_id Int64, category_id Int64, platform_id Int64, и так далее. Это прямое следствие нашего предыдущего решения отказаться от JSON-слайсов в пользу явных колонок. Такой подход делает структуру таблицы фактов максимально плоской и быстрой.
Так как мы собираемся хранить эту таблицу с отчетом M42 в ClickHouse для быстрого чтения, нам необходимо определиться с движком таблицы.
Помимо регулярного расчёта за прошлый период (например, за вчерашний день), мы хотим иметь возможность периодически пересчитывать данные за более старые периоды. Это может понадобиться, если изменилась логика расчета какой-то метрики, или если были найдены и исправлены ошибки в исходных данных. При таком пересчете есть шанс возникновения дублей в таблице с отчётом M42 (старая запись и новая, исправленная).
Такие дубли не должны оказывать влияния на итоговые цифры в отчетах (то есть мы не хотим случайно просуммировать старое и новое значение), однако они будут накапливаться и неоправданно увеличивать размер таблицы и замедлять запросы.
Мы можем использовать движок ReplacingMergeTree для митигации этого риска. Его особенность в том, что при слиянии кусков данных (parts) он удаляет дубликаты
Дубликаты определяются по ключу сортировки (тому, что указан в секции ORDER BY при создании таблицы). Все строки с одинаковыми значениями в столбцах ключа сортировки считаются кандидатами на замену, и движок оставляет только одну из них (обычно последнюю по времени вставки или по специальному версионирующему столбцу, если он указан).
Получается, в нашем случае в ORDER BY должны находиться все ключевые колонки, которые определяют уникальность строки с метрикой: metric_id, metric_date и все наши колонки-дименшены (region_id, category_id и так далее). Но в какой последовательности их указывать? И влияет ли эта последовательность на что-то еще, кроме определения дубликатов?
Да, последовательность колонок в ORDER BY в ClickHouse – это очень важно! На основании этого порядка строится разреженный первичный индекс, который ClickHouse использует для быстрого поиска данных. Поэтому нам необходимо следовать следующим правилам при выборе порядка:
  • Столбцы, которые вы чаще всего используете для фильтрации с точным совпадением (=, IN), должны идти в начале. Это самое важное правило. Если почти каждый запрос фильтрует по metric_id, то он должен быть одним из первых.
  • При прочих равных, столбцы с низкой кардинальностью (мало уникальных значений, например, platform_enum где всего 3-4 значения) лучше ставить раньше столбцов с высокой кардинальностью (много уникальных значений, например, user_id или city_id, если городов тысячи). Это улучшает сжатие данных и может помочь индексу эффективнее отсекать ненужные блоки данных.
Важное замечание: если у вас есть столбец с высокой кардинальностью (например, city_id), но вы очень часто фильтруете именно по нему с точным совпадением, его можно (и даже нужно) поставить раньше столбца с более низкой кардинальностью, по которому фильтруют реже. Приоритет – частота использования в WHERE-условиях.
  • Не делайте ключ сортировки слишком длинным без необходимости. Включение в ORDER BY столбцов, по которым вы никогда не фильтруете и которые не участвуют в логике ReplacingMergeTree (то есть не нужны для определения уникальности), только увеличит размер индекса и может немного замедлить запись данных. В нашем случае все дименшены важны для уникальности.
  • Обязательно включайте столбец даты/времени для временных рядов. Для данных временных рядов (логи, метрики) почти всегда первым или одним из первых ключей в ORDER BY должна быть дата или метка времени (например, metric_date). Это позволяет ClickHouse очень эффективно фильтровать данные по временным диапазонам и является основой для партиционирования.
Таким образом, мы должны колонки, фигурирующие во всех (или почти во всех) запросах, поставить вперёд. В нашем случае это, скорее всего, будут metric_id и metric_date. После чего нужно перечислить дименшены в порядке, который балансирует их кардинальность и частоту использования в фильтрах.
Тут нет универсального рецепта. Необходимо поэкспериментировать с разными комбинациями на реальных запросах, соблюдая баланс между размером таблицы/индекса и производительностью запросов с наиболее частыми фильтрами. Первоначальный набор колонок можно собрать опираясь на CJM, а после релиза проекта проанализировать логи запросов к сервису, чтобы понять, какие фильтры пользователи применяют чаще всего
Однако нужно помнить, что первичный индекс (тот, что указывается в PRIMARY KEY) в ClickHouse строится на основе ключа сортировки (ORDER BY) и его нужно держать как можно более компактным и эффективным для индексации. PRIMARY KEY должен быть оптимизирован для быстрого поиска начальных блоков данных (гранул). ORDER BY определяет детальную сортировку внутри этих блоков и используется для других целей (сжатие, логика специфичных движков типа ReplacingMergeTree). В ClickHouse PRIMARY KEY не обязан совпадать с ORDER BY, но он должен быть его префиксом. Разделяя их (позволяя ORDER BY быть длиннее, чем PRIMARY KEY), вы можете получить лучшее из обоих миров: быстрый поиск по PRIMARY KEY и нужную логику дедупликации и сортировки по полному ORDER BY.
Таким образом, для индекса (PRIMARY KEY) мы будем использовать metric_id, metric_date и, возможно, первые несколько наиболее селективных и часто используемых дименшенов. А в ORDER BY войдут все эти колонки плюс остальные дименшены, чтобы ReplacingMergeTree корректно отрабатывал.
Так как таблица предполагает хранить исторические данные всего временного ряда за несколько лет, мы ожидаем, что она будет занимать терабайты. Поэтому нам потребуется эффективное сжатие (ClickHouse тут хорош по умолчанию, но можно тюнить кодеки) и партиционирование (мы используем кластер ClickHouse на 6 нод, что также влияет на стратегию репликации и шадрирования, но это выходит за рамки структуры самой таблицы).
Поскольку таблица хранит временной ряд, то партиции (секции таблицы, физически разделяющие данные) логично основывать на колонке с датой, например, metric_date. Нам также нужно обеспечить управляемое количество партиций на длительное время. Почему это важно? ClickHouse хранит метаданные для каждой партиции (информацию о кусках данных (parts), их границах, статистику и так далее).
Слишком большое количество партиций (например, если партиционировать по дням и хранить данные за 5 лет, это будет 5*365 = 1825 партиций) приводит к раздуванию этих метаданных. При выполнении запроса, даже если он фильтрует по дате, ClickHouse должен проанализировать метаданные всех потенциально релевантных партиций. Чем больше партиций – тем дольше этот анализ.
Поэтому давайте использовать партиции по месяцам: PARTITION BY toYYYYMM(metric_date). Тогда данные за 3 года дадут 3 * 12 = 36 партиций. За 10 лет – 120 партиций. Это поможет нам соблюсти best practice, согласно которому количество партиций в одной таблице не должно превышать примерно 1000 (а лучше и того меньше, несколько сотен – это хороший ориентир).
Итого, мы готовы собрать DDL (Data Definition Language) для нашей таблицы, где будет храниться отчёт M42:
CREATE TABLE db.m42
(
launch_id UInt32,
metric_date Date,
metric_id UInt32,
metric_value Int64,
dimension1_id Int64,
dimension2_id Int64,
dimension3 Int8,
dimension4 Int8,
dimension5 String
)
ENGINE = ReplicatedReplacingMergeTree
PARTITION BY toYYYYMM(metric_date)
PRIMARY KEY (metric_id, metric_date, dimension1_id, dimension2_id)
ORDER BY (metric_id, metric_date, dimension1_id, dimension2_id, dimension3, dimension4, dimension5);
Отлично! Выглядит солидно. Но, как всегда, есть нюанс.
Как упоминалось выше, количество дименшенов (разрезов) у нас не статично, оно органически растёт. Бизнесу постоянно нужны новые способы «нарезать» данные. И нам необходимо обеспечить лёгкий и, главное, быстрый процесс добавления этих новых дименшенов в нашу уже существующую, огромную таблицу m42_reports
Когда мы добавляем новый дименшен в отчет (например, new_dimension_X), то для новых данных, которые будут рассчитываться с этого момента, значения new_dimension_X будут известны.
А что делать со старыми данными, которые уже лежат в таблице и были рассчитаны до появления этого дименшена? Для них значение new_dimension_X как бы не определено. В таких случаях мы обычно используем некое универсальное значение "Any". Для числовых ID это может быть специальное значение вроде -1, для строковых – пустая строка.
Фактически, "Any" означает, что данная метрика для старого периода агрегирована по всем возможным значениям этого (тогда еще не существовавшего) нового дименшена. Это как бы total по этому новому разрезу для старых дат.
Что ж, казалось бы, план простой: понадобился новый дименшен – добавляем новую колонку в таблицу m42_reports через ALTER TABLE ... ADD COLUMN new_dim_X ..., присваиваем всем существующим строкам значение "Any" (или его эквивалент) по этому новому дименшену, и всё. Ничего сложного, да?
Хотя подождите... Наша таблица m42_reports использует ReplacingMergeTree, и мы договорились, что её ORDER BY ключ должен включать все колонки с дименшенами, чтобы гарантировать уникальность строк и корректно устранять дубли.
И вот тут-то и возникает проблема: ClickHouse не позволяет просто так добавить новую колонку и тут же включить её в ORDER BY существующей таблицы, особенно если эта таблица уже содержит данные. Точнее, ALTER TABLE ... ADD COLUMN ... сделать можно, но ALTER TABLE ... MODIFY ORDER BY (...) – это уже серьезная операция.
Если ключ сортировки меняется, ClickHouse часто требует полной пересортировки данных на диске. А для таблицы в 9 ТБ (и полтора триллиона строк, так как храним всю историю) — это как раз то, чего мы всеми силами пытаемся избежать: процесс может занять неделю, если не больше, и потребует огромного количества дисковых операций и временного пространства.
Если решать проблему «в лоб», то добавление колонки с изменением ORDER BY превращается в задачу пересоздания всей таблицы. Это означает: создать новую таблицу с нужной структурой, перелить в нее все данные из старой (а это 9ТБ!), проверить, что все хорошо, а потом переключить приложение на новую таблицу. Звучит как план на несколько дней (или недель) с привлечением дата-инженера на фул тайм и потенциальными простоями или рисками потери данных.
А вот как мы пришли к решению этой «задачи со звездочкой» – читайте дальше. Это был интересный квест!
Варианты решения: как добавить колонку в ClickHouse и ORDER BY не переливая 9 ТБ данных
Итак, перед нами стена: нужно добавить колонку и обновить ORDER BY, но пересоздавать таблицу нельзя. Давайте рассмотрим наши опции, как мы это обычно делаем, когда упираемся в ограничения.
Первый подход: просто добавить новую колонку при необходимости и добавить в order by.
ALTER TABLE tables_schema.local_table_name ADD COLUMN dimension2_id Int64 -1,
MODIFY ORDER BY (metric_id, metric_date, dimension1_id, dimension2_id)
Увы, ClickHouse сообщит нам, что такое изменение требует перестройки ключа сортировки, а это для него равносильно пересозданию данных на диске. То есть, мы возвращаемся к проблеме многодневного перелива данных. Не подходит.
Второй подход: создать колонки «про запас» при первоначальном создании таблицы и сразу включить их в ORDER BY. А когда понадобится новый дименшен, просто переименовать одну из зарезервированных колонок. Конечно, наш запас колонок конечен и в итоге придётся всё равно пересоздавать таблицу, но мы сможем драматически снизить количество таких итераций.
Пример такой таблицы:
CREATE TABLE db.m42
(
launch_id UInt32,
metric_date Date,
metric_id UInt32,
metric_value Int64,
dimension1_id Int64,
dimension2_id Int64,
dimension3 Int8,
dimension4 Int8,
dimension5 String,
reserved_dimension_1 Int64 DEFAULT -1,
reserved_dimension_2 Int64 DEFAULT -1,
reserved_dimension_3 Int64 DEFAULT -1,
reserved_dimension_4 Int64 DEFAULT -1,
reserved_dimension_5 Int64 DEFAULT -1
)
ENGINE = ReplicatedReplacingMergeTree
PARTITION BY toYYYYMM(metric_date)
PRIMARY KEY (metric_id, metric_date, dimension1_id, dimension2_id)
ORDER BY (metric_id, metric_date, dimension1_id, dimension2_id, dimension3, dimension4, dimension5, reserved_dimension_1, reserved_dimension_2, reserved_dimension_3, reserved_dimension_4, reserved_dimension_5);
Но что же мы получим при переименовании колонки, которая уже есть в ORDER BY и для которой проставлены значения DEFAULT -1 по всем строкам?
ALTER TABLE db.m42 RENAME COLUMN reserved_dimension_1 TO new_actual_dimension_id;
Увы, и здесь нас может ждать разочарование. Операция RENAME COLUMN для колонки, которая является частью ключа сортировки и уже содержит данные, невозможна и запрещена Clickhouse, а значит, таким путём нашу задачу не решить.
Улучшение первого варианта: а что если попробовать добавить новую колонку, включить ее в ORDER BY, но не устанавливать ей никакого DEFAULT-значения при добавлении? То есть, позволить ClickHouse самому использовать его внутренние значения по умолчанию для типа колонки (0 для числовых типов, пустая строка для String, и так далее).
ALTER TABLE db.m42 ADD COLUMN dimension2_id Int64,
MODIFY ORDER BY (metric_id, metric_date, dimension1_id, dimension2_id)
И вот тут – эврика! Оказалось, что если добавлять колонку без явного DEFAULT (полагаясь на дефолт типа данных в ClickHouse), то операция ADD COLUMN выполняется очень быстро (это metadata-only операция для MergeTree таблиц). А последующая MODIFY ORDER BY для добавления этой «пустой» колонки в ключ сортировки также проходит значительно легче, часто без полной пересортировки данных! ClickHouse как бы «дописывает» это новое поле с его дефолтным значением к существующему ключу сортировки «на лету» при последующих операциях слияния кусков (merges).
Однако для этого нам необходимо было сделать одну важную вещь: провести миграцию наших существующих данных и логики. Мы должны были сделать так, чтобы наше концептуальное значение "Any" (которое мы раньше могли обозначать как -1 для Int или строкой 'Any' для String) теперь совпадало с дефолтными значениями ClickHouse для соответствующих типов.
То есть, мы провели подготовительную работу:
  • Для числовых типов (Int, UInt): наше значение "Any" стало 0 (вместо -1).
  • Для строковых типов (String): наше значение "Any" стало '' (пустая строка) (вместо 'Any').
  • Особенно интересно получилось с булевыми значениями. В ClickHouse Bool хранится как UInt8, где 0 это false, а 1 это true. Если мы хотим, чтобы 0 был нашим "Any" для булевых разрезов, то нам нужно было "сдвинуть" настоящие значения:
  • False (которое раньше было 0) стало 1.
  • True (которое раньше было 1) стало 2.
  • А 0 (дефолт ClickHouse для UInt8) теперь у нас означает "Any" для этого булевого разреза.
Чтобы заменить значения "Any" нужно проделать полноценную миграцию с переливкой данных в новую таблицу, но оно того стоило, так как процесс единоразовый.
Мы провели миграцию, в которой наше значение "Any" для каждого типа данных стало совпадать с «нулевым» значением по умолчанию для этого типа в ClickHouse. И тогда нам уже не нужно явно указывать DEFAULT при ADD COLUMN, и, соответственно, ClickHouse не пытается перехешировать или пересортировывать данные при MODIFY ORDER BY. Вуаля, мы можем добавлять новую колонку в ORDER BY практически мгновенно!
После такой подготовки, когда наше представление "Any" совпало с системными дефолтами ClickHouse, процесс добавления нового дименшена стал выглядеть так:
ALTER TABLE db.m42 ADD COLUMN new_dimension_very_cool_id Int64,MODIFY ORDER BY (..., new_dimension_very_cool_id); (выполняется быстро, без пересортировки всей таблицы)
Старые строки, где этого дименшена не было, теперь автоматически для new_dimension_very_cool_id будут иметь значение 0 (дефолт для Int64), что у нас теперь и означает "Any". Новые данные будут записываться уже с осмысленными значениями этого дименшена. ReplacingMergeTree будет корректно работать, так как 0 для старых данных и конкретные ID для новых данных – это разные значения в ключе сортировки.
Отлично, теперь мы можем реализовать механизм автомиграций: M42 при старте или по команде может проверять конфигурацию отчетов, сравнивать её с текущей структурой таблицы в ClickHouse и, если находит новые дименшены в конфиге, которых еще нет в таблице, автоматически применять нужные ALTER TABLE команды. И такой механизм будет работать секунды, а не часы или дни, так как нам больше не требуется пересчитывать ключ сортировки для всей таблицы с отчётом при добавлении колонки.
Мы решили эту задачу, и теперь добавление нового дименшена стало гораздо проще и безопаснее. Всё, что нужно аналитику или разработчику, — это внести необходимые данные о новом дименшене в конфигурацию M42. Система сама подхватит изменения, добавит колонку в ClickHouse, и новый разрез появится в системе, начнёт рассчитываться и отображаться в дашбордах.

Что в итоге

Мы с вами прошли довольно длинный, но, надеюсь, увлекательный путь: от осознания проблемы с ресурсоёмким процессом добавления новых разрезов в ClickHouse до разработки гибкого и быстрого решения, которое позволяет делать это буквально одной строчкой в конфигурации.
Теперь пользователи M42 (продакты и аналитики) могут быстро и самостоятельно настраивать нужные им разрезы для анализа метрик, не перегружая команду дата-инженеров и не затягивая процесс принятия решений из-за технических ограничений
Решение получилось нетривиальным: мы пересмотрели наш подход к хранению данных, особенно к тому, как мы обрабатываем отсутствие значения для новых дименшенов в старых данных. Мы обошли ограничения ClickHouse, связанные с изменением ORDER BY для больших таблиц, и избавились от необходимости пересоздавать или полностью переливать эти огромные таблицы.
В результате:
Улучшили self-service. Теперь продакту или аналитику его команды достаточно внести нужные изменения в настройки отчёта (yaml-конфиг). Эти настройки подхватываются системой, и новый разрез «материализуется» в M42 без долгого ожидания и сложных инженерных работ. Скорость реакции на потребности бизнеса выросла в разы.
Снизили затраты на СУБД и хранилище. Мы избегаем операций, которые требуют копирования терабайтов данных между таблицами или интенсивной пересортировки на месте. Это экономит дисковое пространство (нет временных копий) и снижает нагрузку на кластер ClickHouse.
Существенно сократили затраты инженерного ресурса на поддержку инфраструктуры M42. Раньше добавление разреза было мини-проектом. Теперь это штатная, почти автоматическая операция. Инженеры могут сосредоточиться на развитии функциональности M42, а не на рутинных операциях с таблицами.
Надеюсь, наш опыт будет полезен и вам, если вы столкнетесь с похожими вызовами при проектировании аналитических систем. Главный вывод – не бойтесь искать нестандартные подходы и адаптировать инструменты под свои нужды!
Больше о Trisigma в целом и М42 вы можете узнать по этой ссылке. А вот здесь – подробности о том, что еще мы разрабатываем и используем в AvitoTech.