ApsaraDB for ClickHouse:Moteurs de table

MergeTree

MergeTree est le moteur de base destiné aux charges de travail impliquant des insertions massives. Les données sont insérées par parties et fusionnées en arrière-plan selon l'ordre de tri défini par ORDER BY.

Exemple : MergeTree avec partitionnement

1. Créez une table partitionnée par create_time et triée sur (id, create_time).

   CREATE TABLE test_tbl ON CLUSTER default (
     id UInt16,
     create_time Date,
     comment Nullable(String)
   ) ENGINE = MergeTree()
      PARTITION BY create_time
      ORDER BY (id, create_time)
      PRIMARY KEY (id, create_time)
      SETTINGS index_granularity=8192;

2. Insérez des lignes comportant des clés primaires en double.

   INSERT INTO test_tbl VALUES (1, '2019-12-13', null);
   INSERT INTO test_tbl VALUES (1, '2019-12-13', null);
   INSERT INTO test_tbl VALUES (2, '2019-12-14', null);
   INSERT INTO test_tbl VALUES (3, '2019-12-15', null);
   INSERT INTO test_tbl VALUES (3, '2019-12-15', null);

3. Interrogez la table.

   SELECT * FROM test_tbl;

   Résultat : les cinq lignes sont renvoyées, y compris les doublons :

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

4. Forcez la compaction.

   OPTIMIZE TABLE test_tbl FINAL;

5. Relancez la requête.

   SELECT * FROM test_tbl;

   Résultat : les doublons sont toujours présents. MergeTree ne les supprime pas.

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

Pour plus d'informations, consultez la section MergeTree.

ReplacingMergeTree

ReplacingMergeTree supprime les lignes en double partageant la même clé primaire lors de la compaction en arrière-plan. Utilisez ce moteur lorsque la déduplication à terme est acceptable, car il ne garantit pas l'unicité au moment de l'interrogation.

Limitations

• Dans un cluster distribué, les lignes possédant la même clé primaire peuvent être réparties sur différents shards. La déduplication s'effectue uniquement au sein d'un seul shard, et non entre les shards.

• Avant la fin de la compaction, certains doublons peuvent subsister. La compaction s'exécute en arrière-plan selon un calendrier imprévisible.

• Sur de grands jeux de données, l'exécution manuelle de OPTIMIZE ... FINAL peut prendre beaucoup de temps, rendant la déduplication en temps réel peu pratique.

Exemple : Déduplication avec ReplacingMergeTree

1. Créez une table.

   CREATE TABLE test_tbl_replacing (
     id UInt16,
     create_time Date,
     comment Nullable(String)
   ) ENGINE = ReplacingMergeTree()
      PARTITION BY create_time
      ORDER BY (id, create_time)
      PRIMARY KEY (id, create_time)
      SETTINGS index_granularity=8192;

2. Insérez des lignes comportant des clés primaires en double.

   INSERT INTO test_tbl_replacing VALUES (1, '2019-12-13', null);
   INSERT INTO test_tbl_replacing VALUES (1, '2019-12-13', null);
   INSERT INTO test_tbl_replacing VALUES (2, '2019-12-14', null);
   INSERT INTO test_tbl_replacing VALUES (3, '2019-12-15', null);
   INSERT INTO test_tbl_replacing VALUES (3, '2019-12-15', null);

3. Interrogez la table avant la compaction : les doublons sont toujours présents.

   SELECT * FROM test_tbl_replacing;

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

4. Forcez la compaction.

   OPTIMIZE TABLE test_tbl_replacing FINAL;

5. Relancez la requête : les doublons ont été supprimés.

   SELECT * FROM test_tbl_replacing;

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

CollapsingMergeTree

CollapsingMergeTree suit les changements d'état des lignes à l'aide d'une colonne Sign plutôt que de mettre à jour les lignes directement, car les mises à jour sont coûteuses dans le modèle de stockage append-only de ClickHouse. Chaque ligne est marquée soit comme ligne d'état (Sign = 1), soit comme ligne d'annulation (Sign = -1). Lors de la compaction, les paires de lignes d'état et d'annulation partageant la même clé primaire sont réduites et supprimées.

Fonctionnement de la réduction

Pour mettre à jour l'état d'une ligne :

1. Insérez une ligne d'annulation avec Sign = -1 et les mêmes valeurs de clé primaire que la ligne à remplacer.

2. Insérez une nouvelle ligne d'état avec Sign = 1 et les valeurs mises à jour.

Pour supprimer l'état d'une ligne, insérez une ligne d'annulation correspondant à la ligne d'état d'origine sur toutes les colonnes, à l'exception de Sign.

Remarques d'utilisation

• Si les lignes d'état et d'annulation sont insérées dans le désordre — par exemple, si une ligne d'annulation est insérée puis que sa ligne d'état correspondante arrive séparément — elles risquent de ne pas être réduites correctement. Envisagez d'utiliser VersionedCollapsingMergeTree pour gérer les insertions hors ordre.

• La réduction s'effectue uniquement au sein d'un seul shard. Dans un cluster distribué, les lignes partageant la même clé primaire sur différents nœuds ne sont pas réduites.

• Avant la fin de la compaction, les lignes d'état et d'annulation coexistent. Lorsque vous exécutez des requêtes d'agrégation avant la compaction, adaptez votre code SQL :

  • Remplacez COUNT() par SUM(Sign)

  • Remplacez SUM(col) par SUM(col * Sign)

Exemple : Mises à jour d'état avec CollapsingMergeTree

1. Créez une table avec une colonne Sign.

   CREATE TABLE test_tbl_collapsing
   (
       UserID UInt64,
       PageViews UInt8,
       Duration UInt8,
       Sign Int8
   )
   ENGINE = CollapsingMergeTree(Sign)
   ORDER BY UserID;

2. Insérez la ligne d'état initiale.

   INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, 1);

3. Mettez à jour l'état : insérez une ligne d'annulation pour l'ancien état et une nouvelle ligne d'état.

   Remarque

   Insérez la ligne d'annulation et la nouvelle ligne d'état dans le même lot. Si vous insérez la ligne d'annulation puis la ligne d'état lors d'opérations distinctes, elles peuvent arriver dans le désordre. Dans ce cas, même si la compaction est forcée, les lignes partageant la même clé primaire ne peuvent ni être réduites ni supprimées.

   INSERT INTO test_tbl_collapsing VALUES
     (4324182021466249494, 5, 146, -1),  -- cancel old state
     (4324182021466249494, 6, 185, 1);   -- new state

4. Interrogez la table avant la compaction : les trois lignes sont présentes.

   SELECT * FROM test_tbl_collapsing;

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐
   │ 4324182021466249494 │    5      │    146   │   1   │
   │ 4324182021466249494 │    5      │    146   │  -1   │
   │ 4324182021466249494 │    6      │    185   │   1   │
   └─────────────────────┴───────────┴──────────┴───────┘

   Pour obtenir des résultats d'agrégation corrects avant la compaction, utilisez des expressions prenant en compte Sign :

   SELECT
       UserID,
       SUM(PageViews * Sign) AS PageViews,
       SUM(Duration * Sign) AS Duration
   FROM test_tbl_collapsing
   GROUP BY UserID
   HAVING SUM(Sign) > 0;

   ┌────────UserID───────┬─PageViews─┬─Duration──┐
   │ 4324182021466249494 │    6      │    185    │
   └─────────────────────┴───────────┴───────────┘

5. Forcez la compaction.

   OPTIMIZE TABLE test_tbl_collapsing FINAL;

6. Relancez la requête : seule la dernière ligne d'état subsiste.

   SELECT * FROM test_tbl_collapsing;

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐
   │ 4324182021466249494 │    6      │    185   │   1   │
   └─────────────────────┴───────────┴──────────┴───────┘

VersionedCollapsingMergeTree

VersionedCollapsingMergeTree étend CollapsingMergeTree en ajoutant une colonne Version qui permet au moteur d'associer correctement les lignes d'état et d'annulation, indépendamment de l'ordre d'insertion. Lors de la compaction, les lignes partageant la même clé primaire, la même valeur Version et des valeurs Sign opposées sont réduites et supprimées.

Utilisez ce moteur lorsque les lignes peuvent arriver dans le désordre, par exemple dans des pipelines pilotés par les événements où les événements d'annulation peuvent être traités avant les événements d'état auxquels ils se réfèrent.

Comme pour CollapsingMergeTree, adaptez les requêtes d'agrégation en utilisant SUM(Sign) au lieu de COUNT() et SUM(col * Sign) au lieu de SUM(col).

Exemple : VersionedCollapsingMergeTree avec des insertions hors ordre

1. Créez une table avec les colonnes Sign et Version.

   CREATE TABLE test_tbl_Versioned
   (
       UserID UInt64,
       PageViews UInt8,
       Duration UInt8,
       Sign Int8,
       Version UInt8
   )
   ENGINE = VersionedCollapsingMergeTree(Sign, Version)
   ORDER BY UserID;

2. Insérez d'abord une ligne d'annulation (hors ordre).

   INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, -1, 1);

3. Insérez la ligne d'état correspondante et une nouvelle ligne d'état.

   INSERT INTO test_tbl_Versioned VALUES
     (4324182021466249494, 5, 146, 1, 1),   -- state row matching the cancel above
     (4324182021466249494, 6, 185, 1, 2);   -- new state

4. Interrogez la table avant la compaction.

   SELECT * FROM test_tbl_Versioned;

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐
   │ 4324182021466249494 │    5      │    146   │  -1    │    1   │
   │ 4324182021466249494 │    5      │    146   │   1    │    1   │
   │ 4324182021466249494 │    6      │    185   │   1    │    2   │
   └─────────────────────┴───────────┴──────────┴────────┴────────┘

   Utilisez une agrégation prenant en compte Sign pour obtenir des résultats corrects avant la compaction :

   SELECT
       UserID,
       SUM(PageViews * Sign) AS PageViews,
       SUM(Duration * Sign) AS Duration
   FROM test_tbl_Versioned
   GROUP BY UserID
   HAVING SUM(Sign) > 0;

   ┌────────UserID───────┬─PageViews─┬─Duration─┐
   │ 4324182021466249494 │    6      │    185   │
   └─────────────────────┴───────────┴──────────┘

5. Forcez la compaction.

   OPTIMIZE TABLE test_tbl_Versioned FINAL;

6. Relancez la requête : la colonne Version a correctement apparié les lignes d'annulation et d'état malgré l'insertion hors ordre.

   SELECT * FROM test_tbl_Versioned;

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐
   │ 4324182021466249494 │    6      │    185   │   1    │    2   │
   └─────────────────────┴───────────┴──────────┴────────┴────────┘

SummingMergeTree

Nous recommandons d'utiliser SummingMergeTree conjointement avec MergeTree. Stockez les données brutes détaillées dans une table MergeTree et utilisez SummingMergeTree pour les résultats pré-agrégés. Cela évite toute perte de données si votre clé primaire est mal constituée.

SummingMergeTree pré-agrége les lignes partageant la même clé primaire en une seule ligne en additionnant les colonnes numériques. Cette approche réduit l'espace de stockage et accélère les requêtes d'agrégation.

Remarques d'utilisation

• La pré-agrégation s'effectue lors de la compaction en arrière-plan, et non lors de l'insertion. Certaines données peuvent ne pas encore être agrégées ; incluez donc toujours une clause GROUP BY avec SUM() dans vos requêtes.

• Seules les colonnes numériques sont additionnées. Les colonnes de type chaîne et autres colonnes non numériques qui ne font pas partie de la clé primaire reçoivent une valeur arbitraire après l'agrégation.

Exemple : Pré-agrégation avec SummingMergeTree

1. Créez une table.

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

2. Insérez des lignes, dont certaines partagent la même clé.

   INSERT INTO test_tbl_summing VALUES (1, 1), (1, 2), (2, 1);

3. Interrogez la table avant la compaction : les lignes ne sont pas encore agrégées.

   SELECT * FROM test_tbl_summing;

   ┌─key─┬value─┐
   │  1  │  1   │
   │  1  │  2   │
   │  2  │  1   │
   └─────┴──────┘

4. Forcez la compaction.

   OPTIMIZE TABLE test_tbl_summing FINAL;

5. Effectuez la requête avec GROUP BY et SUM() pour obtenir des résultats corrects.

   SELECT key, SUM(value) FROM test_tbl_summing GROUP BY key;

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

AggregatingMergeTree

AggregatingMergeTree est un moteur de pré-agrégation plus flexible qui prend en charge n'importe quelle fonction d'agrégation, et pas seulement SUM. Il utilise le type de données spécial AggregateFunction et suit un modèle de requête en deux phases :

• Phase d'écriture : utilisez les fonctions suffixées par -State (par exemple, sumState, uniqState) pour stocker les états d'agrégation intermédiaires.

• Phase d'interrogation : utilisez les fonctions suffixées par -Merge (par exemple, sumMerge, uniqMerge) pour finaliser l'agrégation.

AggregatingMergeTree s'utilise de deux manières : avec une vue matérialisée (qui automatise la phase d'écriture) ou directement avec des colonnes AggregateFunction.

Exemple 1 : AggregatingMergeTree avec une vue matérialisée

1. Créez une table de détails.

   CREATE TABLE visits
   (
       UserID UInt64,
       CounterID UInt8,
       StartDate Date,
       Sign Int8
   )
   ENGINE = CollapsingMergeTree(Sign)
   ORDER BY UserID;

2. Créez une vue matérialisée qui pré-agrége la table de détails à l'aide des fonctions suffixées par -State.

   CREATE MATERIALIZED VIEW visits_agg_view
   ENGINE = AggregatingMergeTree()
   PARTITION BY toYYYYMM(StartDate)
   ORDER BY (CounterID, StartDate)
   AS SELECT
       CounterID,
       StartDate,
       sumState(Sign)    AS Visits,
       uniqState(UserID) AS Users
   FROM visits
   GROUP BY CounterID, StartDate;

3. Insérez des données dans la table de détails. La vue matérialisée est mise à jour automatiquement.

   INSERT INTO visits VALUES (0, 0, '2019-11-11', 1);
   INSERT INTO visits VALUES (1, 1, '2019-11-12', 1);

4. Interrogez la vue matérialisée à l'aide des fonctions suffixées par -Merge.

   Remarque

   Utilisez sumMerge et uniqMerge : les fonctions simples sum et uniq ne peuvent pas traiter les colonnes AggregateFunction et renvoient une erreur.

   SELECT
       StartDate,
       sumMerge(Visits) AS Visits,
       uniqMerge(Users) AS Users
   FROM visits_agg_view
   GROUP BY StartDate
   ORDER BY StartDate;

   ┌──StartDate──┬─Visits─┬─Users──┐
   │  2019-11-11 │   1    │   1    │
   │  2019-11-12 │   1    │   1    │
   └─────────────┴────────┴────────┘

Exemple 2 : AggregatingMergeTree avec des colonnes AggregateFunction

1. Créez une table de détails.

   CREATE TABLE detail_table
   (
       CounterID UInt8,
       StartDate Date,
       UserID UInt64
   ) ENGINE = MergeTree()
   PARTITION BY toYYYYMM(StartDate)
   ORDER BY (CounterID, StartDate);

2. Insérez des données.

   INSERT INTO detail_table VALUES (0, '2019-11-11', 1);
   INSERT INTO detail_table VALUES (1, '2019-11-12', 1);

3. Créez une table d'agrégation avec une colonne AggregateFunction.

   CREATE TABLE agg_table
   (
       CounterID UInt8,
       StartDate Date,
       UserID AggregateFunction(uniq, UInt64)
   ) ENGINE = AggregatingMergeTree()
   PARTITION BY toYYYYMM(StartDate)
   ORDER BY (CounterID, StartDate);

4. Insérez des données agrégées à l'aide de uniqState.

   Remarque

   Effectuez l'insertion via une instruction SELECT utilisant des fonctions suffixées par -State. Une instruction INSERT INTO ... VALUES directe avec des valeurs brutes échouera avec une erreur de type.

   INSERT INTO agg_table
   SELECT CounterID, StartDate, uniqState(UserID)
   FROM detail_table
   GROUP BY CounterID, StartDate;

5. Interrogez la table d'agrégation à l'aide de uniqMerge.

   SELECT uniqMerge(UserID) AS state
   FROM agg_table
   GROUP BY CounterID, StartDate;

   ┌─state─┐
   │   1   │
   │   1   │
   └───────┘