What a materialized view saw and what it missed

목표

원천 표에 구체화 뷰를 걸어 INSERT 가 대상 표로 흐르는 것을 확인하고, 뷰가 보지 못하는 세 가지 — 뷰 이전의 행, 병합 전의 중복 키, JOIN 오른쪽 표의 변화 — 를 숫자로 재현한다.

왜 중요한가

ClickHouse 의 구체화 뷰는 "저장된 쿼리" 가 아니라 INSERT 트리거입니다. 그래서 되채우기를 빠뜨리면 과거가 비고, 두 번 되채우면 두 배가 되고, 대상 표를 더하지 않고 읽으면 병합 시점에 따라 숫자가 흔들리고, 차원 표 조인은 늦게 온 차원을 영영 놓칩니다. 이 실습의 채점기는 여러분이 적은 숫자를 믿지 않습니다 — 대상 표를 원천 표(또는 픽스처 생성 식)로 다시 계산한 값과 칸마다 대조하고, 저장한 SELECT 는 읽기 전용으로 다시 돌려 결과와 읽은 행 수를 잽니다.

단계

1. 데이터베이스 mv 와 표 mv.orders 를 만드세요 — 열 order_id UInt64, ts DateTime, shop LowCardinality(String), product_id UInt32, user_id UInt32, qty UInt32, price UInt32 (이 순서), 엔진 MergeTree, ORDER BY (shop, ts). 그리고 /opt/lab/fixtures/mv/orders_1.sql(9월 1–15일, 20만 건)을 한 번 넣으세요.
2. 대상 표 mv.daily_sales (day Date, shop LowCardinality(String), orders UInt64, revenue UInt64) 를 SummingMergeTree, ORDER BY (shop, day) 로 만들고, 구체화 뷰 mv.daily_sales_mv 를 TO mv.daily_sales 로 만드세요 — toDate(ts) AS day, shop, count() AS orders, sum(qty * price) AS revenue 를 GROUP BY day, shop.
3. /opt/lab/fixtures/mv/orders_2.sql(9월 16–30일, 20만 건)을 넣은 직후, mv.orders 의 행 수를 source_orders, mv.daily_sales 의 sum(orders) 를 view_orders 로 /root/ch/mv/gap.json 에 적으세요.
4. 뷰가 보지 못한 1차분을 mv.daily_sales 에 되채워, 대상 표가 원천 전체를 (shop, day) 별로 정확히 담게 하세요. 2차분을 또 넣으면 안 됩니다.
5. mv.daily_sales 에서 shop-3 의 날짜별 주문 수·매출을 구하는 /root/ch/mv/q_daily.sql 을 쓰세요 — 열 day, orders, revenue, 날짜순. 병합 전에도 맞도록 더해서 읽어야 합니다.
6. 대상 표 mv.shop_stats (shop LowCardinality(String), day Date, orders AggregateFunction(count), buyers AggregateFunction(uniqExact, UInt32)) 를 AggregatingMergeTree, ORDER BY (shop, day) 로, 뷰 mv.shop_stats_mv 를 TO mv.shop_stats 로(countState()·uniqExactState(user_id)) 만들고 이미 있는 원천을 한 번 되채우세요. 그리고 상점별 한 달 고유 구매자를 mv.shop_stats 에서 구하는 /root/ch/mv/q_buyers.sql 을 쓰세요 — 열 shop, buyers, 상점순.
7. mv.orders 와 같은 열에 status LowCardinality(String) 을 끝에 더한 mv.raw 를 ENGINE = Null 로 만들고, status = 'paid' 인 행만 mv.orders 로 보내는 뷰 mv.raw_mv 를 만든 뒤 /opt/lab/fixtures/mv/raw.sql 을 한 번 넣으세요.
8. mv.products (product_id UInt32, category LowCardinality(String))(MergeTree, ORDER BY product_id)와 mv.cat_sales (category LowCardinality(String), orders UInt64)(SummingMergeTree, ORDER BY category), 그리고 mv.orders 를 mv.products 와 INNER JOIN 해 범주별 count() 를 보내는 뷰 mv.cat_sales_mv 를 만드세요. 그다음 products.sql → late.sql → products_new.sql (모두 /opt/lab/fixtures/mv/) 순서로 넣고, 늦은 주문 수(order_id >= 2000000)를 late_orders, mv.cat_sales 의 sum(orders) 를 joined_orders, 그 차이를 lost_orders 로 /root/ch/mv/join.json 에 적으세요.

참고

• 서버는 이미 떠 있습니다(clickhouse-client). 멈췄다면 ch-up.
• 읽은 행을 잴 때는 --use_query_condition_cache 0 을 주세요. 같은 조건의 쿼리를 두 번째 돌리면 조건 캐시가 그래뉼을 건너뛰어 숫자가 달라질 수 있습니다. 채점기도 캐시를 끄고 잽니다.
• 픽스처는 주문 번호로 구간이 나뉩니다 — 1차분 0부터, 2차분 200000부터, 원시 1000000부터, 늦은 주문 2000000부터.
• 흔한 실수: 되채우기에 2차분까지 넣어 9월 16–30일이 두 배가 되는 것, JOIN 뷰를 LEFT JOIN 으로 만들어 빈 범주가 생기는 것, 픽스처를 두 번 넣는 것.
• 망가뜨렸다면 대상 표는 TRUNCATE TABLE 뒤 원천 전체를 한 번 다시 넣으면 됩니다(뷰는 그대로 두고).
• 공식 문서: Incremental materialized view · CREATE VIEW · Use materialized views · AggregatingMergeTree · Refreshable materialized view

원천 표를 만들고 1차분을 넣는다

데이터베이스 mv 와 표 mv.orders 를 만드세요. 열 order_id UInt64, ts DateTime, shop LowCardinality(String), product_id UInt32, user_id UInt32, qty UInt32, price UInt32 순서, 엔진 MergeTree, ORDER BY (shop, ts). 그다음 /opt/lab/fixtures/mv/orders_1.sql 을 한 번 실행해 20만 건을 넣으세요.

CREATE DATABASE·CREATE TABLE 뒤 clickhouse-client --queries-file 로 픽스처를 넘깁니다. 픽스처는 해시로 만든 자료라 몇 번을 돌려도 같은 행이 나오지만, MergeTree 는 중복을 막지 않아서 두 번 넣으면 40만 건이 됩니다.

대상 표와 구체화 뷰를 만든다

대상 표 mv.daily_sales (day Date, shop LowCardinality(String), orders UInt64, revenue UInt64) 를 SummingMergeTree, ORDER BY (shop, day) 로 만들고, 구체화 뷰 mv.daily_sales_mv 를 TO mv.daily_sales 로 만드세요. 뷰의 SELECT 는 toDate(ts) AS day, shop, count() AS orders, sum(qty * price) AS revenue FROM mv.orders GROUP BY day, shop 입니다. 만든 직후 mv.daily_sales 의 행 수를 확인해 보세요.

뷰는 결과를 직접 저장하지 않고 TO 로 지정한 표에 씁니다. 대상 표의 정렬 키를 뷰의 GROUP BY 와 맞춰야 병합 때 같은 (shop, day) 가 합쳐집니다. 방금 만든 뷰의 대상 표가 비어 있는 이유가 이 모듈의 첫 번째 교훈입니다.

뷰가 본 것과 원천을 비교한다

/opt/lab/fixtures/mv/orders_2.sql 을 한 번 넣은 직후, mv.orders 의 행 수를 source_orders, mv.daily_sales 의 sum(orders) 를 view_orders 로 /root/ch/mv/gap.json 에 적으세요.

뷰는 INSERT 블록을 입력으로 받는 트리거입니다. 2차분 INSERT 는 뷰가 있을 때 들어왔고, 1차분은 뷰가 생기기 전에 들어왔습니다. 두 숫자의 차이가 곧 되채워야 할 양입니다.

빠진 구간만 되채운다

뷰가 보지 못한 1차분(9월 1–15일)을 INSERT INTO mv.daily_sales SELECT ... 로 되채워, mv.daily_sales 를 (shop, day) 별로 더한 값이 원천 전체를 다시 집계한 값과 칸마다 같게 하세요.

뷰의 SELECT 를 그대로 쓰되 원천에 WHERE 로 구간을 거는 것이 핵심입니다. 뷰가 이미 넣은 날까지 다시 넣으면 SummingMergeTree 가 그 날들을 두 번 더합니다. 경계는 2차분이 시작하는 시각입니다.

병합 전에도 맞게 대상 표를 읽는다

mv.daily_sales 에서 shop = 'shop-3' 의 날짜별 주문 수·매출을 구하는 /root/ch/mv/q_daily.sql 을 쓰세요. 결과 열은 day, orders, revenue, 날짜순입니다. 대상 표만 읽어야 합니다.

뷰의 GROUP BY 는 블록 하나 안에서만 합칩니다. 그래서 병합이 끝나기 전에는 같은 (shop, day) 가 여러 줄 있습니다. 조회에서 다시 sum 하고 GROUP BY day 하면 병합 시점과 상관없이 같은 답이 나옵니다.

더할 수 없는 값은 상태로 담는다

mv.shop_stats (shop LowCardinality(String), day Date, orders AggregateFunction(count), buyers AggregateFunction(uniqExact, UInt32)) 를 AggregatingMergeTree, ORDER BY (shop, day) 로, 뷰 mv.shop_stats_mv 를 TO mv.shop_stats 로(countState() AS orders, uniqExactState(user_id) AS buyers, GROUP BY shop, day) 만들고, 이미 있는 원천을 한 번 되채우세요. 그리고 상점별 한 달 고유 구매자를 mv.shop_stats 에서 구하는 /root/ch/mv/q_buyers.sql 을 쓰세요 — 열 shop, buyers, 상점순.

날마다의 고유 구매자 수를 더하면 여러 날 산 사람이 겹쳐 세어집니다. -State 는 결과 대신 합칠 수 있는 중간 상태를 저장하고, 조회 때 같은 함수의 -Merge 로 합칩니다. 되채우기는 두 번 하면 countMerge 가 두 배가 됩니다.

Null 표에서 시작하는 연쇄 뷰

mv.orders 의 열 뒤에 status LowCardinality(String) 을 더한 표 mv.raw 를 ENGINE = Null 로 만들고, status = 'paid' 인 행의 원천 열 일곱 개만 mv.orders 로 보내는 뷰 mv.raw_mv 를 만드세요. 그다음 /opt/lab/fixtures/mv/raw.sql 을 한 번 넣으세요.

Null 엔진 표는 아무것도 저장하지 않지만 INSERT 는 받아서 뷰를 깨웁니다. raw_mv 가 mv.orders 에 쓴 블록은 다시 daily_sales_mv 와 shop_stats_mv 를 깨웁니다 — 한 번의 INSERT 가 세 표에 닿습니다. mv.raw 를 SELECT 하면 늘 0행입니다.

JOIN 뷰는 오른쪽 표의 변화를 모른다

mv.products (product_id UInt32, category LowCardinality(String))(MergeTree, ORDER BY product_id), mv.cat_sales (category LowCardinality(String), orders UInt64)(SummingMergeTree, ORDER BY category), 그리고 mv.orders AS o INNER JOIN mv.products AS p ON o.product_id = p.product_id 로 범주별 count() AS orders 를 mv.cat_sales 에 보내는 뷰 mv.cat_sales_mv 를 만드세요. 그다음 /opt/lab/fixtures/mv/ 의 products.sql → late.sql → products_new.sql 순서로 넣고, 늦은 주문 수(order_id >= 2000000)를 late_orders, mv.cat_sales 의 sum(orders) 를 joined_orders, 차이를 lost_orders 로 /root/ch/mv/join.json 에 적으세요.

JOIN 이 있는 뷰에서 방아쇠는 맨 왼쪽 표(원천)의 INSERT 뿐이고, 오른쪽 표는 그 순간의 내용을 통째로 읽기만 합니다. 늦은 주문이 들어온 순간 상품 표에 없던 상품의 주문은 INNER JOIN 에서 버려지고, 상품이 나중에 등록되어도 되살아나지 않습니다.