Słowniki w ClickHouse: szybki lookup bez JOIN

Słowniki w ClickHouse: szybki lookup bez JOIN

1. Po co są słowniki — problem JOIN na słownikach

Wróćmy do naszego kasyna online. Masz tabelę zakładów bets, w której przechowujesz sport_id — liczbę od 1 do 20. Ale w raportach trzeba pokazywać nazwę sportu: „Piłka nożna”, „Hokej”, „Tenis”. Zazwyczaj te informacje leżą w osobnej tabeli słownikowej sports.

-- Wolne zapytanie z 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;

Dla miliarda wierszy w bets i 20 wierszy w sports ten JOIN będzie kopiować słownik dla każdego fragmentu danych. ClickHouse wykona broadcast join (roześle małą tabelę na wszystkie shardy), co jest szybkie, ale wciąż obciąża pamięć i procesor.

Słowniki (dictionaries) rozwiązują ten problem inaczej. Słownik to in-memory (w pamięci RAM) słownik, który żyje wewnątrz ClickHouse. Możesz się do niego odwołać po kluczu i otrzymać wartość w mikrosekundach bez wykonywania JOIN.

Analogia z życia: Słownik to jak ściąga na egzaminie. Masz listę 20 wierszy: „1 = piłka nożna, 2 = hokej...”. Gdy potrzebujesz poznać nazwę sportu po ID, po prostu patrzysz na ściągę (pamięć), a nie idziesz do biblioteki po gruby słownik (dysk). To tysiące razy szybciej.

Dlaczego to ważne w ClickHouse: ClickHouse przechowuje dane na dysku, a odczyt nawet małej tabeli przez JOIN wymaga operacji dyskowych. Słownik leży w pamięci (skompresowany i zoptymalizowany), a dostęp do niego to po prostu odczyt z RAM.

2. Typy słowników — jak wybrać strukturę

ClickHouse oferuje kilka typów (LAYOUT) słowników w zależności od:

• rozmiaru danych (ile kluczy),
• typu klucza (prosty lub złożony),
• potrzeby wyszukiwania po zakresie (np. kurs waluty na datę).

Typ	Kiedy używać	Maks. kluczy	Cechy szczególne
flat	Bardzo małe słowniki (do 500k kluczy)	500 000	Najszybszy, przechowywany w tablicy. Klucz — tylko liczby całkowite (UInt*).
hashed	Średnie słowniki (miliony kluczy)	Nieograniczenie	Tablica mieszająca. Pasuje do dowolnych typów kluczy. Nieco wolniejszy niż flat.
sparse_hashed	Bardzo duże (dziesiątki milionów)	Bardzo dużo	Oszczędza pamięć (nie przechowuje zerowych wartości), ale nieco wolniejszy.
range_hashed	Zakres dat (kurs waluty według daty)	Nieograniczenie	Klucz + zakres (start, end). Pozwala szukać get(key, date).
complex_key_hashed	Złożony klucz (np. market_id, selection_id)	Nieograniczenie	Klucz — krotka (tuple) z kilku pól.
ip_trie	Adresy IP (wyszukiwanie po prefiksie)	Do 500k	Dla GeoIP: po IP znajdź kraj/miasto.

Jak wybrać:

• Mniej niż 500 tysięcy kluczy i klucz — liczba całkowita → flat (maksymalna prędkość).
• Więcej niż 500 tysięcy lub klucz niecałkowity → hashed.
• Bardzo dużo kluczy i dużo pustych wartości → sparse_hashed.
• Potrzebne wyszukiwanie po dacie → range_hashed.
• Złożony klucz (kilka pól) → complex_key_hashed.

Analogia: flat — to jak szafa z ponumerowanymi szufladami (indeks — numer). Od razu idziesz do szuflady nr 17. hashed — jak katalog biblioteczny, gdzie najpierw obliczasz półkę po skrócie nazwiska autora. range_hashed — jak archiwum, gdzie szukasz dokumentu, znając datę.

3. Źródła danych — skąd słownik bierze dane

Słownik może być wypełniany z różnych źródeł (SOURCE). ClickHouse sam okresowo aktualizuje słownik ze źródła z zadanym interwałem (LIFETIME).

Obsługiwane źródła:

• CLICKHOUSE — inna tabela w ClickHouse
• MYSQL — tabela w MySQL
• POSTGRESQL — tabela w PostgreSQL
• HTTP — REST API (JSON lub XML)
• FILE — lokalny plik (CSV, TSV)
• REDIS — Redis (klucz-wartość)
• MONGODB — kolekcja MongoDB

Przykład z 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)   -- aktualizuj co 1-2 godziny
LAYOUT(HASHED());

Dlaczego to wygodne: Twój słownik walut może być aktualizowany raz na godzinę z zewnętrznej bazy MySQL, którą prowadzi dział finansowy. ClickHouse sam pobierze zmiany, nie musisz pisać skryptu ETL.

4. Tworzenie słownika z tabeli ClickHouse — krok po kroku

Najczęstszy scenariusz: masz już tabelę słownikową w ClickHouse i chcesz zamienić ją w słownik dla szybkich lookup.

Krok 1: Tworzymy tabelę słownikową (jeśli jej nie ma)

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

-- Wypełniamy danymi
INSERT INTO sports VALUES (1, 'Football', 'team'), (2, 'Hockey', 'team'), (3, 'Tennis', 'individual');

Krok 2: Tworzymy słownik na bazie tej tabeli

CREATE DICTIONARY sports_dict
(
    id       UInt32,          -- kolumna-klucz
    name     String,          -- wartość, którą będziemy pobierać
    category String           -- inna wartość
)
PRIMARY KEY id                -- klucz do wyszukiwania
SOURCE(CLICKHOUSE(
    host 'localhost'
    port 9000
    user 'default'
    password ''
    db 'default'
    table 'sports'
))
LIFETIME(MIN 300 MAX 600)     -- aktualizuj co 5-10 minut
LAYOUT(HASHED());              -- dla naszych 20 rekordów można też flat, ale hashed też ok

Omówienie parametrów:

• PRIMARY KEY id — kolumna, po której będzie wyszukiwanie. Musi być unikalna.
• SOURCE(CLICKHOUSE(...)) — źródło danych. Można wskazać dowolny host, niekoniecznie localhost.
• LIFETIME(MIN 300 MAX 600) — słownik będzie całkowicie przeładowywany co 5–10 minut. MIN i MAX są potrzebne do randomizacji, aby nie wszystkie słowniki na wszystkich serwerach aktualizowały się jednocześnie.
• LAYOUT(HASHED()) — struktura w pamięci. Dla 20 rekordów lepszy flat, ale zostawiamy hashed jako przykład.

Co się stanie po utworzeniu: ClickHouse odczyta całą tabelę sports, załaduje ją do pamięci w postaci tablicy mieszającej. Teraz możesz używać dictGet do szybkiego dostępu.

5. Użycie w zapytaniach — dictGet i przyjaciele

Główna magia zaczyna się w SELECT. Zamiast JOIN sports używasz funkcji słownikowych.

dictGet — główna funkcja

-- Pobierz nazwę sportu po sport_id
SELECT 
    user_id,
    sport_id,
    dictGet('sports_dict', 'name', sport_id) AS sport_name,
    amount
FROM bets
LIMIT 10;

Składnia: dictGet('nazwa_słownika', 'kolumna_wartości', klucz)

dictGetOrDefault — z wartością domyślną

-- Jeśli sport_id nie znaleziony, zwróć 'Unknown'
SELECT 
    user_id,
    sport_id,
    dictGetOrDefault('sports_dict', 'name', sport_id, 'Unknown') AS sport_name
FROM bets;

dictHas — sprawdź, czy klucz istnieje

-- Znajdź zakłady z nieprawidłowym sport_id
SELECT DISTINCT sport_id
FROM bets
WHERE dictHas('sports_dict', sport_id) = 0;   -- zwróci sport_id, których nie ma w słowniku

Pełny przykład z agregacją

-- Top 5 dyscyplin według sumy zakładów 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;

Dlaczego to szybsze niż JOIN: Brak odczytu z dysku, brak dystrybucji słownika po shardach, brak haszowania na etapie zapytania. Słownik jest już w pamięci każdego węzła ClickHouse.

6. Złożone klucze — dictGet z tuple

Gdy klucz składa się z kilku pól (np. market_id + selection_id), użyj LAYOUT(COMPLEX_KEY_HASHED()) i przekaż klucz jako krotkę (tuple).

Tworzenie słownika z kluczem złożonym:

-- Słownik kursów: (market_id, selection_id) → kurs
CREATE DICTIONARY odds_dict
(
    market_id    UInt32,
    selection_id UInt32,
    odds_value   Decimal(10,3)
)
PRIMARY KEY (market_id, selection_id)   -- klucz złożony!
SOURCE(CLICKHOUSE(
    table 'odds_reference'
))
LIFETIME(MIN 60 MAX 120)
LAYOUT(COMPLEX_KEY_HASHED());            -- koniecznie complex_key!

Użycie w zapytaniach:

-- Pobieramy kurs dla konkretnego rynku i wyniku
SELECT 
    bet_id,
    market_id,
    selection_id,
    dictGet('odds_dict', 'odds_value', tuple(market_id, selection_id)) AS odds
FROM bets;

Co to jest tuple? Krotka to po prostu grupa wartości, owinięta w nawiasy. tuple(market_id, selection_id) tworzy klucz postaci (100, 5).

7. Słowniki zakresowe — dla danych historycznych (kurs waluty na datę)

Wyobraź sobie, że masz historyczne kursy walut, które zmieniają się każdego dnia. Potrzebujesz dla każdego zakładu w euro poznać kurs na dzień dokonania zakładu.

Tabela źródłowa (np. w 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

Tworzymy słownik zakresowy:

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())                     -- specjalny typ
RANGE(MIN start_date MAX end_date);        -- wskazujemy kolumny zakresu

Użycie:

-- Dla każdego zakładu w EUR pobieramy kurs na datę zakładu
SELECT 
    bet_id,
    amount_eur,
    created_at,
    dictGet('eur_rates_dict', 'rate', tuple(currency, created_at)) AS rate
FROM bets
WHERE currency = 'EUR';

ClickHouse sam znajdzie rekord w słowniku, gdzie created_at mieści się między start_date a end_date dla danej waluty.

Analogia: To jak kalendarz zmian cen. Mówisz: „Daj kurs na 15 marca”, a słownik patrzy w swój kalendarz: 15 marca wchodzi w interwał 1 marca – 31 marca, kurs 1.10.

8. Monitorowanie słowników — system.dictionaries

Aby zrozumieć, co dzieje się ze słownikami, istnieje tabela systemowa system.dictionaries.

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

Przydatne kolumny:

Kolumna	Co pokazuje
status	LOADED — załadowany, LOADING — ładuje się, FAILED — błąd
origin	Skąd załadowany (ClickHouse, MySQL...)
type	Typ (flat, hashed, range_hashed...)
key	Typ klucza
attribute.names	Jakie kolumny są dostępne
bytes_allocated	Ile pamięci zajmuje (bajty)
query_count	Ile razy był odpytywany
hit_rate	Procent trafień (im wyższy, tym lepiej)
load_factor	Jak bardzo wypełniony jest słownik (dla hashed)
creation_time	Kiedy został załadowany
last_exception	Jeśli status FAILED — tutaj będzie błąd

Monitorowanie pamięci:

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

Jeśli jakiś słownik zajmuje gigabajty — możliwe, że wybrałeś niewłaściwy LAYOUT (np. hashed zamiast sparse_hashed).

9. Gorące przeładowanie — SYSTEM RELOAD DICTIONARY

Słowniki aktualizują się automatycznie zgodnie z LIFETIME. Ale czasami trzeba je wymusić:

• Właśnie poprawiłeś dane w źródle i nie chcesz czekać 10 minut.
• Słownik padł z błędem (np. źródło było niedostępne) i naprawiłeś problem.

-- Przeładuj konkretny słownik
SYSTEM RELOAD DICTIONARY sports_dict;

-- Przeładuj wszystkie słowniki
SYSTEM RELOAD DICTIONARIES;

Co się stanie: ClickHouse ponownie odczyta źródło (np. tabelę sports) i zastąpi zawartość słownika w pamięci. W trakcie przeładowania zapytania używające dictGet będą czekać (lub zwrócą stare dane — zależy od wersji). Dla systemów krytycznych wykonuj przeładowanie w nocy.

Jak sprawdzić, czy słownik załadował się poprawnie:

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

Jeśli status LOADED — wszystko dobrze. Jeśli FAILED — sprawdź last_exception.

10. Przykład architektury: wszystkie słowniki platformy bukmacherskiej

Wyobraź sobie pełną architekturę platformy bukmacherskiej. Masz dziesiątki słowników, które są stale używane w zapytaniach do wzbogacania danych.

Słowniki, które warto utworzyć:

-- 1. Dyscypliny sportowe (20 rekordów, FLAT)
CREATE DICTIONARY sports_dict (id UInt32, name String, category String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'sports'))
LIFETIME(3600) LAYOUT(FLAT());

-- 2. Ligi/mistrzostwa (10k rekordów, 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. Kraje (200 rekordów, FLAT)
CREATE DICTIONARY countries_dict (id UInt32, name String, code String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'countries'))
LIFETIME(86400) LAYOUT(FLAT());   -- zmieniają się rzadko, aktualizuj raz na dobę

-- 4. Waluty z historycznym kursem (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. Prowizje według krajów i typu zakładu (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());

Użycie w jednym zapytaniu:

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;

Zalety takiego podejścia:

• Szybkość: Ani jednego JOIN, tylko bezpośrednie lookup w pamięci.
• Czytelność: Kod jaśniejszy — od razu widać, jakie słowniki są używane.
• Zarządzalność: Aktualizacja słownika (np. prowizji dla Hiszpanii) odbywa się w jednym miejscu, a nie w skryptach ETL.
• Oszczędność pamięci: Słowniki są przechowywane w skompresowanej formie, często zajmują mniej miejsca niż kolumna z danymi zdenormalizowanymi w tabeli.

Co się stanie, jeśli nie użyjesz słowników? Albo zdenormalizujesz dane (powtarzasz nazwę sportu w każdym wierszu zakładu — mnożąc objętość danych 10+ razy), albo robisz JOIN przy każdej agregacji (co na miliardach wierszy jest wolne i bolesne).

Co dalej

Teraz wiesz już wszystko o słownikach. Kolejne tematy:

• Aktualizacja słowników przez HTTP — jak pobierać dane z zewnętrznego API.
• Użycie słowników w widokach zmaterializowanych — do wstępnego wzbogacania danych.
• Klasteryzacja słowników — jak zachowują się słowniki w klastrze ClickHouse (Distributed).

Podsumowanie: Słowniki to niezbędne narzędzie do pracy z danymi słownikowymi w ClickHouse. Zamieniają wolne JOIN z małymi tabelami w błyskawiczne lookup w pamięci. Zasada jest prosta: jeśli słownik nie zmienia się częściej niż raz na minutę, a jego rozmiar pozwala na przechowywanie w RAM — zrób z niego słownik. Twoje zapytania ci podziękują.

---

← Poprzedni: TTL w ClickHouse: Automatyczne zarządzanie cyklem życia danych
→ Następny: Specjalne silniki ClickHouse: gdy MergeTree nie pasuje

— Editorial Team