Créez votre première vue matérialisée

Apprenez à utiliser les vues matérialisées dans ClickHouse pour précalculer et stocker les résultats d’une requête avec un ordre de tri différent, afin d’effectuer rapidement des recherches sur des colonnes non couvertes par votre clé primaire.

Tous les guides de démarrage rapide

Analyse en temps réelEntreposage 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.

Vous devez également avoir suivi le guide de démarrage rapide Créez votre première table MergeTree, car ce guide s’appuie directement sur la table uk_price_paid qui y est créée.

Ce que vous allez créer

Dans le guide de démarrage rapide MergeTree, vous avez vu qu’interroger uk_price_paid par town ou county nécessite un balayage 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 vue matérialisée qui stocke les mêmes données triées par (town, date), ce qui permet des recherches rapides par ville sans modifier la table d’origine. À la fin, vous comprendrez comment les vues matérialisées fonctionnent comme des déclencheurs à l’insertion, comment réinjecter les données existantes, ainsi que le compromis en espace disque qu’implique le stockage des données en double.

1. Comprendre pourquoi vous avez besoin d’une vue matérialisée

   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 — les 30 millions.

   Vous pourriez créer une deuxième table avec un ORDER BY différent, mais il faudrait alors penser à insérer les nouvelles données dans les deux tables à chaque arrivée. Une vue matérialisée automatise cela : elle surveille les insertions dans une table source, transforme les lignes, puis les écrit automatiquement dans une table de destination.

   Considérez une vue matérialisée comme un déclencheur d’insertion : chaque fois que des lignes sont insérées dans la table source, la requête SELECT de la MV s’exécute sur le nouveau bloc de lignes, puis le résultat est inséré dans la table de destination.

2. Créer la table de destination

   Une vue matérialisée a besoin d’un emplacement où stocker sa sortie. Il s’agit simplement d’une table MergeTree standard : vous avez un contrôle total sur son schéma, ORDER BY et PARTITION BY.

   Créez une table triée par (town, date) avec uniquement les colonnes nécessaires aux requêtes par ville :

   CREATE TABLE uk_price_paid_by_town
   (
       town       LowCardinality(String),
       date       Date,
       price      UInt32,
       type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0)
   )
   ENGINE = MergeTree
   PARTITION BY toYYYYMM(date)
   ORDER BY (town, date);
   

   Cette table n’a rien de particulier : c’est une table MergeTree standard. La vue matérialisée que vous créerez ensuite se contentera d’y acheminer les données.

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

   SHOW CREATE TABLE uk_price_paid_by_town;
   
3. Créer la vue matérialisée

   Créez maintenant la vue matérialisée qui relie la table source (uk_price_paid) à la table de destination (uk_price_paid_by_town) :

   CREATE MATERIALIZED VIEW uk_price_paid_by_town_mv
   TO uk_price_paid_by_town
   AS SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   La clause TO uk_price_paid_by_town indique à ClickHouse d’écrire le résultat du SELECT dans votre table de destination. Désormais, chaque fois que des lignes sont insérées dans uk_price_paid, cette vue matérialisée se déclenche et insère les lignes transformées dans uk_price_paid_by_town.

   Attention toutefois : les vues matérialisées ne se déclenchent que lors des insertions. Si vous supprimez ou mettez à jour des lignes dans la table source, la table de destination n’en est pas informée - les vues matérialisées ne restent pas synchronisées avec les suppressions ou les mises à jour. Si vous avez besoin de ce type de synchronisation, envisagez plutôt d’utiliser des projections.

4. Charger les données historiques

   La vue matérialisée ne traite que les insertions futures. Les 30 millions de lignes déjà présentes dans uk_price_paid ont été insérées avant que la vue matérialisée n’existe, la table de destination est donc actuellement vide.

   Chargez-les manuellement :

   INSERT INTO uk_price_paid_by_town
   SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   Cela insère directement dans la table de destination - la MV n’intervient pas à cette étape. Une fois l’opération terminée, vérifiez que le nombre de lignes correspond :

   SELECT
       'uk_price_paid' AS table,
       count() AS rows
   FROM uk_price_paid
   UNION ALL
   SELECT
       'uk_price_paid_by_town' AS table,
       count() AS rows
   FROM uk_price_paid_by_town;
   

   Les deux tables doivent avoir le même nombre de lignes.

5. Interroger la table de destination de la vue matérialisée

   Exécutez maintenant une requête avec un filtre sur town dans la table de destination, puis comparez le résultat à celui obtenu en interrogeant directement la table source.

   Commencez par interroger la table source :

   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;
   

   Consultez les statistiques de la requête : les 30 millions de lignes sont lus, car town ne figure pas dans l’ORDER BY de la table source.

   Exécutez maintenant la même requête sur la table de destination de la vue matérialisée :

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

   Consultez à nouveau les statistiques de la requête : beaucoup moins de lignes sont lues, car la table de destination est triée par (town, date) et ClickHouse peut ignorer toutes les données qui ne correspondent pas à LONDON.

   Exécutez SHOW TABLES pour voir ce qui a été créé :

   SHOW TABLES;
   

   Vous verrez à la fois uk_price_paid_by_town (la table de destination) et uk_price_paid_by_town_mv (la vue). Comme vous avez utilisé CREATE MATERIALIZED VIEW ... TO, vous maîtrisez le nom de la table de destination. Si vous omettez la clause TO, ClickHouse crée une table de destination au nom implicite (.inner.xxx), avec laquelle il est plus difficile de travailler directement. Il est donc recommandé de créer des vues matérialisées avec la clause TO.

6. Observez que les données sont stockées en double

   Les vues matérialisées offrent des lectures plus rapides, au prix d’un espace disque supplémentaire. Interrogez system.parts pour voir combien d’espace chaque table utilise :

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

   Les données sont physiquement stockées deux fois : une fois dans uk_price_paid, triées selon (postcode, addr1, addr2), et une fois dans uk_price_paid_by_town, triées selon (town, date). C’est le compromis fondamental : vous utilisez davantage d’espace disque en contrepartie de lectures plus rapides selon différents modes d’accès.

   La table de destination peut occuper moins d’espace sur disque, car elle contient moins de colonnes et l’ordre de tri (town, date) peut se compresser différemment de celui d’origine.

Étapes suivantes

Dans ce guide de démarrage rapide, vous avez créé une vue matérialisée pour stocker des données de ventes immobilières au Royaume-Uni avec un ordre de tri différent, afin de permettre des recherches rapides par ville sans modifier votre table d’origine. Vous avez appris que les MV agissent comme des déclencheurs à l’insertion, que les données existantes doivent être rechargées manuellement pour compléter l’historique, et que cela se fait au prix d’un espace disque supplémentaire.

Consultez ensuite les guides de démarrage rapide suivants :

• Créez votre première projection

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

• Référence de la vue matérialisée
• Vues matérialisées incrémentielles
• Projections

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

Tous les guides de démarrage rapide