Движок таблицы 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, ...]

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

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

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

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

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(date-column [, sampling_expression], (primary, key), index_granularity, [columns])

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

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 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.

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

Когда настройка 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