Движок таблицы AggregatingMergeTree

Этот движок наследуется от MergeTree, изменяя логику слияния частей данных. ClickHouse заменяет все строки с одинаковым первичным ключом (или, точнее, с одинаковым ключом сортировки) одной строкой (в пределах одной части данных), которая хранит комбинацию состояний агрегатных функций. Таблицы AggregatingMergeTree можно использовать для инкрементальной агрегации данных, в том числе для агрегированных materialized view. Ниже в видео показан пример использования AggregatingMergeTree и агрегатных функций:

Движок обрабатывает все столбцы следующих типов:

AggregatingMergeTree целесообразно использовать, если это позволяет уменьшить количество строк на порядки.

Создание таблицы

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...]

Описание параметров запроса см. в разделе описание запроса. Секции запроса При создании таблицы AggregatingMergeTree требуются те же секции, что и при создании таблицы MergeTree.

SELECT и INSERT

Чтобы вставить данные, используйте запрос INSERT SELECT с агрегатными функциями со суффиксом -State. При выборке данных из таблицы AggregatingMergeTree используйте оператор GROUP BY и те же агрегатные функции, что и при вставке данных, но с суффиксом -Merge. В результатах запроса SELECT значения типа AggregateFunction имеют зависящее от реализации двоичное представление во всех форматах вывода ClickHouse. Например, если вы выгрузите данные в формат TabSeparated с помощью запроса SELECT, этот дамп можно затем загрузить обратно с помощью запроса INSERT.

Пример агрегированного materialized view

В следующем примере предполагается, что у вас есть база данных с именем test. Если она ещё не создана, выполните команду ниже:

CREATE DATABASE test;

Теперь создайте таблицу test.visits, содержащую исходные данные:

CREATE TABLE test.visits
 (
    StartDate DateTime64 NOT NULL,
    CounterID UInt64,
    Sign Nullable(Int32),
    UserID Nullable(Int32)
) ENGINE = MergeTree ORDER BY (StartDate, CounterID);

Далее необходимо создать таблицу AggregatingMergeTree, которая будет хранить AggregationFunctions для отслеживания общего числа посещений и количества уникальных пользователей. Создайте materialized view AggregatingMergeTree, который отслеживает таблицу test.visits и использует тип AggregateFunction:

CREATE TABLE test.agg_visits (
    StartDate DateTime64 NOT NULL,
    CounterID UInt64,
    Visits AggregateFunction(sum, Nullable(Int32)),
    Users AggregateFunction(uniq, Nullable(Int32))
)
ENGINE = AggregatingMergeTree() ORDER BY (StartDate, CounterID);

Создайте materialized view, который заполняет test.agg_visits данными из test.visits:

CREATE MATERIALIZED VIEW test.visits_mv TO test.agg_visits
AS SELECT
    StartDate,
    CounterID,
    sumState(Sign) AS Visits,
    uniqState(UserID) AS Users
FROM test.visits
GROUP BY StartDate, CounterID;

Вставьте данные в таблицу test.visits:

INSERT INTO test.visits (StartDate, CounterID, Sign, UserID)
 VALUES (1667446031000, 1, 3, 4), (1667446031000, 1, 6, 3);

Данные вставляются в обе таблицы: test.visits и test.agg_visits. Чтобы получить агрегированные данные, выполните запрос вида SELECT ... GROUP BY ... из materialized view test.visits_mv:

SELECT
    StartDate,
    sumMerge(Visits) AS Visits,
    uniqMerge(Users) AS Users
FROM test.visits_mv
GROUP BY StartDate
ORDER BY StartDate;

┌───────────────StartDate─┬─Visits─┬─Users─┐
│ 2022-11-03 03:27:11.000 │      9 │     2 │
└─────────────────────────┴────────┴───────┘

Добавьте ещё несколько записей в test.visits, но на этот раз укажите другую временную метку для одной из записей:

INSERT INTO test.visits (StartDate, CounterID, Sign, UserID)
 VALUES (1669446031000, 2, 5, 10), (1667446031000, 3, 7, 5);

Выполните запрос SELECT ещё раз — он вернёт следующий результат:

┌───────────────StartDate─┬─Visits─┬─Users─┐
│ 2022-11-03 03:27:11.000 │     16 │     3 │
│ 2022-11-26 07:00:31.000 │      5 │     1 │
└─────────────────────────┴────────┴───────┘

В некоторых случаях может потребоваться избежать предварительной агрегации строк при вставке, чтобы перенести затраты на агрегацию с момента вставки на момент слияния. Как правило, чтобы избежать ошибки, необходимо включать в конструкцию GROUP BY определения materialized view те столбцы, которые не участвуют в агрегации. Однако этого можно добиться с помощью функции initializeAggregation и параметра optimize_on_insert = 0 (по умолчанию он включён). В таком случае использование GROUP BY больше не требуется:

CREATE MATERIALIZED VIEW test.visits_mv TO test.agg_visits
AS SELECT
    StartDate,
    CounterID,
    initializeAggregation('sumState', Sign) AS Visits,
    initializeAggregation('uniqState', UserID) AS Users
FROM test.visits;

При использовании initializeAggregation для каждой отдельной строки создаётся aggregate state без группировки. Каждая исходная строка создаёт одну строку в materialized view, а фактическая агрегация происходит позже — при слиянии частей в AggregatingMergeTree. Это верно только при optimize_on_insert = 0.

Блог: Использование комбинаторов агрегатных функций в ClickHouse

​Создание таблицы

​SELECT и INSERT

​Пример агрегированного materialized view

​Материалы по теме

Создание таблицы

SELECT и INSERT

Пример агрегированного materialized view

Материалы по теме