Slovníky v ClickHouse: rychlý lookup bez JOIN

Slovníky v ClickHouse: rychlý lookup bez JOIN

1. Proč jsou slovníky potřeba – problém JOIN na číselnících

Vraťme se k našemu online casinu. Máš tabulku sázek bets, ve které je uložen sport_id – číslo od 1 do 20. Ale v reportech potřebuješ zobrazit název sportu: „Fotbal“, „Hokej“, „Tenis“. Obvykle tato informace leží v samostatné číselníkové tabulce sports.

-- Pomalý dotaz s 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;

Pro miliardu řádků v bets a 20 řádků v sports bude tento JOIN kopírovat číselník pro každý kus dat. ClickHouse provede broadcast join (rozešle malou tabulku na všechny shardy), což je sice rychlé, ale stále zatěžuje paměť a procesor.

Slovníky (dictionaries) řeší tento problém jinak. Slovník je in-memory (v operační paměti) číselník, který žije uvnitř ClickHouse. Můžeš se na něj dotázat podle klíče a získat hodnotu za mikrosekundy bez provedení JOIN.

Analogii ze života: Slovník je jako tahák na zkoušku. Máš seznam 20 řádků: „1 = fotbal, 2 = hokej...“. Když potřebuješ zjistit název sportu podle ID, jednoduše se podíváš do taháku (paměť), místo abys šel do knihovny pro tlustý číselník (disk). Je to tisíckrát rychlejší.

Proč je to důležité v ClickHouse: ClickHouse ukládá data na disk a čtení i malé tabulky přes JOIN vyžaduje diskové operace. Slovník leží v paměti (komprimovaný a optimalizovaný) a přístup k němu je pouhé čtení z RAM (operační paměti).

2. Typy slovníků – jak vybrat strukturu

ClickHouse nabízí několik typů (LAYOUT) slovníků v závislosti na:

• velikosti dat (kolik klíčů),
• typu klíče (jednoduchý nebo složený),
• zda je potřeba vyhledávání podle rozsahu (např. kurz měny k datu).

Typ	Kdy použít	Max. klíčů	Vlastnosti
flat	Velmi malé číselníky (do 500k klíčů)	500 000	Nejrychlejší, uložen v poli. Klíč – pouze celá čísla (UInt*).
hashed	Střední číselníky (miliony klíčů)	Neomezeně	Hash tabulka. Vhodný pro libovolné typy klíčů. O něco pomalejší než flat.
sparse_hashed	Velmi velké (desítky milionů)	Velmi mnoho	Šetří paměť (neukládá nulové hodnoty), ale o něco pomalejší.
range_hashed	Rozsah dat (kurz měny podle data)	Neomezeně	Klíč + rozsah (start, end). Umožňuje hledat get(key, date).
complex_key_hashed	Složený klíč (např. market_id, selection_id)	Neomezeně	Klíč – tuple z několika polí.
ip_trie	IP adresy (vyhledávání podle prefixu)	Do 500k	Pro GeoIP: podle IP najít zemi/město.

Jak vybrat:

• Méně než 500 tisíc klíčů a klíč je celé číslo → flat (maximální rychlost).
• Více než 500 tisíc nebo klíč není celý → hashed.
• Velmi mnoho klíčů a mnoho prázdných hodnot → sparse_hashed.
• Potřebuješ vyhledávání podle data → range_hashed.
• Složený klíč (několik polí) → complex_key_hashed.

Analogii: flat je jako skříň s očíslovanými šuplíky (index – číslo). Jdeš rovnou k šuplíku č. 17. hashed je jako knihovní katalog, kde nejprve spočítáš polici podle hashe autorova příjmení. range_hashed je jako archiv, kde hledáš dokument, když znáš datum.

3. Zdroje dat – odkud slovník bere data

Slovník může být naplňován z různých zdrojů (SOURCE). ClickHouse sám periodicky aktualizuje slovník ze zdroje s daným intervalem (LIFETIME).

Podporované zdroje:

• CLICKHOUSE – jiná tabulka v ClickHouse
• MYSQL – tabulka v MySQL
• POSTGRESQL – tabulka v PostgreSQL
• HTTP – REST API (JSON nebo XML)
• FILE – lokální soubor (CSV, TSV)
• REDIS – Redis (klíč-hodnota)
• MONGODB – kolekce MongoDB

Příklad s 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)   -- aktualizovat každých 1-2 hodiny
LAYOUT(HASHED());

Proč je to pohodlné: Tvůj číselník měn se může aktualizovat jednou za hodinu z externí MySQL databáze, kterou spravuje finanční oddělení. ClickHouse sám stáhne změny, nemusíš psát ETL skript (Extract, Transform, Load – výběr, transformace, načtení).

4. Vytvoření slovníku z ClickHouse tabulky – krok za krokem

Nejčastější scénář: už máš číselníkovou tabulku v ClickHouse a chceš ji přeměnit na slovník pro rychlý lookup.

Krok 1: Vytvoříme číselníkovou tabulku (pokud neexistuje)

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

-- Naplníme daty
INSERT INTO sports VALUES (1, 'Football', 'team'), (2, 'Hockey', 'team'), (3, 'Tennis', 'individual');

Krok 2: Vytvoříme slovník nad touto tabulkou

CREATE DICTIONARY sports_dict
(
    id       UInt32,          -- sloupec-klíč
    name     String,          -- hodnota, kterou budeme získávat
    category String           -- další hodnota
)
PRIMARY KEY id                -- klíč pro vyhledávání
SOURCE(CLICKHOUSE(
    host 'localhost'
    port 9000
    user 'default'
    password ''
    db 'default'
    table 'sports'
))
LIFETIME(MIN 300 MAX 600)     -- aktualizovat každých 5-10 minut
LAYOUT(HASHED());              -- pro našich 20 záznamů lze použít i flat, ale hashed je také OK

Rozebíráme parametry:

• PRIMARY KEY id – sloupec, podle kterého se bude vyhledávat. Musí být unikátní.
• SOURCE(CLICKHOUSE(...)) – zdroj dat. Lze zadat libovolný host, ne nutně localhost.
• LIFETIME(MIN 300 MAX 600) – slovník bude zcela znovu načten každých 5–10 minut. MIN a MAX jsou pro randomizaci, aby se všechny slovníky na všech serverech neaktualizovaly současně.
• LAYOUT(HASHED()) – struktura v paměti. Pro 20 záznamů je lepší flat, ale necháme hashed jako příklad.

Co se stane po vytvoření: ClickHouse přečte celou tabulku sports, nahraje ji do paměti jako hash tabulku. Nyní můžeš použít dictGet pro rychlý přístup.

5. Použití v dotazech – dictGet a přátelé

Hlavní kouzlo začíná v SELECT. Místo JOIN sports použiješ funkce slovníku.

dictGet – základní funkce

-- Získat název sportu podle sport_id
SELECT 
    user_id,
    sport_id,
    dictGet('sports_dict', 'name', sport_id) AS sport_name,
    amount
FROM bets
LIMIT 10;

Syntaxe: dictGet('název_slovníku', 'sloupec_hodnoty', klíč)

dictGetOrDefault – s výchozí hodnotou

-- Pokud sport_id není nalezen, vrátit 'Unknown'
SELECT 
    user_id,
    sport_id,
    dictGetOrDefault('sports_dict', 'name', sport_id, 'Unknown') AS sport_name
FROM bets;

dictHas – zkontrolovat, zda klíč existuje

-- Najít sázky s neplatným sport_id
SELECT DISTINCT sport_id
FROM bets
WHERE dictHas('sports_dict', sport_id) = 0;   -- vrátí sport_id, která nejsou ve slovníku

Plnohodnotný příklad s agregací

-- Top 5 sportů podle objemu sázek bez 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;

Proč je to rychlejší než JOIN: Žádné čtení z disku, žádné distribuování číselníku po shardech, žádné hashování ve fázi dotazu. Slovník je již v paměti každého uzlu ClickHouse.

6. Složené klíče – dictGet s tuple

Když klíč sestává z několika polí (např. market_id + selection_id), použij LAYOUT(COMPLEX_KEY_HASHED()) a předej klíč jako tuple.

Vytvoření slovníku se složeným klíčem:

-- Slovník kurzů: (market_id, selection_id) → kurz
CREATE DICTIONARY odds_dict
(
    market_id    UInt32,
    selection_id UInt32,
    odds_value   Decimal(10,3)
)
PRIMARY KEY (market_id, selection_id)   -- složený klíč!
SOURCE(CLICKHOUSE(
    table 'odds_reference'
))
LIFETIME(MIN 60 MAX 120)
LAYOUT(COMPLEX_KEY_HASHED());            -- povinně complex_key!

Použití v dotazech:

-- Získat kurz pro konkrétní trh a výsledek
SELECT 
    bet_id,
    market_id,
    selection_id,
    dictGet('odds_dict', 'odds_value', tuple(market_id, selection_id)) AS odds
FROM bets;

Co je tuple? Tuple je jednoduše skupina hodnot uzavřená v závorkách. tuple(market_id, selection_id) vytvoří klíč ve tvaru (100, 5).

7. Range slovníky – pro historická data (kurz měny k datu)

Představ si, že máš historické kurzy měn, které se mění každý den. Pro každou sázku v eurech potřebuješ znát kurz v den, kdy byla sázka uskutečněna.

Zdrojová tabulka (např. v 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

Vytvoříme range slovník:

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())                     -- speciální typ
RANGE(MIN start_date MAX end_date);        -- určujeme sloupce rozsahu

Použití:

-- Pro každou sázku v EUR získáme kurz k datu sázky
SELECT 
    bet_id,
    amount_eur,
    created_at,
    dictGet('eur_rates_dict', 'rate', tuple(currency, created_at)) AS rate
FROM bets
WHERE currency = 'EUR';

ClickHouse sám najde záznam ve slovníku, kde created_at spadá mezi start_date a end_date pro danou měnu.

Analogii: Je to jako kalendář změn cen. Řekneš: „Dej kurz na 15. března“ a slovník se podívá do svého kalendáře: 15. březen spadá do intervalu 1. března – 31. března, kurz 1.10.

8. Monitorování slovníků – system.dictionaries

Abychom pochopili, co se se slovníky děje, existuje systémová tabulka system.dictionaries.

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

Užitečné sloupce:

Sloupec	Co ukazuje
status	LOADED – načten, LOADING – načítá se, FAILED – chyba
origin	Odkud je načten (ClickHouse, MySQL...)
type	Typ (flat, hashed, range_hashed...)
key	Typ klíče
attribute.names	Jaké sloupce jsou k dispozici
bytes_allocated	Kolik paměti zabírá (bajty)
query_count	Kolikrát byl dotazován
hit_rate	Procento úspěšných dotazů (čím vyšší, tím lepší)
load_factor	Jak je slovník zaplněn (pro hashed)
creation_time	Kdy byl načten
last_exception	Pokud je status FAILED – zde bude chyba

Monitorování paměti:

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

Pokud nějaký slovník zabírá gigabajty – možná jsi zvolil špatný LAYOUT (např. hashed místo sparse_hashed).

9. Horké znovunačtení – SYSTEM RELOAD DICTIONARY

Slovníky se aktualizují automaticky podle LIFETIME. Někdy je ale potřeba je vynutit:

• Právě jsi opravil data ve zdroji a nechceš čekat 10 minut.
• Slovník spadl s chybou (např. zdroj byl nedostupný) a opravil jsi problém.

-- Znovu načíst konkrétní slovník
SYSTEM RELOAD DICTIONARY sports_dict;

-- Znovu načíst všechny slovníky
SYSTEM RELOAD DICTIONARIES;

Co se stane: ClickHouse znovu přečte zdroj (např. tabulku sports) a nahradí obsah slovníku v paměti. Během znovunačtení budou dotazy používající dictGet čekat (nebo vrátí stará data – záleží na verzi). Pro kritické systémy prováděj znovunačtení v noci.

Jak zkontrolovat, že se slovník načetl správně:

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

Pokud je status LOADED – vše v pořádku. Pokud FAILED – podívej se na last_exception.

10. Příklad architektury: všechny číselníky sázkové platformy

Představ si kompletní architekturu sázkové platformy. Máš desítky číselníků, které se neustále používají v dotazech k obohacení dat.

Slovníky, které stojí za to vytvořit:

-- 1. Druhy sportů (20 záznamů, FLAT)
CREATE DICTIONARY sports_dict (id UInt32, name String, category String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'sports'))
LIFETIME(3600) LAYOUT(FLAT());

-- 2. Ligy/šampionáty (10k záznamů, 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. Země (200 záznamů, FLAT)
CREATE DICTIONARY countries_dict (id UInt32, name String, code String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'countries'))
LIFETIME(86400) LAYOUT(FLAT());   -- mění se zřídka, aktualizovat jednou denně

-- 4. Měny s historickým kurzem (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. Provize podle zemí a typu sázky (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());

Použití v jednom dotazu:

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;

Výhody tohoto přístupu:

• Rychlost: Ani jeden JOIN, pouze přímé lookup v paměti.
• Čitelnost: Kód je přehlednější – hned vidíš, které číselníky se používají.
• Spravovatelnost: Aktualizace číselníku (např. provize pro Španělsko) probíhá na jednom místě, ne v ETL skriptech.
• Úspora paměti: Slovníky jsou uloženy v komprimované podobě, často zabírají méně místa než sloupec s denormalizovanými daty v tabulce.

Co se stane, když nepoužiješ slovníky? Buď denormalizuješ data (opakuješ název sportu v každém řádku sázky – znásobíš objem dat 10+ krát), nebo děláš JOIN pro každou agregaci (což je na miliardách řádků pomalé a bolestivé).

Co dál

Teď víš všechno o slovnících. Další témata:

• Aktualizace slovníků přes HTTP – jak stahovat data z externího API.
• Použití slovníků v materializovaných pohledech – pro předběžné obohacení dat.
• Klastrování slovníků – jak se slovníky chovají v clusteru ClickHouse (Distributed).

Závěr: Slovníky jsou nepostradatelným nástrojem pro práci s číselníkovými informacemi v ClickHouse. Mění pomalé JOIN s malými tabulkami na bleskové lookup v paměti. Pravidlo je jednoduché: pokud se číselník nemění častěji než jednou za minutu a jeho velikost umožňuje uložení do RAM – udělej z něj slovník. Tvé dotazy ti poděkují.

---

← Předchozí: TTL v ClickHouse: Automatické řízení životního cyklu dat
→ Další: Speciální enginy ClickHouse: když MergeTree nestačí

— Editorial Team