ClickHouse Materialized Views : déclencheurs sur INSERT

Vues matérialisées dans ClickHouse : la puissance du traitement incrémental

1. Comment fonctionne une MV dans ClickHouse — c'est un déclencheur INSERT, pas un cache SELECT

Dans les bases de données classiques (PostgreSQL, Oracle), une vue matérialisée (MV) est le résultat d'un SELECT stocké sur disque et mis à jour selon un planning (REFRESH). Vous décidez quand la recalculer.

Dans ClickHouse, c'est complètement différent. Ici, une MV est un déclencheur INSERT. Vous créez une MV basée sur un SELECT, et lorsque vous insérez des données dans la table source, ClickHouse exécute automatiquement ce SELECT sur les lignes insérées et insère le résultat dans la table cible.

Différence clé :

• Dans PostgreSQL : vous mettez à jour la MV manuellement ou selon un planning, en lisant la TOTALITÉ de la table source.
• Dans ClickHouse : la MV se déclenche à chaque insertion, en traitant UNIQUEMENT les nouvelles lignes.

Analogie concrète : Une vue classique, c'est comme des lunettes à verres grossissants. Vous regardez les données source à travers elles, en recalculant le résultat à chaque fois. Une vue matérialisée dans ClickHouse, c'est comme un secrétaire assis à côté qui, chaque fois qu'un nouveau document arrive, le copie dans un classeur spécial au format requis. Vous n'avez pas besoin de feuilleter tous les documents à chaque fois ; vous allez directement au classeur.

Pourquoi c'est important : ClickHouse n'a pas de déclencheurs au sens classique. La MV est le seul mécanisme intégré pour réagir à l'insertion de données. Avec elle, vous pouvez :

• Agréger les données à la volée (totaux horaires).
• Dénormaliser les données (rechercher les noms de sports dans un dictionnaire).
• Distribuer les mêmes données brutes vers plusieurs tables avec des structures différentes (agrégats, déduplication, archive complète).

2. MV simple : somme des paris par heure

Commençons par le scénario le plus courant : vous avez une table bets avec des paris, et vous avez besoin de statistiques horaires par sport.

Étape 1 : Créer la table cible (où les données seront agrégées)

CREATE TABLE hourly_sport_stats
(
    hour        DateTime,           -- Début de l'heure (2025-06-01 14:00:00)
    sport_id    UInt8,              -- Type de sport
    bets_count  UInt64,             -- Nombre de paris par heure
    total_amount Decimal(18,2)      -- Montant total des paris
)
ENGINE = SummingMergeTree()         -- Sera additionné par heure + sport_id
ORDER BY (hour, sport_id);

Étape 2 : Créer la MV qui alimentera cette table

CREATE MATERIALIZED VIEW mv_hourly_stats TO hourly_sport_stats AS
SELECT 
    toStartOfHour(created_at) AS hour,   -- Arrondir l'heure au début de l'heure
    sport_id,
    count() AS bets_count,                -- Nombre de paris dans le groupe
    sum(amount) AS total_amount           -- Somme des paris dans le groupe
FROM bets
GROUP BY hour, sport_id;

Ce qui se passe ligne par ligne :

• TO hourly_sport_stats — où insérer le résultat. Vous pouvez l'omettre si la structure de la MV correspond à celle du SELECT, mais il est préférable de le spécifier explicitement.
• AS SELECT ... — la requête exécutée sur chaque lot inséré de données dans la table bets. Pas sur toute la table, seulement sur les nouvelles lignes.
• GROUP BY hour, sport_id — agrégation au sein du lot. Si une insertion contient 1000 lignes pour la même heure, la MV calcule une ligne récapitulative pour cette heure.

Comment l'utiliser :

-- Insérer 100 paris pour 14:00 et 50 pour 15:00
INSERT INTO bets (created_at, sport_id, amount) VALUES 
    ('2025-06-01 14:15:00', 1, 100),
    ('2025-06-01 14:30:00', 1, 200),
    ('2025-06-01 15:00:00', 2, 50),
    ... (147 autres lignes) ...;

-- La MV ajoute ou met à jour automatiquement les lignes dans hourly_sport_stats
-- Pour l'heure 14:00, sport_id=1 : bets_count augmente du nombre de paris de l'insertion
-- Pour l'heure 15:00, sport_id=2 : idem

-- Maintenant, lisez les agrégats instantanément, sans GROUP BY !
SELECT * FROM hourly_sport_stats 
WHERE hour = '2025-06-01 14:00:00';

Limitation importante : La MV dans ClickHouse ne peut pas mettre à jour les lignes existantes dans la table cible lors d'insertions répétées pour la même heure. Mais comme nous utilisons SummingMergeTree, lors des fusions en arrière-plan, les lignes avec le même ORDER BY (hour, sport_id) seront additionnées. Par conséquent, SELECT * FROM hourly_sport_stats peut retourner plusieurs lignes par groupe — enveloppez toujours dans SUM avec GROUP BY, comme avec SummingMergeTree.

3. MV avec AggregatingMergeTree — pour les agrégations complexes

Si vous avez besoin non seulement de sommes mais aussi, par exemple, d'utilisateurs uniques (uniq), de paris moyens (avg), vous devez utiliser AggregatingMergeTree et les fonctions *State / *Merge.

-- Table cible avec des colonnes AggregateFunction
CREATE TABLE hourly_sport_advanced
(
    hour            DateTime,
    sport_id        UInt8,
    bets_count      AggregateFunction(count, UInt64),      -- État pour count
    total_amount    AggregateFunction(sum, Decimal(18,2)), -- État pour sum
    unique_users    AggregateFunction(uniq, UInt64),       -- État pour uniq
    avg_amount      AggregateFunction(avg, Decimal(18,2))  -- État pour avg
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_id);

-- MV avec fonctions *State
CREATE MATERIALIZED VIEW mv_hourly_advanced TO hourly_sport_advanced AS
SELECT 
    toStartOfHour(created_at) AS hour,
    sport_id,
    countState(user_id) AS bets_count,        -- Pas juste count, mais countState
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users,       -- uniq approximatif
    avgState(amount) AS avg_amount
FROM bets
GROUP BY hour, sport_id;

Pourquoi ainsi : AggregatingMergeTree stocke non pas les valeurs finales mais des états intermédiaires. Cela permet de fusionner correctement les agrégations provenant de différentes insertions (par exemple, de deux lots pour la même heure). uniqState stocke une table de hachage des valeurs uniques, pas seulement un nombre.

Lecture des données :

SELECT 
    hour,
    sport_id,
    countMerge(bets_count) AS bets_count,
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,
    avgMerge(avg_amount) AS avg_amount
FROM hourly_sport_advanced
GROUP BY hour, sport_id;

4. MV avec SummingMergeTree pour les compteurs simples

Pour les sommes et compteurs simples, SummingMergeTree est plus simple et plus rapide que AggregatingMergeTree. L'exemple de la section 2 est idéal pour cela.

Autre exemple — compteurs par utilisateur :

-- Table cible : combien de paris chaque utilisateur a faits par jour
CREATE TABLE user_daily_counts
(
    user_id     UInt64,
    day         Date,
    bets_count  UInt64,        -- sera additionné
    total_amount Decimal(18,2) -- sera additionné
)
ENGINE = SummingMergeTree()
ORDER BY (user_id, day);

-- MV
CREATE MATERIALIZED VIEW mv_user_daily TO user_daily_counts AS
SELECT 
    user_id,
    toDate(created_at) AS day,
    count() AS bets_count,
    sum(amount) AS total_amount
FROM bets
GROUP BY user_id, day;

Avantage de SummingMergeTree : Simplicité, pas besoin de fonctions *Merge lors de la lecture. Un simple SUM avec GROUP BY suffit pour la sécurité (si les données n'ont pas fusionné).

Inconvénient : Uniquement des sommes et des compteurs. Pas d'utilisateurs uniques, de max, de moyenne.

5. Chaîne de MV : Événement → Agrégation minute → Horaire → Quotidienne

C'est un modèle puissant pour réduire progressivement la granularité. Au lieu d'agréger directement les données brutes en statistiques quotidiennes (ce qui nécessiterait de recalculer des milliards de lignes), vous construisez une chaîne.

Schéma :

• Paris bruts (table raw_bets) — conserver 7 jours.
• Agrégation minute (table minute_stats) — conserver 30 jours.
• Agrégation horaire (table hourly_stats) — conserver 365 jours.
• Agrégation quotidienne (table daily_stats) — conserver 5 ans.

-- Niveau 1 : agrégation minute à partir des données brutes
CREATE TABLE minute_stats
(
    minute      DateTime,      -- arrondi à la minute
    sport_id    UInt8,
    bets_count  UInt64,
    total_amount Decimal(18,2)
)
ENGINE = SummingMergeTree()
ORDER BY (minute, sport_id);

CREATE MATERIALIZED VIEW mv_minute_from_raw TO minute_stats AS
SELECT 
    toStartOfMinute(created_at) AS minute,
    sport_id,
    count() AS bets_count,
    sum(amount) AS total_amount
FROM raw_bets
GROUP BY minute, sport_id;

-- Niveau 2 : agrégation horaire à partir des minutes
CREATE TABLE hourly_stats
(
    hour        DateTime,
    sport_id    UInt8,
    bets_count  UInt64,
    total_amount Decimal(18,2)
)
ENGINE = SummingMergeTree()
ORDER BY (hour, sport_id);

CREATE MATERIALIZED VIEW mv_hourly_from_minute TO hourly_stats AS
SELECT 
    toStartOfHour(minute) AS hour,   -- arrondir la minute à l'heure
    sport_id,
    sum(bets_count) AS bets_count,    -- additionner les compteurs minute
    sum(total_amount) AS total_amount
FROM minute_stats
GROUP BY hour, sport_id;

-- Niveau 3 : agrégation quotidienne à partir des heures
CREATE TABLE daily_stats
(
    day         Date,
    sport_id    UInt8,
    bets_count  UInt64,
    total_amount Decimal(18,2)
)
ENGINE = SummingMergeTree()
ORDER BY (day, sport_id);

CREATE MATERIALIZED VIEW mv_daily_from_hourly TO daily_stats AS
SELECT 
    toDate(hour) AS day,
    sport_id,
    sum(bets_count) AS bets_count,
    sum(total_amount) AS total_amount
FROM hourly_stats
GROUP BY day, sport_id;

Avantages de la chaîne :

• Chaque niveau travaille avec un volume de données plus petit.
• Vous pouvez supprimer les anciennes données des niveaux inférieurs (TTL) et conserver les agrégats pendant des années.
• Lors de l'interrogation d'un rapport mensuel, vous lisez daily_stats (365 lignes par an), pas raw_bets (des milliards).

Analogie : C'est comme raconter l'intrigue d'un livre : d'abord vous lisez le livre (données brutes), puis vous écrivez un résumé de chapitre (minute), puis un résumé de partie (horaire), puis une annotation (quotidienne). Pour vous souvenir de l'idée principale, vous n'avez pas besoin de relire tout le livre.

6. Modèle Null + MV pour Kafka : distribuer les données vers plusieurs tables

C'est un modèle professionnel pour les systèmes à forte charge. Au lieu d'écrire dans une seule table, vous créez une table Null (trou noir) et plusieurs MV qui lisent depuis celle-ci et écrivent dans différentes tables cibles.

-- Étape 1 : Table réceptrice (Null, ne stocke rien)
CREATE TABLE raw_events_null
(
    user_id     UInt64,
    sport_id    UInt8,
    amount      Decimal(18,2),
    created_at  DateTime,
    ip          String
)
ENGINE = Null;

-- Étape 2 : Table cible 1 — tous les événements bruts (pour investigations)
CREATE TABLE raw_events_archive
(
    user_id     UInt64,
    sport_id    UInt8,
    amount      Decimal(18,2),
    created_at  DateTime,
    ip          String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, user_id);

-- Étape 3 : Table cible 2 — paris dédupliqués (sans IP pour le RGPD)
CREATE TABLE bets_dedup
(
    user_id     UInt64,
    sport_id    UInt8,
    amount      Decimal(18,2),
    created_at  DateTime,
    bet_id      String
)
ENGINE = ReplacingMergeTree(created_at)
ORDER BY (user_id, bet_id);

-- Étape 4 : Table cible 3 — agrégats horaires
CREATE TABLE hourly_agg
(
    hour        DateTime,
    sport_id    UInt8,
    total_amount AggregateFunction(sum, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_id);

-- Étape 5 : MV — archive brute (toutes les lignes)
CREATE MATERIALIZED VIEW mv_archive TO raw_events_archive AS
SELECT * FROM raw_events_null;

-- Étape 6 : MV — paris dédupliqués (générer bet_id à la volée)
CREATE MATERIALIZED VIEW mv_dedup TO bets_dedup AS
SELECT 
    user_id,
    sport_id,
    amount,
    created_at,
    concat(toString(user_id), '_', toString(sipHash64(created_at))) AS bet_id
FROM raw_events_null
WHERE amount != 0;

-- Étape 7 : MV — agrégats horaires
CREATE MATERIALIZED VIEW mv_hourly_agg TO hourly_agg AS
SELECT 
    toStartOfHour(created_at) AS hour,
    sport_id,
    sumState(amount) AS total_amount
FROM raw_events_null
GROUP BY hour, sport_id;

Comment cela fonctionne en pratique :

-- Le consommateur Kafka insère dans raw_events_null (rapide, sans opérations disque)
INSERT INTO raw_events_null VALUES (123, 1, 100.00, now(), '192.168.1.1');

-- Trois MV en parallèle reçoivent ces données et écrivent dans leurs tables :
-- 1. mv_archive → raw_events_archive (copie complète, y compris IP)
-- 2. mv_dedup → bets_dedup (paris dédupliqués, sans IP)
-- 3. mv_hourly_agg → hourly_agg (met à jour les agrégats)

Pourquoi c'est génial :

• Une insertion — trois transformations différentes.
• La table source ne stocke rien (Null), pas de surcharge d'écriture.
• Chaque MV peut être activée/désactivée indépendamment.
• Facile d'ajouter une quatrième MV (par exemple, pour l'export vers un autre format).

7. Le problème : la MV ne traite que les nouvelles données après sa création

Une limitation critique : la MV ne voit pas les données qui étaient déjà dans la table source au moment de sa création. La MV commence à fonctionner seulement à partir du moment de sa création et ne traite que les nouveaux INSERT.

-- Nous avons déjà un milliard de lignes dans bets
SELECT count() FROM bets;  -- 1 000 000 000

-- Créer la MV
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats AS ...;

-- Insérer un nouveau pari
INSERT INTO bets VALUES (now(), 1, 100);

-- Seul ce nouveau pari apparaîtra dans hourly_stats.
-- Le milliard précédent reste non traité !

Comment résoudre le problème — rattrapage des données historiques :

-- Méthode 1 : INSERT manuel en utilisant le même SELECT que dans la MV
INSERT INTO hourly_stats
SELECT 
    toStartOfHour(created_at) AS hour,
    sport_id,
    count() AS bets_count,
    sum(amount) AS total_amount
FROM bets
WHERE created_at < '2025-06-01'   -- anciennes données
GROUP BY hour, sport_id;

-- Maintenant hourly_stats contient à la fois les anciennes et les nouvelles données

Méthode 2 : Recréer la MV avec POPULATE (dangereux !)

-- DANGEREUX : avec CREATE ... POPULATE, la MV traite la TOTALITÉ de la table existante
-- Pendant le traitement, la table peut être verrouillée et des données peuvent être perdues
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats POPULATE AS
SELECT ... FROM bets ...;

Pourquoi POPULATE est dangereux en production :

• Pendant l'exécution du SELECT, la table source peut être verrouillée.
• Si de nouvelles données sont insérées dans la table source pendant POPULATE, elles peuvent être perdues (ni capturées par l'ancienne ni par la nouvelle MV).
• Sur les grandes tables, POPULATE peut prendre des heures.

Approche correcte : Créez la MV sans POPULATE, et effectuez le rattrapage des données historiques manuellement via INSERT avec le même SELECT que dans la MV. C'est sûr, prévisible et ne bloque pas les insertions.

8. Surveillance et débogage des MV

Vérifier si les MV sont actives

-- Lister toutes les MV et leurs tables cibles
SELECT 
    name,
    engine,
    total_rows,
    formatReadableSize(total_bytes) AS size
FROM system.tables
WHERE engine = 'MaterializedView'
AND database = 'default';

Vérifier si la MV se déclenche sur INSERT

-- Activer le journal détaillé (debug uniquement)
SET log_queries = 1;

-- Insérer une ligne dans la table source
INSERT INTO bets (created_at, sport_id, amount) VALUES (now(), 1, 100);

-- Trouver les requêtes liées à la MV
SELECT 
    query,
    query_duration_ms,
    read_rows,
    written_rows
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query LIKE '%MV%'
  AND event_time > now() - INTERVAL 1 MINUTE
ORDER BY event_time DESC;

Vérifier les données dans la table cible

-- Comparer le nombre de groupes uniques dans les tables source et cible
-- (devrait correspondre après le rattrapage)

-- Source : combien de (heure, sport) uniques
SELECT count(*) FROM (
    SELECT toStartOfHour(created_at) AS hour, sport_id 
    FROM bets 
    GROUP BY hour, sport_id
) AS src;

-- Cible :
SELECT count(*) FROM hourly_stats;

Identifier les problèmes de TYPE

Si la MV ne se déclenche pas, le problème est souvent une incompatibilité de type :

-- Erreur : la colonne `sport_id` est de type UInt8 dans la table source, mais Int32 dans la cible de la MV
-- Solution : caster explicitement les types dans le SELECT
SELECT 
    toStartOfHour(created_at) AS hour,
    toUInt8(sport_id) AS sport_id,   -- cast explicite
    count() AS bets_count
FROM bets
GROUP BY hour, sport_id;

9. Schéma réel pour une plateforme de jeux d'argent

Assemblons une architecture complète pour un casino en ligne avec plusieurs exigences :

• Événements détaillés pour les investigations (conserver 7 jours).
• Paris dédupliqués pour les analyses (conserver 90 jours).
• Agrégats horaires pour le tableau de bord (conserver 2 ans).
• Agrégats quotidiens pour les rapports financiers (conserver 10 ans).

-- ===== NIVEAU 0 : Récepteur (Null) =====
CREATE TABLE raw_stream
(
    user_id     UInt64,
    sport_id    UInt8,
    bet_id      String,
    amount      Decimal(18,2),
    created_at  DateTime,
    ip          String
)
ENGINE = Null;

-- ===== TABLES CIBLES =====
-- 1. Archive pour 7 jours (MergeTree + TTL)
CREATE TABLE raw_archive
(
    user_id UInt64, sport_id UInt8, bet_id String,
    amount Decimal(18,2), created_at DateTime, ip String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY created_at
TTL created_at + INTERVAL 7 DAY DELETE;

-- 2. Paris dédupliqués (ReplacingMergeTree)
CREATE TABLE bets_dedup
(
    user_id UInt64, sport_id UInt8, bet_id String,
    amount Decimal(18,2), created_at DateTime
)
ENGINE = ReplacingMergeTree(created_at)
ORDER BY (user_id, bet_id)
TTL created_at + INTERVAL 90 DAY DELETE;

-- 3. Agrégats horaires (SummingMergeTree)
CREATE TABLE hourly_agg
(
    hour DateTime, sport_id UInt8, total_amount Decimal(18,2), bet_count UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (hour, sport_id)
TTL hour + INTERVAL 2 YEAR DELETE;

-- 4. Agrégats quotidiens (AggregatingMergeTree pour uniq)
CREATE TABLE daily_agg
(
    day Date, sport_id UInt8, total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_users AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (day, sport_id)
TTL day + INTERVAL 10 YEAR DELETE;

-- ===== VUES MATÉRIALISÉES =====
CREATE MATERIALIZED VIEW mv_raw_archive TO raw_archive AS
SELECT * FROM raw_stream;

CREATE MATERIALIZED VIEW mv_bets_dedup TO bets_dedup AS
SELECT user_id, sport_id, bet_id, amount, created_at
FROM raw_stream
WHERE bet_id != '';

CREATE MATERIALIZED VIEW mv_hourly_agg TO hourly_agg AS
SELECT 
    toStartOfHour(created_at) AS hour,
    sport_id,
    sum(amount) AS total_amount,
    count() AS bet_count
FROM raw_stream
GROUP BY hour, sport_id;

CREATE MATERIALIZED VIEW mv_daily_agg TO daily_agg AS
SELECT 
    toDate(created_at) AS day,
    sport_id,
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users
FROM raw_stream
GROUP BY day, sport_id;

Flux de données :

1. L'application écrit dans raw_stream (Null) — millisecondes.
2. Quatre MV traitent chaque insertion en parallèle.
3. Chaque table cible reçoit les données sous leur forme agrégée/transformée.
4. Le TTL supprime automatiquement les données obsolètes à chaque niveau.

Avantages par rapport à un seul MERGETREE :

• Vitesse d'insertion maximale (Null + MV travaillent en mémoire).
• Différents niveaux de stockage (7 jours, 90 jours, 2 ans, 10 ans).
• Différents moteurs pour différentes tâches (MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree).
• Aucune perte de performance lors de l'interrogation des anciens agrégats.

Prochaines étapes

Maintenant vous savez comment construire des pipelines complexes de traitement de données en utilisant les MV. Prochains sujets :

• MV avec mises à jour de sources externes — comment combiner des dictionnaires et des MV pour l'enrichissement.
• Débogage de chaînes MV complexes — comment comprendre pourquoi les données n'ont pas atteint la table cible.
• Réplication des MV dans un cluster — comment les MV se comportent sur les tables distribuées.

Résumé : Les vues matérialisées dans ClickHouse ne sont pas seulement un cache. Ce sont un mécanisme puissant de traitement incrémental des données qui permet de construire des architectures d'event sourcing à l'intérieur de la base de données. La règle est simple : si vous avez besoin d'agréger, transformer ou dupliquer des données lors de l'insertion — utilisez une MV. Et n'oubliez jamais de faire le rattrapage des données historiques manuellement, pas via POPULATE en production.

---

← Précédente: Moteurs ClickHouse spéciaux : quand MergeTree ne suffit pas
→ Suivante: Index secondaires (saut) dans ClickHouse : quand ORDER BY ne suffit pas

— Editorial Team