ClickHouse 4 — Nạp dữ liệu & thiết kế schema

Ở bài 3 chúng ta đã hiểu MergeTree hoạt động thế nào: mỗi INSERT sinh ra một part mới trên đĩa, các part được gộp (merge) ở hậu trường. Kiến thức đó không chỉ để chọn ORDER BY — nó chi phối trực tiếp cách bạn nạp dữ liệu. Nạp sai kiểu (ví dụ insert từng dòng qua HTTP) sẽ tạo ra hàng chục nghìn part nhỏ, làm merge không kịp, đọc chậm và ClickHouse sẽ báo lỗi Too many parts.

Bài này trả lời hai câu hỏi song hành: (1) đưa dữ liệu vào ClickHouse thế nào cho đúng — batch, async insert, nạp từ file/URL/S3, và ingest streaming từ Kafka; và (2) thiết kế schema thế nào để tối ưu — chọn kiểu dữ liệu, LowCardinality, tránh Nullable, và xu hướng denormalize thay vì chuẩn hoá như OLTP.

Bối cảnh xuyên suốt: chúng ta cần ingest luồng giao dịch (transaction) của một ngân hàng — hàng chục nghìn bản ghi mỗi giây, đến từ core banking qua Kafka — vào một bảng phân tích để dashboard giám sát gian lận và báo cáo cuối ngày đọc được gần real-time.

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

Vì sao "cách insert" quan trọng đến vậy

Trong PostgreSQL, insert 1 dòng hay 1000 dòng đều bình thường — engine cập nhật heap và B-tree tại chỗ. Trong ClickHouse thì khác về bản chất: mỗi câu INSERT tạo ít nhất một part. Part là một thư mục file cột đã sắp xếp và nén. Nếu bạn gửi 100.000 câu INSERT ... VALUES mỗi câu 1 dòng, bạn tạo 100.000 part nhỏ. Hậu quả:

• Mỗi part có overhead cố định (file metadata, chỉ mục thưa, cột nén tối thiểu vài KB). 1 dòng cũng tốn như 1000 dòng về mặt cấu trúc.
• Luồng merge ở hậu trường phải làm việc điên cuồng để gộp lại, đốt CPU và I/O.
• Số part trong một partition vượt ngưỡng (parts_to_throw_insert, mặc định 3000) → ClickHouse từ chối insert với lỗi Too many parts.

Quy tắc vàng: chèn lô lớn, ít lần. Tài liệu ClickHouse khuyến nghị mỗi INSERT nên chứa từ hàng chục nghìn đến hàng triệu dòng, và không nên insert quá ~1 lần/giây cho mỗi bảng. Việc gom lô (batching) là trách nhiệm của phía ghi.

INSERT theo lô (batch INSERT)

Cách trực tiếp nhất: gom dữ liệu ở tầng ứng dụng rồi gửi một câu INSERT lớn. Định dạng gửi qua HTTP thường là JSONEachRow hoặc Values, nhưng với khối lượng lớn nên dùng format cột hoặc nhị phân (xem phần sau).

-- Cú pháp ClickHouse (minh hoạ, KHÔNG chạy trong sandbox)
INSERT INTO transactions (txn_id, account_no, amount, currency, kind, txn_time)
VALUES
  (1001, 'VN0012', 5000000, 'VND', 'debit',  '2026-07-02 09:15:03'),
  (1002, 'VN0055',  200000, 'VND', 'credit', '2026-07-02 09:15:04'),
  -- ... hàng chục nghìn dòng nữa trong CÙNG một câu INSERT
  (60000, 'VN0102', 1500,   'USD', 'debit',  '2026-07-02 09:15:59');

Nguyên tắc thực chiến khi ghi batch từ ứng dụng (Java/Python/Go):

• Buffer theo thời gian HOẶC theo số dòng, cái nào tới trước: ví dụ flush mỗi 1 giây hoặc mỗi 50.000 dòng.
• Dùng driver hỗ trợ format nhị phân (RowBinary, Native) để giảm chi phí parse phía server.
• Idempotency: ClickHouse tự khử trùng lặp các block insert giống hệt nhau (deduplication theo hash của block) trong một cửa sổ thời gian với bảng Replicated — hữu ích khi retry sau lỗi mạng, nhưng chỉ đúng khi block byte-for-byte giống nhau.

Nếu tầng ứng dụng khó gom lô (ví dụ nhiều microservice cùng ghi lẻ), có hai lối thoát: async insert (dưới đây) hoặc engine Buffer đứng trước MergeTree để gom hộ trong RAM.

Async insert — để server gom lô hộ

Khi bạn không kiểm soát được phía ghi (mỗi request web đẩy 1 dòng), bật asynchronous insert. ClickHouse nhận từng insert nhỏ, đẩy vào một buffer trong bộ nhớ server, và tự flush thành part lớn theo ngưỡng thời gian/kích thước.

-- Bật ở mức session/query (minh hoạ)
SET async_insert = 1;
SET wait_for_async_insert = 1;  -- chờ flush xong mới trả OK (an toàn hơn)

INSERT INTO transactions (txn_id, account_no, amount, currency, kind, txn_time)
VALUES (2001, 'VN0012', 750000, 'VND', 'debit', now());

Các tham số quan trọng:

Đánh đổi cần nhớ: với wait_for_async_insert = 0, client nhận "OK" trước khi dữ liệu thực sự nằm trên đĩa — nếu server sập trước lúc flush, dữ liệu mất. Với luồng giao dịch ngân hàng cần độ bền cao, giữ wait_for_async_insert = 1, hoặc tốt hơn là dùng Kafka làm bộ đệm bền (durable buffer) như phần cuối bài.

Nạp từ file và các format

ClickHouse đọc/ghi rất nhiều format. Ba cái dùng nhiều nhất trong đời thực:

• CSV / CSVWithNames — file trích xuất từ core banking, Excel export. Cần khai báo có header hay không.
• JSONEachRow — mỗi dòng là một JSON object; format phổ biến nhất cho streaming/log.
• Parquet — format cột, nén tốt, giữ schema; chuẩn de-facto cho data lake. Nạp Parquet vào ClickHouse cực nhanh vì cả hai đều là cột.

Qua clickhouse-client

clickhouse-client là công cụ dòng lệnh chuẩn để nạp file lớn. Nó stream file thẳng vào server, không giữ hết trong RAM.

# Nạp file CSV có header (minh hoạ)
clickhouse-client --query \
  "INSERT INTO transactions FORMAT CSVWithNames" < transactions_2026-07-01.csv

# Nạp Parquet
clickhouse-client --query \
  "INSERT INTO transactions FORMAT Parquet" < txn.parquet

Hàm bảng: file / url / s3

ClickHouse có các table function cho phép SELECT (hoặc INSERT ... SELECT) trực tiếp từ nguồn ngoài mà không cần tạo bảng:

-- Đọc trực tiếp file S3 và nạp vào MergeTree (minh hoạ)
INSERT INTO transactions
SELECT * FROM s3(
  'https://bucket.s3.amazonaws.com/txn/2026-07-01/*.parquet',
  'ACCESS_KEY', 'SECRET_KEY',
  'Parquet'
);

-- Đọc từ một URL trả về CSV
SELECT count() FROM url('https://data.internal/export.csv', 'CSVWithNames');

Các hàm file(), url(), s3() (và hdfs(), azureBlobStorage()) rất hữu ích cho ETL một lần (backfill lịch sử) hoặc đọc data lake mà không cần copy trước. Với S3, dùng ký tự đại diện (*, {a,b}) để nạp song song nhiều file cùng lúc — ClickHouse sẽ đọc chúng đa luồng.

Ingest streaming từ Kafka

Đây là kịch bản chính của chúng ta: luồng giao dịch chảy liên tục trên một Kafka topic (giả sử topic txn_events, message dạng JSON). ClickHouse ingest Kafka theo mô hình ba tầng kinh điển:

1. Bảng Kafka engine — một "bảng ảo" đọc từ topic. Nó không lưu dữ liệu; đọc từ nó sẽ consume message (mỗi message chỉ đọc được một lần, như một consumer).
2. Materialized View (MV) — một trigger tự động: mỗi khi bảng Kafka có message mới, MV chạy SELECT và đẩy kết quả sang bảng đích.
3. Bảng MergeTree đích — nơi dữ liệu thật sự được lưu, sắp xếp, nén, và truy vấn.

Vì sao phải có 3 tầng, không đọc thẳng từ bảng Kafka? Vì bảng Kafka chỉ consume một lần và không lưu gì — nó chỉ là cửa ngõ. MV là thứ biến luồng đó thành dữ liệu bền trong MergeTree.

-- 1) Bảng Kafka engine: đọc topic, tự parse JSON (minh hoạ)
CREATE TABLE txn_queue
(
  txn_id      UInt64,
  account_no  String,
  amount      Decimal(18, 2),
  currency    String,
  kind        String,
  txn_time    DateTime
)
ENGINE = Kafka
SETTINGS
  kafka_broker_list = 'broker1:9092,broker2:9092',
  kafka_topic_list  = 'txn_events',
  kafka_group_name  = 'clickhouse_txn_consumer',
  kafka_format      = 'JSONEachRow',
  kafka_num_consumers = 3;

-- 2) Bảng MergeTree đích: nơi lưu thật, tối ưu cho truy vấn
CREATE TABLE transactions
(
  txn_id      UInt64,
  account_no  LowCardinality(String),
  amount      Decimal(18, 2),
  currency    LowCardinality(String),
  kind        Enum8('debit' = 1, 'credit' = 2),
  txn_time    DateTime,
  txn_date    Date MATERIALIZED toDate(txn_time)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(txn_time)
ORDER BY (txn_date, account_no, txn_time);

-- 3) Materialized View: nối bảng Kafka -> MergeTree
CREATE MATERIALIZED VIEW txn_mv TO transactions AS
SELECT txn_id, account_no, amount, currency, kind, txn_time
FROM txn_queue;

Vài điểm thực chiến khi vận hành Kafka engine:

• Gom lô tự nhiên: ClickHouse đọc Kafka theo block (kafka_max_block_size), nên một lần flush của MV đẩy nhiều nghìn dòng — tự động batch, tránh part nhỏ. Đây là lý do Kafka là bộ đệm ingest lý tưởng.
• Song song hoá: kafka_num_consumers nên khớp số partition của topic để đọc song song.
• Xử lý message lỗi: đặt kafka_skip_broken_messages để bỏ qua message parse hỏng thay vì kẹt cả consumer.
• Nhiều MV từ một bảng Kafka: có thể tạo nhiều MV cùng đọc txn_queue để đẩy vào nhiều bảng đích khác nhau (ví dụ một bảng raw + một bảng tổng hợp theo phút). Nếu muốn thế, cân nhắc để mỗi luồng tiêu dùng riêng để tránh tranh chấp.
• Chi tiết mô hình MV và bảng tổng hợp real-time sẽ ở bài 5. Kiến trúc Kafka nền tảng xem Kafka — kiến trúc.

Thiết kế schema tối ưu cho OLAP

Nạp đúng mới là một nửa. Nửa còn lại là kiểu dữ liệu — vì ClickHouse là cột và nén theo cột, chọn kiểu hẹp giúp nén tốt hơn, quét nhanh hơn, tốn ít RAM hơn.

Chọn kiểu số hẹp nhất có thể

Đừng mặc định dùng Int64/String cho mọi thứ. Chọn kiểu vừa khít miền giá trị:

Tiền không bao giờ dùng Float — sai số dấu phẩy động sẽ làm lệch tổng khi cộng dồn hàng triệu giao dịch. Dùng Decimal với số chữ số thập phân cố định.

LowCardinality(String) cho cột ít giá trị phân biệt

Đây là "vũ khí" đặc trưng của ClickHouse. Với cột String mà số giá trị phân biệt thấp (dưới ~vài chục nghìn) — như currency, kind, branch_code, channel — bọc bằng LowCardinality:

currency  LowCardinality(String),   -- 'VND', 'USD', 'EUR' ... vài chục giá trị
channel   LowCardinality(String)    -- 'atm', 'internet', 'mobile', 'branch'

LowCardinality lưu một dictionary các giá trị phân biệt và dùng số nguyên nhỏ tham chiếu vào đó. Kết quả: nén tốt hơn nhiều, GROUP BY/lọc trên cột đó nhanh hẳn. Đây gần như là mặc định cho mọi cột phân loại có bản số (cardinality) thấp. Ngưỡng đơn giản: nếu số giá trị phân biệt < ~10.000 và cột được lọc/nhóm thường xuyên → LowCardinality.

Cảnh báo: đừng dùng LowCardinality cho cột bản số cao (ví dụ account_no hàng triệu giá trị, hay txn_id) — dictionary sẽ phình to và phản tác dụng.

Enum cho tập giá trị cố định, biết trước

Khi tập giá trị cố định và biết trước (loại giao dịch, trạng thái), Enum8/Enum16 còn chặt hơn: lưu dưới dạng số nguyên nhưng hiển thị/ghi bằng chuỗi, và ràng buộc chỉ nhận đúng các giá trị đã khai báo.

kind    Enum8('debit' = 1, 'credit' = 2, 'reversal' = 3),
status  Enum8('pending' = 0, 'settled' = 1, 'failed' = 2)

Đánh đổi: Enum khó thay đổi khi thêm giá trị mới (phải ALTER). Nếu tập giá trị có thể mở rộng → dùng LowCardinality(String) linh hoạt hơn.

Nullable — hạn chế tối đa

Nullable(T) trong ClickHouse tốn chi phí: nó tạo thêm một cột phụ (bitmap đánh dấu NULL) lưu song song, làm chậm quét và nén kém hơn. Trong OLAP, hãy tránh Nullable trừ khi "NULL" thực sự mang ý nghĩa nghiệp vụ khác với 0/rỗng.

Thay vì Nullable, dùng giá trị sentinel hợp lý: amount Decimal DEFAULT 0, note String DEFAULT '', hoặc event_time DateTime DEFAULT toDateTime(0). Chuỗi rỗng '' và số 0 nén cực tốt trong ClickHouse. Chỉ giữ Nullable cho những cột mà việc phân biệt "không có dữ liệu" là bắt buộc về mặt phân tích.

Date / DateTime và cột dẫn xuất

Luôn lưu thời gian bằng kiểu thời gian thật (Date, DateTime, DateTime64 cho độ phân giải mili/micro giây), không lưu chuỗi. Kỹ thuật hay dùng: thêm cột Date MATERIALIZED dẫn xuất từ DateTime để dùng làm khoá ORDER BY/PARTITION BY — vừa gọn (2 byte) vừa cho prune partition hiệu quả (đã thấy ở bài 3):

txn_time  DateTime,
txn_date  Date MATERIALIZED toDate(txn_time)  -- tự tính, không cần app gửi

Denormalize — tư duy ngược với OLTP

Ở OLTP (PostgreSQL/Oracle) ta chuẩn hoá (normalize) để tránh trùng lặp và bảo toàn tính nhất quán khi UPDATE/DELETE. Ở ClickHouse thì ngược lại: JOIN yếu và tốn kém hơn nhiều so với OLTP, và dữ liệu phân tích thường bất biến (append-only). Vì vậy xu hướng chủ đạo là denormalize — nhồi các thuộc tính hay dùng thẳng vào bảng sự kiện (bảng rộng, wide table).

Ví dụ: thay vì bảng transactions tinh gọn rồi JOIN sang accounts và customers mỗi lần truy vấn, ta có thể nhồi sẵn vài cột hay dùng của khách/tài khoản vào ngay bảng giao dịch:

CREATE TABLE transactions_wide
(
  txn_id        UInt64,
  account_no    String,
  amount        Decimal(18, 2),
  currency      LowCardinality(String),
  kind          Enum8('debit'=1,'credit'=2),
  txn_time      DateTime,
  -- các cột denormalize từ account/customer:
  cust_segment  LowCardinality(String),   -- 'retail','sme','corp'
  branch_code   LowCardinality(String),
  customer_city LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (toDate(txn_time), branch_code, account_no);

Đánh đổi: bảng phình to hơn (nhưng ClickHouse nén rất tốt, đặc biệt các cột LowCardinality lặp lại), và nếu thuộc tính khách thay đổi thì các bản ghi cũ vẫn giữ giá trị lúc phát sinh — thường lại đúng về mặt phân tích (snapshot tại thời điểm giao dịch). Khi cần "giá trị mới nhất", dùng dictionary (dưới đây).

Dictionary — thay JOIN cho bảng lookup

Với các bảng tra cứu nhỏ, thay đổi chậm (danh mục chi nhánh, tỉ giá, mã tiền tệ, phân khúc khách), ClickHouse có dictionary — cấu trúc tra cứu trong bộ nhớ, nạp từ nguồn ngoài (PostgreSQL, file, HTTP...) và tự làm mới định kỳ. Thay vì JOIN, ta tra bằng hàm dictGet() — cực nhanh vì dictionary nằm sẵn trong RAM.

-- Khai báo dictionary lấy tên chi nhánh từ PostgreSQL (minh hoạ)
CREATE DICTIONARY dict_branch
(
  branch_code String,
  branch_name String,
  region      String
)
PRIMARY KEY branch_code
SOURCE(POSTGRESQL(host 'pg' port 5432 user 'ro' db 'core' table 'branches'))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(MIN 300 MAX 600);   -- làm mới mỗi 5-10 phút

-- Dùng khi truy vấn: tra tên chi nhánh mà không cần JOIN
SELECT
  branch_code,
  dictGet('dict_branch', 'branch_name', branch_code) AS branch_name,
  count() AS n_txn,
  sum(amount) AS total
FROM transactions_wide
GROUP BY branch_code;

Dictionary là "điểm cân bằng" đẹp: giữ được tính chuẩn hoá cho dữ liệu tham chiếu (một chỗ cập nhật, tự làm mới), nhưng tra cứu nhanh như denormalize. Với dữ liệu tham chiếu thay đổi, dùng dictionary tốt hơn nhồi cứng vào bảng sự kiện.

Use case thực tế

Bài toán: NCB cần một dashboard giám sát giao dịch gần real-time. Core banking phát sự kiện giao dịch lên Kafka topic txn_events (~15.000 msg/giây giờ cao điểm, JSON). Yêu cầu: dashboard trễ tối đa vài giây; báo cáo cuối ngày quét cả tháng < 2 giây; giữ 13 tháng lịch sử.

Thiết kế đã triển khai:

1. Ingest qua Kafka + MV (không insert trực tiếp từ core banking). Lý do: Kafka làm bộ đệm bền, và Kafka engine tự gom lô — mỗi lần MV flush đẩy ~10.000–50.000 dòng, tránh hoàn toàn lỗi Too many parts. Cấu hình kafka_num_consumers = 6 khớp 6 partition của topic.
2. Schema tối ưu:
   • amount Decimal(18,2) — không dùng Float để tổng tiền chính xác tuyệt đối.
   • currency, channel, kind (Enum8) — kiểu hẹp/LowCardinality; currency chỉ ~8 giá trị nên nén còn vài % dung lượng thô.
   • account_no String (KHÔNG LowCardinality vì hàng triệu tài khoản).
   • Bỏ toàn bộ Nullable: note String DEFAULT '', các cột thiếu dùng sentinel.
   • PARTITION BY toYYYYMM(txn_time) + ORDER BY (txn_date, branch_code, account_no) để prune theo tháng và lọc theo chi nhánh nhanh.
3. Dictionary dict_branch, dict_currency_rate (tỉ giá) làm mới mỗi 5 phút → dashboard quy đổi VND bằng dictGet thay vì JOIN.
4. TTL 13 tháng (xem bài 3) tự xoá partition cũ.

Kết quả (đo trên môi trường staging, minh hoạ theo cấu hình thực): độ trễ ingest từ Kafka tới bảng đọc được ~1–3 giây; truy vấn tổng giao dịch 1 tháng theo chi nhánh ~0,4 giây; dung lượng sau nén ~9x nhỏ hơn dữ liệu JSON thô nhờ LowCardinality + Decimal + Date. Sự cố Too many parts biến mất hoàn toàn sau khi chuyển từ insert trực tiếp sang mô hình Kafka + MV.

Để so sánh, cùng luồng này nếu nạp vào PostgreSQL sẽ vấp nghẽn ghi và quét tuần tự khi báo cáo tháng — đúng ranh giới OLTP/OLAP đã bàn ở bài 1.

Ghi nhớ

• Chèn lô lớn, ít lần. Mỗi INSERT tạo ít nhất một part; insert từng dòng → nhiều part nhỏ → merge quá tải → lỗi Too many parts. Nhắm hàng chục nghìn+ dòng mỗi lô, ≤ ~1 insert/giây/bảng.
• Async insert để server tự gom lô khi phía ghi không kiểm soát được lô; giữ wait_for_async_insert = 1 cho dữ liệu cần bền.
• Nạp file qua clickhouse-client ... FORMAT (CSV/JSONEachRow/Parquet) hoặc hàm bảng file()/url()/s3() cho ETL/backfill; Parquet nạp nhanh vì cùng dạng cột.
• Kafka ingest = 3 tầng: bảng Kafka engine (cửa ngõ, không lưu) → Materialized View (trigger) → bảng MergeTree đích. Kafka engine tự batch nên tránh part nhỏ; đây là mô hình streaming chuẩn.
• Chọn kiểu hẹp: UInt nhỏ, Decimal cho tiền (KHÔNG Float), Date/DateTime thay chuỗi.
• LowCardinality(String) cho cột bản số thấp (currency, channel, status); KHÔNG cho cột bản số cao (account_no). Enum cho tập giá trị cố định.
• Tránh Nullable — tốn cột phụ, nén kém; dùng giá trị mặc định/sentinel (0, '').
• Denormalize thay vì chuẩn hoá: JOIN ở ClickHouse yếu, nên nhồi cột hay dùng vào bảng rộng; dùng dictionary + dictGet cho dữ liệu tham chiếu thay đổi để tra cứu nhanh mà không JOIN.