MergeTree engines

ClickHouse lưu toàn bộ dữ liệu trong các bảng dựa trên một trong các biến thể engine MergeTree. Nếu bạn đến từ Snowflake, không có khái niệm tương đương — Snowflake xử lý mọi quyết định lưu trữ ở bên trong. Trong ClickHouse, chọn đúng engine là trách nhiệm của bạn, và chọn sai sẽ tạo ra kết quả sai một cách âm thầm.

Hướng dẫn này bao quát các engine bạn sẽ dùng trong lab NYC Taxi và những bẫy làm sập chân mọi người di chuyển từ Snowflake.

MergeTree là gì?

MergeTree là engine lưu trữ chính của ClickHouse. Dữ liệu được ghi vào các file cột bất biến gọi là part. ClickHouse định kỳ merge các part ở nền — sắp xếp, nén, và tùy engine mà biến đổi chúng theo quy tắc của engine đó.

Hệ quả then chốt: một lượt đọc có thể thấy nhiều phiên bản của cùng một dòng cho đến khi một lần merge xảy ra. Phần lớn engine xử lý việc này một cách trong suốt, nhưng một số (đặc biệt là ReplacingMergeTree) đòi hỏi bạn hiểu vòng đời merge để viết truy vấn đúng.

Khi tạo một bảng MergeTree, bạn buộc phải chỉ định ORDER BY. Nó quyết định:

1. Thứ tự sắp xếp vật lý của dữ liệu trong từng part
2. Primary index (thưa, ở mức block, lưu trong bộ nhớ)
3. Với các engine loại trùng, những cột nào định nghĩa "khóa" để loại trùng

Không có khái niệm riêng nào là primary key, clustered index hay distribution key. ORDER BY là tất cả những thứ đó cùng lúc.

MergeTree

Dùng khi: Bảng chỉ được chèn thêm, hoặc việc cập nhật được xử lý bên ngoài. Không cần loại trùng.

CREATE TABLE default.some_events (
    event_id      String,
    occurred_at   DateTime64(3, 'UTC'),
    payload       String
)
ENGINE = MergeTree()
ORDER BY (occurred_at, event_id);

Đặc điểm:

• Lệnh chèn nối thêm dữ liệu dưới dạng part mới
• Không loại trùng — các dòng trùng lặp được giữ nguyên
• Merge tối ưu lưu trữ và nén nhưng không thay đổi nội dung logic
• Truy vấn đọc tất cả các part khớp khoảng tiền tố của ORDER BY

Khi nào nó sai: Nếu bạn chèn cùng một dòng hai lần (ví dụ, thử lại sau một lỗi mạng), cả hai dòng đều xuất hiện trong kết quả truy vấn. Với các pipeline thực sự chỉ chèn thêm, nơi trùng lặp không thể xảy ra, điều này là đúng. Với bất kỳ bảng nhận cập nhật CDC hay các lượt nạp có thể thử lại, hãy dùng ReplacingMergeTree.

ReplacingMergeTree

Dùng khi: Các dòng có thể được cập nhật (ví dụ, sửa giá cước, đổi trạng thái). Bạn muốn một dòng cho mỗi khóa trong kết quả truy vấn.

CREATE TABLE analytics.fact_trips (
    trip_id       String,
    pickup_at     DateTime64(3, 'UTC'),
    fare_amount   Float64,
    updated_at    DateTime64(3, 'UTC'),
    -- ...
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (toStartOfMonth(pickup_at), pickup_at, trip_id);

Đặc điểm:

• Trong các lần merge ở nền, các dòng có cùng khóa ORDER BY được loại trùng: chỉ dòng có giá trị cột version cao nhất được giữ lại
• Cột version (ở đây là updated_at) quyết định dòng nào thắng — giá trị cao hơn = mới hơn = được giữ
• Việc loại trùng là bất đồng bộ — cho đến khi một lần merge chạy, cả phiên bản cũ và mới cùng tồn tại

Cái bẫy nghiêm trọng: độ trễ loại trùng

Giữa các lần merge, một truy vấn không có FINAL sẽ thấy tất cả các phiên bản của một dòng:

-- This may return multiple rows for the same trip_id
-- if the row has been updated since the last merge
SELECT * FROM analytics.fact_trips WHERE trip_id = 'abc123';

-- This returns exactly one row per trip_id, applying deduplication at query time
SELECT * FROM analytics.fact_trips FINAL WHERE trip_id = 'abc123';

FINAL buộc loại trùng tại thời điểm đọc. Nó chậm hơn đọc không có FINAL vì ClickHouse phải kiểm tra tất cả các part để tìm khóa trùng. Trong lab NYC Taxi, mọi truy vấn trên fact_trips đều dùng FINAL.

Khi nào nó sai:

• Bỏ FINAL trong một lượt tra cứu điểm → âm thầm trả về các dòng trùng; các hàm tổng hợp đếm vượt
• Dùng sai cột version (một cột không tăng khi cập nhật) → giá trị cũ hơn thắng
• Dùng MergeTree thay vì RMT cho một bảng có thay đổi → mọi phiên bản tích tụ; số dòng tăng không giới hạn
• Trông đợi loại trùng đồng bộ → job ETL đọc ngay sau khi chèn và thấy dữ liệu trùng

RMT với dbt: Chiến lược incremental delete_insert xóa các dòng trong khoảng khóa của batch đến trước khi chèn, nên bảng ngay từ đầu đã không có dòng trùng. Vẫn nên dùng FINAL cho an toàn nhưng nó ít then chốt hơn khi chiến lược dbt đã đúng.

AggregatingMergeTree

Dùng khi: Bảng lưu các trạng thái tổng hợp bộ phận cần được merge trong các lần merge ở nền và kết hợp lại tại thời điểm truy vấn.

CREATE TABLE analytics.agg_hourly_revenue (
    hour_bucket   DateTime,
    borough       String,
    fare_sum      AggregateFunction(sum, Float64),
    trip_count    AggregateFunction(count, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour_bucket, borough);

Đặc điểm:

• Các dòng có cùng khóa ORDER BY được merge bằng logic kết hợp của hàm tổng hợp
• Tại thời điểm truy vấn dùng các combinator hậu tố -Merge: sumMerge(fare_sum), countMerge(trip_count)
• Thường được nạp bởi một Materialized View chuyển các lượt chèn thô thành trạng thái bộ phận

Khi nào nên dùng: AggregatingMergeTree dành cho dữ liệu đã tổng hợp trước, nơi các trạng thái bộ phận phải kết hợp được. Trong lab NYC Taxi, agg_hourly_zone_trips được dbt dựng lại mỗi lần chạy — đó là một bảng thay thế toàn phần, không phải bộ tích lũy trạng thái bộ phận. Ở đó hãy dùng ReplacingMergeTree.

Khi nào nó sai: Dùng sum(fare_sum) thay vì sumMerge(fare_sum) tại thời điểm truy vấn sẽ coi trạng thái tổng hợp dạng nhị phân như một Float64 và trả về những con số rác. Đây là một lỗi đúng đắn âm thầm.

CollapsingMergeTree

Dùng khi: Bạn cần xóa hoặc cập nhật dòng bằng cách chèn một "dòng dấu" (sign=1 để chèn, sign=-1 để hủy). Ít gặp hơn nhưng hữu ích cho các mô hình CDC dựa trên sự kiện.

ENGINE = CollapsingMergeTree(sign)

Trong các lần merge, các cặp dòng có sign=1 và sign=-1 cho cùng một khóa triệt tiêu nhau. Không dùng trong lab NYC Taxi — ReplacingMergeTree với một cột version đơn giản hơn cho mô hình chèn-thử-lại của workload này.

MergeTree với TTL

Thêm cơ chế hết hạn dữ liệu theo thời gian cho bất kỳ biến thể MergeTree nào:

CREATE TABLE default.trips_raw (
    trip_id    String,
    pickup_at  DateTime64(3, 'UTC'),
    _synced_at DateTime DEFAULT now(),
    -- ...
)
ENGINE = ReplacingMergeTree(_synced_at)
ORDER BY (pickup_at, trip_id)
TTL toDate(pickup_at) + INTERVAL 2 YEAR;

TTL được kích hoạt trong các lần merge ở nền. Các dòng đã hết hạn bị loại khỏi part khi part đó được merge. Trong lab, TTL không được cấu hình — toàn bộ 4 năm dữ liệu được giữ lại. Trong production, TTL là thiết yếu để kiểm soát chi phí lưu trữ.

Chọn engine: cây quyết định

Does the table receive UPDATE or DELETE operations?
├── No (insert-only, e.g., event log, append-only stream)
│   └── MergeTree()
└── Yes
    ├── Do rows have a version/timestamp column that increases on update?
    │   ├── Yes → ReplacingMergeTree(version_col)
    │   └── No (full reload, e.g., dim tables rebuilt by dbt)
    │       └── MergeTree() — dbt atomic table swap (full rebuild) handles "upsert"
    └── Is the table a pre-aggregated accumulator with combinable states?
        └── AggregatingMergeTree()

Với lab NYC Taxi:

Thiết kế ORDER BY

ORDER BY là quyết định hiệu năng quan trọng nhất trong một bảng ClickHouse. Nó quyết định:

1. Hiệu quả của primary index — các truy vấn lọc trên những cột tiền tố của ORDER BY sẽ bỏ qua các block không liên quan
2. Tỷ lệ nén — dữ liệu đã sắp xếp nén tốt hơn (các giá trị giống nhau nằm cạnh nhau)
3. Khóa loại trùng (với RMT/AMT) — hai dòng chỉ trùng nhau nếu các cột ORDER BY của chúng khớp

Quy tắc thiết kế ORDER BY:

1. Đặt các cột có lực lượng thấp lên trước (ví dụ, borough, payment_type): nhiều dòng cùng chia sẻ một giá trị, nên index bỏ qua được nhiều block hơn
2. Đặt các cột có lực lượng cao xuống cuối (ví dụ, trip_id, UUID): chúng thu hẹp khoảng nhưng không nén tốt khi ở đầu
3. Suy ra các cột từ các bộ lọc truy vấn thực tế, không từ schema nguồn
4. Với bảng RMT, cột cuối cùng nên là định danh dòng duy nhất (bảo đảm một dòng cho mỗi khóa nghiệp vụ)

Phản mẫu: Sao chép primary key của nguồn làm ORDER BY. Nếu TRIPS_RAW của Snowflake không có thứ tự sắp xếp tường minh, sao chép thứ tự schema của Snowflake (trip_id trước) sẽ cho ClickHouse một ORDER BY ngẫu nhiên — không bỏ qua được block nào cho bất kỳ truy vấn phân tích nào.

Ví dụ suy ra cho fact_trips:

Các truy vấn Q1–Q7 đều lọc trên pickup_at theo hình thức nào đó:

• Q1: WHERE pickup_at >= ...
• Q2: ORDER BY week, pickup_location_id
• Q3: WHERE pickup_at >= CURRENT_DATE - 7
• Q4: GROUP BY DATE_TRUNC('day', pickup_at)

Vậy pickup_at phải nằm trong ORDER BY và nên ở gần đầu. Dùng toStartOfMonth(pickup_at) làm cột đầu tiên tạo ra một tiền tố thô hơn, cho phép lược bớt ở mức partition ngay cả khi không có mệnh đề PARTITION BY. trip_id xuống cuối để bảo đảm tính duy nhất cho RMT.

Kết quả: ORDER BY (toStartOfMonth(pickup_at), pickup_at, trip_id)

PARTITION BY

PARTITION BY là tùy chọn và tách biệt với ORDER BY. Nó tạo các partition thư mục vật lý — mỗi partition là một tập part độc lập.

PARTITION BY toYYYYMM(pickup_at)

Dùng PARTITION BY khi:

• Bạn cần DROP hiệu quả cả một khoảng thời gian (ALTER TABLE DROP PARTITION '202401')
• Bạn muốn TTL hoạt động theo tháng thay vì theo từng dòng
• Bảng rất lớn (>1TB) và metadata theo partition sẽ giúp cho việc lập kế hoạch truy vấn

KHÔNG dùng PARTITION BY để thay cho ORDER BY. Một lỗi phổ biến là đặt toYYYYMM(date) vào PARTITION BY và bỏ nó khỏi ORDER BY — điều này chặn việc bỏ qua block trong phạm vi một partition.

Với lab NYC Taxi, PARTITION BY là không cần thiết — tập dữ liệu có 50 triệu dòng (~8GB sau nén), nằm thoải mái trong tầm hiệu năng của một partition duy nhất.

Tóm tắt các cái bẫy chính