Créez votre première projection

Apprenez à utiliser les projections dans ClickHouse pour stocker une copie supplémentaire triée de vos données dans la même table, afin de permettre des recherches rapides sur des colonnes non couvertes par votre clé primaire.

Tous les guides de démarrage rapide

Analytique en temps réelData warehousingObservabilityIA/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.

Vous devez également avoir suivi les guides de démarrage rapide suivants, car ce guide s’appuie directement sur la table uk_price_paid et sur les concepts qui y sont présentés :

• Créer votre première table MergeTree
• Créer votre première vue matérialisée

Ce que vous allez créer

Dans le guide de démarrage rapide sur MergeTree, vous avez vu qu’effectuer des requêtes sur uk_price_paid selon town ou county nécessite un scan complet de la table, car celle-ci est triée par (postcode, addr1, addr2). Dans ce guide de démarrage rapide, vous allez résoudre ce problème en créant une projection — une représentation triée supplémentaire de vos données, stockée dans la même table. Contrairement aux vues matérialisées, les projections ne nécessitent pas de table de destination distincte, restent synchronisées avec les mutations (suppressions et mises à jour) et sont utilisées de manière transparente par l’optimiseur de requêtes — vous continuez à interroger la même table. À la fin, vous saurez comment ajouter et matérialiser une projection, comment ClickHouse la sélectionne automatiquement et dans quels cas privilégier les projections aux vues matérialisées.

1. Comprendre pourquoi vous avez besoin d’une projection

   Votre table uk_price_paid est triée selon (postcode, addr1, addr2). Cela signifie que ClickHouse peut ignorer de grands blocs de données lorsque vous filtrez sur postcode, addr1 ou addr2, mais les requêtes qui filtrent sur town doivent parcourir chaque ligne - soit la totalité des 30 millions.

   Une projection stocke une copie triée supplémentaire de (certaines ou de toutes les) colonnes dans la même table. Lorsque vous interrogez la table, l’optimiseur de requêtes vérifie automatiquement si la lecture depuis la projection nécessiterait moins de granules que les données de base, et l’utilise de façon transparente si c’est le cas.

   Principales différences par rapport aux vues matérialisées :

   • Pas de table distincte - la projection se trouve dans uk_price_paid elle-même
   • Optimisation transparente des requêtes - vous interrogez uk_price_paid normalement ; ClickHouse choisit automatiquement la projection
   • Reste synchronisée avec les mutations - les suppressions et les mises à jour appliquées à la table sont répercutées dans la projection

   Pour en savoir plus, consultez la documentation de référence sur les projections.

2. Ajouter une projection à votre table

   Définissez une projection sur uk_price_paid qui stocke town, date, price et type, triés selon (town, date) :

   ALTER TABLE uk_price_paid
       ADD PROJECTION uk_price_paid_by_town
       (
           SELECT town, date, price, type
           ORDER BY (town, date)
       );
   

   Cela enregistre la projection dans les métadonnées de la table, mais ne la matérialise pas pour les données existantes : seules les insertions futures l’alimenteront.

   Vérifiez que la projection apparaît dans la définition de la table :

   SHOW CREATE TABLE uk_price_paid;
   

   Vous devriez voir le bloc PROJECTION uk_price_paid_by_town dans le résultat.

3. Matérialiser la projection pour les données existantes

   Comme les vues matérialisées, une projection ajoutée récemment ne s’applique qu’aux futures insertions. Pour l’alimenter avec les 30 millions de lignes déjà présentes dans la table, matérialisez-la explicitement :

   ALTER TABLE uk_price_paid
       MATERIALIZE PROJECTION uk_price_paid_by_town;
   

   Cela s’exécute en arrière-plan, sous forme de mutation. Vous pouvez en suivre la progression :

   SELECT
       mutation_id,
       command,
       is_done
   FROM system.mutations
   WHERE table = 'uk_price_paid'
   ORDER BY create_time DESC
   LIMIT 5;
   

   Une fois is_done = 1, la projection est entièrement matérialisée. Vous pouvez aussi le vérifier en consultant system.projection_parts :

   SELECT
       name,
       count() AS parts,
       sum(rows) AS total_rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
   FROM system.projection_parts
   WHERE table = 'uk_price_paid'
     AND active = true
   GROUP BY name;
   
4. Interroger la table et observer l’utilisation automatique des projections

   Exécutez maintenant une requête en filtrant sur town — sur la même table que précédemment :

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   Vérifiez les statistiques de la requête : beaucoup moins de lignes sont lues qu’avant la création de la projection, car ClickHouse a automatiquement choisi de lire à partir de la projection uk_price_paid_by_town au lieu de parcourir les données de base.

   Vous pouvez confirmer que la projection a bien été utilisée avec EXPLAIN :

   EXPLAIN
   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   Recherchez ReadFromMergeTree dans le résultat, avec une référence au nom de la projection. Si vous souhaitez comparer explicitement les performances, vous pouvez désactiver l’optimisation des projections pour une seule requête :

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC
   SETTINGS optimize_use_projections = 0;
   

   Cela force un parcours complet de la table, ce qui vous permet de voir la différence du nombre de lignes lues.

5. Comparer les projections et les vues matérialisées

   Les projections et les vues matérialisées résolvent toutes deux le même problème — accélérer les lectures pour d’autres modes d’accès — mais elles impliquent des compromis différents. En bref, les projections sont les mieux adaptées lorsque vous avez simplement besoin d’un ordre de tri différent sur les mêmes données ; les vues matérialisées sont plus flexibles lorsque vous devez transformer, agréger ou acheminer des données vers un schéma différent. Pour une comparaison détaillée, consultez Vues matérialisées et projections.

6. Observez le surcoût de stockage

   Les projections stockent une seconde copie des colonnes sélectionnées dans la même table, ce qui augmente l’espace disque utilisé. Interrogez system.parts pour voir la taille totale de uk_price_paid (qui inclut désormais les données des projections) :

   SELECT
       table,
       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
   GROUP BY table;
   

   Vous pouvez également consulter l’espace de stockage propre aux projections :

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

   Il s’agit du même compromis fondamental que pour les vues matérialisées : plus d’espace disque pour des lectures plus rapides. La projection peut être plus petite qu’une copie complète, car elle n’inclut que les quatre colonnes sélectionnées et la compression varie selon l’ordre de tri.

Étapes suivantes

Dans ce guide de démarrage rapide, vous avez ajouté une projection à uk_price_paid qui stocke les données triées par (town, date), ce qui permet d’effectuer rapidement des recherches par ville sans créer de table distincte. Vous avez appris que les projections sont sélectionnées de manière transparente par l’optimiseur de requêtes, restent synchronisées avec les mutations et utilisent davantage d’espace disque pour améliorer les performances en lecture.

Poursuivez avec les guides de démarrage rapide suivants :

Ou approfondissez avec la documentation de référence :

• Vues matérialisées versus projections
• Référence sur les projections
• Moteur de table MergeTree

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

Tous les guides de démarrage rapide