Агрегации и приближённые вычисления

Представление здесь - триггер на вставку

Materialized view в ClickHouse работает не так, как звучит. Это не сохранённый результат запроса, который база поддерживает в актуальном состоянии. Это триггер: при каждой вставке в исходную таблицу приходящий блок прогоняется через запрос представления, и результат дописывается в целевую таблицу.

Отсюда первый сюрприз, который проверяется за минуту. Создаём таблицу, вставляем две строки, потом создаём представление - в нём ноль строк. Историю оно не видит вовсе, потому что вставки уже прошли. Вставляем третью строку - в представлении появляется одна. Старые данные заливаются руками, отдельным INSERT ... SELECT по сырью.

Второй сюрприз - правки. Если строку в сырье изменить или удалить мутацией, представление об этом не узнает: триггер срабатывает только на вставку. Агрегат разъезжается с сырьём молча и навсегда. Значит либо сырьё не мутируем вообще, либо пересобираем затронутые партиции агрегата целиком.

// Из этих двух свойств следует операционная гигиена: агрегат регулярно сверяют с сырьём. Ошибка в GROUP BY внутри представления копит расхождение с первого дня и обнаруживается обычно на встрече с бизнесом.

CREATE TABLE src (id UInt32) ENGINE = MergeTree ORDER BY id;
INSERT INTO src VALUES (1), (2);

CREATE MATERIALIZED VIEW mv ENGINE = MergeTree ORDER BY id
AS SELECT id FROM src;

SELECT count() FROM mv;   -- 0  историю не увидел
INSERT INTO src VALUES (3);
SELECT count() FROM mv;   -- 1  только новая вставка

Состояния агрегатов: -State и -Merge

Хранить в агрегате готовые суммы кажется естественным, пока речь про суммы. Сложить дневные суммы в месячную легко. А вот сложить дневные значения «уникальных пользователей» нельзя: один и тот же человек заходил и в понедельник, и во вторник, и сумма его посчитает дважды.

Решение - хранить не число, а промежуточное состояние агрегата. Функция с суффиксом -State пишет его в таблицу, функция с суффиксом -Merge доагрегирует при чтении. Состояния корректно складываются между кусками, днями и партициями: два дневных uniqState объединяются в месячный без потери точности, потому что внутри лежит структура, знающая про пересечения.

Колонка при этом имеет тип AggregateFunction(sum, UInt64) или подобный. Если прочитать её обычным SELECT без -Merge, вернётся не число, а бинарное состояние - это и есть классический сюрприз первого дашборда. Проверено: sumState по числам от 0 до 9 читается через sumMerge и даёт 45.

// Для простых суммируемых величин есть SimpleAggregateFunction - легче полноценного состояния и читается как обычная колонка. Для uniq и quantile так не выйдет: им нужна структура, а не число.

CREATE TABLE agg (d Date, s AggregateFunction(sum, UInt64))
ENGINE = AggregatingMergeTree ORDER BY d;

INSERT INTO agg SELECT toDate('2024-01-01'), sumState(toUInt64(number))
FROM numbers(10);

SELECT s FROM agg;           -- бинарное состояние, а не число
SELECT sumMerge(s) FROM agg; -- 45

-State / -Merge

запись промежуточного состояния агрегата / его доагрегация при чтении

Гранулярность агрегата: считаем размер

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

Пример. Часовая корзина, плюс измерения: страна (10 значений), тип события (50), платформа (3), тариф (4). Считаем: 24 умножить на 10, на 50, на 3, на 4 - это 144 тысячи строк в сутки, около 4,3 миллиона в месяц. Дашборд читает миллионы вместо миллиардов, паттерн работает.

Теперь добавим «на всякий случай» ещё три измерения по десять значений каждое. Умножаем на тысячу - 144 миллиона строк в сутки. Агрегат стал сопоставим с сырьём, и весь смысл потерян. Поэтому измерения выбирают под конкретные вопросы дашборда, а не про запас, и делают несколько узких агрегатов вместо одного универсального.

// Отдельно про память. GROUP BY выполняется хеш-таблицей в оперативке, и группировка по user_id на миллиардах строк её съест. Настройка max_bytes_before_external_group_by, разрешающая сброс на диск, по умолчанию равна нулю - то есть спилла нет, запрос просто упрётся в лимит памяти и упадёт. Правильный ход - не поднимать лимит, а группировать по меньшей кардинальности.

Как отвечать: «Дашборд по миллиардам событий тормозит. Как ускоришь ClickHouse'ом?»

Каскадом агрегатов. Сырьё остаётся в MergeTree, на вставку вешаю materialized view, которое пишет состояния агрегатов в AggregatingMergeTree с гранулярностью под вопросы дашборда - час или день на несколько ключевых измерений. Прикидываю размер заранее умножением кардинальностей: час на четыре измерения даёт порядка сотни тысяч строк в сутки, и дашборд читает миллионы вместо миллиардов. Читает он через функции с суффиксом -Merge, иначе вернутся бинарные состояния вместо чисел. Два подводных камня проговариваю сразу: представление видит только новые вставки, поэтому историю заливаю руками отдельным запросом, и правок сырья оно не видит вообще - значит сырьё не мутируем, либо пересобираем партиции агрегата. И закладываю регулярную сверку агрегата с сырьём: ошибка в GROUP BY копит расхождение молча.

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

На чём валят

• −Создать представление и ждать, что оно посчитает историю: оно видит только новые вставки.
• −Мутировать сырьё при живом представлении - агрегат разъедется с ним навсегда.
• −Читать AggregatingMergeTree без функций -Merge и получить бинарные состояния вместо чисел.
• −Складывать дневные значения уникальных пользователей как числа: для этого и нужны состояния.
• −Набрать в агрегат десять измерений «на всякий случай» и получить таблицу размером с сырьё.
• −Рассчитывать на сброс GROUP BY на диск: по умолчанию он выключен, запрос упадёт по памяти.