Materializovaná zobrazení v ClickHouse: síla inkrementálního zpracování
V běžných databázích (PostgreSQL, Oracle) je materializované zobrazení (Materialized View, MV) výsledek SELECTu, který se ukládá na disk a aktualizuje podle plánu (REFRESH). Sám rozhoduješ, kdy ho přepočítat.
V ClickHouse je to jinak. Zde je MV trigger na INSERT. Vytvoříš MV na základě SELECTu, a když vkládáš data do zdrojové tabulky, ClickHouse automaticky provede tento SELECT nad vloženými řádky a vloží výsledek do cílové tabulky.
Klíčový rozdíl:
- V PostgreSQL: aktualizuješ MV ručně nebo podle plánu, čteš CELOU zdrojovou tabulku.
- V ClickHouse: MV se spouští při každém vložení, zpracovává POUZE nové řádky.
Analogii ze života: Běžné zobrazení (VIEW) je jako brýle se zvětšovacími skly. Díváš se přes ně na zdrojová data, pokaždé přepočítáváš výsledek. Materializované zobrazení v ClickHouse je jako sekretářka, která sedí vedle a pokaždé, když přijde nový dokument, přepíše ho do speciální kartotéky v požadovaném formátu. Nemusíš pokaždé listovat všemi dokumenty, prostě jdeš do kartotéky.
Proč je to důležité: V ClickHouse nejsou triggery v klasickém smyslu. MV je jediný vestavěný mechanismus pro reakci na vkládání dat. S jeho pomocí můžeš:
- Agregovat data za běhu (hodinové souhrny).
- Denormalizovat data (dosazovat názvy sportů ze slovníku).
- Rozeslat stejná surová data do několika tabulek s různými strukturami (agregáty, deduplikace, plný archiv).
2. Jednoduché MV: sčítáme sázky po hodinách
Začneme nejčastějším scénářem: máš tabulku bets se sázkami a potřebuješ hodinovou statistiku podle sportů.
Krok 1: Vytvoříme cílovou tabulku (kam se budou agregovat data)
CREATE TABLE hourly_sport_stats
(
hour DateTime, -- Začátek hodiny (2025-06-01 14:00:00)
sport_id UInt8, -- Sport
bets_count UInt64, -- Počet sázek za hodinu
total_amount Decimal(18,2) -- Celková částka všech sázek
)
ENGINE = SummingMergeTree() -- Budeme sčítat podle hour + sport_id
ORDER BY (hour, sport_id);Krok 2: Vytvoříme MV, které bude tuto tabulku plnit
CREATE MATERIALIZED VIEW mv_hourly_stats TO hourly_sport_stats AS
SELECT
toStartOfHour(created_at) AS hour, -- Zaokrouhlíme čas na začátek hodiny
sport_id,
count() AS bets_count, -- Počet sázek ve skupině
sum(amount) AS total_amount -- Součet částek ve skupině
FROM bets
GROUP BY hour, sport_id;Co se zde děje řádek po řádku:
TO hourly_sport_stats– kam vložit výsledek. Lze neuvest, pokud struktura MV odpovídá struktuře SELECTu, ale je lepší uvádět explicitně.AS SELECT ...– dotaz, který se provádí nad každou vloženou dávkou dat do tabulkybets. Ne nad celou tabulkou, ale pouze nad novými řádky.GROUP BY hour, sport_id– agregace uvnitř dávky. Pokud je v jedné vložce 1000 řádků za jednu hodinu, MV spočítá jeden souhrnný řádek pro tuto hodinu.
Jak to používat:
-- Vložíme 100 sázek za 14:00 a 50 za 15:00
INSERT INTO bets (created_at, sport_id, amount) VALUES
('2025-06-01 14:15:00', 1, 100),
('2025-06-01 14:30:00', 1, 200),
('2025-06-01 15:00:00', 2, 50),
... (dalších 147 řádků) ...;
-- MV automaticky přidá nebo aktualizuje řádky v hourly_sport_stats
-- Pro hodinu 14:00, sport_id=1: bets_count se zvýší o počet sázek z vložky
-- Pro hodinu 15:00, sport_id=2: obdobně
-- Nyní čteme agregáty okamžitě, bez GROUP BY!
SELECT * FROM hourly_sport_stats
WHERE hour = '2025-06-01 14:00:00';Důležité omezení: MV v ClickHouse neumí aktualizovat již existující řádky v cílové tabulce při opakovaných vložkách za stejnou hodinu. Ale protože používáme SummingMergeTree, při fúzování na pozadí se řádky se stejným ORDER BY (hour, sport_id) sečtou. Proto SELECT * FROM hourly_sport_stats může vrátit několik řádků na jednu skupinu – vždy obaluj do SUM s GROUP BY, stejně jako v případě SummingMergeTree.
3. MV s AggregatingMergeTree – pro složité agregáty
Pokud potřebuješ nejen součty, ale například unikátní uživatele (uniq), průměrné sázky (avg), musíš použít AggregatingMergeTree a funkce *State / *Merge.
-- Cílová tabulka s AggregateFunction sloupci
CREATE TABLE hourly_sport_advanced
(
hour DateTime,
sport_id UInt8,
bets_count AggregateFunction(count, UInt64), -- Stav pro count
total_amount AggregateFunction(sum, Decimal(18,2)), -- Stav pro sum
unique_users AggregateFunction(uniq, UInt64), -- Stav pro uniq
avg_amount AggregateFunction(avg, Decimal(18,2)) -- Stav pro avg
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_id);
-- MV s funkcemi *State
CREATE MATERIALIZED VIEW mv_hourly_advanced TO hourly_sport_advanced AS
SELECT
toStartOfHour(created_at) AS hour,
sport_id,
countState(user_id) AS bets_count, -- Ne jen count, ale countState
sumState(amount) AS total_amount,
uniqState(user_id) AS unique_users, -- Přibližný uniq
avgState(amount) AS avg_amount
FROM bets
GROUP BY hour, sport_id;Proč právě takto: AggregatingMergeTree ukládá nikoli konečné hodnoty, ale mezistavy. To umožňuje správně slučovat agregáty z různých vložek (např. ze dvou dávek za jednu hodinu). uniqState ukládá hashovací tabulku unikátních hodnot, ne jen číslo.
Čtení dat:
SELECT
hour,
sport_id,
countMerge(bets_count) AS bets_count,
sumMerge(total_amount) AS total_amount,
uniqMerge(unique_users) AS unique_users,
avgMerge(avg_amount) AS avg_amount
FROM hourly_sport_advanced
GROUP BY hour, sport_id;4. MV s SummingMergeTree pro jednoduché počítadla
Pro jednoduché součty a počítadla je SummingMergeTree jednodušší a rychlejší než AggregatingMergeTree. Příklad z oddílu 2 je pro to ideální.
Další příklad – počítadla podle uživatelů:
-- Cílová tabulka: kolik sázek udělal každý uživatel za den
CREATE TABLE user_daily_counts
(
user_id UInt64,
day Date,
bets_count UInt64, -- bude se sčítat
total_amount Decimal(18,2) -- bude se sčítat
)
ENGINE = SummingMergeTree()
ORDER BY (user_id, day);
-- MV
CREATE MATERIALIZED VIEW mv_user_daily TO user_daily_counts AS
SELECT
user_id,
toDate(created_at) AS day,
count() AS bets_count,
sum(amount) AS total_amount
FROM bets
GROUP BY user_id, day;Výhoda SummingMergeTree: Jednoduchost, není potřeba funkcí *Merge při čtení. Stačí obyčejný SUM s GROUP BY pro jistotu (pokud se data nesloučila).
Nevýhoda: Pouze součty a počítadla. Žádní unikátní uživatelé, maxima, průměry.
5. Řetězec MV: událost → minutová agregace → hodinová → denní
Toto je mocný vzor pro postupné snižování granularity. Místo agregace ze surových dat přímo do denní statistiky (což by vyžadovalo přepočítávat miliardy řádků) stavíš řetězec.
Schéma:
- Surové sázky (tabulka
raw_bets) – uchováváme 7 dní. - Minutová agregace (tabulka
minute_stats) – uchováváme 30 dní. - Hodinová agregace (tabulka
hourly_stats) – uchováváme 365 dní. - Denní agregace (tabulka
daily_stats) – uchováváme 5 let.
-- Úroveň 1: minutová agregace ze surových dat
CREATE TABLE minute_stats
(
minute DateTime, -- zaokrouhleno na minutu
sport_id UInt8,
bets_count UInt64,
total_amount Decimal(18,2)
)
ENGINE = SummingMergeTree()
ORDER BY (minute, sport_id);
CREATE MATERIALIZED VIEW mv_minute_from_raw TO minute_stats AS
SELECT
toStartOfMinute(created_at) AS minute,
sport_id,
count() AS bets_count,
sum(amount) AS total_amount
FROM raw_bets
GROUP BY minute, sport_id;
-- Úroveň 2: hodinová agregace z minutové
CREATE TABLE hourly_stats
(
hour DateTime,
sport_id UInt8,
bets_count UInt64,
total_amount Decimal(18,2)
)
ENGINE = SummingMergeTree()
ORDER BY (hour, sport_id);
CREATE MATERIALIZED VIEW mv_hourly_from_minute TO hourly_stats AS
SELECT
toStartOfHour(minute) AS hour, -- zaokrouhlíme minutu na hodinu
sport_id,
sum(bets_count) AS bets_count, -- sečteme minutová počítadla
sum(total_amount) AS total_amount
FROM minute_stats
GROUP BY hour, sport_id;
-- Úroveň 3: denní agregace z hodinové
CREATE TABLE daily_stats
(
day Date,
sport_id UInt8,
bets_count UInt64,
total_amount Decimal(18,2)
)
ENGINE = SummingMergeTree()
ORDER BY (day, sport_id);
CREATE MATERIALIZED VIEW mv_daily_from_hourly TO daily_stats AS
SELECT
toDate(hour) AS day,
sport_id,
sum(bets_count) AS bets_count,
sum(total_amount) AS total_amount
FROM hourly_stats
GROUP BY day, sport_id;Výhody řetězce:
- Každá úroveň pracuje s menším objemem dat.
- Můžeš mazat stará data z nižších úrovní (TTL) a uchovávat agregáty roky.
- Při dotazu na měsíční report čteš
daily_stats(365 řádků za rok), neraw_bets(miliardy).
Analogii: Je to jako převyprávění děje knihy: nejprve přečteš knihu (surová data), pak napíšeš stručný obsah po kapitolách (minutová), pak po částech (hodinová), pak anotaci (denní). Aby sis vzpomněl na hlavní myšlenku, nemusíš znovu číst celou knihu.
6. Vzor Null + MV pro Kafka: rozeslat data do několika tabulek
Toto je profesionální vzor pro vysoce zatížené systémy. Místo zápisu do jedné tabulky vytvoříš Null tabulku (černou díru) a několik MV, která z ní čtou a zapisují do různých cílových tabulek.
-- Krok 1: Přijímací tabulka (Null, nic neukládá)
CREATE TABLE raw_events_null
(
user_id UInt64,
sport_id UInt8,
amount Decimal(18,2),
created_at DateTime,
ip String
)
ENGINE = Null;
-- Krok 2: Cílová tabulka 1 – všechna surová události (pro vyšetřování)
CREATE TABLE raw_events_archive
(
user_id UInt64,
sport_id UInt8,
amount Decimal(18,2),
created_at DateTime,
ip String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, user_id);
-- Krok 3: Cílová tabulka 2 – deduplikované sázky (bez IP kvůli GDPR)
CREATE TABLE bets_dedup
(
user_id UInt64,
sport_id UInt8,
amount Decimal(18,2),
created_at DateTime,
bet_id String
)
ENGINE = ReplacingMergeTree(created_at)
ORDER BY (user_id, bet_id);
-- Krok 4: Cílová tabulka 3 – hodinové agregáty
CREATE TABLE hourly_agg
(
hour DateTime,
sport_id UInt8,
total_amount AggregateFunction(sum, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_id);
-- Krok 5: MV – surový archiv (všechny řádky)
CREATE MATERIALIZED VIEW mv_archive TO raw_events_archive AS
SELECT * FROM raw_events_null;
-- Krok 6: MV – deduplikované sázky (generujeme bet_id za běhu)
CREATE MATERIALIZED VIEW mv_dedup TO bets_dedup AS
SELECT
user_id,
sport_id,
amount,
created_at,
concat(toString(user_id), '_', toString(sipHash64(created_at))) AS bet_id
FROM raw_events_null
WHERE amount != 0;
-- Krok 7: MV – hodinové agregáty
CREATE MATERIALIZED VIEW mv_hourly_agg TO hourly_agg AS
SELECT
toStartOfHour(created_at) AS hour,
sport_id,
sumState(amount) AS total_amount
FROM raw_events_null
GROUP BY hour, sport_id;Jak to funguje v praxi:
-- Kafka consumer vkládá do raw_events_null (rychle, žádné diskové operace)
INSERT INTO raw_events_null VALUES (123, 1, 100.00, now(), '192.168.1.1');
-- Tři MV paralelně získávají tato data a zapisují do svých tabulek:
-- 1. mv_archive → raw_events_archive (plná kopie včetně IP)
-- 2. mv_dedup → bets_dedup (deduplikované sázky, bez IP)
-- 3. mv_hourly_agg → hourly_agg (aktualizuje agregáty)Proč je to geniální:
- Jedno vložení – tři různá transformace.
- Zdrojová tabulka nic neukládá (Null), žádná režie zápisu.
- Každé MV lze zapnout/vypnout nezávisle.
- Snadno přidáš čtvrté MV (např. pro export do jiného formátu).
7. Problém: MV zpracovává pouze nová data po svém vytvoření
Nejdůležitější omezení: MV nevidí data, která již byla ve zdrojové tabulce v okamžiku jeho vytvoření. MV začíná pracovat až od okamžiku vytvoření a zpracovává pouze nové INSERTy.
-- Už máme miliardu řádků v bets
SELECT count() FROM bets; -- 1,000,000,000
-- Vytvoříme MV
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats AS ...;
-- Vložíme novou sázku
INSERT INTO bets VALUES (now(), 1, 100);
-- Do hourly_stats se dostane pouze tato nová sázka.
-- Předchozí miliarda zůstala nezpracována!Jak vyřešit problém – nahrání historických dat:
-- Způsob 1: Ruční INSERT s použitím stejného SELECTu jako v MV
INSERT INTO hourly_stats
SELECT
toStartOfHour(created_at) AS hour,
sport_id,
count() AS bets_count,
sum(amount) AS total_amount
FROM bets
WHERE created_at < '2025-06-01' -- stará data
GROUP BY hour, sport_id;
-- Nyní jsou v hourly_stats jak stará, tak nová dataZpůsob 2: Přetvořit MV s POPULATE (nebezpečné!)
-- NEBEZPEČNÉ: při CREATE ... POPULATE MV zpracuje CELOU existující tabulku
-- Během zpracování je tabulka uzamčena, data mohou být ztracena
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats POPULATE AS
SELECT ... FROM bets ...;Proč je POPULATE v produkci nebezpečný:
- Po dobu provádění SELECTu může být zdrojová tabulka uzamčena.
- Pokud jsou během POPULATE do zdrojové tabulky vkládána nová data, mohou být ztracena (nedostanou se ani do starého, ani do nového MV).
- U velkých tabulek může POPULATE trvat hodiny.
Správný přístup: Vytvoř MV bez POPULATE a historická data nahraj ručně pomocí INSERTu se stejným SELECTem jako v MV. Je to bezpečné, předvídatelné a neblokuje vkládání.
8. Monitorování a ladění MV
Kontrola, zda jsou MV aktivní
-- Seznam všech MV a jejich cílových tabulek
SELECT
name,
engine,
total_rows,
formatReadableSize(total_bytes) AS size
FROM system.tables
WHERE engine = 'MaterializedView'
AND database = 'default';Kontrola, zda MV reaguje na INSERT
-- Zapnout detailní log (pouze pro ladění)
SET log_queries = 1;
-- Vložit jeden řádek do zdrojové tabulky
INSERT INTO bets (created_at, sport_id, amount) VALUES (now(), 1, 100);
-- Najít dotazy související s MV
SELECT
query,
query_duration_ms,
read_rows,
written_rows
FROM system.query_log
WHERE type = 'QueryFinish'
AND query LIKE '%MV%'
AND event_time > now() - INTERVAL 1 MINUTE
ORDER BY event_time DESC;Kontrola dat v cílové tabulce
-- Porovnat počet unikátních skupin ve zdrojové a cílové tabulce
-- (mělo by se shodovat po nahrání historie)
-- Zdrojová: kolik unikátních (hodina, sport)
SELECT count(*) FROM (
SELECT toStartOfHour(created_at) AS hour, sport_id
FROM bets
GROUP BY hour, sport_id
) AS src;
-- Cílová:
SELECT count(*) FROM hourly_stats;Odhalení problémů s typy
Pokud MV nereaguje, často je problém v nekompatibilitě typů:
-- Chyba: Sloupec `sport_id` typ UInt8 ve zdrojové tabulce, ale Int32 v cíli MV
-- Řešení: explicitně přetypuj v SELECTu
SELECT
toStartOfHour(created_at) AS hour,
toUInt8(sport_id) AS sport_id, -- explicitní přetypování
count() AS bets_count
FROM bets
GROUP BY hour, sport_id;9. Reálné schéma pro gamblingovou platformu
Sestavíme kompletní architekturu pro online kasino s několika požadavky:
- Detailní události pro vyšetřování (uchovávat 7 dní).
- Deduplikované sázky pro analytiku (uchovávat 90 dní).
- Hodinové agregáty pro dashboard (uchovávat 2 roky).
- Denní agregáty pro finanční reporty (uchovávat 10 let).
-- ===== ÚROVEŇ 0: Přijímač (Null) =====
CREATE TABLE raw_stream
(
user_id UInt64,
sport_id UInt8,
bet_id String,
amount Decimal(18,2),
created_at DateTime,
ip String
)
ENGINE = Null;
-- ===== CÍLOVÉ TABULKY =====
-- 1. Archiv na 7 dní (MergeTree + TTL)
CREATE TABLE raw_archive
(
user_id UInt64, sport_id UInt8, bet_id String,
amount Decimal(18,2), created_at DateTime, ip String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY created_at
TTL created_at + INTERVAL 7 DAY DELETE;
-- 2. Deduplikované sázky (ReplacingMergeTree)
CREATE TABLE bets_dedup
(
user_id UInt64, sport_id UInt8, bet_id String,
amount Decimal(18,2), created_at DateTime
)
ENGINE = ReplacingMergeTree(created_at)
ORDER BY (user_id, bet_id)
TTL created_at + INTERVAL 90 DAY DELETE;
-- 3. Hodinové agregáty (SummingMergeTree)
CREATE TABLE hourly_agg
(
hour DateTime, sport_id UInt8, total_amount Decimal(18,2), bet_count UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (hour, sport_id)
TTL hour + INTERVAL 2 YEAR DELETE;
-- 4. Denní agregáty (AggregatingMergeTree pro uniq)
CREATE TABLE daily_agg
(
day Date, sport_id UInt8, total_amount AggregateFunction(sum, Decimal(18,2)),
unique_users AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (day, sport_id)
TTL day + INTERVAL 10 YEAR DELETE;
-- ===== MATERIALIZOVANÁ ZOBRAZENÍ =====
CREATE MATERIALIZED VIEW mv_raw_archive TO raw_archive AS
SELECT * FROM raw_stream;
CREATE MATERIALIZED VIEW mv_bets_dedup TO bets_dedup AS
SELECT user_id, sport_id, bet_id, amount, created_at
FROM raw_stream
WHERE bet_id != '';
CREATE MATERIALIZED VIEW mv_hourly_agg TO hourly_agg AS
SELECT
toStartOfHour(created_at) AS hour,
sport_id,
sum(amount) AS total_amount,
count() AS bet_count
FROM raw_stream
GROUP BY hour, sport_id;
CREATE MATERIALIZED VIEW mv_daily_agg TO daily_agg AS
SELECT
toDate(created_at) AS day,
sport_id,
sumState(amount) AS total_amount,
uniqState(user_id) AS unique_users
FROM raw_stream
GROUP BY day, sport_id;Tok dat:
- Aplikace zapisuje do
raw_stream(Null) – milisekundy. - Čtyři MV paralelně zpracovávají každé vložení.
- Každá cílová tabulka získává data ve své agregované/transformované podobě.
- TTL automaticky odstraňuje zastaralá data na každé úrovni.
Výhody oproti jednomu MERGETREE:
- Rychlost vkládání je maximální (Null + MV pracují v paměti).
- Různé úrovně uchovávání (7 dní, 90 dní, 2 roky, 10 let).
- Různé enginy pro různé úkoly (MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree).
- Žádná ztráta výkonu při dotazech na staré agregáty.
Co dál
Nyní umíš stavět složité datové pipeline pomocí MV. Další témata:
- MV s aktualizací externích zdrojů – jak kombinovat slovníky a MV pro obohacování.
- Ladění složitých řetězců MV – jak zjistit, proč se data nedostala do cílové tabulky.
- Replikace MV v clusteru – jak se MV chovají na distribuovaných tabulkách.
Shrnutí: Materializovaná zobrazení v ClickHouse nejsou jen cache. Jsou to mocný mechanismus inkrementálního zpracování dat, který umožňuje budovat skutečné event-sourcing architektury uvnitř databáze. Pravidlo je jednoduché: pokud potřebuješ agregovat, transformovat nebo duplikovat data při vkládání – použij MV. A nikdy nezapomeň nahrát historická data ručně, ne přes POPULATE v produkci.
← Předchozí: Speciální enginy ClickHouse: když MergeTree nestačí
→ Další: Sekundární (skipping) indexy v ClickHouse: když index podle ORDER BY nestačí
— Editorial Team
Zatím žádné komentáře.