Moteur de table AggregatingMergeTree

Le moteur hérite de MergeTree en modifiant la logique de fusion des parties de données. 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 (au sein d'une même partie de données) qui stocke une combinaison d'états de fonctions d'agrégation.

Vous pouvez utiliser les tables AggregatingMergeTree pour l'agrégation incrémentielle des données, notamment pour les vues matérialisées agrégées.

Vous trouverez ci-dessous une vidéo montrant comment utiliser AggregatingMergeTree et les fonctions d'agrégation :

Le moteur traite toutes les colonnes des types suivants :

• AggregateFunction
• SimpleAggregateFunction

Il est recommandé d'utiliser AggregatingMergeTree si cela réduit le nombre de lignes de plusieurs ordres de grandeur.

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

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

Clauses de la requête

Lors de la création d’une table AggregatingMergeTree, 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 [=] AggregatingMergeTree(date-column [, sampling_expression], (primary, key), index_granularity)

Pour insérer des données, utilisez la requête INSERT SELECT avec des fonctions d'agrégation -State-. Lorsque vous sélectionnez des données dans une table AggregatingMergeTree, utilisez la clause GROUP BY et les mêmes fonctions d'agrégation que lors de l'insertion des données, mais avec le suffixe -Merge.

Dans les résultats d'une requête SELECT, les valeurs de type AggregateFunction ont une représentation binaire spécifique à l'implémentation dans tous les formats de sortie de ClickHouse. Par exemple, si vous exportez des données au format TabSeparated avec une requête SELECT, cet export peut ensuite être rechargé à l'aide d'une requête INSERT.

L'exemple suivant suppose que vous disposez d'une base de données nommée test. Créez-la si elle n'existe pas encore à l'aide de la commande ci-dessous :

CREATE DATABASE test;

Créez maintenant la table test.visits qui contient les données brutes :

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

Ensuite, vous avez besoin d'une table AggregatingMergeTree qui stockera des AggregationFunctions chargées de suivre le nombre total de visites et le nombre d'utilisateurs uniques.

Créez une vue matérialisée AggregatingMergeTree qui surveille la table test.visits et utilise le type 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);

Créez une vue matérialisée qui alimente test.agg_visits à partir de 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;

Insérez des données dans la table test.visits :

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

Les données sont insérées dans test.visits et test.agg_visits.

Pour récupérer les données agrégées, exécutez une requête telle que SELECT ... GROUP BY ... depuis la vue matérialisée 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 │
└─────────────────────────┴────────┴───────┘

Ajoutez quelques enregistrements supplémentaires dans test.visits, mais cette fois en utilisant un timestamp différent pour l'un des enregistrements :

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

Exécutez à nouveau la requête SELECT, qui renverra la sortie suivante :

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

Dans certains cas, vous pouvez souhaiter éviter la pré-agrégation des lignes au moment de l'insertion afin de reporter le coût de l'agrégation de l'insert time vers le merge time. En règle générale, il est nécessaire d'inclure les colonnes qui ne font pas partie de l'agrégation dans la clause GROUP BY de la définition de la vue matérialisée pour éviter une erreur. Vous pouvez cependant utiliser la fonction initializeAggregation avec le paramètre optimize_on_insert = 0 (activé par défaut) pour y parvenir. L'utilisation de GROUP BY n'est alors plus nécessaire :

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;

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 à l’agrégation. Cela signifie que les sous-colonnes AggregateFunction ou SimpleAggregateFunction à l’intérieur d’un Tuple sont agrégées selon leurs fonctions respectives, comme s’il s’agissait de colonnes de premier niveau.

Les sous-colonnes appartenant à un Tuple dans la clé de tri sont exclues de l’agrégation. Les sous-colonnes non agrégées sont traitées comme des colonnes ordinaires (leur première valeur est conservée).

CREATE TABLE agg_tuples
(
    key UInt32,
    metrics Tuple(
        total_visits SimpleAggregateFunction(sum, UInt64),
        unique_users SimpleAggregateFunction(max, UInt64)
    )
) ENGINE = AggregatingMergeTree()
ORDER BY key
SETTINGS allow_tuple_element_aggregation = 1;

INSERT INTO agg_tuples VALUES (1, (100, 5));
INSERT INTO agg_tuples VALUES (1, (200, 8));
INSERT INTO agg_tuples VALUES (2, (50, 3));

OPTIMIZE TABLE agg_tuples FINAL;

SELECT key, metrics.total_visits, metrics.unique_users FROM agg_tuples ORDER BY key;

┌─key─┬─metrics.total_visits─┬─metrics.unique_users─┐
│   1 │                  300 │                    8 │
│   2 │                   50 │                    3 │
└─────┴──────────────────────┴──────────────────────┘

total_visits est agrégé à l’aide de sum (100 + 200 = 300), tandis que unique_users est agrégé à l’aide de max (max(5, 8) = 8).

• Blog : Utilisation des combinateurs d’agrégation dans ClickHouse