Wörterbücher in ClickHouse: schnelle Suche ohne JOIN

Wörterbücher in ClickHouse: Schnelle Lookups ohne JOIN

1. Warum Wörterbücher nötig sind – Das JOIN-Problem mit Referenzdaten

Kehren wir zu unserem Online-Casino zurück. Sie haben eine Tabelle bets, die sport_id speichert – eine Zahl von 1 bis 20. Aber in Berichten müssen Sie den Sportnamen anzeigen: „Fußball“, „Eishockey“, „Tennis“. Diese Informationen leben normalerweise in einer separaten Referenztabelle sports.

-- Langsame Abfrage mit 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;

Bei einer Milliarde Zeilen in bets und 20 Zeilen in sports wird dieser JOIN die Referenztabelle für jeden Datenblock kopieren. ClickHouse führt einen Broadcast-Join durch (sendet die kleine Tabelle an alle Shards), was schnell ist, aber dennoch Speicher und CPU verbraucht.

Wörterbücher lösen dieses Problem anders. Ein Wörterbuch ist eine In-Memory-Referenztabelle, die in ClickHouse lebt. Sie können einen Schlüssel nachschlagen und einen Wert in Mikrosekunden erhalten, ohne einen JOIN auszuführen.

Analogie aus dem echten Leben: Ein Wörterbuch ist wie ein Spickzettel für eine Prüfung. Sie haben eine Liste mit 20 Zeilen: „1 = Fußball, 2 = Eishockey …“. Wenn Sie den Sportnamen anhand der ID finden müssen, werfen Sie nur einen Blick auf den Spickzettel (Speicher), anstatt in die Bibliothek zu gehen, um ein dickes Nachschlagewerk (Festplatte) zu holen. Das ist tausendmal schneller.

Warum das in ClickHouse wichtig ist: ClickHouse speichert Daten auf der Festplatte, und das Lesen selbst einer kleinen Tabelle über JOIN erfordert Festplattenoperationen. Ein Wörterbuch befindet sich im Arbeitsspeicher (komprimiert und optimiert), und der Zugriff darauf ist einfach das Lesen aus dem RAM.

2. Wörterbuchtypen – Wie man die Struktur auswählt

ClickHouse bietet mehrere Wörterbuchtypen (LAYOUT) an, abhängig von:

• Datengröße (wie viele Schlüssel),
• Schlüsseltyp (einfach oder zusammengesetzt),
• ob Bereichssuche erforderlich ist (z. B. Wechselkurs zu einem Datum).

Typ	Wann verwenden	Maximale Schlüssel	Merkmale
flat	Sehr kleine Wörterbücher (bis zu 500k Schlüssel)	500.000	Schnellste, in einem Array gespeichert. Schlüssel muss Integer sein (UInt*).
hashed	Mittlere Wörterbücher (Millionen von Schlüsseln)	Unbegrenzt	Hash-Tabelle. Geeignet für jeden Schlüsseltyp. Etwas langsamer als flat.
sparse_hashed	Sehr große (zig Millionen)	Sehr viele	Spart Speicher (speichert keine Nullwerte), aber etwas langsamer.
range_hashed	Datumsbereiche (Wechselkurs nach Datum)	Unbegrenzt	Schlüssel + Bereich (Start, Ende). Ermöglicht get(key, date)-Lookup.
complex_key_hashed	Zusammengesetzter Schlüssel (z. B. market_id, selection_id)	Unbegrenzt	Schlüssel ist ein Tupel aus mehreren Feldern.
ip_trie	IP-Adressen (Präfix-Lookup)	Bis zu 500k	Für GeoIP: Land/Stadt anhand der IP finden.

Wie wählen:

• Weniger als 500k Schlüssel und Schlüssel ist Integer → flat (maximale Geschwindigkeit).
• Mehr als 500k Schlüssel oder Schlüssel ist kein Integer → hashed.
• Sehr viele Schlüssel und viele Nullwerte → sparse_hashed.
• Datumsbasierte Suche erforderlich → range_hashed.
• Zusammengesetzter Schlüssel (mehrere Felder) → complex_key_hashed.

Analogie: flat ist wie ein Schrank mit nummerierten Schubladen (Index = Nummer). Sie gehen direkt zu Schublade Nr. 17. hashed ist wie ein Bibliothekskatalog, bei dem Sie zuerst das Regal durch Hashen des Autorennamens berechnen. range_hashed ist wie ein Archiv, in dem Sie nach einem Dokument mit bekanntem Datum suchen.

3. Datenquellen – Woher das Wörterbuch seine Daten bezieht

Ein Wörterbuch kann aus verschiedenen Quellen (SOURCE) befüllt werden. ClickHouse aktualisiert das Wörterbuch periodisch aus der Quelle in einem bestimmten Intervall (LIFETIME).

Unterstützte Quellen:

• CLICKHOUSE – eine andere ClickHouse-Tabelle
• MYSQL – MySQL-Tabelle
• POSTGRESQL – PostgreSQL-Tabelle
• HTTP – REST-API (JSON oder XML)
• FILE – lokale Datei (CSV, TSV)
• REDIS – Redis (Key-Value)
• MONGODB – MongoDB-Collection

Beispiel mit 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)   -- alle 1-2 Stunden aktualisieren
LAYOUT(HASHED());

Warum das praktisch ist: Ihre Währungsreferenz kann einmal pro Stunde aus einer externen MySQL-Datenbank aktualisiert werden, die von der Finanzabteilung gepflegt wird. ClickHouse übernimmt die Änderungen automatisch; Sie müssen kein ETL-Skript schreiben.

4. Erstellen eines Wörterbuchs aus einer ClickHouse-Tabelle – Schritt für Schritt

Das häufigste Szenario: Sie haben bereits eine Referenztabelle in ClickHouse und möchten sie in ein Wörterbuch für schnelle Lookups umwandeln.

Schritt 1: Erstellen der Referenztabelle (falls nicht vorhanden)

CREATE TABLE sports
(
    id       UInt32,          -- Sport-ID (1, 2, 3...)
    name     String,          -- 'Fußball', 'Eishockey', 'Tennis'
    category String           -- 'Mannschaft', 'Einzel', 'E-Sport'
)
ENGINE = MergeTree()
ORDER BY id;

-- Mit Daten befüllen
INSERT INTO sports VALUES (1, 'Fußball', 'Mannschaft'), (2, 'Eishockey', 'Mannschaft'), (3, 'Tennis', 'Einzel');

Schritt 2: Erstellen eines Wörterbuchs auf Basis dieser Tabelle

CREATE DICTIONARY sports_dict
(
    id       UInt32,          -- Schlüsselspalte
    name     String,          -- abzurufender Wert
    category String           -- ein weiterer Wert
)
PRIMARY KEY id                -- Lookup-Schlüssel
SOURCE(CLICKHOUSE(
    host 'localhost'
    port 9000
    user 'default'
    password ''
    db 'default'
    table 'sports'
))
LIFETIME(MIN 300 MAX 600)     -- alle 5-10 Minuten aktualisieren
LAYOUT(HASHED());              -- für unsere 20 Datensätze ist flat auch in Ordnung, aber hashed funktioniert auch

Aufschlüsselung der Parameter:

• PRIMARY KEY id – die Spalte, die für den Lookup verwendet wird. Muss eindeutig sein.
• SOURCE(CLICKHOUSE(...)) – Datenquelle. Sie können jeden Host angeben, nicht nur localhost.
• LIFETIME(MIN 300 MAX 600) – das Wörterbuch wird alle 5–10 Minuten vollständig neu geladen. MIN und MAX werden zur Randomisierung verwendet, um zu verhindern, dass alle Wörterbücher auf allen Servern gleichzeitig aktualisiert werden.
• LAYOUT(HASHED()) – In-Memory-Struktur. Für 20 Datensätze ist flat besser, aber wir behalten hashed als Beispiel.

Was nach der Erstellung passiert: ClickHouse liest die gesamte sports-Tabelle, lädt sie als Hash-Tabelle in den Speicher. Jetzt können Sie dictGet für schnellen Zugriff verwenden.

5. Verwenden von Wörterbüchern in Abfragen – dictGet und Verwandte

Die eigentliche Magie beginnt in SELECT. Statt JOIN sports verwenden Sie Wörterbuchfunktionen.

dictGet – die Hauptfunktion

-- Sportnamen nach sport_id abrufen
SELECT 
    user_id,
    sport_id,
    dictGet('sports_dict', 'name', sport_id) AS sport_name,
    amount
FROM bets
LIMIT 10;

Syntax: dictGet('Wörterbuchname', 'Wertspalte', Schlüssel)

dictGetOrDefault – mit einem Standardwert

-- Wenn sport_id nicht gefunden, 'Unbekannt' zurückgeben
SELECT 
    user_id,
    sport_id,
    dictGetOrDefault('sports_dict', 'name', sport_id, 'Unbekannt') AS sport_name
FROM bets;

dictHas – prüfen, ob ein Schlüssel existiert

-- Wetten mit ungültiger sport_id finden
SELECT DISTINCT sport_id
FROM bets
WHERE dictHas('sports_dict', sport_id) = 0;   -- gibt sport_ids zurück, die nicht im Wörterbuch sind

Vollständiges Beispiel mit Aggregation

-- Top 5 Sportarten nach Gesamtwetteinsatz ohne 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;

Warum das schneller ist als JOIN: Keine Festplattenlesevorgänge, keine Verteilung der Referenztabelle über Shards, kein Hashing zur Abfragezeit. Das Wörterbuch befindet sich bereits im Speicher auf jedem ClickHouse-Knoten.

6. Komplexe Schlüssel – dictGet mit Tuple

Wenn der Schlüssel aus mehreren Feldern besteht (z. B. market_id + selection_id), verwenden Sie LAYOUT(COMPLEX_KEY_HASHED()) und übergeben Sie den Schlüssel als Tupel.

Erstellen eines Wörterbuchs mit einem zusammengesetzten Schlüssel:

-- Quoten-Wörterbuch: (market_id, selection_id) → Quotenwert
CREATE DICTIONARY odds_dict
(
    market_id    UInt32,
    selection_id UInt32,
    odds_value   Decimal(10,3)
)
PRIMARY KEY (market_id, selection_id)   -- zusammengesetzter Schlüssel!
SOURCE(CLICKHOUSE(
    table 'odds_reference'
))
LIFETIME(MIN 60 MAX 120)
LAYOUT(COMPLEX_KEY_HASHED());            -- muss complex_key sein!

Verwendung in Abfragen:

-- Quote für einen bestimmten Markt und ein bestimmtes Ergebnis abrufen
SELECT 
    bet_id,
    market_id,
    selection_id,
    dictGet('odds_dict', 'odds_value', tuple(market_id, selection_id)) AS odds
FROM bets;

Was ist ein Tupel? Ein Tupel ist einfach eine Gruppe von Werten, die in Klammern eingeschlossen sind. tuple(market_id, selection_id) erzeugt einen Schlüssel wie (100, 5).

7. Bereichswörterbücher – für historische Daten (Wechselkurs zu einem Datum)

Stellen Sie sich vor, Sie haben historische Wechselkurse, die sich täglich ändern. Für jede Wette in Euro benötigen Sie den Kurs zum Datum der Wette.

Quelltabelle (z. B. in MySQL):

Währung	start_date	end_date	Kurs
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

Erstellen eines Bereichswörterbuchs:

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())                     -- spezieller Typ
RANGE(MIN start_date MAX end_date);        -- Bereichsspalten angeben

Verwendung:

-- Für jede EUR-Wette den Kurs zum Wette-Datum abrufen
SELECT 
    bet_id,
    amount_eur,
    created_at,
    dictGet('eur_rates_dict', 'rate', tuple(currency, created_at)) AS rate
FROM bets
WHERE currency = 'EUR';

ClickHouse findet automatisch den Datensatz, bei dem created_at zwischen start_date und end_date für die angegebene Währung liegt.

Analogie: Es ist wie ein Kalender mit Preisänderungen. Sie sagen: „Gib mir den Kurs für den 15. März“, und das Wörterbuch überprüft seinen Kalender: Der 15. März fällt in das Intervall 1. März – 31. März, Kurs 1,10.

8. Überwachen von Wörterbüchern – system.dictionaries

Um zu verstehen, was mit Wörterbüchern passiert, gibt es die Systemtabelle system.dictionaries.

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

Nützliche Spalten:

Spalte	Was sie anzeigt
status	LOADED – geladen, LOADING – wird geladen, FAILED – Fehler
origin	Quelle (ClickHouse, MySQL…)
type	Typ (flat, hashed, range_hashed…)
key	Schlüsseltyp
attribute.names	Verfügbare Spalten
bytes_allocated	Speichernutzung (Bytes)
query_count	Anzahl der Lookups
hit_rate	Trefferquote (höher ist besser)
load_factor	Wie voll das Wörterbuch ist (bei hashed)
creation_time	Wann geladen
last_exception	Wenn Status FAILED, steht der Fehler hier

Speicherüberwachung:

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

Wenn ein Wörterbuch Gigabyte belegt, haben Sie möglicherweise den falschen LAYOUT gewählt (z. B. hashed statt sparse_hashed).

9. Hot Reload – SYSTEM RELOAD DICTIONARY

Wörterbücher aktualisieren sich automatisch gemäß LIFETIME. Aber manchmal müssen Sie ein Update erzwingen:

• Sie haben gerade Daten in der Quelle korrigiert und möchten nicht 10 Minuten warten.
• Das Wörterbuch ist fehlgeschlagen (z. B. Quelle nicht verfügbar) und Sie haben das Problem behoben.

-- Ein bestimmtes Wörterbuch neu laden
SYSTEM RELOAD DICTIONARY sports_dict;

-- Alle Wörterbücher neu laden
SYSTEM RELOAD DICTIONARIES;

Was passiert: ClickHouse liest die Quelle erneut (z. B. die sports-Tabelle) und ersetzt den Wörterbuchinhalt im Speicher. Während des Neuladens warten Abfragen, die dictGet verwenden (oder geben alte Daten zurück, je nach Version). Führen Sie für kritische Systeme Neuladungen nachts durch.

So überprüfen Sie, ob das Wörterbuch korrekt geladen wurde:

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

Wenn der Status LOADED ist, ist alles in Ordnung. Wenn FAILED, überprüfen Sie last_exception.

10. Beispielarchitektur: Alle Referenzwörterbücher für eine Wettplattform

Stellen Sie sich eine vollständige Wettplattform-Architektur vor. Sie haben Dutzende von Referenzwörterbüchern, die ständig in Abfragen verwendet werden, um Daten anzureichern.

Zu erstellende Wörterbücher:

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

-- 2. Ligen/Meisterschaften (10k Datensätze, 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. Länder (200 Datensätze, FLAT)
CREATE DICTIONARY countries_dict (id UInt32, name String, code String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'countries'))
LIFETIME(86400) LAYOUT(FLAT());   -- ändern sich selten, einmal täglich aktualisieren

-- 4. Währungen mit historischen Kursen (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. Provisionen nach Land und Wettart (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());

Verwendung in einer einzelnen Abfrage:

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;

Vorteile dieses Ansatzes:

• Geschwindigkeit: Keine JOINs, nur direkte Speicherzugriffe.
• Lesbarkeit: Der Code ist klarer – Sie sehen sofort, welche Wörterbücher verwendet werden.
• Verwaltbarkeit: Das Aktualisieren einer Referenz (z. B. Provision für Spanien) erfolgt an einer Stelle, nicht in ETL-Skripten.
• Speichereffizienz: Wörterbücher werden komprimiert gespeichert und benötigen oft weniger Platz als eine denormalisierte Spalte in einer Tabelle.

Was passiert, wenn Sie keine Wörterbücher verwenden? Sie denormalisieren entweder Daten (wiederholen den Sportnamen in jeder Wettzeile – das vervielfacht das Datenvolumen um das 10-fache oder mehr) oder führen bei jeder Aggregation einen JOIN durch (was bei Milliarden von Zeilen langsam und mühsam ist).

Wie geht es weiter?

Jetzt wissen Sie alles über Wörterbücher. Nächste Themen:

• Aktualisieren von Wörterbüchern über HTTP – wie man Daten aus einer externen API abruft.
• Verwenden von Wörterbüchern in materialisierten Ansichten – zur Voranreicherung von Daten.
• Wörterbuch-Clustering – wie sich Wörterbücher in einem ClickHouse-Cluster (Distributed) verhalten.

Fazit: Wörterbücher sind ein unverzichtbares Werkzeug für die Arbeit mit Referenzdaten in ClickHouse. Sie verwandeln langsame JOINs mit kleinen Tabellen in blitzschnelle Speicherzugriffe. Die Regel ist einfach: Wenn sich die Referenz nicht öfter als einmal pro Minute ändert und ihre Größe das Speichern im RAM erlaubt – machen Sie ein Wörterbuch daraus. Ihre Abfragen werden es Ihnen danken.

---

← Vorherige: TTL in ClickHouse: Automatisches Datenlebenszyklus-Management
→ Nächste: Spezielle ClickHouse-Engines: Wenn MergeTree nicht passt

— Editorial Team