Créez votre première 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.

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;

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.

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.

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.

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.