Materializowane widoki w ClickHouse: potęga przetwarzania przyrostowego
W znanych bazach danych (PostgreSQL, Oracle) materializowany widok (Materialized View, MV) to wynik SELECT, który jest zapisywany na dysku i odświeżany zgodnie z harmonogramem (REFRESH). Sam decydujesz, kiedy go przeliczyć.
W ClickHouse jest inaczej. Tutaj MV to wyzwalacz na INSERT. Tworzysz MV na podstawie SELECT, a gdy wstawiasz dane do tabeli źródłowej, ClickHouse automatycznie wykonuje ten SELECT na wstawionych wierszach i wstawia wynik do tabeli docelowej.
Kluczowa różnica:
- W PostgreSQL: aktualizujesz MV ręcznie lub zgodnie z harmonogramem, czytając CAŁĄ tabelę źródłową.
- W ClickHouse: MV uruchamia się przy każdym wstawieniu, przetwarzając TYLKO nowe wiersze.
Analogia z życia: Zwykły widok (VIEW) jest jak okulary powiększające. Patrzysz przez nie na dane źródłowe, za każdym razem przeliczając wynik. Materializowany widok w ClickHouse to jak sekretarz, który siedzi obok i za każdym razem, gdy przychodzi nowy dokument, przepisuje go do specjalnej kartoteki w odpowiednim formacie. Nie musisz za każdym razem przeglądać wszystkich dokumentów, po prostu idziesz do kartoteki.
Dlaczego to ważne: W ClickHouse nie ma wyzwalaczy w klasycznym rozumieniu. MV to jedyny wbudowany mechanizm reagowania na wstawianie danych. Za jego pomocą możesz:
- Agregować dane na bieżąco (podsumowania godzinowe).
- Denormalizować dane (podstawiać nazwy sportów ze słownika).
- Rozsyłać te same surowe dane do kilku tabel o różnych strukturach (agregaty, deduplikacja, pełne archiwum).
2. Proste MV: sumowanie zakładów na godzinę
Zaczniemy od najczęstszego scenariusza: masz tabelę bets z zakładami i potrzebujesz statystyk godzinowych według sportów.
Krok 1: Tworzymy tabelę docelową (do której będą agregowane dane)
CREATE TABLE hourly_sport_stats
(
hour DateTime, -- Początek godziny (2025-06-01 14:00:00)
sport_id UInt8, -- Sport
bets_count UInt64, -- Liczba zakładów na godzinę
total_amount Decimal(18,2) -- Suma wszystkich zakładów
)
ENGINE = SummingMergeTree() -- Będziemy sumować według hour + sport_id
ORDER BY (hour, sport_id);Krok 2: Tworzymy MV, które będzie wypełniać tę tabelę
CREATE MATERIALIZED VIEW mv_hourly_stats TO hourly_sport_stats AS
SELECT
toStartOfHour(created_at) AS hour, -- Zaokrąglamy czas do początku godziny
sport_id,
count() AS bets_count, -- Liczba zakładów w grupie
sum(amount) AS total_amount -- Suma zakładów w grupie
FROM bets
GROUP BY hour, sport_id;Co się dzieje wiersz po wierszu:
TO hourly_sport_stats– gdzie wstawić wynik. Można pominąć, jeśli struktura MV pokrywa się ze strukturą SELECT, ale lepiej podać jawnie.AS SELECT ...– zapytanie, które jest wykonywane na każdej wstawionej paczce danych do tabelibets. Nie na całej tabeli, a tylko na nowych wierszach.GROUP BY hour, sport_id– agregacja wewnątrz paczki. Jeśli w jednym wstawieniu jest 1000 wierszy z tej samej godziny, MV policzy jeden wiersz podsumowujący dla tej godziny.
Jak tego użyć:
-- Wstawiamy 100 zakładów z godziny 14:00 i 50 z godziny 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),
... (jeszcze 147 wierszy) ...;
-- MV automatycznie doda lub zaktualizuje wiersze w hourly_sport_stats
-- Dla godziny 14:00, sport_id=1: bets_count zwiększy się o liczbę zakładów z wstawienia
-- Dla godziny 15:00, sport_id=2: analogicznie
-- Teraz czytamy agregaty natychmiast, bez GROUP BY!
SELECT * FROM hourly_sport_stats
WHERE hour = '2025-06-01 14:00:00';Ważne ograniczenie: MV w ClickHouse nie potrafi aktualizować istniejących wierszy w tabeli docelowej przy ponownych wstawieniach dla tej samej godziny. Ale ponieważ używamy SummingMergeTree, podczas scalania w tle wiersze z tym samym ORDER BY (hour, sport_id) zostaną zsumowane. Dlatego SELECT * FROM hourly_sport_stats może zwrócić kilka wierszy dla jednej grupy – zawsze opakowuj w SUM z GROUP BY, tak jak w przypadku SummingMergeTree.
3. MV z AggregatingMergeTree – dla złożonych agregacji
Jeśli potrzebujesz nie tylko sum, ale także np. unikalnych użytkowników (uniq), średnich zakładów (avg), musisz użyć AggregatingMergeTree i funkcji *State / *Merge.
-- Tabela docelowa z kolumnami AggregateFunction
CREATE TABLE hourly_sport_advanced
(
hour DateTime,
sport_id UInt8,
bets_count AggregateFunction(count, UInt64), -- Stan dla count
total_amount AggregateFunction(sum, Decimal(18,2)), -- Stan dla sum
unique_users AggregateFunction(uniq, UInt64), -- Stan dla uniq
avg_amount AggregateFunction(avg, Decimal(18,2)) -- Stan dla avg
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_id);
-- MV z funkcjami *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, -- Nie zwykły count, a countState
sumState(amount) AS total_amount,
uniqState(user_id) AS unique_users, -- Przybliżony uniq
avgState(amount) AS avg_amount
FROM bets
GROUP BY hour, sport_id;Dlaczego tak: AggregatingMergeTree przechowuje nie wartości końcowe, ale stany pośrednie. Pozwala to poprawnie łączyć agregaty z różnych wstawień (np. z dwóch paczek dla tej samej godziny). uniqState przechowuje tablicę mieszającą unikalnych wartości, a nie tylko liczbę.
Odczyt danych:
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 z SummingMergeTree dla prostych liczników
Dla prostych sum i liczników SummingMergeTree jest prostszy i szybszy niż AggregatingMergeTree. Przykład z sekcji 2 jest do tego idealny.
Inny przykład – liczniki według użytkowników:
-- Tabela docelowa: ile zakładów postawił każdy użytkownik dziennie
CREATE TABLE user_daily_counts
(
user_id UInt64,
day Date,
bets_count UInt64, -- będzie sumowane
total_amount Decimal(18,2) -- będzie sumowane
)
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;Zaleta SummingMergeTree: Prostota, brak konieczności używania funkcji *Merge przy odczycie. Wystarczy zwykłe SUM z GROUP BY dla bezpieczeństwa (jeśli dane nie zostały scalone).
Wada: Tylko sumy i liczniki. Żadnych unikalnych użytkowników, maksimów, średnich.
5. Łańcuch MV: zdarzenie → agregacja minutowa → godzinowa → dzienna
To potężny wzorzec do stopniowego zmniejszania szczegółowości. Zamiast agregować z surowych danych bezpośrednio do statystyk dziennych (co wymagałoby przeliczenia miliardów wierszy), budujesz łańcuch.
Schemat:
- Surowe zakłady (tabela
raw_bets) – przechowujemy 7 dni. - Agregacja minutowa (tabela
minute_stats) – przechowujemy 30 dni. - Agregacja godzinowa (tabela
hourly_stats) – przechowujemy 365 dni. - Agregacja dzienna (tabela
daily_stats) – przechowujemy 5 lat.
-- Poziom 1: agregacja minutowa z surowych danych
CREATE TABLE minute_stats
(
minute DateTime, -- zaokrąglone do minuty
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;
-- Poziom 2: agregacja godzinowa z minutowej
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, -- zaokrąglamy minutę do godziny
sport_id,
sum(bets_count) AS bets_count, -- sumujemy liczniki minutowe
sum(total_amount) AS total_amount
FROM minute_stats
GROUP BY hour, sport_id;
-- Poziom 3: agregacja dzienna z godzinowej
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;Zalety łańcucha:
- Każdy poziom pracuje na mniejszej objętości danych.
- Możesz usuwać stare dane z niższych poziomów (TTL) i przechowywać agregaty przez lata.
- Przy zapytaniu o raport miesięczny czytasz
daily_stats(365 wierszy rocznie), a nieraw_bets(miliardy).
Analogia: To jak opowiadanie fabuły książki: najpierw czytasz książkę (surowe dane), potem piszesz krótkie streszczenie rozdziałów (minutowe), potem – części (godzinowe), potem – adnotację (dzienną). Aby przypomnieć sobie główną myśl, nie musisz czytać całej książki od nowa.
6. Wzorzec Null + MV dla Kafka: rozsyłanie danych do wielu tabel
To profesjonalny wzorzec dla systemów o wysokim obciążeniu. Zamiast pisać do jednej tabeli, tworzysz tabelę Null (czarną dziurę) i kilka MV, które z niej czytają i zapisują do różnych tabel docelowych.
-- Krok 1: Tabela odbiorcza (Null, nic nie przechowuje)
CREATE TABLE raw_events_null
(
user_id UInt64,
sport_id UInt8,
amount Decimal(18,2),
created_at DateTime,
ip String
)
ENGINE = Null;
-- Krok 2: Tabela docelowa 1 – wszystkie surowe zdarzenia (do dochodzeń)
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: Tabela docelowa 2 – zdeduplikowane zakłady (bez IP dla 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: Tabela docelowa 3 – agregaty godzinowe
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 – surowe archiwum (wszystkie wiersze)
CREATE MATERIALIZED VIEW mv_archive TO raw_events_archive AS
SELECT * FROM raw_events_null;
-- Krok 6: MV – zdeduplikowane zakłady (generujemy bet_id na bieżąco)
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 – agregaty godzinowe
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 działa w praktyce:
-- Konsument Kafka wstawia do raw_events_null (szybko, brak operacji dyskowych)
INSERT INTO raw_events_null VALUES (123, 1, 100.00, now(), '192.168.1.1');
-- Trzy MV równolegle otrzymują te dane i zapisują do swoich tabel:
-- 1. mv_archive → raw_events_archive (pełna kopia, w tym IP)
-- 2. mv_dedup → bets_dedup (zdeduplikowane zakłady, bez IP)
-- 3. mv_hourly_agg → hourly_agg (aktualizuje agregaty)Dlaczego to genialne:
- Jedno wstawienie – trzy różne transformacje.
- Tabela źródłowa nic nie przechowuje (Null), brak narzutu na zapis.
- Każde MV można włączyć/wyłączyć niezależnie.
- Łatwo dodać czwarte MV (np. do eksportu w innym formacie).
7. Problem: MV przetwarza tylko nowe dane po utworzeniu
Bardzo ważne ograniczenie: MV nie widzi danych, które już były w tabeli źródłowej w momencie jego utworzenia. MV zaczyna działać dopiero od momentu utworzenia i przetwarza tylko nowe INSERT.
-- Mamy już miliard wierszy w bets
SELECT count() FROM bets; -- 1,000,000,000
-- Tworzymy MV
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats AS ...;
-- Wstawiamy nowy zakład
INSERT INTO bets VALUES (now(), 1, 100);
-- W hourly_stats trafi tylko ten nowy zakład.
-- Poprzedni miliard pozostał nieprzetworzony!Jak rozwiązać problem – wypełnienie danymi historycznymi:
-- Sposób 1: Ręczny INSERT z użyciem tego samego SELECT co w 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' -- stare dane
GROUP BY hour, sport_id;
-- Teraz w hourly_stats są zarówno stare, jak i nowe daneSposób 2: Ponowne utworzenie MV z POPULATE (niebezpieczne!)
-- NIEBEZPIECZNE: przy CREATE ... POPULATE MV przetworzy CAŁĄ istniejącą tabelę
-- Podczas przetwarzania tabela może być zablokowana, dane mogą zostać utracone
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats POPULATE AS
SELECT ... FROM bets ...;Dlaczego POPULATE jest niebezpieczny w produkcji:
- Na czas wykonywania SELECT tabela źródłowa może być zablokowana.
- Jeśli podczas POPULATE do tabeli źródłowej wstawiane są nowe dane, mogą one zostać utracone (nie trafią ani do starego, ani do nowego MV).
- W przypadku dużych tabel POPULATE może trwać godzinami.
Prawidłowe podejście: Utwórz MV bez POPULATE, a dane historyczne wypełnij ręcznie przez INSERT z tym samym SELECT co w MV. Jest to bezpieczne, przewidywalne i nie blokuje wstawień.
8. Monitorowanie i debugowanie MV
Sprawdzenie, czy MV są aktywne
-- Lista wszystkich MV i ich tabel docelowych
SELECT
name,
engine,
total_rows,
formatReadableSize(total_bytes) AS size
FROM system.tables
WHERE engine = 'MaterializedView'
AND database = 'default';Sprawdzenie, czy MV uruchamia się na INSERT
-- Włącz szczegółowy log (tylko do debugowania)
SET log_queries = 1;
-- Wstaw jeden wiersz do tabeli źródłowej
INSERT INTO bets (created_at, sport_id, amount) VALUES (now(), 1, 100);
-- Znajdź zapytania związane z 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;Sprawdzenie danych w tabeli docelowej
-- Porównaj liczbę unikalnych grup w tabeli źródłowej i docelowej
-- (powinna być zgodna po wypełnieniu historii)
-- Źródło: ile unikalnych (godzina, sport)
SELECT count(*) FROM (
SELECT toStartOfHour(created_at) AS hour, sport_id
FROM bets
GROUP BY hour, sport_id
) AS src;
-- Cel:
SELECT count(*) FROM hourly_stats;Identyfikacja problemów z typami
Jeśli MV nie działa, często problemem jest niezgodność typów:
-- Błąd: Kolumna `sport_id` typu UInt8 w tabeli źródłowej, ale Int32 w docelowej MV
-- Rozwiązanie: jawnie rzutuj typy w SELECT
SELECT
toStartOfHour(created_at) AS hour,
toUInt8(sport_id) AS sport_id, -- jawne rzutowanie
count() AS bets_count
FROM bets
GROUP BY hour, sport_id;9. Rzeczywisty schemat dla platformy hazardowej
Zbudujemy pełną architekturę dla kasyna online z kilkoma wymaganiami:
- Szczegółowe zdarzenia do dochodzeń (przechowywać 7 dni).
- Zdeduplikowane zakłady do analityki (przechowywać 90 dni).
- Agregaty godzinowe do dashboardu (przechowywać 2 lata).
- Agregaty dzienne do raportów finansowych (przechowywać 10 lat).
-- ===== POZIOM 0: Odbiornik (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;
-- ===== TABELE DOCELOWE =====
-- 1. Archiwum na 7 dni (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. Zdeduplikowane zakłady (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. Agregaty godzinowe (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. Agregaty dzienne (AggregatingMergeTree dla 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;
-- ===== MATERIALIZOWANE WIDOKI =====
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;Przepływ danych:
- Aplikacja zapisuje do
raw_stream(Null) – milisekundy. - Cztery MV równolegle przetwarzają każde wstawienie.
- Każda tabela docelowa otrzymuje dane w swojej zagregowanej/przekształconej postaci.
- TTL automatycznie usuwa nieaktualne dane na każdym poziomie.
Zalety w porównaniu z pojedynczym MERGETREE:
- Maksymalna szybkość wstawiania (Null + MV działają w pamięci).
- Różne poziomy przechowywania (7 dni, 90 dni, 2 lata, 10 lat).
- Różne silniki dla różnych zadań (MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree).
- Brak utraty wydajności przy zapytaniach do starych agregatów.
Co dalej
Teraz umiesz budować złożone potoki przetwarzania danych za pomocą MV. Kolejne tematy:
- MV z aktualizacją źródeł zewnętrznych – jak łączyć słowniki i MV w celu wzbogacania danych.
- Debugowanie złożonych łańcuchów MV – jak zrozumieć, dlaczego dane nie trafiły do tabeli docelowej.
- Replikacja MV w klastrze – jak MV zachowują się na tabelach rozproszonych.
Podsumowanie: Materializowane widoki w ClickHouse to nie tylko pamięć podręczna. To potężny mechanizm przyrostowego przetwarzania danych, pozwalający budować prawdziwe architektury event-sourcing wewnątrz bazy danych. Zasada jest prosta: jeśli potrzebujesz agregować, przekształcać lub duplikować dane podczas wstawiania – użyj MV. I nigdy nie zapominaj o ręcznym wypełnianiu danych historycznych, zamiast używania POPULATE w produkcji.
← Poprzedni: Specjalne silniki ClickHouse: gdy MergeTree nie pasuje
→ Następny: Indeksy wtórne (skipping) w ClickHouse: gdy indeks po ORDER BY nie wystarcza
— Editorial Team
Brak komentarzy.