ClickHouse operations

Tài liệu này giải thích các khái niệm ClickHouse bạn sẽ gặp trong lab di chuyển này — chúng là gì, tồn tại để làm gì, và khác với các cấu trúc Snowflake bạn đã dùng ở Phần 1 như thế nào.

1. Table engine

ClickHouse không phải một cơ sở dữ liệu chỉ có một engine. Mọi bảng bạn tạo đều phải khai báo engine của nó, thứ quyết định dữ liệu được lưu trên đĩa như thế nào, dòng trùng được xử lý ra sao, và những khả năng nào khả dụng. Chọn sai engine là lỗi phổ biến nhất trong thiết kế schema ClickHouse.

MergeTree

Engine nền tảng cho hầu hết mọi bảng production.

CREATE TABLE analytics.dim_taxi_zones (
    zone_id      UInt16,
    borough      String,
    service_zone String
) ENGINE = MergeTree()
ORDER BY zone_id;

Nó làm gì: ClickHouse lưu dữ liệu trong các part — những khối đã sắp xếp và nén trên đĩa. Khi bạn chèn dữ liệu, các part mới được ghi ra. Ở nền, ClickHouse liên tục merge các part nhỏ thành part lớn hơn, giữ cho dữ liệu được sắp xếp theo khóa ORDER BY. Tên engine đến từ đó.

Khi nào dùng: Bất kỳ bảng nào bạn không cần loại trùng và các lượt chèn chỉ nối thêm hoặc là nạp hàng loạt (bảng chiều, bảng sự kiện thô, bảng log).

Tính chất then chốt: Không có việc cưỡng chế primary key. Hai dòng có giá trị ORDER BY giống nhau đều được lưu. Nếu bạn cần loại trùng, hãy dùng ReplacingMergeTree.

ReplacingMergeTree(version_col)

Engine loại trùng. Nó mở rộng MergeTree bằng một quy tắc: trong một lần merge ở nền, nếu hai dòng có cùng khóa ORDER BY, chỉ giữ dòng có giá trị version_col cao nhất.

CREATE TABLE analytics.fact_trips (
    trip_id     String,
    pickup_at   DateTime,
    total_amount Float64,
    updated_at  DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (pickup_at, trip_id);

Quan trọng — nhất quán cuối cùng: Việc loại trùng chỉ xảy ra trong các lần merge ở nền. Ở bất kỳ thời điểm nào, bảng của bạn có thể chứa các dòng trùng. Đó gọi là nhất quán cuối cùng (eventual consistency). Để có kết quả đã loại trùng hoàn toàn tại thời điểm truy vấn, hãy thêm FINAL vào SELECT:

-- Without FINAL: may return duplicates if merges haven't run
SELECT * FROM analytics.fact_trips WHERE trip_id = 'abc';

-- With FINAL: forces deduplication at query time (slower, always correct)
SELECT * FROM analytics.fact_trips FINAL WHERE trip_id = 'abc';

dbt dùng nó thế nào: Adapter dbt-clickhouse dùng chiến lược incremental delete_insert làm cơ chế chính — nó xóa tường minh các dòng có khóa khớp rồi chèn dòng mới, cách này luôn đúng. ReplacingMergeTree hoạt động như một lưới an toàn làm sạch mọi dòng trùng lọt qua (ví dụ, từ một lượt chèn bộ phận bị lỗi).

Tương đương trong Snowflake: Không có tương đương trực tiếp. Trong Snowflake bạn đã dùng MERGE INTO ... WHEN MATCHED THEN UPDATE. ClickHouse không có câu lệnh MERGE — ReplacingMergeTree cộng với FINAL đạt được cùng kết quả logic.

Refreshable Materialized View

ClickHouse hỗ trợ hai loại materialized view.

MV theo trigger (truyền thống): Thực thi trên mỗi lượt INSERT, chỉ xử lý batch vừa được chèn.

-- Trigger-based: only sees the rows inserted in the current batch
CREATE MATERIALIZED VIEW analytics.mv_realtime_counts
TO analytics.counts_table AS
SELECT pickup_date, count() AS trips
FROM default.trips_raw
GROUP BY pickup_date;

MV có thể refresh (theo lịch): Chạy lại toàn bộ truy vấn theo lịch, giống một cron job.

-- Refreshable: runs the full SELECT every 3 minutes
CREATE MATERIALIZED VIEW analytics.mv_hourly_revenue
REFRESH EVERY 180 SECOND AS
SELECT
    toStartOfHour(pickup_at) AS hour_bucket,
    pickup_borough,
    sum(total_amount)        AS revenue
FROM analytics.fact_trips FINAL
GROUP BY hour_bucket, pickup_borough;

Khi nào dùng loại nào:

• Theo trigger: tổng hợp thời gian thực trên các luồng chèn, khi bạn chỉ cần xử lý dữ liệu mới
• Có thể refresh: các phép tổng hợp truy vấn fact_trips FINAL (phải thấy toàn bảng để loại trùng), hoặc các dashboard chấp nhận dữ liệu cũ vài phút để đổi lấy logic đơn giản hơn

Sửa khoảng refresh:

ALTER TABLE analytics.mv_hourly_revenue MODIFY REFRESH EVERY 60 SECOND;

2. Khóa sắp xếp (ORDER BY)

Trong Snowflake, bạn dùng CLUSTER BY như một gợi ý cho optimizer. Trong ClickHouse, ORDER BY chính là primary index — nó quyết định thứ tự sắp xếp vật lý của dữ liệu trên đĩa và chi phối mọi lượt quét theo khoảng.

Cách nó hoạt động

ClickHouse lưu một primary index thưa: một mục index cho mỗi khoảng ~8.192 dòng (một granule dữ liệu). Khi bạn lọc theo các cột ORDER BY, ClickHouse bỏ qua trọn cả granule mà không đọc chúng. Đó là lý do ClickHouse quét được hàng tỷ dòng mỗi giây — phần lớn dữ liệu không bao giờ rời khỏi đĩa.

Thứ tự theo lực lượng có ý nghĩa

Luôn đặt các cột có lực lượng thấp lên trước, các cột có lực lượng cao xuống cuối. Điều này cho index khả năng bỏ qua tối đa trong trường hợp thông thường.

-- Good: low cardinality (borough, ~6 values) first, then high cardinality (trip_id)
ORDER BY (pickup_borough, toStartOfMonth(pickup_at), trip_id)

-- Bad: high cardinality first — the index can't skip anything useful
ORDER BY (trip_id, pickup_borough, pickup_at)

CLUSTER BY của Snowflake vs ORDER BY của ClickHouse

Ví dụ: khớp với một cluster key Snowflake đang có

-- Snowflake
CLUSTER BY (DATE_TRUNC('month', PICKUP_AT), PICKUP_LOCATION_ID)

-- ClickHouse equivalent
ORDER BY (toStartOfMonth(pickup_at), pickup_location_id, trip_id)
-- Note: trip_id added as tiebreaker to ensure unique sort order

Skip index (nhắc ngắn)

Với các cột không nằm trong khóa ORDER BY, ClickHouse hỗ trợ skip index (bloom filter, minmax, set) lưu metadata ở mức cột cho từng granule. Hữu ích khi lọc trên các cột lực lượng thấp xuất hiện sau một cột lực lượng cao trong khóa sắp xếp.

-- Add a bloom filter skip index on payment_type
ALTER TABLE analytics.fact_trips
ADD INDEX idx_payment_type payment_type TYPE bloom_filter GRANULARITY 4;

3. Xử lý JSON

Kiểu cột VARIANT của Snowflake hỗ trợ ký hiệu colon-path để đi xuyên JSON lồng nhau. ClickHouse dùng các hàm JSONExtract* tường minh thay cho việc đó.

Bảng dịch song song

Các biến thể hàm

-- Float (returns 0.0 if key missing or wrong type)
JSONExtractFloat(trip_metadata, 'driver', 'rating')

-- String (returns '' if missing)
JSONExtractString(trip_metadata, 'app', 'version')

-- Integer (returns 0 if missing)
JSONExtractInt(trip_metadata, 'driver', 'id')

-- Bool (returns 0/1)
JSONExtractBool(trip_metadata, 'app', 'is_shared')

-- Raw value as string (preserves JSON sub-object)
JSONExtractRaw(trip_metadata, 'route')

Mẹo hiệu năng

Nếu bạn truy vấn cùng một cột JSON nhiều lần, hãy xem xét trích xuất các trường thành cột có kiểu ở mức model staging (trong stg_trips.sql) thay vì gọi JSONExtractFloat trong mọi truy vấn xuôi dòng. Đây chính là điều các model dbt của lab này làm.

4. Các hàm ngày/giờ

Snowflake và ClickHouse có năng lực ngày/giờ tương tự nhau nhưng cú pháp khác nhau. Những phép dịch phổ biến nhất:

Bảng dịch song song

Vài tiện lợi chỉ có ở ClickHouse

yesterday()              -- today() - 1 day
toStartOfWeek(col)       -- Monday of the containing week
toStartOfQuarter(col)    -- first day of the quarter
toYear(col)              -- extract year as integer
toMonth(col)             -- extract month as integer (1-12)
toDayOfWeek(col)         -- 1=Monday, 7=Sunday

Cú pháp interval

-- ClickHouse
now() - INTERVAL 7 DAY
now() - INTERVAL 1 HOUR
now() - INTERVAL 30 MINUTE
pickup_at + INTERVAL 90 SECOND

-- Snowflake equivalent
DATEADD('day', -7, CURRENT_TIMESTAMP())
DATEADD('hour', -1, CURRENT_TIMESTAMP())

5. Các hàm xấp xỉ

ClickHouse được xây cho các workload phân tích, nơi câu trả lời chính xác trên hàng tỷ dòng chậm hơn câu trả lời xấp xỉ vốn đã đủ chính xác cho dashboard. ClickHouse có sẵn nhiều hàm tổng hợp xấp xỉ tích hợp.

Đếm giá trị phân biệt

-- Exact (like Snowflake COUNT(DISTINCT ...))
SELECT uniqExact(trip_id) FROM analytics.fact_trips FINAL;

-- Approximate — good for "how many unique passengers today?"
SELECT uniq(passenger_id) FROM analytics.fact_trips FINAL;

Phân vị

-- P95 trip duration — approximate but uses O(1) memory
SELECT quantileTDigest(0.95)(duration_minutes)
FROM analytics.fact_trips FINAL;

-- Multiple percentiles in one pass
SELECT quantileTDigestMerge(0.5)(state), quantileTDigestMerge(0.95)(state)
FROM analytics.fact_trips FINAL;

Nguyên tắc thực dụng: Dùng uniq và quantileTDigest cho dashboard tương tác. Chỉ dùng uniqExact và quantile khi bạn cần giá trị chính xác cho tính phí, SLA hoặc tuân thủ.

6. Dictionary

Dictionary là các bảng tra cứu trong bộ nhớ mà ClickHouse giữ nóng và join sẵn tại thời điểm truy vấn. Chúng là tương đương trong ClickHouse của một bảng chiều nhỏ mà bạn muốn join mà không phải trả giá cho một JOIN đầy đủ.

Chúng là gì

Một dictionary dựa trên một nguồn (một bảng ClickHouse, một file, hoặc một cơ sở dữ liệu bên ngoài) và được nạp vào bộ nhớ khi service khởi động hoặc khi bạn gọi SYSTEM RELOAD DICTIONARIES. Việc tra cứu diễn ra qua một khóa, trả về một hoặc nhiều thuộc tính.

Cú pháp CREATE DICTIONARY

-- From 04_create_dictionary.sql
CREATE DICTIONARY analytics.taxi_zones_dict (
    zone_id      UInt16,
    borough      String,
    service_zone String
)
PRIMARY KEY zone_id
SOURCE(CLICKHOUSE(
    TABLE 'dim_taxi_zones'
    DB    'analytics'
))
LIFETIME(MIN 300 MAX 600)   -- refresh every 5-10 minutes
LAYOUT(FLAT());             -- hash map, best for < 1M rows

Các tùy chọn LAYOUT:

• FLAT() — array được đánh chỉ số bằng khóa số nguyên, nhanh nhất, yêu cầu khóa số nguyên liên tiếp
• HASHED() — hash map, dùng được với mọi khóa số nguyên
• COMPLEX_KEY_HASHED() — hash map với khóa phức hợp hoặc khóa dạng chuỗi

Cách dùng dictGet

-- Instead of: JOIN analytics.dim_taxi_zones USING (zone_id)
SELECT
    trip_id,
    dictGet('analytics.taxi_zones_dict', 'borough', toUInt64(pickup_location_id)) AS pickup_borough,
    dictGet('analytics.taxi_zones_dict', 'borough', toUInt64(dropoff_location_id)) AS dropoff_borough
FROM analytics.fact_trips FINAL;

Khi nào dùng dictionary thay vì JOIN

7. Mệnh đề SAMPLE

ClickHouse hỗ trợ lấy mẫu ở mức dòng ngay trong cú pháp truy vấn. Lấy mẫu đọc một phần dữ liệu theo cách tất định — hữu ích cho phân tích khám phá khi bạn không cần kết quả chính xác.

Cú pháp

-- Read approximately 10% of rows
SELECT count(), avg(total_amount)
FROM analytics.fact_trips SAMPLE 0.1;

-- Read a specific number of rows (approximately)
SELECT trip_id, pickup_at, total_amount
FROM analytics.fact_trips SAMPLE 1000000;

Quy đổi kết quả

Khi lấy mẫu, hãy nhân các giá trị tổng hợp với 1 / sample_rate để ước lượng giá trị trên toàn bảng:

-- Estimate total revenue from 10% sample
SELECT sum(total_amount) * 10 AS estimated_total_revenue
FROM analytics.fact_trips SAMPLE 0.1;

Khi nào dùng SAMPLE

• Phân tích khám phá ("logic truy vấn của tôi có đúng không?") trước khi chạy trên toàn bảng
• Các ô dashboard nơi giá trị xấp xỉ là chấp nhận được
• Huấn luyện model ML trên một tập con đại diện

Lưu ý: SAMPLE yêu cầu khóa ORDER BY của bảng phải bắt đầu bằng cột lấy mẫu, hoặc bạn phải thêm mệnh đề SAMPLE BY vào câu lệnh CREATE TABLE. Bảng trips_raw của lab được tạo với SAMPLE BY cityHash64(trip_id) chính vì mục đích này.

8. Ghi chú về adapter dbt-clickhouse

Adapter dbt-clickhouse (dbt-clickhouse>=1.8) hỗ trợ phần lớn tính năng dbt tiêu chuẩn nhưng có một số hành vi riêng của ClickHouse mà bạn cần hiểu.

Chiến lược incremental delete_insert

ClickHouse không có MERGE INTO. Chiến lược delete_insert của adapter dbt-clickhouse mô phỏng nó:

1. Xóa các dòng khỏi bảng đích nơi (các) cột khóa khớp với batch đến
2. Chèn toàn bộ tập dòng đến

-- What dbt generates for incremental models
DELETE FROM analytics.fact_trips WHERE trip_id IN (SELECT trip_id FROM __dbt_tmp);
INSERT INTO analytics.fact_trips SELECT * FROM __dbt_tmp;

Cấu hình nó trong model của bạn:

{{
    config(
        materialized='incremental',
        incremental_strategy='delete_insert',
        unique_key='trip_id',
        engine='ReplacingMergeTree(updated_at)',
        order_by='(pickup_at, trip_id)'
    )
}}

Cấu hình engine và order_by

Mọi bảng MergeTree đều cần một engine và một ORDER BY. Hãy khai cả hai trong phần config của model dbt:

{{
    config(
        engine='MergeTree()',
        order_by='(zone_id)'
    )
}}

order_by thay cho cluster_by

Trong các model dbt trên Snowflake, có thể bạn đã dùng cluster_by. Với dbt-clickhouse, hãy dùng order_by thay thế. Không có gì tương đương với cluster_by của Snowflake trong ClickHouse — ORDER BY luôn là thứ tự sắp xếp vật lý.

profiles.yml cho ClickHouse Cloud

ClickHouse Cloud yêu cầu TLS. Đặt secure: true:

# ~/.dbt/profiles.yml
nyc_taxi_ch:
  target: dev
  outputs:
    dev:
      type: clickhouse
      host: "{{ env_var('CLICKHOUSE_HOST') }}"
      port: 8443
      user: default
      password: "{{ env_var('CLICKHOUSE_PASSWORD') }}"
      schema: analytics       # default database for models without a custom schema
      secure: true
      threads: 4

Đặt tên schema và các database riêng

ClickHouse dùng thuật ngữ database ở nơi Snowflake dùng schema. Adapter dbt-clickhouse ánh xạ schema của dbt sang database của ClickHouse. Macro generate_schema_name trong project này ghi đè hành vi mặc định của dbt để các model có +schema: analytics nằm trong database analytics, chứ không phải staging_analytics.

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
  {%- if custom_schema_name is none -%}
    {{ target.schema }}
  {%- else -%}
    {{ custom_schema_name }}
  {%- endif -%}
{%- endmacro %}

Đây chính là mô hình đã dùng trong project dbt trên Snowflake (Phần 1) — macro được giữ giống hệt một cách có chủ ý để hành vi đặt tên schema nhất quán trên cả hai adapter.