Moteur de table SummingMergeTree

Le moteur hérite de MergeTree. La différence est que, lors de la fusion des parts de données des tables SummingMergeTree, ClickHouse remplace toutes les lignes ayant la même clé primaire (ou, plus précisément, la même clé de tri) par une seule ligne contenant les valeurs additionnées des colonnes de type de données numérique. Si la clé de tri est définie de sorte qu'une même valeur de clé corresponde à un grand nombre de lignes, cela réduit considérablement le volume de stockage et accélère la sélection des données.

Nous recommandons d'utiliser ce moteur avec MergeTree. Stockez les données complètes dans une table MergeTree et utilisez SummingMergeTree pour stocker des données agrégées, par exemple pour préparer des rapports. Cette approche vous évitera de perdre des données précieuses à cause d'une clé primaire mal définie.

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

Pour une description des paramètres de la requête, voir description de la requête.

columns - un tuple contenant les noms des colonnes dont les valeurs seront additionnées. Paramètre facultatif. Les colonnes doivent être de type numérique et ne doivent pas faire partie de la partition ni de la clé de tri.

Si columns n'est pas spécifié, ClickHouse additionne les valeurs de toutes les colonnes de type numérique qui ne font pas partie de la clé de tri.

Lors de la création d'une table SummingMergeTree, les mêmes clauses sont requises que pour la création d'une table 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])

Prenons la table suivante :

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

Insérez-y des données :

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

ClickHouse peut ne pas avoir encore additionné toutes les lignes (voir ci-dessous) ; nous utilisons donc une fonction d’agrégation sum et la clause GROUP BY dans la requête.

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

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

Lorsque des données sont insérées dans une table, elles sont enregistrées telles quelles. ClickHouse fusionne périodiquement les parties de données insérées, et c’est à ce moment-là que les lignes ayant la même clé primaire sont additionnées et remplacées par une seule ligne dans chaque partie de données résultante.

ClickHouse peut fusionner les parties de données de telle sorte que différentes parties de données résultantes puissent contenir des lignes ayant la même clé primaire, c.-à-d. que la sommation sera incomplète. Par conséquent, il convient d’utiliser dans une requête (SELECT) une fonction d’agrégation sum() et la clause GROUP BY, comme décrit dans l’exemple ci-dessus.

Les valeurs des colonnes de type de données numérique sont additionnées. L’ensemble des colonnes est défini par le paramètre columns.

Si les valeurs sont égales à 0 dans toutes les colonnes à sommer, la ligne est supprimée.

Si une colonne ne fait pas partie de la clé primaire et n’est pas additionnée, une valeur arbitraire est choisie parmi les valeurs existantes.

Les valeurs des colonnes de la clé primaire ne sont pas additionnées.

Pour les colonnes de type AggregateFunction, ClickHouse se comporte comme le moteur AggregatingMergeTree et agrège selon la fonction.

Une table peut contenir des structures de données imbriquées qui sont traitées de manière particulière.

Si le nom d'une table imbriquée se termine par Map et qu'elle contient au moins deux colonnes répondant aux critères suivants :

• la première colonne est numérique (*Int*, Date, DateTime) ou de type chaîne (String, FixedString), appelons-la key,
• les autres colonnes sont arithmétiques (*Int*, Float32/64), appelons-les (values...),

alors cette table imbriquée est interprétée comme une association de key => (values...), et lors de la fusion de ses lignes, les éléments de deux ensembles de données sont fusionnés par key, avec addition des (values...) correspondantes.

Exemples :

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 │
└──────┴─────────┴─────────────┴────────┘

Lorsque vous demandez des données, utilisez la fonction sumMap(key, value) pour agréger les Map.

Pour une structure de données imbriquée, vous n’avez pas besoin de spécifier ses colonnes dans le tuple des colonnes à sommer.

Lorsque le paramètre allow_tuple_element_aggregation est activé, les colonnes Tuple sont aplaties récursivement afin que chaque élément terminal participe indépendamment à la sommation. Cela permet de stocker plusieurs métriques dans une seule colonne Tuple et de les additionner élément par élément lors des fusions.

Les mêmes règles s'appliquent aux sous-colonnes aplaties qu'aux colonnes ordinaires :

• Seules les sous-colonnes numériques sont additionnées.
• Les sous-colonnes appartenant à un Tuple dans la clé de tri ou la clé de partition sont exclues de la sommation.
• Si columns est spécifié, seules les sous-colonnes des colonnes Tuple répertoriées sont additionnées.
• Si toutes les sous-colonnes numériques d'une ligne valent zéro après la sommation, la ligne est supprimée.

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 │
└─────┴─────────────────────┴────────────────┴────────────────────────────┘

• Blog : Utiliser les combinateurs d’agrégation dans ClickHouse