Dictionnaires dans ClickHouse : recherche rapide sans JOIN

Dictionnaires dans ClickHouse : Recherche rapide sans JOIN

1. Pourquoi les dictionnaires sont nécessaires — Le problème des JOIN avec les données de référence

Revenons à notre casino en ligne. Vous avez une table bets qui stocke sport_id — un nombre de 1 à 20. Mais dans les rapports, vous devez afficher le nom du sport : "Football", "Hockey", "Tennis". Cette information se trouve généralement dans une table de référence séparée sports.

-- Requête lente avec JOIN
SELECT 
    b.user_id,
    s.name AS sport_name,
    sum(b.amount) AS total
FROM bets b
JOIN sports s ON b.sport_id = s.id
GROUP BY b.user_id, s.name;

Pour un milliard de lignes dans bets et 20 lignes dans sports, ce JOIN va copier la table de référence pour chaque bloc de données. ClickHouse effectue un broadcast join (envoie la petite table à tous les shards), ce qui est rapide mais consomme tout de même de la mémoire et du CPU.

Les dictionnaires résolvent ce problème différemment. Un dictionnaire est une table de référence en mémoire qui vit à l'intérieur de ClickHouse. Vous pouvez rechercher une clé et obtenir une valeur en microsecondes sans exécuter de JOIN.

Analogie concrète : Un dictionnaire est comme une antisèche pour un examen. Vous avez une liste de 20 lignes : "1 = football, 2 = hockey...". Lorsque vous devez trouver le nom du sport par son ID, vous jetez un coup d'œil à l'antisèche (mémoire), au lieu d'aller à la bibliothèque chercher un gros livre de référence (disque). C'est des milliers de fois plus rapide.

Pourquoi c'est important dans ClickHouse : ClickHouse stocke les données sur disque, et lire même une petite table via JOIN nécessite des opérations disque. Un dictionnaire réside en mémoire (compressé et optimisé), et y accéder revient simplement à lire la RAM.

2. Types de dictionnaires — Comment choisir la structure

ClickHouse propose plusieurs types de dictionnaires (LAYOUT) selon :

• la taille des données (nombre de clés),
• le type de clé (simple ou composite),
• la nécessité d'une recherche par plage (ex. taux de change à une date).

Type	Quand l'utiliser	Nombre max de clés	Caractéristiques
flat	Dictionnaires très petits (jusqu'à 500k clés)	500 000	Plus rapide, stocké dans un tableau. La clé doit être un entier (UInt*).
hashed	Dictionnaires moyens (millions de clés)	Illimité	Table de hachage. Convient à tout type de clé. Légèrement plus lent que flat.
sparse_hashed	Très grands (dizaines de millions)	Très nombreux	Économise la mémoire (ne stocke pas les valeurs nulles), mais légèrement plus lent.
range_hashed	Plages de dates (taux de change par date)	Illimité	Clé + plage (début, fin). Permet la recherche get(key, date).
complex_key_hashed	Clé composite (ex. market_id, selection_id)	Illimité	La clé est un tuple de plusieurs champs.
ip_trie	Adresses IP (recherche par préfixe)	Jusqu'à 500k	Pour GeoIP : trouver le pays/ville par IP.

Comment choisir :

• Moins de 500k clés et clé entière → flat (vitesse maximale).
• Plus de 500k clés ou clé non entière → hashed.
• Très nombreuses clés et beaucoup de valeurs nulles → sparse_hashed.
• Recherche par date nécessaire → range_hashed.
• Clé composite (plusieurs champs) → complex_key_hashed.

Analogie : flat est comme une armoire avec des tiroirs numérotés (index = numéro). Vous allez directement au tiroir n°17. hashed est comme un catalogue de bibliothèque où vous calculez d'abord l'étagère en hachant le nom de l'auteur. range_hashed est comme une archive où vous cherchez un document en connaissant la date.

3. Sources de données — D'où le dictionnaire obtient ses données

Un dictionnaire peut être alimenté à partir de diverses sources (SOURCE). ClickHouse met périodiquement à jour le dictionnaire à partir de la source à un intervalle spécifié (LIFETIME).

Sources prises en charge :

• CLICKHOUSE — une autre table ClickHouse
• MYSQL — table MySQL
• POSTGRESQL — table PostgreSQL
• HTTP — API REST (JSON ou XML)
• FILE — fichier local (CSV, TSV)
• REDIS — Redis (clé-valeur)
• MONGODB — collection MongoDB

Exemple avec MySQL :

CREATE DICTIONARY currencies_dict
(
    code String,
    name String,
    rate Decimal(10,4)
)
PRIMARY KEY code
SOURCE(MYSQL(
    host 'mysql-host'
    port 3306
    user 'reader'
    password 'secret'
    db 'reference'
    table 'currencies'
))
LIFETIME(MIN 3600 MAX 7200)   -- mise à jour toutes les 1 à 2 heures
LAYOUT(HASHED());

Pourquoi c'est pratique : Votre référence de devises peut être mise à jour une fois par heure à partir d'une base MySQL externe gérée par le service financier. ClickHouse récupère les modifications automatiquement ; vous n'avez pas besoin d'écrire un script ETL.

4. Créer un dictionnaire à partir d'une table ClickHouse — Pas à pas

Le scénario le plus courant : vous avez déjà une table de référence dans ClickHouse et vous voulez la transformer en dictionnaire pour des recherches rapides.

Étape 1 : Créer la table de référence (si elle n'existe pas)

CREATE TABLE sports
(
    id       UInt32,          -- ID du sport (1, 2, 3...)
    name     String,          -- 'Football', 'Hockey', 'Tennis'
    category String           -- 'team', 'individual', 'esports'
)
ENGINE = MergeTree()
ORDER BY id;

-- Remplir avec des données
INSERT INTO sports VALUES (1, 'Football', 'team'), (2, 'Hockey', 'team'), (3, 'Tennis', 'individual');

Étape 2 : Créer un dictionnaire au-dessus de cette table

CREATE DICTIONARY sports_dict
(
    id       UInt32,          -- colonne clé
    name     String,          -- valeur à récupérer
    category String           -- une autre valeur
)
PRIMARY KEY id                -- clé de recherche
SOURCE(CLICKHOUSE(
    host 'localhost'
    port 9000
    user 'default'
    password ''
    db 'default'
    table 'sports'
))
LIFETIME(MIN 300 MAX 600)     -- mise à jour toutes les 5 à 10 minutes
LAYOUT(HASHED());              -- pour nos 20 enregistrements, flat convient aussi, mais hashed fonctionne

Détail des paramètres :

• PRIMARY KEY id — la colonne utilisée pour la recherche. Doit être unique.
• SOURCE(CLICKHOUSE(...)) — source de données. Vous pouvez spécifier n'importe quel hôte, pas seulement localhost.
• LIFETIME(MIN 300 MAX 600) — le dictionnaire sera rechargé complètement toutes les 5 à 10 minutes. MIN et MAX sont utilisés pour la randomisation afin d'éviter que tous les dictionnaires de tous les serveurs ne se mettent à jour simultanément.
• LAYOUT(HASHED()) — structure en mémoire. Pour 20 enregistrements, flat est meilleur, mais nous gardons hashed comme exemple.

Ce qui se passe après la création : ClickHouse lit toute la table sports, la charge en mémoire sous forme de table de hachage. Vous pouvez maintenant utiliser dictGet pour un accès rapide.

5. Utiliser les dictionnaires dans les requêtes — dictGet et compagnie

La vraie magie commence dans SELECT. Au lieu de JOIN sports, vous utilisez les fonctions de dictionnaire.

dictGet — la fonction principale

-- Obtenir le nom du sport par sport_id
SELECT 
    user_id,
    sport_id,
    dictGet('sports_dict', 'name', sport_id) AS sport_name,
    amount
FROM bets
LIMIT 10;

Syntaxe : dictGet('nom_dictionnaire', 'colonne_valeur', clé)

dictGetOrDefault — avec une valeur par défaut

-- Si sport_id n'est pas trouvé, retourner 'Inconnu'
SELECT 
    user_id,
    sport_id,
    dictGetOrDefault('sports_dict', 'name', sport_id, 'Inconnu') AS sport_name
FROM bets;

dictHas — vérifier si une clé existe

-- Trouver les paris avec un sport_id invalide
SELECT DISTINCT sport_id
FROM bets
WHERE dictHas('sports_dict', sport_id) = 0;   -- retourne les sport_id absents du dictionnaire

Exemple complet avec agrégation

-- Top 5 sports par montant total misé sans JOIN !
SELECT 
    dictGet('sports_dict', 'name', sport_id) AS sport_name,
    sum(amount) AS total_amount,
    count() AS bet_count
FROM bets
WHERE created_at >= today() - 7
GROUP BY sport_id
ORDER BY total_amount DESC
LIMIT 5;

Pourquoi c'est plus rapide qu'un JOIN : Pas de lectures disque, pas de distribution de la table de référence sur les shards, pas de hachage au moment de la requête. Le dictionnaire est déjà en mémoire sur chaque nœud ClickHouse.

6. Clés complexes — dictGet avec Tuple

Lorsque la clé est composée de plusieurs champs (ex. market_id + selection_id), utilisez LAYOUT(COMPLEX_KEY_HASHED()) et passez la clé sous forme de tuple.

Créer un dictionnaire avec une clé composite :

-- Dictionnaire des cotes : (market_id, selection_id) → valeur de la cote
CREATE DICTIONARY odds_dict
(
    market_id    UInt32,
    selection_id UInt32,
    odds_value   Decimal(10,3)
)
PRIMARY KEY (market_id, selection_id)   -- clé composite !
SOURCE(CLICKHOUSE(
    table 'odds_reference'
))
LIFETIME(MIN 60 MAX 120)
LAYOUT(COMPLEX_KEY_HASHED());            -- doit être complex_key !

Utilisation dans les requêtes :

-- Obtenir la cote pour un marché et un résultat spécifiques
SELECT 
    bet_id,
    market_id,
    selection_id,
    dictGet('odds_dict', 'odds_value', tuple(market_id, selection_id)) AS odds
FROM bets;

Qu'est-ce qu'un tuple ? Un tuple est simplement un groupe de valeurs entre parenthèses. tuple(market_id, selection_id) crée une clé comme (100, 5).

7. Dictionnaires de plage — pour les données historiques (taux de change à une date)

Imaginez que vous ayez des taux de change historiques qui changent quotidiennement. Pour chaque pari en euros, vous avez besoin du taux à la date du pari.

Table source (ex. dans MySQL) :

currency	start_date	end_date	rate
EUR	2025-01-01	2025-01-31	1.05
EUR	2025-02-01	2025-02-28	1.08
EUR	2025-03-01	2099-12-31	1.10

Créer un dictionnaire de plage :

CREATE DICTIONARY eur_rates_dict
(
    currency   String,
    start_date Date,
    end_date   Date,
    rate       Decimal(10,4)
)
PRIMARY KEY currency
SOURCE(CLICKHOUSE(table 'eur_rates'))
LIFETIME(MIN 3600 MAX 7200)
LAYOUT(RANGE_HASHED())                     -- type spécial
RANGE(MIN start_date MAX end_date);        -- spécifier les colonnes de plage

Utilisation :

-- Pour chaque pari en EUR, obtenir le taux à la date du pari
SELECT 
    bet_id,
    amount_eur,
    created_at,
    dictGet('eur_rates_dict', 'rate', tuple(currency, created_at)) AS rate
FROM bets
WHERE currency = 'EUR';

ClickHouse trouve automatiquement l'enregistrement où created_at se situe entre start_date et end_date pour la devise donnée.

Analogie : C'est comme un calendrier des changements de prix. Vous dites : "Donne-moi le taux pour le 15 mars", et le dictionnaire consulte son calendrier : le 15 mars tombe dans l'intervalle du 1er mars au 31 mars, taux 1.10.

8. Surveiller les dictionnaires — system.dictionaries

Pour comprendre ce qui se passe avec les dictionnaires, il y a la table système system.dictionaries.

SELECT *
FROM system.dictionaries
WHERE name = 'sports_dict';

Colonnes utiles :

Colonne	Ce qu'elle montre
status	LOADED — chargé, LOADING — en cours de chargement, FAILED — erreur
origin	Source (ClickHouse, MySQL...)
type	Type (flat, hashed, range_hashed...)
key	Type de clé
attribute.names	Colonnes disponibles
bytes_allocated	Utilisation mémoire (octets)
query_count	Nombre de recherches
hit_rate	Taux de succès (plus élevé est meilleur)
load_factor	À quel point le dictionnaire est plein (pour hashed)
creation_time	Quand il a été chargé
last_exception	Si le statut est FAILED, l'erreur est ici

Surveillance de la mémoire :

SELECT 
    name,
    formatReadableSize(bytes_allocated) AS memory,
    query_count,
    hit_rate
FROM system.dictionaries
WHERE status = 'LOADED'
ORDER BY bytes_allocated DESC;

Si un dictionnaire prend des gigaoctets, vous avez peut-être choisi le mauvais LAYOUT (ex. hashed au lieu de sparse_hashed).

9. Rechargement à chaud — SYSTEM RELOAD DICTIONARY

Les dictionnaires se mettent à jour automatiquement selon LIFETIME. Mais parfois, vous devez forcer une mise à jour :

• Vous venez de corriger des données dans la source et ne voulez pas attendre 10 minutes.
• Le dictionnaire a échoué (ex. source indisponible) et vous avez résolu le problème.

-- Recharger un dictionnaire spécifique
SYSTEM RELOAD DICTIONARY sports_dict;

-- Recharger tous les dictionnaires
SYSTEM RELOAD DICTIONARIES;

Ce qui se passe : ClickHouse relit la source (ex. la table sports) et remplace le contenu du dictionnaire en mémoire. Pendant le rechargement, les requêtes utilisant dictGet attendent (ou retournent les anciennes données, selon la version). Pour les systèmes critiques, effectuez les rechargements la nuit.

Comment vérifier que le dictionnaire s'est chargé correctement :

SELECT status, last_exception 
FROM system.dictionaries 
WHERE name = 'sports_dict';

Si le statut est LOADED, tout va bien. Si FAILED, vérifiez last_exception.

10. Exemple d'architecture : Tous les dictionnaires de référence pour une plateforme de paris

Imaginez une architecture complète de plateforme de paris. Vous avez des dizaines de dictionnaires de référence constamment utilisés dans les requêtes pour enrichir les données.

Dictionnaires à créer :

-- 1. Sports (20 enregistrements, FLAT)
CREATE DICTIONARY sports_dict (id UInt32, name String, category String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'sports'))
LIFETIME(3600) LAYOUT(FLAT());

-- 2. Ligues/Championnats (10k enregistrements, HASHED)
CREATE DICTIONARY leagues_dict (id UInt32, name String, sport_id UInt32, country_id UInt32)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'leagues'))
LIFETIME(3600) LAYOUT(HASHED());

-- 3. Pays (200 enregistrements, FLAT)
CREATE DICTIONARY countries_dict (id UInt32, name String, code String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'countries'))
LIFETIME(86400) LAYOUT(FLAT());   -- changent rarement, mise à jour une fois par jour

-- 4. Devises avec taux historiques (RANGE)
CREATE DICTIONARY exchange_rates_dict (currency String, start_date Date, end_date Date, rate Decimal(10,4))
PRIMARY KEY currency
SOURCE(CLICKHOUSE(table 'exchange_rates'))
LIFETIME(3600) LAYOUT(RANGE_HASHED()) RANGE(MIN start_date MAX end_date);

-- 5. Commissions par pays et type de pari (COMPLEX_KEY)
CREATE DICTIONARY commission_dict (country_id UInt32, bet_type String, commission Decimal(5,2))
PRIMARY KEY (country_id, bet_type)
SOURCE(CLICKHOUSE(table 'commissions'))
LIFETIME(7200) LAYOUT(COMPLEX_KEY_HASHED());

Utilisation dans une seule requête :

SELECT 
    b.user_id,
    dictGet('sports_dict', 'name', b.sport_id) AS sport_name,
    dictGet('leagues_dict', 'name', b.league_id) AS league_name,
    dictGet('countries_dict', 'name', dictGet('leagues_dict', 'country_id', b.league_id)) AS country_name,
    b.amount_eur * dictGet('exchange_rates_dict', 'rate', tuple('EUR', toDate(b.created_at))) AS amount_usd,
    dictGet('commission_dict', 'commission', tuple(dictGet('leagues_dict', 'country_id', b.league_id), 'prematch')) AS commission
FROM bets b
WHERE b.created_at >= today() - 7;

Avantages de cette approche :

• Vitesse : Pas de JOIN, seulement des recherches directes en mémoire.
• Lisibilité : Le code est plus clair — vous voyez immédiatement quels dictionnaires sont utilisés.
• Gouvernance : Mettre à jour une référence (ex. commission pour l'Espagne) se fait à un seul endroit, pas dans des scripts ETL.
• Efficacité mémoire : Les dictionnaires sont stockés compressés, prenant souvent moins de place qu'une colonne dénormalisée dans une table.

Que se passe-t-il si vous n'utilisez pas de dictionnaires ? Soit vous dénormalisez les données (répétez le nom du sport dans chaque ligne de pari — multipliant le volume de données par 10 fois ou plus), soit vous effectuez un JOIN à chaque agrégation (lent et pénible sur des milliards de lignes).

Prochaines étapes

Maintenant, vous savez tout sur les dictionnaires. Prochains sujets :

• Mettre à jour les dictionnaires via HTTP — comment récupérer des données depuis une API externe.
• Utiliser les dictionnaires dans les vues matérialisées — pour pré-enrichir les données.
• Clustering des dictionnaires — comment les dictionnaires se comportent dans un cluster ClickHouse (Distributed).

En résumé : Les dictionnaires sont un outil indispensable pour travailler avec des données de référence dans ClickHouse. Ils transforment les JOIN lents avec de petites tables en recherches mémoire ultra-rapides. La règle est simple : si la référence ne change pas plus d'une fois par minute et que sa taille permet de la stocker en RAM — faites-en un dictionnaire. Vos requêtes vous remercieront.

---

← Précédente: TTL dans ClickHouse : Gestion Automatique du Cycle de Vie des Données
→ Suivante: Moteurs ClickHouse spéciaux : quand MergeTree ne suffit pas

— Editorial Team