ClickHouse의 구체화된 뷰: 증분 처리의 힘
1. ClickHouse에서 MV가 작동하는 방식 — SELECT 캐시가 아닌 INSERT 트리거
익숙한 데이터베이스(PostgreSQL, Oracle)에서 구체화된 뷰(MV)는 디스크에 저장되고 일정(REFRESH)에 따라 업데이트되는 SELECT 결과입니다. 언제 다시 계산할지 결정합니다.
ClickHouse에서는 완전히 다릅니다. 여기서 MV는 INSERT 트리거입니다. SELECT를 기반으로 MV를 생성하고, 소스 테이블에 데이터를 삽입하면 ClickHouse가 자동으로 삽입된 행에 대해 해당 SELECT를 실행하고 결과를 대상 테이블에 삽입합니다.
주요 차이점:
Google AdInline article slot
- PostgreSQL: 수동 또는 일정에 따라 MV를 업데이트하며, 전체 소스 테이블을 읽습니다.
- ClickHouse: MV는 각 INSERT 시에만 실행되며, 새로운 행만 처리합니다.
실생활 비유: 일반 VIEW는 돋보기 안경과 같습니다. 이를 통해 소스 데이터를 보며 매번 결과를 다시 계산합니다. ClickHouse의 구체화된 뷰는 옆에 앉아 새 문서가 도착할 때마다 특별한 파일 캐비닛에 필요한 형식으로 복사해주는 비서와 같습니다. 매번 모든 문서를 뒤질 필요 없이 파일 캐비닛으로 가면 됩니다.
이것이 중요한 이유: ClickHouse에는 전통적인 의미의 트리거가 없습니다. MV는 데이터 삽입에 반응하는 유일한 내장 메커니즘입니다. 이를 통해 다음을 수행할 수 있습니다:
- 실시간으로 데이터 집계 (시간별 합계)
- 데이터 비정규화 (사전에서 스포츠 이름 조회)
- 동일한 원시 데이터를 여러 테이블(집계, 중복 제거, 전체 아카이브)에 분산
2. 간단한 MV: 시간별 베팅 합계
가장 일반적인 시나리오부터 시작하겠습니다: bets 테이블에 베팅 데이터가 있고, 스포츠별 시간별 통계가 필요합니다.
Google AdInline article slot
1단계: 대상 테이블 생성 (데이터가 집계될 곳)
CREATE TABLE hourly_sport_stats
(
hour DateTime, -- 시간 시작 (2025-06-01 14:00:00)
sport_id UInt8, -- 스포츠 종류
bets_count UInt64, -- 시간당 베팅 수
total_amount Decimal(18,2) -- 총 베팅 금액
)
ENGINE = SummingMergeTree() -- hour + sport_id로 합산
ORDER BY (hour, sport_id);2단계: 이 테이블을 채울 MV 생성
CREATE MATERIALIZED VIEW mv_hourly_stats TO hourly_sport_stats AS
SELECT
toStartOfHour(created_at) AS hour, -- 시간을 시간 시작으로 반올림
sport_id,
count() AS bets_count, -- 그룹 내 베팅 수
sum(amount) AS total_amount -- 그룹 내 베팅 합계
FROM bets
GROUP BY hour, sport_id;라인별 설명:
Google AdInline article slot
TO hourly_sport_stats— 결과를 삽입할 위치. MV 구조가 SELECT 구조와 일치하면 생략 가능하지만 명시적으로 지정하는 것이 좋습니다.AS SELECT ...—bets테이블의 각 삽입 배치에 대해 실행되는 쿼리. 전체 테이블이 아닌 새 행에 대해서만 실행됩니다.GROUP BY hour, sport_id— 배치 내 집계. 하나의 INSERT에 동일한 시간에 대한 1000개의 행이 있으면 MV는 해당 시간에 대해 하나의 요약 행을 계산합니다.
사용 방법:
-- 14:00에 100개, 15:00에 50개의 베팅 삽입
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),
... (147 more rows) ...;
-- MV가 자동으로 hourly_sport_stats에 행을 추가하거나 업데이트
-- 14:00, sport_id=1: bets_count가 삽입된 베팅 수만큼 증가
-- 15:00, sport_id=2: 동일
-- 이제 GROUP BY 없이 즉시 집계 결과 읽기!
SELECT * FROM hourly_sport_stats
WHERE hour = '2025-06-01 14:00:00';중요한 제한 사항: ClickHouse의 MV는 동일한 시간에 대해 반복 삽입 시 대상 테이블의 기존 행을 업데이트할 수 없습니다. 그러나 SummingMergeTree를 사용하므로 백그라운드 병합 중에 동일한 ORDER BY (hour, sport_id)를 가진 행이 합산됩니다. 따라서 SELECT * FROM hourly_sport_stats는 그룹당 여러 행을 반환할 수 있습니다 — SummingMergeTree와 마찬가지로 항상 GROUP BY와 함께 SUM으로 감싸야 합니다.
3. 복잡한 집계를 위한 AggregatingMergeTree를 사용한 MV
합계뿐만 아니라 고유 사용자(uniq), 평균 베팅(avg) 등이 필요한 경우 AggregatingMergeTree와 *State / *Merge 함수를 사용해야 합니다.
-- AggregateFunction 열이 있는 대상 테이블
CREATE TABLE hourly_sport_advanced
(
hour DateTime,
sport_id UInt8,
bets_count AggregateFunction(count, UInt64), -- count 상태
total_amount AggregateFunction(sum, Decimal(18,2)), -- sum 상태
unique_users AggregateFunction(uniq, UInt64), -- uniq 상태
avg_amount AggregateFunction(avg, Decimal(18,2)) -- avg 상태
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_id);
-- *State 함수를 사용한 MV
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, -- count가 아닌 countState
sumState(amount) AS total_amount,
uniqState(user_id) AS unique_users, -- 근사 uniq
avgState(amount) AS avg_amount
FROM bets
GROUP BY hour, sport_id;이렇게 하는 이유: AggregatingMergeTree는 최종 값이 아닌 중간 상태를 저장합니다. 이를 통해 서로 다른 삽입(예: 동일한 시간에 대한 두 배치)의 집계를 올바르게 병합할 수 있습니다. uniqState는 단순한 숫자가 아닌 고유 값의 해시 테이블을 저장합니다.
데이터 읽기:
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. 간단한 카운터를 위한 SummingMergeTree를 사용한 MV
간단한 합계와 카운터의 경우 SummingMergeTree가 AggregatingMergeTree보다 간단하고 빠릅니다. 섹션 2의 예제가 이에 이상적입니다.
또 다른 예제 — 사용자별 카운터:
-- 대상 테이블: 각 사용자가 하루에 만든 베팅 수
CREATE TABLE user_daily_counts
(
user_id UInt64,
day Date,
bets_count UInt64, -- 합산됨
total_amount Decimal(18,2) -- 합산됨
)
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;SummingMergeTree의 장점: 단순성, 읽을 때 *Merge 함수가 필요 없음. 안전을 위해 GROUP BY와 함께 간단한 SUM만 있으면 됩니다(데이터가 아직 병합되지 않은 경우).
단점: 합계와 카운터만 가능. 고유 사용자, 최대값, 평균은 불가.
5. MV 체인: 이벤트 → 분 집계 → 시간별 → 일별
점진적으로 세분성을 줄이는 강력한 패턴입니다. 원시 데이터에서 직접 일별 통계로 집계하는 대신(수십억 행을 다시 계산해야 함) 체인을 구축합니다.
스키마:
- 원시 베팅 (테이블
raw_bets) — 7일 저장. - 분 집계 (테이블
minute_stats) — 30일 저장. - 시간별 집계 (테이블
hourly_stats) — 365일 저장. - 일별 집계 (테이블
daily_stats) — 5년 저장.
-- 레벨 1: 원시 데이터에서 분 집계
CREATE TABLE minute_stats
(
minute DateTime, -- 분으로 반올림
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;
-- 레벨 2: 분에서 시간별 집계
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, -- 분을 시간으로 반올림
sport_id,
sum(bets_count) AS bets_count, -- 분 카운터 합산
sum(total_amount) AS total_amount
FROM minute_stats
GROUP BY hour, sport_id;
-- 레벨 3: 시간별에서 일별 집계
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;체인의 장점:
- 각 레벨은 더 적은 양의 데이터로 작업합니다.
- 하위 레벨에서 오래된 데이터를 삭제하고(TTL) 집계를 수년간 저장할 수 있습니다.
- 월별 보고서를 쿼리할 때
daily_stats(연간 365행)를 읽고raw_bets(수십억)는 읽지 않습니다.
비유: 책의 줄거리를 다시 말하는 것과 같습니다: 먼저 책을 읽고(원시 데이터), 장 요약(분), 부분 요약(시간별), 주석(일별)을 작성합니다. 주요 아이디어를 기억하기 위해 전체 책을 다시 읽을 필요가 없습니다.
6. Kafka를 위한 Null + MV 패턴: 여러 테이블로 데이터 분산
고부하 시스템을 위한 전문가 패턴입니다. 하나의 테이블에 쓰는 대신 Null 테이블(블랙홀)과 여러 MV를 생성하여 Null 테이블에서 읽고 다른 대상 테이블에 씁니다.
-- 1단계: 수신 테이블 (Null, 아무것도 저장하지 않음)
CREATE TABLE raw_events_null
(
user_id UInt64,
sport_id UInt8,
amount Decimal(18,2),
created_at DateTime,
ip String
)
ENGINE = Null;
-- 2단계: 대상 테이블 1 — 모든 원시 이벤트 (조사용)
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);
-- 3단계: 대상 테이블 2 — 중복 제거된 베팅 (GDPR을 위해 IP 없음)
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);
-- 4단계: 대상 테이블 3 — 시간별 집계
CREATE TABLE hourly_agg
(
hour DateTime,
sport_id UInt8,
total_amount AggregateFunction(sum, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_id);
-- 5단계: MV — 원시 아카이브 (모든 행)
CREATE MATERIALIZED VIEW mv_archive TO raw_events_archive AS
SELECT * FROM raw_events_null;
-- 6단계: MV — 중복 제거된 베팅 (즉시 bet_id 생성)
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;
-- 7단계: MV — 시간별 집계
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;실제 작동 방식:
-- Kafka 소비자가 raw_events_null에 삽입 (빠름, 디스크 작업 없음)
INSERT INTO raw_events_null VALUES (123, 1, 100.00, now(), '192.168.1.1');
-- 세 개의 MV가 병렬로 이 데이터를 수신하여 각자의 테이블에 씁니다:
-- 1. mv_archive → raw_events_archive (전체 복사본, IP 포함)
-- 2. mv_dedup → bets_dedup (중복 제거된 베팅, IP 없음)
-- 3. mv_hourly_agg → hourly_agg (집계 업데이트)이것이 훌륭한 이유:
- 하나의 삽입으로 세 가지 다른 변환.
- 소스 테이블은 아무것도 저장하지 않음(Null), 쓰기 오버헤드 없음.
- 각 MV는 독립적으로 활성화/비활성화 가능.
- 네 번째 MV(예: 다른 형식으로 내보내기)를 쉽게 추가 가능.
7. 문제: MV는 생성 후에만 새 데이터를 처리함
중요한 제한 사항: MV는 생성 시점에 소스 테이블에 이미 있던 데이터를 보지 못합니다. MV는 생성된 순간부터만 작동하며 새로운 INSERT만 처리합니다.
-- bets에 이미 10억 행이 있음
SELECT count() FROM bets; -- 1,000,000,000
-- MV 생성
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats AS ...;
-- 새 베팅 삽입
INSERT INTO bets VALUES (now(), 1, 100);
-- 이 새 베팅만 hourly_stats에 나타납니다.
-- 이전 10억 행은 처리되지 않은 상태로 남습니다!문제 해결 방법 — 과거 데이터 백필:
-- 방법 1: MV와 동일한 SELECT를 사용한 수동 INSERT
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' -- 오래된 데이터
GROUP BY hour, sport_id;
-- 이제 hourly_stats에는 오래된 데이터와 새 데이터가 모두 포함됩니다.방법 2: POPULATE로 MV 재생성 (위험!)
-- 위험: CREATE ... POPULATE를 사용하면 MV가 기존 테이블 전체를 처리합니다.
-- 처리 중 테이블이 잠기고 데이터가 손실될 수 있습니다.
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats POPULATE AS
SELECT ... FROM bets ...;프로덕션에서 POPULATE가 위험한 이유:
- SELECT 실행 중 소스 테이블이 잠길 수 있습니다.
- POPULATE 중에 소스 테이블에 새 데이터가 삽입되면 손실될 수 있습니다(이전 MV나 새 MV 모두 캡처하지 않음).
- 대규모 테이블에서 POPULATE는 몇 시간이 걸릴 수 있습니다.
올바른 접근 방식: POPULATE 없이 MV를 생성하고, MV와 동일한 SELECT를 사용하여 수동 INSERT로 과거 데이터를 백필합니다. 이는 안전하고 예측 가능하며 삽입을 차단하지 않습니다.
8. MV 모니터링 및 디버깅
MV가 활성 상태인지 확인
-- 모든 MV와 대상 테이블 나열
SELECT
name,
engine,
total_rows,
formatReadableSize(total_bytes) AS size
FROM system.tables
WHERE engine = 'MaterializedView'
AND database = 'default';INSERT 시 MV가 실행되는지 확인
-- 상세 로그 활성화 (디버그 전용)
SET log_queries = 1;
-- 소스 테이블에 한 행 삽입
INSERT INTO bets (created_at, sport_id, amount) VALUES (now(), 1, 100);
-- 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;대상 테이블의 데이터 확인
-- 소스와 대상 테이블의 고유 그룹 수 비교
-- (백필 후 일치해야 함)
-- 소스: 고유 (hour, sport) 수
SELECT count(*) FROM (
SELECT toStartOfHour(created_at) AS hour, sport_id
FROM bets
GROUP BY hour, sport_id
) AS src;
-- 대상:
SELECT count(*) FROM hourly_stats;TYPE 문제 식별
MV가 실행되지 않는 경우, 종종 유형 불일치가 문제입니다:
-- 오류: 소스 테이블의 `sport_id` 유형은 UInt8이지만 MV 대상에서는 Int32입니다.
-- 해결 방법: SELECT에서 유형을 명시적으로 변환
SELECT
toStartOfHour(created_at) AS hour,
toUInt8(sport_id) AS sport_id, -- 명시적 변환
count() AS bets_count
FROM bets
GROUP BY hour, sport_id;9. 도박 플랫폼을 위한 실제 스키마
여러 요구 사항이 있는 온라인 카지노의 완전한 아키텍처를 조립해 보겠습니다:
- 조사용 상세 이벤트 (7일 저장).
- 분석용 중복 제거된 베팅 (90일 저장).
- 대시보드용 시간별 집계 (2년 저장).
- 재무 보고서용 일별 집계 (10년 저장).
-- ===== 레벨 0: 수신기 (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;
-- ===== 대상 테이블 =====
-- 1. 7일 아카이브 (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. 중복 제거된 베팅 (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. 시간별 집계 (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. 일별 집계 (uniq를 위한 AggregatingMergeTree)
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;
-- ===== 구체화된 뷰 =====
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;데이터 흐름:
- 애플리케이션이
raw_stream(Null)에 씁니다 — 밀리초. - 4개의 MV가 각 삽입을 병렬로 처리합니다.
- 각 대상 테이블은 집계/변환된 형태로 데이터를 수신합니다.
- TTL이 각 레벨에서 오래된 데이터를 자동으로 삭제합니다.
단일 MERGETREE에 비해 장점:
- 최대 삽입 속도 (Null + MV가 메모리에서 작동).
- 다양한 저장 수준 (7일, 90일, 2년, 10년).
- 다양한 작업에 다른 엔진 사용 (MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree).
- 오래된 집계 쿼리 시 성능 저하 없음.
다음 단계
이제 MV를 사용하여 복잡한 데이터 처리 파이프라인을 구축하는 방법을 알게 되었습니다. 다음 주제:
- 외부 소스 업데이트가 있는 MV — 딕셔너리와 MV를 결합하여 데이터 보강하는 방법.
- 복잡한 MV 체인 디버깅 — 데이터가 대상 테이블에 도달하지 못한 이유를 이해하는 방법.
- 클러스터에서 MV 복제 — 분산 테이블에서 MV가 작동하는 방식.
요약: ClickHouse의 구체화된 뷰는 단순한 캐시가 아닙니다. 데이터베이스 내에서 진정한 이벤트 소싱 아키텍처를 구축할 수 있는 강력한 증분 데이터 처리 메커니즘입니다. 규칙은 간단합니다: 삽입 시 데이터를 집계, 변환 또는 복제해야 하는 경우 MV를 사용하십시오. 그리고 프로덕션에서 POPULATE를 사용하지 말고 항상 수동으로 과거 데이터를 백필하는 것을 잊지 마십시오.
← 이전 글: 특별한 ClickHouse 엔진: MergeTree가 적합하지 않을 때
→ 다음 글: ClickHouse의 보조(스킵) 인덱스: ORDER BY 인덱스만으로 부족할 때
— Editorial Team
아직 댓글이 없습니다.