Привет! Меня зовут Влад Божьев, я старший разработчик юнита АБ-тестирования Авито. В нашей команде мы ежедневно работаем с по-настоящему большими объёмами данных – это не просто фигура речи, это наша реальность. Мы помогаем создавать метрики, которые помогают бизнесу не «гадать на кофейной гуще», а принимать взвешенные решения, основанные на данных.

Один из наших ключевых инструментов – M42, сервис для визуализации метрик. Он позволяет быстро проверять гипотезы, анализировать отклонения и оценивать инициативы. M42 – это часть единого аналитического продукта Trisigma, вторая его половина – платформа для A/B-тестирования. Trisigma считает и визуализирует метрики в конкретных A/B-экспериментах, а M42 помогает принимать решения глобально по продукту. Видео с подробным разбором продукта и его возможностей:

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

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

Что внутри статьи:

M42: инструмент для метрик, гипотез и ответа на главный вопрос вселенной

Проектируем таблицы M42

Прорабатываем реализацию

Добавляем промежуточный предрасчет отчёта M42

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

Что в итоге

M42: инструмент для метрик, гипотез и ответа на главный вопрос вселенной

Начнем с контекста. Почему вообще решили разработать этот сервис? Основная проблема — высокая загрузка аналитиков и низкий уровень self-service. Зачастую бизнес принимает решения на основе ad-hoc запросов к аналитикам и тут возникает несколько проблем.

Проблемы для бизнеса: 

• ad-hoc задачи не вмещаются в спринты аналитиков;

• ожидание выполнения ad-hoc запроса вынуждает откладывать задачи -> снижается скорость принятия решений;

• готовый дашборд нужно искать или ждать изготовление с нуля;

• готовые дашборды — это не слишком гибкое решение.

Проблемы для аналитиков: 

• они перегружены ad-hoc: не хватает времени на исследовательские задачи; 

• много рутины: выгрузка одних и тех же SQL с новыми JOIN;

• создают «одноразовые» дашборды и выгрузки.

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

Что такое M42? Это инструмент для визуализации метрик, созданный командой Авито с помощью Trino, Clickhouse и SQL-магии, обёрнутой в SPA (Single Page Application). Можно, например, посмотреть количество кликов на рекламу, метрику рекламной выручки и выяснить прочие интересные подробности. По факту, основные задачи инструмента – это анализ и визуализация метрик, формирование гипотез, оценка потенциала инициатив компании.

Кому полезен такой инструмент? Компаниям, которые работают с метриками и принимают решения на основе данных, а конкретно таким специалистам, как продакты, маркетологи, менеджеры и аналитики. Не нужно уметь в SQL и понимать, как устроен DWH, чтобы получить нужную аналитику. Плюс в том, что специалист перестает быть ограниченным готовыми выгрузками от аналитиков.

Теперь разберемся, какие есть возможности у пользователя в М42. Он может:

• рассчитать потенциал своей идеи;

• найти причины отклонений своих метрик;

• посмотреть динамику метрик других команд и сравнить;

•  перепроверить отчет;

• и делать много чего еще, подробнее рассказываю ниже.
  
  

  

Теперь вкратце разберем алгоритм работы пользователя в М42:

1) Аналитик создает ключевые сущности в конфигурациях в yaml-формате: источники метрик, сами метрики и разрезы.

2) Далее настраивает, какие метрики в каких разрезах считать, – по сути создает yaml-конфиг отчета. 

3) Теперь любой бизнес-юзер получает регулярно обновляющийся отчет с возможностью просмотра результатов с гибкой системой фильтров по разрезам.

А еще тут возникает естественный вопрос – почему сервис называется M42? Все просто: M – это метрики, а 42 – ответ на «главный вопрос жизни, вселенной и вообще» из «Автостопом по галактике»

Проектируем таблицы M42

Что имеем

Теперь самое интересное – давайте попробуем вместе разработать оптимальный способ хранения отчётов M42. Сразу оговорюсь, мы коснемся процесса расчёта M42 лишь вскользь, потому что это тема для отдельной, не менее увлекательной статьи, а сегодня сосредоточимся именно на хранении данных. Следите за обновлениями, как говорится ;)

Но прежде разберемся, какие вводные у нас есть:

Есть Clickstream — это внутренний инструмент для сбора пользовательской активности. На основе этих данных мы и строим метрики. 

Слой витрин представляет собой регулярно обновляемые таблицы, которые агрегируют сырые данные из Clickstream и других источников. 

Семантический слой – представляет  собой набор базовых сущностей: источники метрик, сами метрики, разрезы и прочие, из них строится вся комплексная аналитика в A/B и M42. 

Семантический слой формирует регулярный SQL, который считается на движке Trino, и результатом которого являются отчёты по A/B и M42. 

Затем отчёты выгружаются в Clickhouse для быстрого чтения и отображаются в UI SPA Trisigma.

Ключевое требование к системе – наличие возможности гибко настраивать разрезы по различным параметрам, в которых нам интересна конкретная метрика. При этом количество таких параметров (мы их называем дименшенами или измерениями) может составлять десятки, а у каждого из них – сотни, тысячи, а то и сотни тысяч уникальных значений (например, ID рекламных кампаний).

Так, мы можем фильтровать данные по регионам и/или по конкретным вертикалям бизнеса, чтобы оценить, как они влияют на те или иные показатели.

Такой подход даёт нам возможность проводить глубинный анализ и видеть картину не в «средних» цифрах, а в деталях, что важно для принятия решений.

Прорабатываем реализацию

Давайте подумаем над возможной реализацией такой системы: 

Как гласит один из ключевых принципов проектирования – KISS (Keep It Simple, Stupid) – не усложняй без необходимости.

Раз у нас все сырые данные для расчёта уже хранятся в таблицах фактов (в DWH), то, может, мы можем попробовать рассчитывать отчёт на лету? То есть, пользователь в M42 выбирает метрику, нужные разрезы, период, нажимает «показать», и в этот момент летит запрос в Trino, который считает все из DWH. Затрагиваем только запрошенные пользователем разрезы и метрики. Звучит просто и элегантно, не правда ли?

Но будет ли такой запрос выполняться достаточно быстро? Давайте прикинем.

В ключевом источнике данных Авито, нашем Clickstream, за один месяц накапливается примерно 45 миллиардов строк. А наиболее частый сценарий просмотра метрики в M42 – это просмотр временного ряда за 3 месяца, а то и за год.

Получается, что наш запрос «на лету» должен будет обрабатывать десятки, а то и сотни миллиардов строк! И это не просто SELECT, это запросы с группировками (GROUP BY), агрегациями (SUM, COUNT DISTINCT, AVG), соединениями (JOIN).

На наших данных даже запрос по относительно небольшому числу слайсов (комбинаций разрезов) будет работать часы (или вовсе не поместится в память и упадет). Пользователь просто не будет ждать минуты, не говоря уже о часах. Это абсолютно неприемлемо для UX. Нужен отклик в пределах секунд.

Хорошо, скажете вы, а что если внедрить слой кеширования? Будем предрассчитывать наиболее востребованные комбинации фильтров по разрезам и складывать куда-нибудь, откуда их можно быстро достать (aka cache ahead). Давайте попробуем оценить, какое количество комбинаций нам пришлось бы кешировать для приемлемой отзывчивости.

В нашем семантическом слое около 250 различных разрезов. У каждого из них – от десятков до тысяч, а иногда и сотен тысяч уникальных значений. Если попытаться посчитать все возможные комбинации даже для небольшого подмножества разрезов, цифра получится астрономической. Формула для числа комбинаций N_comb = V1 V2 ... * Vk (где Vi – количество значений у i-го разреза, а K – количество выбранных разрезов) дает нам это понять. 

Но помним, что смысл кеширующих слоев в движках семантических слоев – это кешировать не всё на свете, а только самое популярное. Однако даже если мы ограничимся только самые популярные комбинации значений, то на реальных данных это все равно будут многие миллиарды комбинаций.

Такое количество уникальных фильтров потребовало бы построения крайне сложной и требовательной к ресурсам системы кеширования. И все равно вероятность «промаха мимо кеша» была бы очень высока. А промах – это снова ожидание отчета, исчисляемое часами.

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

Время ответа ручки API, которая отдает данные для отчета, должно быть очень маленьким, иначе UI M42 будет «тормозить» и вызывать раздражение. Часы ожидания тут точно не про нас.

Добавляем промежуточный предрасчет отчёта M42

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

Мы принимаем день как минимальную единицу расчета. Это означает, что пользователь сможет просматривать полные данные без дальнейших мутаций отчёта с задержкой в один день (то есть сегодня он видит данные «за вчера»). Для большинства аналитических задач такой гранулярности и свежести вполне достаточно.

Итак, мы решили, что данные нужно предрассчитывать. Теперь вопрос – как их структурировать?

Давайте попробуем ввести структуру для хранения комбинаций: введем понятие слайса (slice). Пусть слайс – это конкретная комбинация значений выбранных измерений (дименшенов). Например, (region='Москва', category='Авто', platform='Android') – это один слайс. Мы можем каждой такой уникальной комбинации назначить свой числовой идентификатор (slice_id). Для этого можно вести отдельную таблицу-справочник: slices (slice_id UInt, dimension1_value, dimension2_value, ...) или, как вариант, slices (slice_id UInt, slice_definition JSON).

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

CREATE TABLE db.slices
(
  slice_id UInt64,
  slice_definition JSON
)

Идея с JSON выглядит заманчиво: slice_definition мог бы хранить что-то вроде {"region": "Москва", "category_id": 123, "platform": "Android"}. Это позволит легко расширять набор разрезов в отчёте M42, не изменяя структуру таблицы фактов (где будут храниться сами метрики), ведь там будет просто slice_id.

Однако нужно помнить, что фильтрация по значениям дименшенов – это ключевая возможность M42

Пользователи будут постоянно хотеть отфильтровать отчет по Москве или по Android. Значит, нам нужно убедиться, что такая фильтрация по содержимому JSON будет работать эффективно.

Давайте снова посчитаем. Как мы уже выяснили выше – у дименшенов могут быть миллиарды комбинаций. Если мы хотим по ним эффективно фильтровать, нам нужно будет их все загрузить в нашу гипотетическую таблицу slices (slice_id, slice_definition JSON) для дальнейшего использования при построении отчетов.

Поскольку мы планируем активно читать и фильтровать по этой таблице слайсов, нам нужно обеспечить быстрое чтение. Но вот беда: фильтрация по содержимому JSON-поля в большинстве СУБД (включая ClickHouse) – операция крайне неэффективная, даже если JSON не очень большой. ClickHouse, конечно, умеет работать с JSON, но это не его основная сильная сторона для фильтрации больших объемов.

Значит, нам понадобятся индексы. А как индексировать JSON? В ClickHouse для этого можно создать MATERIALIZED колонки для каждого дименшена, извлекая значения из JSON (JSONExtractString(slice_definition, 'region') AS region_name_materialized), и уже на эти материализованные колонки «вешать» индексы.

Давайте взвесим риски хранения комбинаций разрезов в JSON формате в этой таблице-справочнике слайсов:

• Дублирование данных и размер. Хотя нам доступны индексы через материализованные колонки, такой индекс требует существенного дублирования данных. Во-первых, сама материализованная колонка – это уже копия данных из JSON. Во-вторых, сам индекс тоже занимает место. При учёте, что там будет храниться от 20 миллиардов строк (если мы решим материализовать все возможные комбинации, что, как мы помним, очень много), такая таблица slices будет занимать терабайты дискового пространства даже без учёта индексов. Размер таблицы здесь действительно имеет значение, при этом используя json колонку не получится применить колоночное сжатие эффективно, так как Clickhouse не сможет применить сортировку по колонкам перед сжатием.

• Сложность и производительность запросов. Запросы к таблице со слайсами для фильтрации потребуют применения конструкций типа JSONExtract... (если мы не используем материализованные колонки для всех полей, по которым можем фильтровать, что маловероятно). Это существенно снижает читабельность SQL и может быть медленнее, чем прямая фильтрация по типизированной колонке.

• Обновления и вставки. По мере развития платформы органически будут появляться новые комбинации значений дименшенов (например, новый город или новая категория). Их потребуется вставить в таблицу со слайсами. Однако операции вставки будут существенно замедлены необходимостью обновлять материализованные колонки и их индексы.

• Избыточность JSON. Сам формат JSON достаточно многословен. Имена ключей ("region", "category_id") повторяются в каждой строке (если мы храним JSON для каждой комбинации). Это требует больше места для хранения, чем если бы значения дименшенов хранились в отдельных типизированных колонках.

Таким образом, конструкция с JSON-хранением слайсов в таблице-справочнике несет много накладных расходов, потенциально медленная и не позволит строить простые для анализа запросы. Давайте проанализируем другие опции.

А что, если мы разделим слайс на отдельные колонки под каждый дименшен в этой таблице-справочнике? То есть, вместо slices (slice_id, slice_json) у нас будет slice_definitions (slice_id, region_id, category_id, platform_id, ...)? Это, кажется, как раз то, что «доктор прописал» для справочника комбинаций! 

Давайте прикинем, что нам это даст:

Фильтрация. Она станет просто молниеносной по сравнению с JSON. Ведь фильтровать по конкретной колонке с нативным типом данных (WHERE region_id = 123) – это стандартная, хорошо оптимизированная операция для любой СУБД, и ClickHouse тут не исключение. Никаких JSONExtract, просто WHERE dimension_X = 'value'. Красота! И индексы по таким колонкам будут работать как часы, без всяких там «материализованных костылей» для JSON.

Размер хранимых данных. Мы сразу избавляемся от многословности JSON. Каждое значение будет храниться в своем оптимальном формате. Если какой-то дименшен имеет, скажем, всего 250 уникальных значений (например, коды стран), его можно уложить в UInt8 (или даже Enum, если СУБД поддерживает и это эффективно для такого количества), а не таскать строковое представление в JSON.

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

Читабельность и простота запросов. SQL-запросы станут не только быстрее, но и гораздо чище и понятнее. Коллегам-аналитикам (и нам самим через полгода) будет проще разбираться в логике и писать новые аналитические выборки, не ломая голову над конструкциями для извлечения данных из JSON.

Операции вставки и обновления. Добавление новых комбинаций дименшенов (а они, как мы понимаем, будут появляться) не будет вызывать такой головной боли с перестройкой громоздких материализованных JSON-атрибутов и их индексов. Обновление индексов по обычным, атомарным колонкам происходит гораздо эффективнее и быстрее.

Таким образом, каждая строка в такой таблице-справочнике slice_definitions будет максимально «легкой», и операции с ней будут быстрыми. Мы получаем прозрачную структуру, предсказуемую производительность и возможность гибко работать с данными, не упираясь в ограничения JSON-парсинга на лету или сложности с материализованными представлениями.

По сути, мы возвращаемся к классическому, проверенному временем реляционному подходу там, где он действительно «сияет» – в эффективной обработке и фильтрации структурированных данных. Да, нам придется перечислить все дименшены как отдельные колонки в таблице slice_definitions, и если появится совершенно новый тип дименшена (например, «тип браузера»), нам нужно будет добавить новую колонку в эту таблицу (ALTER TABLE slice_definitions ADD COLUMN browser_id ...). Но выигрыш в производительности, экономии места и простоте обслуживания, на мой взгляд, с лихвой перекрывает это «неудобство».

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

Итак, мы определились, как будем хранить и идентифицировать уникальные комбинации разрезов (слайсы). Теперь нам необходимо продумать структуру самой таблицы с отчетом, где будут лежать фактические значения метрик. Наша цель – получить наилучшую производительность и возможность масштабировать количество обрабатываемых разрезов по метрикам.

Пойдем по шагам. Что нам нужно хранить в каждой строке этой таблицы?

1. Дата, к которой относится рассчитанное значение метрики: metric_date Date. Это основа для анализа временных рядов.

2. Идентификатор метрики: metric_id UInt... (тип подбираем по количеству метрик, например, UInt32). Это числовой ID, который ссылается на справочник метрик, где хранится ее название, формула расчета и так далее.

3. Значение метрики: metric_value Int…

4. Идентификаторы дименшенов (разрезов): Вместо slice_id, который мы обсуждали для гипотетического JSON-хранилища, мы  будем хранить значения каждого дименшена в отдельной колонке, такая денормализация позволит ускорить наши запросы к отчёту ещё сильнее устранив накладные расходы на join.
   
   Например: 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.