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

Движок наследуется от MergeTree. Отличие состоит в том, что при слиянии частей данных в таблицах SummingMergeTree ClickHouse заменяет все строки с одинаковым первичным ключом (или, точнее, с одинаковым ключом сортировки) одной строкой, содержащей суммарные значения в столбцах с числовым типом данных. Если ключ сортировки устроен так, что одному значению ключа соответствует большое количество строк, это значительно уменьшает объем хранимых данных и ускоряет выборку. Мы рекомендуем использовать этот движок вместе с MergeTree. Храните полные данные в таблице MergeTree, а SummingMergeTree используйте для хранения агрегированных данных, например при подготовке отчетов. Такой подход поможет избежать потери ценных данных из-за неправильно составленного первичного ключа.

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

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

Описание параметров запроса см. в разделе Описание запроса.

Параметры SummingMergeTree

Столбцы

columns — кортеж с именами столбцов, по которым будут суммироваться значения. Необязательный параметр. Столбцы должны иметь числовой тип и не входить в партицию или ключ сортировки. Если columns не указан, ClickHouse суммирует значения во всех столбцах с числовым типом данных, которые не входят в ключ сортировки.

Секции запроса

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

Пример использования

Рассмотрим следующую таблицу:

CREATE TABLE summtt
(
    key UInt32,
    value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key

Вставьте в неё данные:

INSERT INTO summtt VALUES(1,1),(1,2),(2,1)

ClickHouse может суммировать строки не полностью (см. ниже), поэтому в запросе мы используем агрегатную функцию sum и предложение GROUP BY.

SELECT key, sum(value) FROM summtt GROUP BY key

┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘

Обработка данных

Когда данные вставляются в таблицу, они сохраняются как есть. ClickHouse периодически выполняет слияние вставленных частей данных, и именно в этот момент строки с одинаковым первичным ключом суммируются и заменяются одной строкой в каждой результирующей части данных. ClickHouse может выполнять слияние частей данных так, что разные результирующие части данных могут содержать строки с одинаковым первичным ключом, то есть суммирование будет неполным. Поэтому в запросе следует использовать (SELECT) агрегатную функцию sum() и GROUP BY, как описано в примере выше.

Общие правила суммирования

Суммируются значения в столбцах с числовым типом данных. Набор столбцов задаётся параметром columns. Если во всех столбцах, участвующих в суммировании, значения равны 0, строка удаляется. Если столбец не входит в первичный ключ и не суммируется, из существующих значений выбирается произвольное. Для столбцов, входящих в первичный ключ, значения не суммируются.

Суммирование в столбцах AggregateFunction

Для столбцов типа AggregateFunction ClickHouse работает как движок AggregatingMergeTree, выполняя агрегацию в соответствии с функцией.

Вложенные структуры

Таблица может иметь вложенные структуры данных, которые обрабатываются особым образом. Если имя вложенной таблицы оканчивается на Map и она содержит как минимум два столбца, удовлетворяющих следующим критериям:

первый столбец — числовой (*Int*, Date, DateTime) или строковый (String, FixedString), назовём его key,
остальные столбцы — арифметические (*Int*, Float32/64), назовём их (values...),

то такая вложенная таблица интерпретируется как отображение key => (values...), и при слиянии её строк элементы двух наборов данных объединяются по key с суммированием соответствующих (values...). Примеры:

DROP TABLE IF EXISTS nested_sum;
CREATE TABLE nested_sum
(
    date Date,
    site UInt32,
    hitsMap Nested(
        browser String,
        imps UInt32,
        clicks UInt32
    )
) ENGINE = SummingMergeTree
PRIMARY KEY (date, site);

INSERT INTO nested_sum VALUES ('2020-01-01', 12, ['Firefox', 'Opera'], [10, 5], [2, 1]);
INSERT INTO nested_sum VALUES ('2020-01-01', 12, ['Chrome', 'Firefox'], [20, 1], [1, 1]);
INSERT INTO nested_sum VALUES ('2020-01-01', 12, ['IE'], [22], [0]);
INSERT INTO nested_sum VALUES ('2020-01-01', 10, ['Chrome'], [4], [3]);

OPTIMIZE TABLE nested_sum FINAL; -- emulate merge

SELECT * FROM nested_sum;
┌───────date─┬─site─┬─hitsMap.browser───────────────────┬─hitsMap.imps─┬─hitsMap.clicks─┐
│ 2020-01-01 │   10 │ ['Chrome']                        │ [4]          │ [3]            │
│ 2020-01-01 │   12 │ ['Chrome','Firefox','IE','Opera'] │ [20,11,22,5] │ [1,3,0,1]      │
└────────────┴──────┴───────────────────────────────────┴──────────────┴────────────────┘

SELECT
    site,
    browser,
    impressions,
    clicks
FROM
(
    SELECT
        site,
        sumMap(hitsMap.browser, hitsMap.imps, hitsMap.clicks) AS imps_map
    FROM nested_sum
    GROUP BY site
)
ARRAY JOIN
    imps_map.1 AS browser,
    imps_map.2 AS impressions,
    imps_map.3 AS clicks;

┌─site─┬─browser─┬─impressions─┬─clicks─┐
│   12 │ Chrome  │          20 │      1 │
│   12 │ Firefox │          11 │      3 │
│   12 │ IE      │          22 │      0 │
│   12 │ Opera   │           5 │      1 │
│   10 │ Chrome  │           4 │      3 │
└──────┴─────────┴─────────────┴────────┘

При запросе данных используйте функцию sumMap(key, value) для агрегации Map. Для вложенной структуры данных не нужно указывать её столбцы в кортеже столбцов, используемом для суммирования.

Агрегация элементов Tuple

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

Суммируются только числовые подстолбцы.
Подстолбцы, входящие в Tuple в ключе сортировки или ключе партиционирования, исключаются из суммирования.
Если указан columns, суммируются только подстолбцы перечисленных столбцов Tuple.
Если после суммирования все числовые подстолбцы строки равны нулю, строка удаляется.

Эта настройка неизменяема и должна быть указана при создании таблицы.

CREATE TABLE summing_tuples
(
    key UInt32,
    metrics Tuple(
        impressions UInt64,
        clicks UInt64,
        nested Tuple(
            conversions UInt64
        )
    )
) ENGINE = SummingMergeTree()
ORDER BY key
SETTINGS allow_tuple_element_aggregation = 1;

INSERT INTO summing_tuples VALUES (1, (100, 10, (1)));
INSERT INTO summing_tuples VALUES (1, (200, 20, (3)));

OPTIMIZE TABLE summing_tuples FINAL;

SELECT key, metrics.impressions, metrics.clicks, metrics.nested.conversions FROM summing_tuples;

┌─key─┬─metrics.impressions─┬─metrics.clicks─┬─metrics.nested.conversions─┐
│   1 │                 300 │             30 │                          4 │
└─────┴─────────────────────┴────────────────┴────────────────────────────┘

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

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

​Параметры SummingMergeTree

​Столбцы

​Секции запроса

​Пример использования

​Обработка данных

​Общие правила суммирования

​Суммирование в столбцах AggregateFunction

​Вложенные структуры

​Агрегация элементов Tuple

​См. также

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

Параметры SummingMergeTree

Столбцы

Секции запроса

Пример использования

Обработка данных

Общие правила суммирования

Суммирование в столбцах AggregateFunction

Вложенные структуры

Агрегация элементов Tuple

См. также