Moteurs ClickHouse spéciaux : quand MergeTree n'est pas nécessaire

Moteurs ClickHouse spéciaux : quand MergeTree ne suffit pas

1. Moteur Memory — table RAM pour données temporaires

Imaginez que vous deviez traiter rapidement un lot de paris — les regrouper, calculer des totaux intermédiaires, puis les envoyer vers la table principale. Vous ne voulez pas écrire sur le disque car les données sont temporaires et nécessaires uniquement pour la durée de la requête.

Le moteur Memory stocke les données entièrement en RAM. C'est le moteur le plus rapide — aucune opération disque, aucune compression, aucun index (sauf la clé primaire). Mais il y a un inconvénient : au redémarrage de ClickHouse, la table devient vide. Les données ne sont PAS persistantes.

Quand l'utiliser :

• Tables intermédiaires pour les processus ETL. Par exemple, vous avez chargé un million de paris depuis Kafka, les avez dédupliqués, puis insérés dans la table MergeTree principale.
• Cache pour les cotes en direct — les cotes changent chaque seconde, pas besoin de stocker l'historique, seulement l'instantané actuel.
• Petites tables de référence (jusqu'à 10–15 millions de lignes) qui sont recréées à chaque exécution de script.

Exemple pour un cache de cotes en direct :

-- Table pour les cotes actuelles (vit en RAM)
CREATE TABLE live_odds_cache
(
    event_id     UInt64,          -- ID de l'événement (match)
    market_id    UInt32,          -- ID du marché
    selection_id UInt32,          -- ID de la sélection
    odds         Decimal(10,3),   -- Cote
    updated_at   DateTime
)
ENGINE = Memory()
ORDER BY (event_id, market_id, selection_id);   -- ORDER BY obligatoire, mais l'index est inefficace

Insertion de données (par exemple, depuis un flux) :

-- Nouvelle cote arrivée, insertion
INSERT INTO live_odds_cache VALUES (100500, 10, 200, 1.85, now());

-- Lecture de la cote actuelle pour un pari
SELECT odds FROM live_odds_cache 
WHERE event_id = 100500 AND market_id = 10 AND selection_id = 200;

Pièges :

• La table Memory ne supporte pas les fusions — si vous faites beaucoup de UPDATE (via insertion avec annulation), la mémoire gonfle. Utilisez TRUNCATE pour vider.
• Au redémarrage de ClickHouse, les données sont perdues. Ne stockez rien de critique ici.
• La taille de la table est limitée par la RAM disponible. Si la table atteint 50 Go sur un serveur avec 64 Go de RAM, le serveur plantera.

Analogie : Le moteur Memory est comme un tableau blanc. Rapide à écrire, rapide à lire, mais après le passage du concierge (redémarrage), le tableau est vide.

2. Moteur Buffer — mise en mémoire tampon des INSERT avant écriture

Vous avez 10 000 paris par seconde. Chaque pari est un INSERT séparé. Si vous écrivez chacun directement dans une table MergeTree, ClickHouse créera des milliers de mini-parts, ralentissant les fusions en arrière-plan et réduisant les performances.

Le moteur Buffer résout ce problème : il collecte les insertions dans un tampon mémoire et les écrit dans la table cible par lots volumineux lorsque les conditions sont remplies (nombre de lignes, taille ou temps).

Syntaxe avec paramètres :

CREATE TABLE bets_buffer AS bets   -- copie la structure de la table bets
ENGINE = Buffer(
    'default',        -- nom de la base de données de la table cible
    'bets',           -- nom de la table cible (les données seront vidées ici)
    16,               -- nombre de threads de vidage parallèles
    10,               -- délai minimum en secondes (min_time)
    100,              -- délai maximum en secondes (max_time)
    10000,            -- nombre minimum de lignes pour le vidage
    1000000,          -- nombre maximum de lignes pour le vidage
    10000000,         -- taille minimum en octets pour le vidage
    100000000         -- taille maximum en octets pour le vidage
);

Paramètres de vidage :

Paramètre	Valeur	Signification
min_time	10 s	Ne pas vider avant 10 secondes
max_time	100 s	Vider au plus tard après 100 secondes
min_rows	10 000	Si 10k lignes accumulées, peut vider
max_rows	1 000 000	Si 1 million de lignes accumulées, vider d'urgence
min_bytes	10 Mo	Si 10 Mo accumulés, peut vider
max_bytes	100 Mo	Si 100 Mo accumulés, vider d'urgence

Comment cela fonctionne en pratique :

1. Vous insérez dans bets_buffer (rapide, simple écriture en mémoire).
2. ClickHouse attend que suffisamment de données s'accumulent (par exemple, 100k lignes ou 30 secondes).
3. Ensuite, il vide asynchrone (en arrière-plan) le lot dans la table principale bets (MergeTree).
4. En conséquence, bets reçoit de grandes parts (100k lignes), accélérant les fusions en arrière-plan.

Pourquoi ne pas insérer directement dans MergeTree ? Chaque INSERT dans MergeTree crée une mini-part. Si vous faites 10 000 INSERT par seconde, après une minute vous aurez 600 000 parts. La fusion en arrière-plan ne peut pas suivre. ClickHouse se plaindra de Too many parts, et les insertions ralentiront.

Piège : Au redémarrage de ClickHouse, le tampon est perdu. Les données qui n'ont pas été vidées dans bets disparaissent. Utilisez donc le moteur Buffer uniquement si la perte de quelques secondes de données est acceptable (par exemple, pour l'analytique, pas pour les soldes).

3. Moteur Null — trou noir pour les données

Le moteur Null absorbe simplement les données. Elles ne sont écrites nulle part, ni stockées, ni indexées. Mais il y a une astuce : si une table avec le moteur Null a des vues matérialisées, ces vues reçoivent les données et les traitent.

Modèle : Kafka → Null + Vue matérialisée → MergeTree

C'est une architecture classique pour les insertions à haute charge depuis Kafka.

-- Étape 1 : Table réceptrice (trou noir)
CREATE TABLE bets_null
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = Null;   -- ne stocke rien

-- Étape 2 : Table cible (où l'on sauvegarde réellement)
CREATE TABLE bets
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, user_id);

-- Étape 3 : Vue matérialisée (pont)
CREATE MATERIALIZED VIEW bets_mv TO bets AS
SELECT * FROM bets_null;   -- toutes les données qui entrent dans bets_null aboutissent dans bets

Ce qui se passe maintenant :

-- Le client (ou le consommateur Kafka) insère dans bets_null
INSERT INTO bets_null VALUES (123, 100.00, now());   -- instantané

-- Les données passent par la vue matérialisée et sont sauvegardées dans bets
-- Elles ne sont pas sauvegardées dans bets_null lui-même

Pourquoi faire cela ?

• bets_null est une table très légère ; elle ne crée pas de fichiers sur le disque.
• Tous les abonnés (vues matérialisées) reçoivent les données simultanément.
• Vous pouvez attacher plusieurs vues matérialisées à une seule table Null : une pour les données brutes dans MergeTree, une autre pour les agrégats dans AggregatingMergeTree, une autre pour la déduplication dans ReplacingMergeTree.

Analogie : Le moteur Null est comme une boîte aux lettres avec un trou au fond. Les lettres tombent mais ne restent pas. Mais tous vos secrétaires (vues matérialisées) parviennent à les lire et à les copier dans leurs dossiers.

4. Moteurs Log/TinyLog/StripeLog — moteurs simples pour petites données

Cette famille de moteurs est destinée aux petites tables (jusqu'à 1–2 millions de lignes) où les hautes performances et les index ne sont pas nécessaires.

Moteur	Caractéristiques	Quand l'utiliser
TinyLog	Un fichier par colonne	Très petites tables (<100k lignes), intermédiaire
Log	Chaque colonne dans un fichier séparé, avec un marqueur pour la lecture parallèle	Tables jusqu'à 1 million de lignes, besoin de lecture rapide
StripeLog	Toutes les colonnes dans un seul fichier (compact)	Économie d'espace, lecture peu fréquente

Exemple pour une table de référence des ligues (200 lignes) :

-- Table de référence des ligues de football (change une fois par mois)
CREATE TABLE leagues_ref
(
    league_id   UInt32,
    name        String,
    country     String,
    updated_at  Date
)
ENGINE = TinyLog();   -- aussi simple que possible, pas de ORDER BY

Pourquoi pas MergeTree ? MergeTree crée des index, des partitions, de la compression — c'est excessif pour 200 lignes. TinyLog prend moins de place et est plus simple à maintenir.

Piège : Ces moteurs ne supportent pas ALTER DELETE et ALTER UPDATE. Si vous devez modifier des données, vous devrez recréer la table.

5. Moteur URL — table comme point de terminaison HTTP

Le moteur URL permet de lire des données directement depuis une source HTTP (API) et même d'insérer des données via PUT.

CREATE TABLE currency_rates_url
(
    base   String,
    rate   Decimal(10,4),
    date   Date
)
ENGINE = URL('https://api.exchangerate.com/latest?base=USD', CSV)
SETTINGS 
    method = 'GET',
    format = 'CSV',
    headers = 'Authorization: Bearer token123';

Utilisation :

-- Lire les taux actuels directement depuis l'API
SELECT * FROM currency_rates_url;

Scénario réel : Une petite tâche analytique où vous ne voulez pas mettre en place un ETL. Par exemple, une fois par heure, vous lisez les taux de change depuis une API gratuite, les joignez aux paris et recalculez les montants.

Pièges :

• Pas d'index ; chaque requête effectue un scan complet de la source.
• Si l'API renvoie une erreur, la requête échoue.
• Pas adapté aux requêtes à forte charge (l'hypothèse est que les données sont mises en cache dans ClickHouse, pas lues depuis l'API à chaque fois).

6. Moteur File — table comme fichier sur disque

Permet de lire et d'écrire des fichiers dans le système de fichiers local du serveur ClickHouse. Supporte les formats CSV, TSV, JSONEachRow, Parquet.

-- Table qui lit un fichier CSV
CREATE TABLE imported_players
(
    user_id UInt64,
    username String
)
ENGINE = File(CSV, '/var/lib/clickhouse/user_files/players.csv');

Quand l'utiliser :

• Chargement de données depuis des fichiers (un administrateur a placé un CSV avec de nouveaux utilisateurs).
• Exportation de résultats de requêtes vers un fichier via INSERT INTO ... SELECT.

Piège : ClickHouse doit avoir accès au dossier (généralement /var/lib/clickhouse/user_files/ pour des raisons de sécurité).

7. Moteur S3 — requêtes directes vers S3

Lit les données directement depuis un bucket Amazon S3 (ou MinIO, Yandex Object Storage). Ne copie pas les données dans ClickHouse.

CREATE TABLE logs_s3
(
    timestamp DateTime,
    message String
)
ENGINE = S3(
    'https://mybucket.s3.amazonaws.com/logs/*.parquet',
    'AWS_ACCESS_KEY', 'AWS_SECRET_KEY',
    'Parquet'
);

Quand l'utiliser :

• Vous avez des téraoctets de logs dans S3 et souhaitez exécuter des requêtes analytiques occasionnelles sans les copier dans ClickHouse.
• Données froides (S3 est moins cher que les disques ClickHouse).

Piège : Chaque requête télécharge les données depuis S3, ce qui peut être lent et coûteux (pour le trafic sortant). Convient uniquement pour des requêtes peu fréquentes.

8. Moteur PostgreSQL — données en direct depuis PostgreSQL

Le moteur PostgreSQL permet de lire et d'écrire dans des tables PostgreSQL comme s'il s'agissait de tables ClickHouse.

CREATE TABLE pg_players
(
    user_id UInt64,
    balance Decimal(18,2)
)
ENGINE = PostgreSQL(
    'postgres-host:5432',   -- hôte et port
    'betting',               -- base de données
    'players',               -- table dans PostgreSQL
    'clickhouse_user',       -- utilisateur
    'password'               -- mot de passe
);

Utilisation :

-- Lire les soldes actuels depuis PostgreSQL
SELECT * FROM pg_players WHERE user_id = 123;

-- Vous pouvez même faire des JOIN avec des tables ClickHouse
SELECT b.user_id, b.amount, p.balance
FROM bets b
JOIN pg_players p ON b.user_id = p.user_id;

Quand l'utiliser :

• Vous migrez progressivement de PostgreSQL vers ClickHouse, et certaines données résident encore dans l'ancienne base.
• Vous avez besoin de données en direct mises à jour par une application externe et ne voulez pas mettre en place d'ETL.

Pièges :

• Chaque requête va vers PostgreSQL, ce qui peut être lent pour de gros volumes.
• ClickHouse ne peut pas construire un plan d'exécution efficace impliquant une telle table (pas de pushdown).

9. Modèle architectural pour les paris : Kafka → Buffer → MergeTree

Maintenant, rassemblons tout cela. Imaginez que vous recevez 10 000+ messages par seconde depuis Kafka pour chaque événement de pari. Vous devez les sauvegarder dans ClickHouse avec une latence minimale et sans créer des milliers de mini-parts.

Architecture prête :

-- 1. Table réceptrice (Null) pour Kafka
CREATE TABLE bets_kafka
(
    user_id     UInt64,
    event_id    UInt64,
    amount      Decimal(18,2),
    bet_time    DateTime
)
ENGINE = Null;

-- 2. Table MergeTree cible
CREATE TABLE bets
(
    user_id     UInt64,
    event_id    UInt64,
    amount      Decimal(18,2),
    bet_time    DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(bet_time)
ORDER BY (bet_time, user_id);

-- 3. Table Buffer pour lisser les insertions
CREATE TABLE bets_buffer AS bets
ENGINE = Buffer('default', 'bets', 16, 5, 60, 10000, 1000000, 10000000, 100000000);

-- 4. Vue matérialisée : Kafka → Null → Buffer (via la vue)
CREATE MATERIALIZED VIEW bets_kafka_mv TO bets_buffer AS
SELECT * FROM bets_kafka;

-- 5. Une autre vue pour les agrégats en temps réel (optionnelle)
CREATE MATERIALIZED VIEW bets_stats_mv TO bets_hourly_agg AS
SELECT 
    toStartOfHour(bet_time) AS hour,
    countState() AS bet_count,
    sumState(amount) AS total_amount
FROM bets_kafka
GROUP BY hour;

Flux de données :

1. Kafka Connect insère les messages dans bets_kafka (moteur Null).
2. bets_kafka_mv (vue matérialisée) redirige les données vers bets_buffer.
3. bets_buffer accumule les lots en mémoire (par exemple, 100k lignes ou 60 secondes).
4. Le tampon se vide dans bets (MergeTree) en grandes parts.
5. En parallèle, la seconde vue matérialisée construit des agrégats horaires pour les tableaux de bord.

Pourquoi c'est optimal :

• Kafka écrit dans Null (instantané, sans surcharge).
• Le Buffer empêche la multiplication des milliers de mini-parts.
• MergeTree reçoit de grandes parts, les fusions fonctionnent efficacement.
• Les agrégats sont construits en temps réel via la seconde vue.

Que se passe-t-il si vous écrivez directement dans MergeTree depuis Kafka : À 10k événements/s, 600k parts sont créées en une minute. ClickHouse plante avec Too many parts. Le moteur Buffer vous en sauve.

Quand choisir quel moteur — Aide-mémoire

Tâche	Moteur	Pourquoi
Stockage persistant, analytique	MergeTree (ou *MergeTree)	Fondation de ClickHouse, index, compression
Mise en mémoire tampon des fortes charges	Buffer	Colle les petits INSERT en grandes parts
Données temporaires (session, intermédiaire)	Memory	Vitesse maximale, données non critiques
Consommateur Kafka sans stockage	Null + MV	Données uniquement pour les vues
Petite table de référence statique	TinyLog / Log	Simplicité, moins de métadonnées
Connexion à PostgreSQL	PostgreSQL ENGINE	Données en direct sans ETL
Requêtes rares vers S3	S3 ENGINE	Stockage froid peu coûteux
Import depuis des fichiers	File ENGINE	Copie unique

Prochaines étapes

Vous avez découvert des moteurs spéciaux qui résolvent des problèmes au-delà du MergeTree standard. Maintenant vous savez :

• Memory pour le cache et les données intermédiaires,
• Buffer pour la protection contre les INSERT trop fréquents,
• Null pour « bifurquer » les données vers plusieurs flux via MV,
• URL / File / S3 / PostgreSQL pour les données externes.

Prochains sujets à approfondir :

• Comment configurer le moteur Kafka — connecteur intégré à Kafka sans connecteur séparé.
• Vues matérialisées avancées — chaînes de vues pour ETL complexes.
• Tables distribuées — comment partitionner les données sur plusieurs serveurs.

En résumé : Toutes les tâches dans ClickHouse ne se résolvent pas avec MergeTree. Parfois, vous avez besoin de Buffer pour éviter de tuer le serveur avec des insertions, parfois de Null + MV pour distribuer les données vers différents agrégats, et parfois de Memory pour le hachage temporaire. La règle principale : concevez d'abord le flux de données, puis choisissez le moteur, pas l'inverse.

---

← Précédente: Dictionnaires dans ClickHouse : Recherche rapide sans JOIN
→ Suivante: Vues matérialisées dans ClickHouse : la puissance du traitement incrémental

— Editorial Team