Insert Summaries in Batches and Match the Raw Data with Sums and States

목표

원본 이벤트에서 (site, day) 요약을 SummingMergeTree 와 AggregatingMergeTree 에 나눠 넣고, 병합 전에도 원본과 똑같은 답을 내는 조회를 씁니다. 합으로 줄일 수 없는 값은 상태로 담아야 한다는 것과 그 상태를 더 큰 단위로 다시 합치는 법을 확인합니다.

왜 중요한가

요약 표는 대시보드를 싸게 만들지만, 병합이 끝나기 전에는 같은 키가 여러 행으로 흩어져 있습니다. 그 상태에서 SELECT * 로 읽거나, 서로 다른 사용자 수를 숫자로 저장해 더하면 틀린 답이 조용히 나옵니다. 이 실습은 병합을 멈춰 그 틀린 상태를 고정하고, 채점기가 여러분의 쿼리 결과를 원본 agg.hits 를 직접 집계한 값과 칸마다 대조합니다. 몇 번에 나눠 넣었는지는 system.part_log 기록으로 확인합니다.

단계

1. 데이터베이스 agg 와 표 agg.hits 를 만드세요 — 열 ts DateTime, site LowCardinality(String), user_id UInt64, dur_ms UInt32, is_bot UInt8(이 순서), 엔진 MergeTree, ORDER BY (site, ts).
2. /opt/lab/fixtures/aggregating/hits.sql 을 한 번 실행해 100만 행을 넣으세요.
3. agg.daily (site LowCardinality(String), day Date, hits UInt64, dur_total UInt64) 를 ENGINE = SummingMergeTree ORDER BY (site, day) 로 만들고 SYSTEM STOP MERGES agg.daily 로 병합을 멈춘 뒤, agg.hits 를 시간대별(toHour(ts) < 8, 8 이상 16 미만, 16 이상)로 INSERT 세 번에 나눠 site, toDate(ts), count(), sum(dur_ms) 를 넣으세요.
4. 병합 전인 지금도 원본과 같은 (site, day)별 조회수·체류 합을 내는 /root/ch/aggregating/q_daily.sql 을 만드세요(agg.daily 만 읽고, 열은 site, day, hits, dur_total 순서). 그리고 agg.daily 의 지금 행 수와 서로 다른 (site, day) 수를 /root/ch/aggregating/raw.json 에 raw_rows·keys 로 적으세요.
5. SYSTEM START MERGES agg.daily 뒤 OPTIMIZE TABLE agg.daily FINAL 로 합쳐, 키마다 한 행이 되게 하세요.
6. agg.daily_state (site LowCardinality(String), day Date, users AggregateFunction(uniqExact, UInt64), avg_dur AggregateFunction(avg, UInt32), human_hits AggregateFunction(countIf, UInt8)) 를 ENGINE = AggregatingMergeTree ORDER BY (site, day) 로 만들고 병합을 멈춘 뒤, 3단계와 같은 시간대 세 묶음으로 uniqExactState(user_id)·avgState(dur_ms)·countIfState(is_bot = 0) 을 넣으세요.
7. agg.daily_state 만 읽어 (site, day)별 users·avg_dur·human_hits 를 내는 /root/ch/aggregating/q_state.sql 을 만드세요(열은 site, day, users, avg_dur, human_hits 순서). 그리고 병합 전 각 행의 finalizeAggregation(users) 를 그냥 더한 값과 q_state.sql 의 users 를 모두 더한 값을 /root/ch/aggregating/naive.json 에 naive_users_sum·true_users_sum 으로 적으세요.
8. agg.day_total (day Date, users AggregateFunction(uniqExact, UInt64)) 를 ENGINE = AggregatingMergeTree ORDER BY day 로 만들어 agg.daily_state 의 상태를 uniqExactMergeState(users) 로 날짜별로 다시 합쳐 넣고, agg.day_total 만 읽어 날짜별 (사이트 구분 없는) 서로 다른 사용자 수를 내는 /root/ch/aggregating/q_day.sql 을 만드세요(열은 day, users).

참고

• 서버는 파드가 뜰 때 이미 떠 있습니다. clickhouse-client 만 치면 붙습니다. 멈췄다면 ch-up(서버를 다시 띄우면 STOP MERGES 가 풀립니다).
• INSERT 기록은 system.part_log(event_type = 'NewPart')에 남습니다. 채점기는 표의 UUID 로 거릅니다. 다시 하려면 TRUNCATE TABLE 뒤 세 번 넣으면 됩니다.
• 상태 열은 사람이 읽는 값이 아닙니다. 확인할 때는 finalizeAggregation(열) 이나 -Merge 함수로 끝내서 봅니다.
• 흔한 실수: 한 INSERT 로 원본 전체를 넣는 것 — 같은 키가 넣는 순간 합쳐져 "병합 전" 을 볼 수 없습니다. 조회에서 sum(uniqExactMerge(...)) 처럼 끝낸 값을 다시 더하는 것 — 겹치는 사람을 여러 번 셉니다.
• 공식 문서: SummingMergeTree · AggregatingMergeTree · Aggregate Function Combinators · system.part_log

원본 이벤트 표

데이터베이스 agg 와 표 agg.hits 를 만드세요. 열은 ts DateTime, site LowCardinality(String), user_id UInt64, dur_ms UInt32, is_bot UInt8 순서, 엔진은 MergeTree, 정렬 키는 ORDER BY (site, ts) 입니다.

요약 표는 늘 원본에서 만들어집니다. 원본을 MergeTree 로 그대로 두면 요약을 잘못 만들어도 다시 만들 수 있습니다 — 문서가 SummingMergeTree 를 MergeTree 와 함께 쓰라고 권하는 이유입니다.

원본 100만 행

/opt/lab/fixtures/aggregating/hits.sql 을 한 번 실행해 agg.hits 에 1,000,000행을 넣으세요.

clickhouse-client 에 --queries-file 로 넘기면 됩니다. 사이트 5개 × 2026년 9월 30일이라 (site, day) 조합은 150개입니다. 두 번 넣었다면 TRUNCATE 뒤 다시 넣으세요.

SummingMergeTree 에 세 번 나눠 넣는다

agg.daily (site LowCardinality(String), day Date, hits UInt64, dur_total UInt64) 를 ENGINE = SummingMergeTree ORDER BY (site, day) 로 만들고 SYSTEM STOP MERGES agg.daily 로 병합을 멈춘 뒤, agg.hits 를 시간대별(toHour(ts) < 8 · 8 이상 16 미만 · 16 이상)로 INSERT 세 번에 나눠 site, toDate(ts), count(), sum(dur_ms) 를 (site, day) 로 묶어 넣으세요.

하루치 요약이 세 번에 나눠 도착하는 상황입니다. 각 INSERT 는 그 시간대만 GROUP BY 해서 넣으므로, 병합 전에는 같은 (site, day) 가 세 행으로 있습니다. 병합을 멈추는 것은 넣기 전에 해야 합니다.

병합 전에도 맞는 조회

agg.daily 만 읽어 (site, day)별 조회수·체류 합을 원본과 똑같이 내는 /root/ch/aggregating/q_daily.sql 을 만드세요. 열은 site, day, hits, dur_total 순서입니다. 그리고 agg.daily 의 지금 행 수와 서로 다른 (site, day) 수를 /root/ch/aggregating/raw.json 에 raw_rows·keys 로 적으세요.

병합이 끝났는지 알 수 없으니 조회에서 한 번 더 묶어 더합니다. SELECT * 로 읽으면 지금은 키마다 세 행이 나옵니다. 원본 agg.hits 를 읽으면 안 됩니다 — 요약 표만으로 답을 내는 것이 목적입니다.

병합하면 키마다 한 행

SYSTEM START MERGES agg.daily 뒤 OPTIMIZE TABLE agg.daily FINAL 로 합쳐, agg.daily 가 파트 하나·키마다 한 행이 되게 하세요.

병합이 같은 키의 숫자 열을 더해 한 행으로 접습니다. 합친 뒤 SELECT * 가 원본 집계와 같아지는지 보세요. 운영에서는 병합 시점을 모르므로 4단계 같은 조회가 여전히 필요합니다.

합으로 줄일 수 없는 값은 상태로

agg.daily_state (site LowCardinality(String), day Date, users AggregateFunction(uniqExact, UInt64), avg_dur AggregateFunction(avg, UInt32), human_hits AggregateFunction(countIf, UInt8)) 를 ENGINE = AggregatingMergeTree ORDER BY (site, day) 로 만들고 SYSTEM STOP MERGES agg.daily_state 뒤, 3단계와 같은 시간대 세 묶음으로 uniqExactState(user_id)·avgState(dur_ms)·countIfState(is_bot = 0) 을 (site, day) 로 묶어 넣으세요.

열 타입 AggregateFunction(함수, 인자 타입) 은 그 함수의 중간 상태를 담는다는 뜻이고, 넣을 때는 같은 함수에 -State 를 붙여 만듭니다. -If 조합자는 조건을 마지막 인자로 받습니다. 병합 전이라 세 묶음의 상태가 따로 있어야 합니다.

상태는 합친 뒤에 끝낸다

agg.daily_state 만 읽어 (site, day)별 users(서로 다른 사용자 수)·avg_dur(평균 체류)·human_hits(봇이 아닌 조회수)를 원본과 똑같이 내는 /root/ch/aggregating/q_state.sql 을 만드세요(열 순서 site, day, users, avg_dur, human_hits). 그리고 병합 전 각 행의 finalizeAggregation(users) 를 그냥 더한 값과 q_state.sql 의 users 를 모두 더한 값을 /root/ch/aggregating/naive.json 에 naive_users_sum·true_users_sum 으로 적으세요.

-Merge 를 붙인 함수가 같은 키의 상태들을 합친 뒤 결과를 냅니다. finalizeAggregation 은 상태 하나를 그 자리에서 끝내므로, 행마다 끝낸 숫자를 더하면 여러 시간대에 온 사람을 여러 번 셉니다. 두 합이 얼마나 다른지 보세요.

상태를 더 큰 단위로 다시 합친다

agg.day_total (day Date, users AggregateFunction(uniqExact, UInt64)) 를 ENGINE = AggregatingMergeTree ORDER BY day 로 만들어 agg.daily_state 의 users 상태를 uniqExactMergeState(users) 로 날짜별로 합쳐 넣고, agg.day_total 만 읽어 날짜별(사이트 구분 없는) 서로 다른 사용자 수를 내는 /root/ch/aggregating/q_day.sql 을 만드세요(열은 day, users).

-MergeState 는 상태들을 합쳐 결과가 아니라 다시 상태를 돌려주므로, 그 결과를 또 다른 AggregatingMergeTree 에 넣을 수 있습니다. 원본을 다시 읽지 않고 사이트별 상태에서 만드세요. 사이트별 사용자 수(숫자)를 더하면 같은 사람을 여러 번 셉니다.