Créez votre première table MergeTree

Découvrez le fonctionnement du principal moteur de table de ClickHouse en créant une table MergeTree, en chargeant des données britanniques sur les prix de l’immobilier et en observant comment les parts et les fusions affectent le stockage et les performances des requêtes.

Tous les guides de démarrage rapide

Analytique en temps réelEntrepôt de donnéesObservabilitéIA/MLCloudOss

Prérequis

To successfully follow this guide, you’ll need the following:

• A running ClickHouse Cloud service. If you don’t have one yet, complete the Create your first Cloud service quickstart first.

Ce que vous allez créer

Dans ce guide de démarrage rapide, vous allez créer une table MergeTree pour stocker des enregistrements de ventes de biens résidentiels au Royaume-Uni depuis 1995. Vous concevrez un schéma avec des types de colonnes adaptés, choisirez des clauses ORDER BY et PARTITION BY pertinentes, chargerez des données directement depuis S3, puis interrogerez system.parts pour voir comment ClickHouse organise physiquement les données sur disque. À la fin, vous comprendrez pourquoi le moteur MergeTree est à la base de presque toutes les tables ClickHouse, et comment ses choix de tri et de partitionnement influencent directement les performances des requêtes.

1. Comprendre le fonctionnement de MergeTree

   Avant d’écrire la moindre requête SQL, il est utile de comprendre ce qui distingue MergeTree d’une table de base de données traditionnelle.

   Lorsque vous insérez des données dans une table MergeTree, ClickHouse n’écrit pas les lignes une par une. À la place, il écrit une data part — un petit bloc de lignes trié et compressé — directement sur disque. ClickHouse fusionne ensuite ces parties en arrière-plan au fil du temps. C’est de là que vient le nom : merge + tree.

   Chaque data part est triée selon l’expression ORDER BY de la table. Cet ordre de tri devient l’index de clé primaire, ce qui permet à ClickHouse d’ignorer de grands blocs de données qu’il n’a pas besoin de lire lors d’une requête (c’est ce qu’on appelle le data pruning). Plus les colonnes de votre ORDER BY sont sélectives pour vos requêtes les plus fréquentes, moins ClickHouse lit de données.

   Trois clauses contrôlent la manière dont MergeTree organise vos données :

   Clause	Ce qu’elle fait
   ORDER BY	Trie physiquement les données au sein de chaque partie. Détermine la clé primaire. Obligatoire.
   PARTITION BY	Répartit les données en partitions distinctes, généralement par plage de dates. Les parties de partitions différentes ne sont jamais fusionnées, ce qui permet un élagage rapide des partitions.
   PRIMARY KEY	Prend par défaut la valeur de ORDER BY, sauf si vous définissez explicitement un préfixe plus court. L’index clairsemé est construit à partir de celle-ci.

   Vous devriez maintenant être en mesure d’expliquer la relation entre les data parts, la clé primaire et les performances des requêtes dans une table MergeTree.

2. Aperçu des données source

   Avant de créer votre table, examinez le fichier source à l’aide de la fonction de table s3. Cela vous permet d’interroger directement S3 sans avoir à écrire d’abord des données dans ClickHouse.

   Exécutez ce qui suit dans votre console SQL :

   DESCRIBE s3(
   'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
   );
   

   Notez que presque chaque colonne est inférée en Nullable(String). ClickHouse lit un CSV brut, il ne connaît donc pas les véritables types de données ; c’est ce que vous corrigerez lorsque vous définirez le schéma de votre table à l’étape suivante.

   Affichez un aperçu de quelques lignes :

   SELECT *
   FROM s3(
   'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
   )
   LIMIT 5;
   

   Le jeu de données contient les ventes de biens résidentiels en Angleterre et au pays de Galles enregistrées par le HM Land Registry, y compris l’id de la transaction, le price de vente, la date, le type de bien, les champs d’adresse et les identifiants géographiques. Vous remarquerez également deux colonnes finales (column15, column16) qui sont vides - vous pouvez les ignorer.

   Vérifiez-le en confirmant que vous voyez des lignes avec notamment les colonnes id, price, date, postcode, type, town et county.

3. Concevez et créez votre table MergeTree

   Créez maintenant une table permanente avec un schéma approprié. Les types de colonnes ci-dessous ont été choisis délibérément :

   • LowCardinality(String) est utilisé pour les colonnes ayant un nombre limité de valeurs uniques (codes postaux, noms de villes, noms de comtés). Il utilise en interne un encodage par dictionnaire, ce qui réduit considérablement le volume de stockage et améliore les performances pour le regroupement et le filtrage sur ces colonnes.
   • Enum8 encode les colonnes type et duration sous forme de petits entiers sur le disque, tout en conservant des libellés textuels lisibles dans les requêtes. Le CSV source utilise des codes à une seule lettre ; nous les mapperons donc lors de l’insertion.
   • PARTITION BY toYYYYMM(date) crée une partition par mois calendaire, ce qui permet à ClickHouse d’ignorer des mois entiers lorsque votre clause WHERE filtre sur date.
   • ORDER BY (postcode, addr1, addr2) trie les données pour permettre des recherches rapides par adresse du bien — le modèle d’accès le plus naturel pour ce jeu de données.

   CREATE TABLE uk_price_paid
   (
   price      UInt32,
   date       Date,
   postcode   LowCardinality(String),
   type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
   is_new     UInt8,
   duration   Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
   addr1      String,
   addr2      String,
   street     LowCardinality(String),
   locality   LowCardinality(String),
   town       LowCardinality(String),
   district   LowCardinality(String),
   county     LowCardinality(String)
   )
   ENGINE = MergeTree
   PARTITION BY toYYYYMM(date)
   ORDER BY (postcode, addr1, addr2);
   

   Vérifiez que la table a bien été créée en exécutant :

   SHOW CREATE TABLE uk_price_paid;
   

   Double-cliquez sur la cellule de résultat pour afficher la sortie complète. Notez que, bien que vous ayez spécifié ENGINE = MergeTree, ClickHouse Cloud a créé la table avec SharedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}'). C’est normal : Cloud convertit automatiquement MergeTree en SharedMergeTree, ce qui ajoute la réplication et la prise en charge du stockage partagé. Le comportement et l’interface de requête restent les mêmes.

4. Charger des données depuis S3

   Insérez l’intégralité du jeu de données en sélectionnant directement à partir de la fonction de table s3(). ClickHouse lit le fichier compressé depuis S3 en flux et l’écrit dans votre table sous forme de parties triées.

   INSERT INTO uk_price_paid
   SELECT
       toUInt32(price),
       date,
       postcode,
       transform(type, ['T', 'S', 'D', 'F', 'O'],
           ['terraced', 'semi-detached', 'detached', 'flat', 'other'], 'other') AS type,
       if(is_new = 'Y', 1, 0) AS is_new,
       transform(duration, ['F', 'L', 'U'],
           ['freehold', 'leasehold', 'unknown'], 'unknown') AS duration,
       addr1,
       addr2,
       street,
       locality,
       town,
       district,
       county
   FROM s3(
   'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
   );
   

   Comme le CSV source stocke tout sous forme de chaînes de caractères avec des codes d’une seule lettre (par ex. T pour maison mitoyenne, F pour pleine propriété, Y/N pour construction neuve), nous utilisons transform pour les convertir en libellés explicites et toUInt32/if pour convertir les colonnes numériques. Les colonnes id, column15 et column16 sont exclues, car nous n’en avons pas besoin.

   Cela prendra une à deux minutes, selon la taille de votre service. Une fois l’opération terminée, confirmez le nombre de lignes :

   SELECT formatReadableQuantity(count())
   FROM uk_price_paid;
   

   Vous devriez voir environ 30 millions de lignes chargées.

5. Inspecter les parts à l’aide de system.parts

   C’est ici que les composants internes de MergeTree deviennent visibles. La table system.parts suit chaque data part sur le disque pour chaque table MergeTree de votre service.

   SELECT
   partition,
   name,
   rows,
   bytes_on_disk,
   marks
   FROM system.parts
   WHERE table = 'uk_price_paid'
   AND active = true
   ORDER BY partition
   LIMIT 20;
   

   Chaque ligne représente une partie de données active. Remarquez :

   • partition - la valeur YYYYMM dérivée de votre expression PARTITION BY. Les données de chaque mois sont isolées.
   • name - le nom de la partie encode la partition, la plage de numéros de blocs et le niveau de fusion (par ex. 199501_1_4_2 signifie la partition 199501, les blocs 1–4, fusionnés deux fois).
   • marks - le nombre de granules d’index. Chaque granule couvre 8 192 lignes par défaut, et l’index de clé primaire stocke une entrée par granule. C’est cet index clairsemé qui reste en mémoire et permet d’ignorer rapidement les données non pertinentes.
   • bytes_on_disk - ClickHouse compresse par défaut chaque partie, colonne par colonne, avec LZ4. Comparez cette valeur à la taille brute pour apprécier le taux de compression.

   Pour voir le nombre total de parties et la taille compressée totale de votre table, exécutez :

   SELECT
   count()          AS parts,
   sum(rows)        AS total_rows,
   formatReadableSize(sum(bytes_on_disk)) AS compressed_size
   FROM system.parts
   WHERE table = 'uk_price_paid'
   AND active = true;
   

   Si vous exécutez de nouveau cette requête plus tard, vous remarquerez peut-être que le nombre de parts a diminué. C’est le merge de MergeTree à l’œuvre : ClickHouse fusionne en continu les petites parts en parts plus grandes en arrière-plan, ce qui réduit le nombre de parts. Le filtre active = true garantit que vous ne voyez que les parts actuelles, fusionnées, plutôt que d’anciennes parts en attente de nettoyage.

6. Interrogez les données et observez le comportement de la clé primaire

   Exécutez maintenant de véritables requêtes analytiques. Commencez par rechercher les ventes les plus élevées jamais enregistrées :

   SELECT
   addr1,
   addr2,
   town,
   county,
   price,
   date
   FROM uk_price_paid
   ORDER BY price DESC
   LIMIT 5;
   

   Vérifiez les statistiques de la requête dans la Console SQL : notez que les 30 033 199 lignes ont toutes été lues. Comme price ne fait pas partie de la clé ORDER BY, ClickHouse ne peut pas utiliser l’index primaire pour ignorer certaines données et doit donc effectuer un scan complet de la table.

   Ensuite, recherchez le prix de vente moyen par comté :

   SELECT
   county,
   round(avg(price)) AS avg_price,
   count()           AS sales
   FROM uk_price_paid
   GROUP BY county
   ORDER BY avg_price DESC;
   

   Là encore, les 30 033 199 lignes sont toutes lues - county ne figure ni dans ORDER BY ni dans PARTITION BY, donc ClickHouse parcourt l’ensemble de la table.

   Exécutez maintenant une requête qui combine l’agrégation avec votre ORDER BY. Comme les données sont triées selon (postcode, addr1, addr2), le filtrage sur un préfixe de code postal permet à ClickHouse d’ignorer la majeure partie de la table. Nous déterminons ici le prix de vente moyen par année pour les biens immobiliers de la zone de code postal SW1A :

   SELECT
   toYear(date) AS year,
   round(avg(price)) AS avg_price,
   count() AS sales,
   min(price) AS cheapest,
   max(price) AS most_expensive
   FROM uk_price_paid
   WHERE postcode LIKE 'SW1A%'
   GROUP BY year
   ORDER BY year DESC;
   

   Consultez les statistiques de la requête dans la console SQL après chaque requête. L’agrégation filtrée sur postcode ne devrait lire qu’une fraction des lignes de la table, ce qui montre l’index de clé primaire à l’œuvre. Comparez cela aux requêtes précédentes, qui effectuent un balayage plus large : la différence montre pourquoi il est important de choisir le bon ORDER BY.

Étapes suivantes

Dans ce guide de démarrage rapide, vous avez créé une table MergeTree à partir de zéro, chargé 30 millions d’enregistrements de ventes immobilières au Royaume-Uni depuis S3, exploré comment ClickHouse organise les données en parts triées et en partitions, et exécuté des requêtes qui démontrent la puissance de l’index de clé primaire.

Le moteur MergeTree constitue la base : à partir de là, vous pouvez explorer les moteurs spécialisés qui s’appuient sur lui, ou découvrir comment les vues matérialisées prolongent ce modèle.

Consultez ensuite les guides de démarrage rapide suivants :

• Introduction aux vues matérialisées

Ou allez plus loin avec la documentation de référence :

• Référence du moteur MergeTree
• Référence de system.parts
• Choisir les bons types de colonnes

Check out the ClickHouse academy for on-demand and live training

Tous les guides de démarrage rapide