Moteurs MergeTree

ClickHouse stocke toutes les données dans des tables reposant sur une variante du moteur MergeTree. Si vous venez de Snowflake, ce concept n’a pas d’équivalent : Snowflake gère toutes les décisions de stockage en interne. Dans ClickHouse, il vous appartient de choisir le moteur adapté, et un mauvais choix produit des résultats incorrects sans avertissement.

Ce guide présente les moteurs utilisés dans le laboratoire NYC Taxi et les pièges que rencontre tout spécialiste d’une migration depuis Snowflake.

Qu’est-ce que MergeTree ?

MergeTree est le principal moteur de stockage de ClickHouse. Les données sont écrites dans des fichiers en colonnes immuables appelés parties. ClickHouse fusionne périodiquement ces parties en arrière-plan : il les trie, les compresse et, selon les règles du moteur, peut les transformer.

Conséquence essentielle : une lecture peut voir plusieurs versions d’une ligne tant qu’aucune fusion n’a eu lieu. La plupart des moteurs gèrent ce comportement de manière transparente, mais certains, notamment ReplacingMergeTree, exigent de comprendre le cycle de fusion pour écrire des requêtes correctes.

Lors de la création d’une table MergeTree, vous devez préciser ORDER BY. Celui-ci détermine :

1. l’ordre de tri physique des données dans chaque partie ;
2. l’index primaire, clairsemé, au niveau des blocs et stocké en mémoire ;
3. pour les moteurs qui dédupliquent, les colonnes définissant la « clé » de déduplication.

Il n’existe aucun concept distinct de clé primaire, d’index clusterisé ou de clé de distribution. ORDER BY remplit tous ces rôles à la fois.

MergeTree

À utiliser lorsque : la table ne reçoit que des insertions ou lorsque les mises à jour sont gérées à l’extérieur. Aucune déduplication n’est nécessaire.

CREATE TABLE default.some_events (
    event_id      String,
    occurred_at   DateTime64(3, 'UTC'),
    payload       String
)
ENGINE = MergeTree()
ORDER BY (occurred_at, event_id);

Caractéristiques :

• Les insertions ajoutent les données sous forme de nouvelles parties
• Aucune déduplication : les lignes en double sont conservées
• Les fusions optimisent le stockage et la compression sans modifier le contenu logique
• Les requêtes lisent toutes les parties correspondant à la plage du préfixe ORDER BY

Cause d’erreur : si vous insérez deux fois la même ligne, par exemple après une nouvelle tentative consécutive à une erreur réseau, les deux apparaissent dans les résultats. C’est correct pour un pipeline exclusivement alimenté par insertion dans lequel aucun doublon ne peut se produire. Pour toute table recevant des mises à jour CDC ou des chargements susceptibles d’être relancés, utilisez ReplacingMergeTree.

ReplacingMergeTree

À utiliser lorsque : des lignes peuvent être modifiées, par exemple après une correction de tarif ou un changement de statut, et que vous souhaitez une seule ligne par clé dans les résultats.

CREATE TABLE analytics.fact_trips (
    trip_id       String,
    pickup_at     DateTime64(3, 'UTC'),
    fare_amount   Float64,
    updated_at    DateTime64(3, 'UTC'),
    -- ...
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (toStartOfMonth(pickup_at), pickup_at, trip_id);

Caractéristiques :

• Pendant les fusions en arrière-plan, les lignes partageant la même clé ORDER BY sont dédupliquées : seule celle dont la valeur de colonne de version est la plus élevée est conservée
• La colonne de version, ici updated_at, détermine la ligne gagnante : la valeur la plus élevée, donc la plus récente, est conservée
• La déduplication est asynchrone : jusqu’à une fusion, l’ancienne et la nouvelle version coexistent

Piège essentiel : le retard de déduplication

Entre deux fusions, une requête sans FINAL voit toutes les versions d’une ligne :

-- This may return multiple rows for the same trip_id
-- if the row has been updated since the last merge
SELECT * FROM analytics.fact_trips WHERE trip_id = 'abc123';

-- This returns exactly one row per trip_id, applying deduplication at query time
SELECT * FROM analytics.fact_trips FINAL WHERE trip_id = 'abc123';

FINAL force la déduplication au moment de la lecture. Cette opération est plus lente qu’une lecture sans FINAL, car ClickHouse doit rechercher les clés en double dans toutes les parties. Dans le laboratoire NYC Taxi, toutes les requêtes sur fact_trips emploient FINAL.

Causes d’erreur :

• Omettre FINAL lors d’une recherche ponctuelle → renvoie silencieusement des lignes en double et surestime les agrégats
• Employer une mauvaise colonne de version, dont la valeur n’augmente pas à chaque mise à jour → les anciennes valeurs l’emportent
• Employer MergeTree au lieu de RMT pour une table mutable → toutes les versions s’accumulent et le nombre de lignes augmente sans limite
• Attendre une déduplication synchrone → la tâche ETL lit immédiatement après l’insertion et voit des doublons

RMT avec dbt : la stratégie incrémentielle delete_insert supprime les lignes de la plage de clés du lot entrant avant l’insertion, si bien que la table ne contient jamais de doublons. FINAL reste recommandé par sécurité, mais devient moins critique lorsque la stratégie dbt est correcte.

AggregatingMergeTree

À utiliser lorsque : la table stocke des états d’agrégation partiels qui doivent être fusionnés pendant les fusions en arrière-plan, puis combinés au moment de la requête.

CREATE TABLE analytics.agg_hourly_revenue (
    hour_bucket   DateTime,
    borough       String,
    fare_sum      AggregateFunction(sum, Float64),
    trip_count    AggregateFunction(count, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour_bucket, borough);

Caractéristiques :

• Les lignes partageant la même clé ORDER BY sont fusionnées selon la logique de combinaison de la fonction d’agrégation
• Au moment de la requête, on utilise des fonctions de combinaison portant le suffixe -Merge : sumMerge(fare_sum), countMerge(trip_count)
• Une vue matérialisée qui transforme les insertions brutes en états partiels alimente généralement la table

Quand l’utiliser : AggregatingMergeTree convient aux données préagrégées dont les états partiels doivent pouvoir être combinés. Dans le laboratoire NYC Taxi, agg_hourly_zone_trips est reconstruit par dbt à chaque exécution : il s’agit d’une table intégralement remplacée et non d’un accumulateur d’états partiels. Utilisez-y ReplacingMergeTree.

Cause d’erreur : employer sum(fare_sum) au lieu de sumMerge(fare_sum) au moment de la requête interprète l’état binaire de l’agrégat comme un Float64 et renvoie des valeurs absurdes, sans signaler l’erreur.

CollapsingMergeTree

À utiliser lorsque : vous devez supprimer ou modifier des lignes en insérant une « ligne de signe », avec sign=1 pour une insertion et sign=-1 pour une annulation. Ce cas est moins courant, mais utile pour des modèles CDC fondés sur les événements.

ENGINE = CollapsingMergeTree(sign)

Lors des fusions, les paires de lignes de même clé dont les signes valent 1 et -1 s’annulent. Ce moteur n’est pas utilisé dans le laboratoire NYC Taxi : ReplacingMergeTree avec une colonne de version convient mieux au modèle de nouvelle tentative d’insertion de cette charge de travail.

MergeTree avec TTL

Ajoutez une expiration temporelle des données à n’importe quelle variante de MergeTree :

CREATE TABLE default.trips_raw (
    trip_id    String,
    pickup_at  DateTime64(3, 'UTC'),
    _synced_at DateTime DEFAULT now(),
    -- ...
)
ENGINE = ReplacingMergeTree(_synced_at)
ORDER BY (pickup_at, trip_id)
TTL toDate(pickup_at) + INTERVAL 2 YEAR;

La TTL s’applique pendant les fusions en arrière-plan. Les lignes expirées sont retirées des parties au moment de leur fusion. Le laboratoire ne configure aucune TTL : les quatre années de données sont conservées. En production, la TTL est indispensable pour maîtriser les coûts de stockage.

Choisir un moteur : arbre de décision

Does the table receive UPDATE or DELETE operations?
├── No (insert-only, e.g., event log, append-only stream)
│   └── MergeTree()
└── Yes
    ├── Do rows have a version/timestamp column that increases on update?
    │   ├── Yes → ReplacingMergeTree(version_col)
    │   └── No (full reload, e.g., dim tables rebuilt by dbt)
    │       └── MergeTree() — dbt atomic table swap (full rebuild) handles "upsert"
    └── Is the table a pre-aggregated accumulator with combinable states?
        └── AggregatingMergeTree()

Pour le laboratoire NYC Taxi :

Conception de ORDER BY

Le ORDER BY est la décision de performance la plus importante d’une table ClickHouse. Il détermine :

1. L’efficacité de l’index primaire — les requêtes qui filtrent sur les colonnes du préfixe ORDER BY ignorent les blocs non pertinents
2. Le taux de compression — les données triées se compressent mieux, car des valeurs semblables sont adjacentes
3. La clé de déduplication, pour RMT et AMT — deux lignes ne sont des doublons que si leurs colonnes ORDER BY correspondent

Règles de conception de ORDER BY :

1. Placez d’abord les colonnes à faible cardinalité, par exemple borough ou payment_type : davantage de lignes partagent une valeur et l’index peut ignorer plus de blocs
2. Placez en dernier les colonnes à forte cardinalité, par exemple trip_id ou un UUID : elles réduisent la plage, mais se compressent moins bien en début de clé
3. Déduisez les colonnes des filtres réels des requêtes, et non du schéma source
4. Pour les tables RMT, la dernière colonne doit être l’identifiant de ligne unique, afin de garantir une ligne par clé métier

Anti-modèle : recopier la clé primaire source dans ORDER BY. Si TRIPS_RAW ne possède aucun tri explicite dans Snowflake, reprendre l’ordre du schéma Snowflake, avec trip_id en premier, donne à ClickHouse un ORDER BY aléatoire : aucune requête analytique ne peut alors ignorer de blocs.

Exemple de dérivation pour fact_trips :

Les requêtes Q1 à Q7 filtrent toutes sur pickup_at sous une forme ou une autre :

• Q1 : WHERE pickup_at >= ...
• Q2 : ORDER BY week, pickup_location_id
• Q3 : WHERE pickup_at >= CURRENT_DATE - 7
• Q4 : GROUP BY DATE_TRUNC('day', pickup_at)

pickup_at doit donc figurer dans ORDER BY, plutôt au début. Employer toStartOfMonth(pickup_at) comme première colonne crée un préfixe plus grossier qui permet d’éliminer des partitions même sans clause PARTITION BY. Pour assurer l’unicité RMT, trip_id vient en dernier.

Résultat : ORDER BY (toStartOfMonth(pickup_at), pickup_at, trip_id)

PARTITION BY

PARTITION BY est facultatif et distinct de ORDER BY. Il crée des partitions sous forme de répertoires physiques, chacune constituant un ensemble indépendant de parties.

PARTITION BY toYYYYMM(pickup_at)

Utilisez PARTITION BY lorsque :

• vous devez supprimer efficacement une plage de temps entière avec ALTER TABLE DROP PARTITION '202401' ;
• vous souhaitez que la TTL s’applique par mois plutôt que par ligne ;
• la table est très grande, plus de 1 To, et que les métadonnées par partition peuvent aider la planification des requêtes.

N’utilisez PAS PARTITION BY pour remplacer ORDER BY. Une erreur courante consiste à placer toYYYYMM(date) dans PARTITION BY sans l’inclure dans ORDER BY, ce qui empêche d’ignorer des blocs au sein d’une partition.

Le laboratoire NYC Taxi n’a pas besoin de PARTITION BY : ses 50 millions de lignes, soit environ 8 Go compressés, restent largement dans la plage de performances d’une partition unique.

Récapitulatif des principaux pièges