การดำเนินงาน ClickHouse

เอกสารนี้อธิบายแนวคิดของ ClickHouse ที่คุณจะพบในแล็บย้ายระบบนี้ — มันคืออะไร, มีไว้ทำไม และต่างจากโครงสร้างของ Snowflake ที่คุณใช้ใน Part 1 อย่างไร

1. เอนจินของตาราง

ClickHouse ไม่ใช่ฐานข้อมูลที่มีเอนจินเดียว ทุกตารางที่คุณสร้างต้องประกาศ เอนจิน ของมัน ซึ่งกำหนดว่าข้อมูลถูกเก็บบนดิสก์อย่างไร, ข้อมูลซ้ำถูกจัดการอย่างไร และมีความสามารถอะไรให้ใช้ การเลือกเอนจินผิดเป็นความผิดพลาดที่พบบ่อยที่สุดในการออกแบบสคีมาของ ClickHouse

MergeTree

เอนจินพื้นฐานสำหรับตารางในการใช้งานจริงแทบทั้งหมด

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

มันทำอะไร: ClickHouse เก็บข้อมูลเป็น part — ก้อนข้อมูลที่เรียงลำดับและบีบอัดแล้วบนดิสก์ เมื่อคุณ insert ข้อมูล part ใหม่จะถูกเขียนขึ้น ในเบื้องหลัง ClickHouse merge part เล็กเข้าเป็น part ใหญ่อย่างต่อเนื่อง โดยคงข้อมูลให้เรียงตามคีย์ ORDER BY ชื่อของเอนจินมาจากตรงนี้

ใช้เมื่อไร: ตารางใดก็ตามที่คุณไม่ต้องกำจัดข้อมูลซ้ำ และการ insert เป็นแบบเขียนต่อท้ายหรือโหลดเป็นก้อนใหญ่ (ตารางมิติ, ตารางอีเวนต์ดิบ, ตารางล็อก)

คุณสมบัติสำคัญ: ไม่มีการบังคับใช้ primary key สองแถวที่มีค่า ORDER BY เหมือนกันจะถูกเก็บไว้ทั้งคู่ ถ้าคุณต้องกำจัดข้อมูลซ้ำ ให้ใช้ ReplacingMergeTree

ReplacingMergeTree(version_col)

เอนจินสำหรับกำจัดข้อมูลซ้ำ ขยาย MergeTree ด้วยกฎหนึ่งข้อ: ระหว่างการ merge เบื้องหลัง ถ้าสองแถวมีคีย์ ORDER BY เหมือนกัน ให้เก็บเฉพาะแถวที่มีค่า version_col สูงสุด

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);

สำคัญ — eventual consistency: การกำจัดข้อมูลซ้ำเกิดขึ้นระหว่างการ merge เบื้องหลังเท่านั้น ณ ช่วงเวลาใดก็ตาม ตารางของคุณอาจมีแถวซ้ำอยู่ นี่เรียกว่า eventual consistency เพื่อให้ได้ผลลัพธ์ที่กำจัดข้อมูลซ้ำครบถ้วนตอนคิวรี ให้เพิ่ม FINAL ใน 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 ใช้มันอย่างไร: dbt-clickhouse adapter ใช้กลยุทธ์ incremental แบบ delete_insert เป็นกลไกหลัก — มันลบแถวที่คีย์ตรงกันอย่างชัดแจ้งแล้วแทรกแถวใหม่เข้าไป ซึ่งถูกต้องเสมอ ReplacingMergeTree ทำหน้าที่เป็น ตะแกรงกันพลาด ที่เก็บกวาดข้อมูลซ้ำที่หลุดรอดมา (เช่น จากการ insert บางส่วนที่ล้มเหลว)

สิ่งเทียบเท่าใน Snowflake: ไม่มีสิ่งเทียบเท่าโดยตรง ใน Snowflake คุณใช้ MERGE INTO ... WHEN MATCHED THEN UPDATE ClickHouse ไม่มีคำสั่ง MERGE — ReplacingMergeTree บวกกับ FINAL ให้ผลลัพธ์เชิงตรรกะเดียวกัน

Refreshable Materialized View

ClickHouse รองรับ materialized view สองแบบ

MV แบบทริกเกอร์ (แบบดั้งเดิม): ทำงานทุกครั้งที่มี INSERT และประมวลผลเฉพาะแบตช์ที่เพิ่งถูกแทรกเข้ามา

-- 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;

Refreshable MV (ตามกำหนดเวลา): รันคิวรีทั้งหมดซ้ำตามกำหนดเวลา เหมือนงาน cron

-- 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;

เมื่อไรใช้แบบไหน:

• แบบทริกเกอร์: การรวมข้อมูลแบบเรียลไทม์บนสตรีมการ insert ที่คุณต้องประมวลผลแค่ข้อมูลใหม่
• Refreshable: การรวมข้อมูลที่คิวรี fact_trips FINAL (ต้องเห็นทั้งตารางเพื่อกำจัดข้อมูลซ้ำ) หรือแดชบอร์ดที่ยอมรับความเก่าของข้อมูลไม่กี่นาทีเพื่อแลกกับตรรกะที่เรียบง่ายกว่า

การแก้ช่วงเวลารีเฟรช:

ALTER TABLE analytics.mv_hourly_revenue MODIFY REFRESH EVERY 60 SECOND;

2. คีย์การเรียงลำดับ (ORDER BY)

ใน Snowflake คุณใช้ CLUSTER BY เป็นคำใบ้ให้ตัวปรับแต่งคิวรี ใน ClickHouse ORDER BY คือ primary index — มันกำหนดลำดับการเรียงทางกายภาพของข้อมูลบนดิสก์ และเป็นตัวขับการสแกนช่วงทั้งหมด

มันทำงานอย่างไร

ClickHouse เก็บ primary index แบบ sparse: หนึ่งรายการอินเด็กซ์ต่อประมาณ 8,192 แถว (หนึ่ง data granule) เมื่อคุณกรองด้วยคอลัมน์ ORDER BY ClickHouse จะข้าม granule ทั้งก้อนไปโดยไม่อ่านมันเลย นี่คือเหตุผลที่ ClickHouse สแกนข้อมูลได้หลายพันล้านแถวต่อวินาที — ข้อมูลส่วนใหญ่ไม่เคยถูกอ่านออกจากดิสก์

ลำดับตาม cardinality มีความสำคัญ

วาง คอลัมน์ที่มี cardinality ต่ำไว้ก่อน และ คอลัมน์ที่มี cardinality สูงไว้ท้าย เสมอ นี่ให้อินเด็กซ์มีพลังข้ามข้อมูลสูงสุดในกรณีทั่วไป

-- 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)

Snowflake CLUSTER BY เทียบกับ ClickHouse ORDER BY

ตัวอย่าง: การจับคู่กับ cluster key ของ Snowflake ที่มีอยู่แล้ว

-- 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 (กล่าวถึงโดยย่อ)

สำหรับคอลัมน์ที่ไม่ได้อยู่ในคีย์ ORDER BY ClickHouse รองรับ skip index (bloom filter, minmax, set) ที่เก็บเมทาดาทาระดับคอลัมน์ต่อหนึ่ง granule มีประโยชน์เมื่อต้องกรองคอลัมน์ที่มี cardinality ต่ำซึ่งอยู่หลังคอลัมน์ที่มี cardinality สูงใน sort key

-- 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. การจัดการ JSON

ชนิดคอลัมน์ VARIANT ของ Snowflake รองรับสัญกรณ์ colon-path ในการไล่เข้าไปใน JSON ที่ซ้อนกัน ส่วน ClickHouse ใช้ฟังก์ชัน JSONExtract* อย่างชัดแจ้งแทน

ตารางแปลงแบบเทียบข้างกัน

รูปแบบต่าง ๆ ของฟังก์ชัน

-- 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')

เคล็ดลับด้านประสิทธิภาพ

ถ้าคุณคิวรีคอลัมน์ JSON เดียวกันซ้ำ ๆ ให้พิจารณาสกัดฟิลด์ออกมาเป็นคอลัมน์ที่มีชนิดข้อมูลชัดเจนที่ระดับโมเดล staging (ใน stg_trips.sql) แทนที่จะเรียก JSONExtractFloat ในทุกคิวรีปลายน้ำ นี่คือสิ่งที่โมเดล dbt ของแล็บนี้ทำ

4. ฟังก์ชันวันที่/เวลา

Snowflake และ ClickHouse มีความสามารถด้านวันที่/เวลาคล้ายกันแต่ไวยากรณ์ต่างกัน การแปลงที่พบบ่อยที่สุด:

ตารางแปลงแบบเทียบข้างกัน

ความสะดวกเพิ่มเติมที่มีเฉพาะใน 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

ไวยากรณ์ 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. ฟังก์ชันแบบประมาณค่า

ClickHouse ถูกสร้างมาสำหรับเวิร์กโหลดวิเคราะห์ ที่ซึ่งคำตอบแบบเป๊ะบนข้อมูลหลายพันล้านแถวช้ากว่าคำตอบแบบประมาณค่าที่แม่นพอสำหรับแดชบอร์ด ClickHouse มาพร้อมฟังก์ชัน aggregate แบบประมาณค่าในตัวหลายตัว

การนับค่าที่ไม่ซ้ำ

-- 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;

เปอร์เซ็นไทล์

-- 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;

หลักคิดคร่าว ๆ: ใช้ uniq และ quantileTDigest สำหรับแดชบอร์ดแบบอินเทอร์แอกทีฟ ใช้ uniqExact และ quantile เฉพาะเมื่อคุณต้องการค่าที่เป๊ะสำหรับการคิดเงิน, SLA หรือการกำกับดูแล

6. Dictionary

Dictionary คือ ตารางค้นหาที่อยู่ในหน่วยความจำ ซึ่ง ClickHouse เก็บให้พร้อมใช้และ join ไว้ล่วงหน้าให้ตอนคิวรี มันเป็นสิ่งเทียบเท่าใน ClickHouse ของตารางมิติเล็ก ๆ ที่คุณอยาก join ด้วยโดยไม่ต้องเสียต้นทุนของ JOIN เต็มรูปแบบ

มันคืออะไร

dictionary มีแหล่งข้อมูลรองรับอยู่ (ตาราง ClickHouse, ไฟล์ หรือฐานข้อมูลภายนอก) และถูกโหลดเข้าหน่วยความจำเมื่อเซอร์วิสเริ่มทำงาน หรือเมื่อคุณเรียก SYSTEM RELOAD DICTIONARIES การค้นหาทำผ่านคีย์ และคืนแอตทริบิวต์หนึ่งตัวหรือมากกว่า

ไวยากรณ์ 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

ตัวเลือกของ LAYOUT:

• FLAT() — อาร์เรย์ที่อินเด็กซ์ด้วยคีย์จำนวนเต็ม, เร็วที่สุด, ต้องใช้คีย์จำนวนเต็มที่เรียงต่อเนื่อง
• HASHED() — hash map, ใช้ได้กับคีย์จำนวนเต็มใด ๆ
• COMPLEX_KEY_HASHED() — hash map ที่ใช้คีย์ประกอบหรือคีย์ที่เป็นสตริง

การใช้ 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;

เมื่อไรใช้ dictionary เทียบกับ JOIN

7. Clause SAMPLE

ClickHouse รองรับการสุ่มตัวอย่างระดับแถวได้โดยตรงในไวยากรณ์คิวรี การสุ่มตัวอย่างอ่านข้อมูลเป็นสัดส่วนแบบกำหนดผลได้แน่นอน — มีประโยชน์สำหรับการวิเคราะห์เชิงสำรวจเมื่อคุณไม่ต้องการผลลัพธ์ที่เป๊ะ

ไวยากรณ์

-- 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;

การปรับสเกลผลลัพธ์

เมื่อสุ่มตัวอย่าง ให้คูณค่ารวมด้วย 1 / sample_rate เพื่อประมาณค่าของทั้งตาราง:

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

เมื่อไรควรใช้ SAMPLE

• การวิเคราะห์เชิงสำรวจ ("ตรรกะคิวรีของฉันถูกไหม") ก่อนจะรันบนทั้งตาราง
• ไทล์แดชบอร์ดที่ยอมรับค่าประมาณได้
• การเทรนโมเดล ML บนชุดข้อมูลย่อยที่เป็นตัวแทน

หมายเหตุ: SAMPLE ต้องการให้คีย์ ORDER BY ของตารางเริ่มด้วยคอลัมน์ที่ใช้สุ่มตัวอย่าง หรือคุณต้องเพิ่ม clause SAMPLE BY ในคำสั่ง CREATE TABLE ตาราง trips_raw ของแล็บนี้ถูกสร้างด้วย SAMPLE BY cityHash64(trip_id) เพื่อจุดประสงค์นี้

8. บันทึกเกี่ยวกับ dbt-clickhouse adapter

dbt-clickhouse adapter (dbt-clickhouse>=1.8) รองรับฟีเจอร์มาตรฐานของ dbt เกือบทั้งหมด แต่มีพฤติกรรมบางอย่างที่เฉพาะกับ ClickHouse ซึ่งคุณต้องเข้าใจ

กลยุทธ์ incremental แบบ delete_insert

ClickHouse ไม่มี MERGE INTO กลยุทธ์ delete_insert ของ dbt-clickhouse adapter จำลองมันขึ้นมา:

1. ลบแถวออกจากตารางเป้าหมายที่คอลัมน์คีย์ตรงกับแบตช์ที่เข้ามา
2. แทรกแถวที่เข้ามาทั้งชุด

-- 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;

ตั้งค่ามันในโมเดลของคุณ:

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

คอนฟิก engine และ order_by

ทุกตาราง MergeTree ต้องมีเอนจินและ ORDER BY ระบุทั้งสองอย่างในคอนฟิกของโมเดล dbt:

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

order_by แทน cluster_by

ในโมเดล dbt บน Snowflake คุณอาจเคยใช้ cluster_by ใน dbt-clickhouse ให้ใช้ order_by แทน ไม่มีสิ่งเทียบเท่ากับ cluster_by ของ Snowflake ใน ClickHouse — ORDER BY คือลำดับการเรียงทางกายภาพเสมอ

profiles.yml สำหรับ ClickHouse Cloud

ClickHouse Cloud ต้องใช้ TLS ตั้งค่า 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

การตั้งชื่อสคีมาและฐานข้อมูลที่แยกกัน

ClickHouse ใช้คำว่า database ในที่ที่ Snowflake ใช้ schema dbt-clickhouse adapter แมปสคีมาของ dbt ไปยังฐานข้อมูลของ ClickHouse แมโคร generate_schema_name ในโปรเจกต์นี้เขียนทับพฤติกรรมเริ่มต้นของ dbt เพื่อให้โมเดลที่มี +schema: analytics ไปลงในฐานข้อมูล analytics ไม่ใช่ 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 %}

นี่เป็นรูปแบบเดียวกับที่ใช้ในโปรเจกต์ dbt บน Snowflake (Part 1) — แมโครถูกทำให้เหมือนกันโดยเจตนา เพื่อให้พฤติกรรมการตั้งชื่อสคีมาสอดคล้องกันในทั้งสอง adapter