SummingMergeTree와 AggregatingMergeTree: 고통 없는 증분 집계
1. 집계 엔진이 필요한 이유 — 대규모 데이터 계산 문제
온라인 카지노로 돌아가 봅시다. 매일 플레이어들은 수백만 건의 베팅을 합니다. 대시보드 소유자는 각 사용자가 하루에 몇 번의 베팅을 했고 총 얼마를 베팅했는지 확인해야 합니다.
일반 데이터베이스(PostgreSQL, MySQL)에서는 다음과 같이 작성합니다:
SELECT user_id, date, COUNT(*), SUM(amount)
FROM bets
GROUP BY user_id, date1억 개의 행이 있는 테이블에서 이러한 쿼리는... 음, 아시겠죠 — 오래 걸립니다. 매우 오래 걸립니다. 데이터베이스가 모든 행을 읽고, 정렬 또는 해싱한 다음 집계를 계산해야 하기 때문입니다.
ClickHouse가 더 빠르긴 하지만, 마법은 아닙니다. 데이터가 많을수록 GROUP BY는 더 오래 걸립니다. 그리고 보고서가 많고 "지금 당장" 필요하다면 성능이 문제가 됩니다.
아이디어: 집계를 미리 계산해서 저장하면 어떨까요? 그러면 "user_id=123이 어제 몇 번 베팅했는지" 같은 쿼리는 전체 집계가 아닌 단순히 한 행을 SELECT하는 것일 뿐입니다.
이를 위해 ClickHouse는 두 가지 특수 테이블 엔진인 SummingMergeTree와 AggregatingMergeTree를 제공합니다. 이 엔진들은 백그라운드에서 데이터 파트 병합 중에 무거운 작업을 대신 수행합니다.
실생활 비유: 가게에서 판매를 추적한다고 상상해 보세요. 각 판매는 영수증입니다. 주인이 "오늘 얼마나 팔았나요?"라는 보고서를 요청하면 매번 모든 영수증을 뒤질 수 있습니다. 또는 하루가 끝날 때 "오늘 150건 판매, 5000루블"이라고 총계를 적는 노트를 유지할 수도 있습니다. SummingMergeTree는 이 노트를 자동으로 유지하는 것과 같습니다.
2. SummingMergeTree — 자동 합산기
작동 방식
SummingMergeTree는 데이터 파트 병합(백그라운드) 중에 동일한 정렬 키(ORDER BY)를 가진 행들의 숫자 값을 합산하는 엔진입니다.
합산 규칙:
- 모든 숫자 열(
UInt*,Int*,Float*,Decimal*타입)이 자동으로 합산됩니다. - 다른 열(문자열, 날짜, 배열)은 처음 발견된 행에서 가져옵니다 — 이 점을 기억하는 것이 중요하며, 예상과 다를 수 있습니다.
- 열이 숫자가 아니지만 어떻게든 집계하려면
SummingMergeTree는 적합하지 않으며AggregatingMergeTree가 필요합니다.
"Summing"인 이유: 동일한 키를 가진 여러 행이 하나로 축소되고, 그 안의 숫자는 원래 행들의 숫자 합계입니다.
CREATE TABLE — 자세히 살펴보기
-- 사용자별 일일 통계 테이블 생성
CREATE TABLE daily_stats
(
date Date, -- 통계 일자
user_id UInt64, -- 플레이어 ID
bets_count UInt64, -- 일일 베팅 수 (합산됨)
total_amount Decimal(18,2) -- 총 베팅 금액 (합산됨)
)
ENGINE = SummingMergeTree() -- 자동 합산 엔진
ORDER BY (date, user_id) -- 그룹화 키: 날짜와 사용자별여기서 중요한 점:
ENGINE = SummingMergeTree()— 괄호 안에 합산할 열을 지정할 수 있습니다:SummingMergeTree(bets_count, total_amount). 지정하지 않으면 모든 숫자 열이 합산됩니다(ORDER BY의 열은 제외, 해당 값은 고유성을 결정합니다).ORDER BY (date, user_id)— 이 열들은 어떤 행이 하나로 병합될지 결정합니다. 즉, 동일한 날짜와 동일한user_id를 가진 모든 행은 병합 중에 하나의 행으로 축소되며, 여기서bets_count와total_amount는 합계입니다.
ORDER BY가 너무 넓으면 어떻게 되나요? 예를 들어 bets_count를 포함하면 각 고유한 베팅(다른 카운트를 가진)은 별도의 행으로 남습니다. 키가 다르기 때문에 합산이 발생하지 않습니다. 함정 #1 (나중에 다시 다룹니다).
데이터 삽입 방법
원시 이벤트 삽입 (각 행은 하나의 베팅):
-- 2025-06-01에 사용자 123의 세 번 베팅 삽입
INSERT INTO daily_stats VALUES
('2025-06-01', 123, 1, 100.00), -- 100루블 베팅 1회
('2025-06-01', 123, 1, 250.00), -- 250루블 베팅 2회
('2025-06-01', 456, 1, 50.00); -- 다른 사용자
-- 집계되지 않은 데이터를 삽입할 수 있습니다 — 엔진이 처리합니다백그라운드 병합 후(수초에서 수시간 소요 가능), date='2025-06-01'이고 user_id=123인 행들은 하나로 병합됩니다: ('2025-06-01', 123, 2, 350.00).
GROUP BY 없이 읽기 — 마법과 한계
아이디어는 병합이 발생한 후 집계 없이 데이터를 읽을 수 있다는 것입니다:
-- 병합이 이미 발생했다면, 이 쿼리는 사용자별 하루에 한 행을 반환합니다
SELECT date, user_id, bets_count, total_amount
FROM daily_stats
WHERE date = '2025-06-01';하지만 문제가 있습니다. 병합 사이에 데이터는 중복 키를 가진 다른 파트에 있을 수 있습니다. 따라서 실제로는 여전히 SUM과 함께 작성합니다:
SELECT date, user_id, SUM(bets_count), SUM(total_amount)
FROM daily_stats
WHERE date = '2025-06-01'
GROUP BY date, user_id;왜 작동하나요? 데이터가 병합되지 않았더라도 SUM이 모든 것을 올바르게 더하기 때문입니다. 병합되었다면 그룹당 하나의 행이 있고 SUM은 단순히 그 값을 반환합니다. 쿼리는 여전히 데이터를 읽지만, 이제는 더 적은 양(원시 행 대신 집계된 행)을 읽습니다.
비유: SummingMergeTree는 동일한 영수증을 미리 붙여주는 조수와 같습니다. 하지만 여전히 "각 날짜의 총계를 보여줘"라고 요청합니다. 영수증이 이미 붙어 있으면 총계는 한 행의 숫자와 일치합니다. 그렇지 않아도 올바른 총계를 얻습니다. 중요한 것은 수백만 개의 영수증이 아니라 수천 개의 요약을 읽는다는 점입니다.
3. 문제: 병합 사이에 SELECT에서 SUM이 필요함
이것은 종종 오해되는 핵심 포인트입니다.
순진한 접근 방식 (잘못됨):
-- 병합 후 데이터가 이미 집계되었다고 생각하여 초보자가 작성:
SELECT * FROM daily_stats WHERE date = '2025-06-01';
-- 그리고 한 사용자에 대해 여러 행을 얻음 (병합되지 않은 경우)올바른 접근 방식 (안전):
SELECT date, user_id, SUM(bets_count), SUM(total_amount)
FROM daily_stats
GROUP BY date, user_id;왜?
- 병합 전후 모두 항상 올바른 결과를 얻습니다.
- 데이터 크기는 여전히 원시 베팅 테이블보다 작습니다.
- ClickHouse는 이러한 쿼리를 잘 최적화합니다.
SUM을 생략할 수 있는 경우는? 필요한 데이터가 이미 병합되었음을 절대적으로 확신하는 경우에만 가능합니다. 예를 들어, 강제 OPTIMIZE TABLE daily_stats 후 (하지만 이는 비용이 많이 드는 작업이므로 매번 하지 마십시오).
4. AggregatingMergeTree — 단순 합산으로 충분하지 않을 때
SummingMergeTree는 숫자만 더할 수 있습니다. 하지만 다음이 필요하다면?
- 고유 사용자 수 (합계가 아님)?
- 최대 또는 최소 찾기?
- 평균 계산?
- 고유 값 계산을 위한
uniq와 같은 근사 알고리즘 사용?
이를 위해 AggregatingMergeTree가 있습니다. 이 엔진은 값 자체가 아니라 집계 함수의 상태 — 나중에 최종 결과를 얻을 수 있는 특수 중간 데이터를 저장합니다.
비유: SummingMergeTree는 최종 합계만 저장합니다. 하지만 AggregatingMergeTree는 합계뿐만 아니라 카운터(나중에 평균을 계산하기 위해) 또는 고유 값의 해시 테이블(나중에 몇 개인지 알려주기 위해)도 저장합니다. 이는 "총계가 있습니다"와 "모든 데이터가 압축된 형태로 기록된 노트가 있습니다"의 차이와 같습니다.
AggregateFunction을 사용한 CREATE TABLE
-- 집계된 대시보드 통계 테이블 생성
CREATE TABLE dashboard_hourly
(
event_hour DateTime, -- 이벤트 시간
sport_type String, -- 스포츠 종목 (축구, 농구...)
total_bets AggregateFunction(sum, UInt64), -- 베팅 수 합계
total_amount AggregateFunction(sum, Decimal(18,2)), -- 금액 합계
unique_users AggregateFunction(uniq, UInt64), -- 고유 플레이어 (근사)
avg_bet_amount AggregateFunction(avg, Decimal(18,2)), -- 평균 베팅 크기
max_bet AggregateFunction(max, Decimal(18,2)) -- 최대 베팅
)
ENGINE = AggregatingMergeTree()
ORDER BY (event_hour, sport_type);낯선 부분 설명:
AggregateFunction(sum, UInt64)—UInt64타입 데이터에 대한 집계 함수sum의 상태를 저장하는 열 타입입니다. 숫자가 아니라 ClickHouse 내부 구조입니다.- 왜 단순히
UInt64가 아닌가? 일부 함수(uniq,avg)의 경우 최종 결과보다 더 많은 데이터를 저장해야 하기 때문입니다.avg는 합계와 개수를 모두 저장합니다.uniq는 해시 테이블을 저장합니다. - 동일한
ORDER BY(동일한 시간과 스포츠 종목)를 가진 두 행이 병합될 때 — 집계 함수의 상태가 결합됩니다.sum의 경우 중간 합계를 더하는 것입니다.uniq의 경우 두 고유 값 해시 테이블을 병합하는 것입니다.
State 함수를 사용한 INSERT SELECT로 데이터 삽입
AggregateFunction 열에는 일반 값을 삽입할 수 없습니다. 원시 값에서 상태를 생성하는 특수 *State 함수를 사용해야 합니다.
-- 원시 베팅 테이블에서 집계된 데이터 삽입
INSERT INTO dashboard_hourly
SELECT
toStartOfHour(event_time) AS event_hour, -- 시간을 시간 단위로 반올림
sport_type,
sumState(bets_count) AS total_bets, -- sum의 상태
sumState(amount) AS total_amount, -- 금액 합계의 상태
uniqState(user_id) AS unique_users, -- 고유를 위한 상태
avgState(amount) AS avg_bet_amount, -- 평균을 위한 상태
maxState(amount) AS max_bet -- 최대를 위한 상태
FROM raw_bets
WHERE event_time >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type;여기서 일어나는 일:
toStartOfHour(event_time)— 시간을 시간 단위로 자르는 ClickHouse 함수:2025-06-01 12:34:56→2025-06-01 12:00:00.sumState(amount)—SUM(amount)대신sumState(amount)를 작성합니다. 결과는 집계 함수의 상태이며, 타입은AggregateFunction(sum, ...)입니다.- 삽입 쿼리에서
GROUP BY는 필수입니다! 원시 테이블의 데이터를 그룹(시간+스포츠)으로 집계한 다음 각 그룹을dashboard_hourly에 하나의 행으로 삽입하기 때문입니다.
Merge 함수를 통한 읽기
데이터를 읽으려면 *Merge 함수를 사용합니다:
SELECT
event_hour,
sport_type,
sumMerge(total_bets) AS total_bets, -- 상태 → 숫자
sumMerge(total_amount) AS total_amount,
uniqMerge(unique_users) AS unique_users, -- 고유 사용자
avgMerge(avg_bet_amount) AS avg_bet_amount,
maxMerge(max_bet) AS max_bet
FROM dashboard_hourly
WHERE event_hour >= '2025-06-01 00:00:00'
GROUP BY event_hour, sport_type; -- 여전히 그룹화 필요 (데이터가 병합되지 않은 경우)왜 다시 GROUP BY인가? SummingMergeTree와 같은 이유입니다: 병합 사이에 동일한 ORDER BY를 가진 여러 행이 있을 수 있습니다. *Merge와 함께 GROUP BY를 사용하면 어떤 상태에서든 올바른 결과를 얻을 수 있습니다.
5. 구체화된 뷰(Materialized View)와의 사용 패턴
AggregatingMergeTree를 가장 강력하게 사용하는 방법은 구체화된 뷰와 결합하는 것입니다. 원시 데이터를 일반 테이블에 삽입하면 뷰가 자동으로 집계하여 집계 테이블에 저장합니다.
비유: 컨베이어 벨트를 설정하는 것과 같습니다: 원시 영수증은 한 상자에 들어가고, 자동 분류기가 매분 일일 총계를 수집하여 다른 상자에 넣습니다. 분석가는 두 번째 상자만 봅니다 — 빠르고 즉석에서 GROUP BY할 필요 없습니다.
전체 예제: 운영자 대시보드를 위한 시간별 통계
1단계: 원시 테이블 — 이벤트(베팅)가 여기에 삽입됩니다
CREATE TABLE raw_bets
(
event_time DateTime,
sport_type String,
user_id UInt64,
amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY event_time;2단계: 집계 테이블 — 준비된 통계가 여기에 저장됩니다
CREATE TABLE bets_hourly_agg
(
hour DateTime,
sport_type String,
total_bets AggregateFunction(sum, UInt64),
total_amount AggregateFunction(sum, Decimal(18,2)),
unique_users AggregateFunction(uniq, UInt64),
avg_bet AggregateFunction(avg, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_type);3단계: 구체화된 뷰 — 둘 사이의 다리
CREATE MATERIALIZED VIEW bets_mv TO bets_hourly_agg AS
SELECT
toStartOfHour(event_time) AS hour,
sport_type,
sumState(1) AS total_bets, -- 각 행은 하나의 베팅
sumState(amount) AS total_amount,
uniqState(user_id) AS unique_users,
avgState(amount) AS avg_bet
FROM raw_bets
GROUP BY hour, sport_type;이제 어떻게 되나요?
- 일반
INSERT로raw_bets에 행을 삽입합니다. - ClickHouse가 자동으로 (거의 즉시) 구체화된 뷰를 통해 실행합니다.
- 뷰는 삽입된 배치에서만 데이터를 집계하고 결과를
bets_hourly_agg에 삽입합니다. bets_hourly_agg에서는 동일한(hour, sport_type)을 가진 여러 행이 일시적으로 누적될 수 있지만, 백그라운드 병합 중에 병합됩니다.
대시보드 읽기:
SELECT
hour,
sport_type,
sumMerge(total_bets) AS total_bets,
sumMerge(total_amount) AS total_amount,
uniqMerge(unique_users) AS unique_users,
avgMerge(avg_bet) AS avg_bet
FROM bets_hourly_agg
WHERE hour >= today() - 7
GROUP BY hour, sport_type;이 쿼리는 집계된 데이터만 읽으며, 원시 베팅보다 수천 배 적은 공간을 차지합니다.
6. 실제 예제: 스포츠 이벤트 대시보드
당신이 북메이커 운영자라고 상상해 보세요. 대시보드에 다음을 표시해야 합니다:
- 각 경기(축구, 챔피언스리그, "레알" 대 "바이에른")에 대해
- 지난 5분 동안 얼마나 많은 베팅이 이루어졌는지
- 모든 베팅의 총 금액
- 고유 플레이어 수
- 평균 베팅
원시 데이터: 초당 5000건의 베팅. 모든 것을 저장하고 매번 처음부터 집계하는 것은 미친 짓입니다.
해결책:
-- 5분 간격으로 경기별 집계 테이블
CREATE TABLE match_stats_5min
(
match_id String,
interval_5min DateTime,
total_bets AggregateFunction(sum, UInt64),
total_amount AggregateFunction(sum, Decimal(18,2)),
unique_users AggregateFunction(uniq, UInt64),
max_bet AggregateFunction(max, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (match_id, interval_5min);
-- 구체화된 뷰
CREATE MATERIALIZED VIEW match_stats_mv TO match_stats_5min AS
SELECT
match_id,
toStartOfFiveMinute(event_time) AS interval_5min,
sumState(1) AS total_bets,
sumState(amount) AS total_amount,
uniqState(user_id) AS unique_users,
maxState(amount) AS max_bet
FROM raw_bets
GROUP BY match_id, interval_5min;이제 대시보드는 match_stats_5min을 쿼리하여 초 대신 밀리초 단위로 응답을 받습니다.
7. SummingMergeTree와 AggregatingMergeTree를 사용하지 말아야 할 때
SummingMergeTree가 적합한 경우:
- 숫자 값만 합산하면 됩니다.
- 병합 사이에
SUM과 함께GROUP BY를 사용해도 괜찮습니다. - 그룹화 키의 카디널리티가 너무 높지 않습니다(예: 수십억 고유 사용자는 괜찮지만 데이터가 더 많아집니다).
SummingMergeTree가 적합하지 않은 경우:
- 고유 사용자 수(
uniq,count(DISTINCT))를 계산해야 합니다 —AggregatingMergeTree만 작동합니다. - 다른 집계(
avg,min,max)가 필요합니다 — 역시AggregatingMergeTree만 가능합니다. - 데이터가 항상 이미 집계된 상태일 것으로 예상합니다 — 그렇게 작동하지 않습니다.
- 데이터가 업데이트됩니다(삽입만이 아님) — 이 엔진들은 업데이트 의미론에 적합하지 않습니다.
AggregatingMergeTree가 적합한 경우:
- 다양한 유형의 집계(합계, 고유, 평균, 최대)가 필요합니다.
*State로INSERT하고*Merge로SELECT하는 것을 기꺼이 할 의향이 있습니다.- 자동 집계를 위해 구체화된 뷰를 사용합니다.
- 원시 데이터의 양이 방대하고 집계가 훨씬 작습니다.
AggregatingMergeTree가 적합하지 않은 경우:
- 팀에
AggregateFunction이 무엇이고 어떻게 작업하는지 설명할 준비가 되지 않았습니다. 학습 곡선이 더 높습니다. - 정확한 고유성이 필요하고 근사가 허용되지 않습니다(
uniq는 확률적 구조, 오류 ~2%). 정확한 경우groupBitmap을 사용하거나 다른 시스템에서 계산하십시오. - 데이터 양이 적습니다(수백만 행) — 일반
GROUP BY가 더 간단합니다. - 집계 스키마를 자주 변경합니다(새 메트릭 추가) — 구체화된 뷰를 다시 만드는 것이 번거롭습니다.
8. 일반 MergeTree + GROUP BY와 비교
| 특성 | MergeTree + GROUP BY | SummingMergeTree | AggregatingMergeTree |
|---|---|---|---|
| 삽입 속도 | 최대 | 높음 | 중간 (상태로 인해) |
| 읽기 속도 (넓은 범위) | 낮음 (모든 것 읽음) | 높음 (집계 읽음) | 높음 |
| 읽기 속도 (포인트) | 중간 | 높음 | 높음 |
| 저장 공간 | 최대 | 최소 (집계) | 약간 더 많음 (상태) |
| 코드 복잡성 | 낮음 | 낮음 (테이블만) | 높음 (*State, *Merge) |
| 집계 유연성 | 모든 것 | 합계만 | 모든 것 (AggregateFunction 통해) |
9. 일반적인 함정
함정 #1: ORDER BY에 충분한 필드가 포함되지 않음
-- 나쁨: user_id 없이 date만
CREATE TABLE bad_agg ENGINE = SummingMergeTree ORDER BY date;
-- 병합 중에 동일한 날짜의 모든 행이 하나로 축소됨
-- 사용자 수준 세부 정보를 잃음올바름: 집계하려는 모든 필드를 ORDER BY에 포함하십시오.
함정 #2: SELECT에서 GROUP BY를 잊음
-- 나쁨: 데이터가 병합되지 않았더라도 GROUP BY 없음
SELECT date, SUM(bets_count) FROM daily_stats WHERE date = '2025-06-01';
-- 동일한 날짜지만 다른 user_id를 가진 두 행이 있으면 오류 발생
-- ClickHouse가 어떤 user_id를 표시할지 모름올바름: 항상 ORDER BY와 동일한 필드로 그룹화하십시오.
함정 #3: 비확률적 uniq
ClickHouse의 uniq는 확률적 함수입니다. 오류 ~2-3%. 정확한 고유성이 필요하면 uniqExact 또는 groupBitmap을 사용하십시오.
함정 #4: 오래된 데이터 업데이트
SummingMergeTree와 AggregatingMergeTree는 업데이트를 좋아하지 않습니다. 어제의 베팅을 수정해야 한다면 반대 부호로 새 행을 삽입하는 것이 더 쉽습니다(CollapsingMergeTree 통해).
10. 다음 단계
이제 집계 엔진을 마스터했으므로 다음 주제를 살펴보십시오:
- 작업에 적합한 엔진을 선택하는 방법 — 모든
*MergeTree엔진 비교. - 구체화된 뷰 자세히 알아보기 — 디버깅 방법, 스키마 업데이트 방법.
- 백그라운드 병합 튜닝 — 집계가 더 빠르게 축소되도록.
결론: SummingMergeTree와 AggregatingMergeTree는 테라바이트 단위의 데이터에서 대시보드가 지연되는 것을 원하지 않는 사람들을 위한 도구입니다. 초기에 약간 더 많은 이해가 필요하지만 실제 워크로드에서 여러 배로 보상받습니다. 주요 규칙: 읽을 때 항상 GROUP BY와 집계 함수를 사용하십시오 — 그러면 병합 전후로 안전합니다.
← 이전 글: ReplacingMergeTree: ClickHouse에서 중복을 고통 없이 제거하는 방법
→ 다음 글: CollapsingMergeTree: ClickHouse에서 UPDATE 없이 집계를 업데이트하는 방법
— Editorial Team
아직 댓글이 없습니다.