ClickHouse 5 — Truy vấn phân tích & Materialized View

Ở bài 3 và bài 4 ta đã dựng được bảng MergeTree và nạp dữ liệu vào. Bây giờ mới tới phần khiến ClickHouse trở thành công cụ phân tích thời gian thực: cách nó truy vấn. ClickHouse nói một phương ngữ SQL rất gần ANSI — SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... LIMIT đều quen thuộc — nhưng bên trên đó là một kho hàm tổng hợp (aggregate function) phong phú đến mức khác hẳn Postgres/Oracle, cùng cơ chế Materialized View hoạt động theo một triết lý hoàn toàn khác với "materialized view" mà bạn biết ở các CSDL truyền thống.

Bài này đi qua: các hàm tổng hợp đặc thù, họ combinator (-If, -State, -Merge), arrayJoin và mảng, các mệnh đề mở rộng (WITH TOTALS, LIMIT BY), rồi tới trung tâm là Materialized View + AggregatingMergeTree để dựng rollup incremental, và cuối cùng là projections. Bối cảnh xuyên suốt: một dashboard giám sát giao dịch (transaction) của ngân hàng, cần số liệu tổng hợp theo phút, cập nhật gần như tức thời trên hàng tỷ dòng.

Lưu ý: các block SQL trong bài đều là cú pháp ClickHouse, dùng để minh hoạ. Sandbox của app là PostgreSQL read-only nên không block nào được đánh dấu "chạy được".

SQL của ClickHouse: giống ANSI, khác ở tổng hợp

Về khung chung, ClickHouse hỗ trợ hầu hết cú pháp SQL chuẩn: subquery, JOIN (nhiều kiểu), CTE WITH, window function, CASE WHEN, UNION ALL. Điểm khác nằm ở chỗ ClickHouse được thiết kế để quét cột và tổng hợp theo lô cực nhanh, nên nó ưu tiên GROUP BY và có sẵn hàng trăm hàm tổng hợp chuyên biệt. Một truy vấn dashboard điển hình:

SELECT
    toStartOfMinute(created_at) AS minute,
    kind,
    count()                     AS txn_count,
    sum(amount)                 AS total_amount,
    avg(amount)                 AS avg_amount
FROM transactions
WHERE created_at >= now() - INTERVAL 1 HOUR
GROUP BY minute, kind
ORDER BY minute DESC;

Vài khác biệt cú pháp cần nhớ ngay:

• count() viết không đối số (không phải COUNT(*)), tuy count(*) vẫn chấp nhận.
• Rất nhiều hàm thời gian dạng toStartOfMinute, toStartOfHour, toStartOfDay, toStartOfInterval(created_at, INTERVAL 5 MINUTE) — dùng để "gom mốc thời gian" cho rollup.
• ClickHouse cho phép dùng alias ngay trong GROUP BY/WHERE (như minute ở trên), điều mà Postgres không cho trong WHERE.
• Kiểu dữ liệu tường minh hơn: UInt64, Float64, DateTime, LowCardinality(String)...

Hàm tổng hợp đặc thù — vũ khí chính

Đây là nơi ClickHouse tỏa sáng. Nhiều hàm đánh đổi độ chính xác tuyệt đối lấy tốc độ và tiết kiệm RAM, rất hợp cho phân tích quy mô lớn.

Đếm distinct: uniq / uniqExact / uniqCombined

Đếm số giá trị phân biệt (COUNT(DISTINCT ...)) là thao tác đắt — về mặt lý thuyết cần giữ toàn bộ tập giá trị trong bộ nhớ. ClickHouse cung cấp cả bản chính xác lẫn xấp xỉ:

SELECT
    toStartOfMinute(created_at) AS minute,
    uniq(account_id)      AS active_accounts_approx,
    uniqExact(account_id) AS active_accounts_exact
FROM transactions
GROUP BY minute;

Trong dashboard "số tài khoản hoạt động mỗi phút", uniq cho kết quả gần như tức thời với sai số không đáng kể — hoàn toàn chấp nhận được cho biểu đồ giám sát. Chỉ khi cần con số đối soát chính xác (báo cáo pháp lý) mới nên dùng uniqExact.

Phân vị (quantile) — đo trải nghiệm, không chỉ trung bình

Trung bình che giấu đuôi phân phối. Để trả lời "95% giao dịch có số tiền dưới bao nhiêu?" hay "độ trễ p99 của hệ thống?", ta cần phân vị (percentile). ClickHouse dùng thuật toán xấp xỉ (reservoir sampling) mặc định:

SELECT
    quantile(0.5)(amount)   AS p50,
    quantile(0.95)(amount)  AS p95,
    quantile(0.99)(amount)  AS p99,
    quantiles(0.5, 0.9, 0.99)(amount) AS p_multi   -- trả về mảng
FROM transactions
WHERE created_at >= today();

Cú pháp "hai cặp ngoặc" là đặc trưng ClickHouse: quantile(tham_số)(cột) — cặp đầu là tham số của hàm, cặp sau là đối số dữ liệu. Các biến thể:

• quantile — xấp xỉ mặc định, nhanh.
• quantileExact — chính xác (sắp xếp toàn bộ), tốn RAM.
• quantileTDigest, quantileTiming — tối ưu cho số lớn / đo độ trễ (ms).
• quantiles(...) — tính nhiều phân vị một lượt, tiết kiệm hơn gọi nhiều lần.

Combinator -If: tổng hợp có điều kiện, một lần quét

Thay vì viết sum(CASE WHEN ... THEN amount ELSE 0 END), ClickHouse gắn hậu tố -If vào bất kỳ hàm tổng hợp nào để lọc ngay trong lúc gộp:

SELECT
    toStartOfMinute(created_at) AS minute,
    countIf(kind = 'debit')            AS debit_count,
    sumIf(amount, kind = 'debit')      AS debit_amount,
    sumIf(amount, amount > 100000000)  AS large_txn_amount,
    avgIf(amount, kind = 'credit')     AS avg_credit
FROM transactions
GROUP BY minute;

Điểm mạnh: chỉ quét bảng một lần mà tính được nhiều chỉ số theo nhiều điều kiện khác nhau. Đây là cách chuẩn để dựng bảng dashboard nhiều cột (debit/credit/reversal...) mà không lặp GROUP BY. Combinator có thể chồng với các combinator khác (ví dụ uniqIf, quantileIf).

argMax / argMin, topK, và các hàm "giữ theo nhóm"

• argMax(arg, val) trả về giá trị arg tại dòng có val lớn nhất — cực hữu ích để lấy "trạng thái mới nhất":

-- Số dư/loại giao dịch cuối cùng của mỗi tài khoản
SELECT
    account_id,
    argMax(amount, created_at)  AS last_amount,
    argMax(kind, created_at)    AS last_kind,
    max(created_at)             AS last_time
FROM transactions
GROUP BY account_id;

• topK(N)(x) trả về N giá trị xuất hiện nhiều nhất (xấp xỉ, thuật toán frequent-items):

SELECT topK(10)(account_id) AS top_accounts
FROM transactions
WHERE created_at >= today();   -- 10 tài khoản giao dịch nhiều nhất hôm nay

• any(x) lấy một giá trị bất kỳ trong nhóm (rẻ, không cần sort) — hữu ích khi bạn biết cột đó hằng số trong nhóm.
• groupArray(x) / groupUniqArray(x) gom các giá trị trong nhóm thành một mảng.

arrayJoin và làm việc với mảng

ClickHouse có kiểu Array hạng nhất. arrayJoin(arr) "bung" một dòng chứa mảng thành nhiều dòng (giống unnest/explode), thường dùng khi một giao dịch gắn nhiều tag/kênh:

SELECT channel, count()
FROM (
    SELECT arrayJoin(channels) AS channel
    FROM transactions_enriched
)
GROUP BY channel;

Kèm theo là bộ hàm mảng bậc cao: arraySum, arrayFilter(x -> x > 0, arr), arrayMap, has(arr, v), arrayElement. Chúng cho phép xử lý dữ liệu bán cấu trúc ngay trong SQL mà không phải normalize.

Mệnh đề mở rộng: WITH TOTALS và LIMIT BY

• WITH TOTALS: sau khối kết quả GROUP BY, ClickHouse trả thêm một dòng tổng cho toàn bộ (không theo nhóm) — tiện cho footer "Tổng cộng" của bảng dashboard mà không cần query thứ hai:

SELECT kind, count() AS c, sum(amount) AS s
FROM transactions
WHERE created_at >= today()
GROUP BY kind
WITH TOTALS;

• LIMIT n BY cột: lấy n dòng đầu cho mỗi giá trị của cột. Ví dụ "3 giao dịch lớn nhất của mỗi tài khoản":

SELECT account_id, amount, created_at
FROM transactions
ORDER BY account_id, amount DESC
LIMIT 3 BY account_id;

LIMIT BY gọn hơn nhiều so với window function ROW_NUMBER() OVER (PARTITION BY ...) cho bài toán top-N-per-group.

Combinator -State / -Merge: nền tảng của rollup incremental

Đây là ý tưởng then chốt để hiểu Materialized View. Một hàm tổng hợp bình thường trả về kết quả cuối (một số). Nhưng combinator -State khiến hàm trả về trạng thái tổng hợp trung gian — một cấu trúc nhị phân (ví dụ: với avg là cặp (tổng, số lượng); với uniq là bản phác HLL). Trạng thái này có thể lưu lại và gộp tiếp sau này.

• sumState(amount), avgState(amount), uniqState(account_id), quantilesState(0.95)(amount) → trả về giá trị kiểu AggregateFunction(...).
• Combinator -Merge làm ngược lại: nhận các trạng thái đã lưu, gộp chúng và trả ra kết quả cuối.

-- Ghi trạng thái (thường vào bảng đích của MV)
SELECT
    toStartOfMinute(created_at) AS minute,
    sumState(amount)            AS amount_state,
    uniqState(account_id)       AS acct_state
FROM transactions
GROUP BY minute;

-- Đọc kết quả cuối từ trạng thái đã lưu
SELECT
    minute,
    sumMerge(amount_state)  AS total_amount,
    uniqMerge(acct_state)   AS active_accounts
FROM txn_rollup_minute
GROUP BY minute;

Vì sao quan trọng? Vì trạng thái có tính kết hợp (associative): gộp trạng thái của lô A với lô B cho kết quả đúng như gộp toàn bộ A+B một lần. Nhờ đó ta có thể cộng dồn tăng dần khi dữ liệu mới tới, thay vì tính lại từ đầu. Đó chính là cơ chế của rollup thời gian thực.

Materialized View trong ClickHouse — KHÁC hẳn SQL thường

Ở Postgres/Oracle, MATERIALIZED VIEW là một snapshot của kết quả query, chỉ được cập nhật khi bạn chạy REFRESH (toàn bộ, tốn kém, không real-time). ClickHouse hoàn toàn khác:

Materialized View của ClickHouse là một trigger lúc INSERT. Mỗi khi có dữ liệu mới được ghi vào bảng nguồn, ClickHouse chạy query của MV trên đúng lô vừa insert rồi đẩy kết quả (đã biến đổi/rollup) sang bảng đích. MV không lưu dữ liệu — bảng đích mới lưu.

Điểm mấu chốt cần khắc cốt:

1. MV chỉ nhìn thấy lô INSERT mới, không nhìn dữ liệu cũ và không thấy dữ liệu chèn qua đường khác.
2. MV chạy incremental: mỗi lô insert sinh ra một phần rollup, không quét lại bảng lớn.
3. Bảng đích thường là AggregatingMergeTree (hoặc SummingMergeTree) để cộng dồn các phần rollup này lại thành số cuối.
4. MV không tự nạp dữ liệu lịch sử đã tồn tại trước khi tạo — muốn có, phải backfill thủ công (INSERT INTO target SELECT ...).

Ghép MV + AggregatingMergeTree bằng -State/-Merge

Đây là mẫu kinh điển. Ba thành phần:

(1) Bảng đích — dùng AggregatingMergeTree, các cột đo lưu dạng trạng thái tổng hợp:

CREATE TABLE txn_rollup_minute
(
    minute        DateTime,
    kind          LowCardinality(String),
    txn_count     AggregateFunction(count),
    amount_state  AggregateFunction(sum, Float64),
    acct_state    AggregateFunction(uniq, UInt64),
    p95_state     AggregateFunction(quantiles(0.95), Float64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (minute, kind);

(2) Materialized View — trigger biến đổi lô insert thành trạng thái rồi ghi vào bảng đích:

CREATE MATERIALIZED VIEW mv_txn_rollup
TO txn_rollup_minute
AS
SELECT
    toStartOfMinute(created_at) AS minute,
    kind,
    countState()                AS txn_count,
    sumState(amount)            AS amount_state,
    uniqState(account_id)       AS acct_state,
    quantilesState(0.95)(amount) AS p95_state
FROM transactions
GROUP BY minute, kind;

(3) Truy vấn dashboard — luôn đọc qua -Merge (vì AggregatingMergeTree gộp part bất đồng bộ, dữ liệu chưa merge xong sẽ nằm rải ở nhiều part):

SELECT
    minute,
    kind,
    countMerge(txn_count)   AS txn_count,
    sumMerge(amount_state)  AS total_amount,
    uniqMerge(acct_state)   AS active_accounts,
    quantilesMerge(0.95)(p95_state)[1] AS p95_amount
FROM txn_rollup_minute
WHERE minute >= now() - INTERVAL 6 HOUR
GROUP BY minute, kind
ORDER BY minute DESC;

Nguyên tắc vàng: ghi bằng -State, đọc bằng -Merge, và luôn GROUP BY khi đọc bảng Aggregating — nếu quên -Merge/GROUP BY, bạn sẽ thấy nhiều dòng trùng mốc thời gian vì các part chưa được gộp (đúng cái bẫy "merge bất định" đã nói ở bài 3).

Nếu chỉ cần cộng dồn đơn giản (count/sum, không cần uniq/quantile), có thể dùng SummingMergeTree cho bảng đích với cột số thường (không cần -State) — nhẹ và dễ đọc hơn, nhưng kém linh hoạt.

Sơ đồ luồng rollup thời gian thực

Projections — chỉ mục thay thế nằm trong bảng

MV giải quyết rollup, nhưng đôi khi ta chỉ muốn cùng dữ liệu nhưng sắp xếp/gộp theo cách khác để một loại query cụ thể chạy nhanh. Đó là việc của projection — một "bản sao ẩn" của dữ liệu, lưu bên trong chính bảng, được ClickHouse tự đồng bộ và tự chọn khi thấy có lợi (không cần sửa query):

ALTER TABLE transactions
ADD PROJECTION proj_by_account
(
    SELECT account_id, toStartOfDay(created_at) AS day, sum(amount), count()
    GROUP BY account_id, day
);
ALTER TABLE transactions MATERIALIZE PROJECTION proj_by_account;

Có hai kiểu dùng:

• Projection gộp (aggregate): chứa sẵn kết quả GROUP BY — query tổng hợp khớp sẽ đọc thẳng từ projection.
• Projection sắp xếp lại (normal): cùng dữ liệu nhưng ORDER BY khác, giúp lọc theo cột không nằm trong sort key chính.

So sánh nhanh MV vs Projection:

Quy tắc thực dụng: projection cho tối ưu trong-bảng minh bạch; MV + AggregatingMergeTree khi cần rollup lớn, đổi granularity, hoặc cấp dữ liệu cho một bảng/hệ thống khác.

Use case thực tế

Bối cảnh. Trung tâm giám sát giao dịch của NCB cần một dashboard "sức khỏe kênh thanh toán" cập nhật gần thời gian thực: mỗi phút hiển thị số giao dịch, tổng giá trị, số tài khoản hoạt động, tỷ lệ giao dịch lớn (>100 triệu), và p95 số tiền — theo từng kind (debit/credit/reversal). Bảng transactions nhận ~8.000 dòng/giây từ Kafka, tích lũy khoảng 700 triệu dòng/ngày.

Vấn đề với cách "query thẳng". Ban đầu team để dashboard chạy trực tiếp GROUP BY toStartOfMinute(created_at) trên bảng raw với uniqExact và quantileExact. Với khung 6 giờ (~170 triệu dòng), mỗi lần refresh quét gần 2,5 GB cột nén, mất 4–7 giây và ngốn RAM cho uniqExact — 30 người cùng mở dashboard làm cụm quá tải.

Giải pháp rollup incremental.

1. Tạo bảng đích txn_rollup_minute (AggregatingMergeTree, ORDER BY (minute, kind)) với các cột countState / sumState / uniqState / quantilesState(0.95) và một sumStateIf cho giao dịch lớn.
2. Tạo mv_txn_rollup ... TO txn_rollup_minute. Từ lúc này mọi lô Kafka insert đều tự sinh phần rollup.
3. Backfill 30 ngày lịch sử một lần bằng INSERT INTO txn_rollup_minute SELECT ... (vì MV không nhìn dữ liệu cũ).
4. Dashboard đổi sang đọc txn_rollup_minute với -Merge + GROUP BY minute, kind.

Kết quả. Query dashboard giờ chỉ quét bảng rollup — mỗi phút chỉ có vài dòng (số kind × số phút), khung 6 giờ chỉ còn ~1.000 dòng. Thời gian phản hồi rớt từ 4–7 giây xuống dưới 80 ms, RAM gần như không đáng kể, và số liệu trễ chỉ đúng bằng độ trễ merge của một lô insert (vài giây). uniq xấp xỉ cho sai số <2% — chấp nhận được cho biểu đồ; báo cáo đối soát cuối ngày vẫn chạy uniqExact trên bảng raw theo lịch. Bổ sung một projection proj_by_account giúp truy vấn "lịch sử một tài khoản" (điều tra gian lận) không cần quét toàn bảng.

Ghi nhớ

• ClickHouse SQL gần ANSI nhưng mạnh ở hàm tổng hợp: count() không đối số, cho dùng alias trong WHERE/GROUP BY, nhiều hàm toStartOf... để gom mốc thời gian.
• Đếm distinct: uniq (HLL, xấp xỉ, mặc định nên dùng) < uniqCombined < uniqExact (chính xác, tốn RAM). Phân vị: quantile(p)(x) xấp xỉ, quantiles(...) tính nhiều phân vị một lượt.
• Combinator -If (sumIf, countIf, avgIf) = tổng hợp có điều kiện, một lần quét — dựng dashboard nhiều cột không cần lặp GROUP BY.
• argMax(arg, val) lấy trạng thái mới nhất; topK(N) lấy top xuất hiện nhiều nhất (xấp xỉ); arrayJoin bung mảng thành dòng.
• WITH TOTALS cho dòng tổng; LIMIT n BY col cho top-N mỗi nhóm — gọn hơn window function.
• -State lưu trạng thái tổng hợp trung gian (có tính kết hợp), -Merge gộp lại thành kết quả cuối — nền tảng của rollup incremental.
• Materialized View của ClickHouse là trigger lúc INSERT, chỉ thấy lô mới, đẩy dữ liệu đã rollup sang bảng đích; KHÁC hẳn MV snapshot của Postgres/Oracle. Không tự backfill dữ liệu cũ.
• Mẫu chuẩn: bảng đích AggregatingMergeTree + MV ghi -State + dashboard đọc -Merge kèm GROUP BY. Quên -Merge/GROUP BY → trùng dòng do part chưa merge.
• Projection = chỉ mục/bản sắp xếp lại nằm trong bảng, ClickHouse tự đồng bộ và tự chọn; dùng để tăng tốc query trên chính bảng mà không sửa câu query.