เอนจิน MergeTree

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

คู่มือนี้ครอบคลุมเอนจินที่คุณจะใช้ในแล็บแท็กซี่ NYC และจุดที่พลาดง่ายซึ่งสะดุดขาคนที่ย้ายระบบจาก Snowflake ทุกคน

MergeTree คืออะไร

MergeTree เป็นเอนจินสตอเรจหลักของ ClickHouse ข้อมูลถูกเขียนลงไฟล์แบบคอลัมน์ที่แก้ไขไม่ได้ ซึ่งเรียกว่า part ClickHouse merge part เข้าด้วยกันเป็นระยะในเบื้องหลัง — เรียงลำดับ, บีบอัด และแปลงข้อมูลตามกฎของเอนจินเมื่อจำเป็น

ผลที่ตามมาซึ่งสำคัญที่สุด: การอ่านอาจเห็นแถวเดียวกันหลายเวอร์ชันจนกว่าจะมีการ merge เกิดขึ้น เอนจินส่วนใหญ่จัดการเรื่องนี้ให้อย่างโปร่งใส แต่บางตัว (โดยเฉพาะ ReplacingMergeTree) บังคับให้คุณต้องเข้าใจวงจรชีวิตของการ merge เพื่อเขียนคิวรีให้ถูกต้อง

เมื่อคุณสร้างตาราง MergeTree คุณต้องระบุ ORDER BY ซึ่งเป็นตัวกำหนด:

1. ลำดับการเรียงทางกายภาพของข้อมูลภายในแต่ละ part
2. primary index (แบบ sparse, ระดับบล็อก, เก็บอยู่ในหน่วยความจำ)
3. สำหรับเอนจินที่กำจัดข้อมูลซ้ำ: คอลัมน์ใดเป็นตัวกำหนด "คีย์" ที่ใช้กำจัดข้อมูลซ้ำ

ไม่มีแนวคิดแยกของ primary key, clustered index หรือ distribution key ORDER BY เป็นทั้งหมดนั้นในตัวเดียว

MergeTree

ใช้เมื่อ: ตารางเป็นแบบเขียนต่อท้ายเท่านั้น หรือการอัปเดตถูกจัดการจากภายนอก ไม่ต้องกำจัดข้อมูลซ้ำ

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

คุณลักษณะ:

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

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

ReplacingMergeTree

ใช้เมื่อ: แถวอาจถูกอัปเดตได้ (เช่น การแก้ไขค่าโดยสาร, การเปลี่ยนสถานะ) และคุณต้องการหนึ่งแถวต่อหนึ่งคีย์ในผลคิวรี

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

คุณลักษณะ:

• ระหว่างการ merge เบื้องหลัง แถวที่มีคีย์ ORDER BY เหมือนกันจะถูกกำจัดข้อมูลซ้ำ: เก็บไว้เฉพาะแถวที่มี ค่าคอลัมน์เวอร์ชันสูงสุด
• คอลัมน์เวอร์ชัน (ที่นี่คือ updated_at) กำหนดว่าแถวไหนชนะ — ค่าสูงกว่า = ใหม่กว่า = ถูกเก็บไว้
• การกำจัดข้อมูลซ้ำเป็นแบบ อะซิงโครนัส — จนกว่าจะมีการ merge เกิดขึ้น ทั้งเวอร์ชันเก่าและใหม่จะอยู่ร่วมกัน

จุดที่พลาดง่ายที่สำคัญที่สุด: ความหน่วงของการกำจัดข้อมูลซ้ำ

ระหว่างการ merge คิวรีที่ไม่มี FINAL จะเห็น ทุก เวอร์ชันของแถว:

-- 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 บังคับให้กำจัดข้อมูลซ้ำตอนอ่าน มันช้ากว่าการอ่านโดยไม่มี FINAL เพราะ ClickHouse ต้องตรวจทุก part เพื่อหาคีย์ที่ซ้ำ ในแล็บแท็กซี่ NYC คิวรีทุกตัวที่ทำกับ fact_trips ใช้ FINAL

เมื่อมันพลาด:

• ลืมใส่ FINAL ในการค้นหาแบบจุดเดียว → คืนแถวซ้ำอย่างเงียบ ๆ; ค่ารวมนับเกิน
• ใช้คอลัมน์เวอร์ชันผิด (คอลัมน์ที่ไม่เพิ่มค่าตอนอัปเดต) → ค่าที่เก่ากว่าชนะ
• ใช้ MergeTree แทน RMT กับตารางที่เปลี่ยนแปลงได้ → ทุกเวอร์ชันสะสมไว้; จำนวนแถวโตอย่างไม่มีขอบเขต
• คาดหวังการกำจัดข้อมูลซ้ำแบบซิงโครนัส → งาน ETL อ่านทันทีหลัง insert แล้วเห็นข้อมูลซ้ำ

RMT กับ dbt: กลยุทธ์ incremental แบบ delete_insert ลบแถวในช่วงคีย์ของแบตช์ที่เข้ามาก่อนจะแทรก ตารางจึงไม่มีข้อมูลซ้ำตั้งแต่แรก ยังแนะนำให้ใช้ FINAL เพื่อความปลอดภัย แต่มันสำคัญน้อยลงเมื่อกลยุทธ์ของ dbt ถูกต้อง

AggregatingMergeTree

ใช้เมื่อ: ตารางเก็บสถานะการรวมข้อมูลบางส่วน (partial aggregation state) ที่ควรถูก merge ระหว่างการ merge เบื้องหลัง และนำมารวมกันตอนคิวรี

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

คุณลักษณะ:

• แถวที่มีคีย์ ORDER BY เหมือนกันจะถูก merge ด้วยตรรกะ combiner ของฟังก์ชัน aggregate
• ตอนคิวรีใช้ combiner ที่ลงท้ายด้วย -Merge: sumMerge(fare_sum), countMerge(trip_count)
• ปกติถูกป้อนข้อมูลด้วย materialized view ที่แปลงข้อมูล insert ดิบให้เป็นสถานะบางส่วน

ควรใช้เมื่อไร: AggregatingMergeTree มีไว้สำหรับข้อมูลที่รวมไว้ล่วงหน้าซึ่งสถานะบางส่วนต้องนำมารวมกันได้ ในแล็บแท็กซี่ NYC ตาราง agg_hourly_zone_trips ถูกสร้างใหม่โดย dbt ทุกครั้งที่รัน — มันเป็นตารางที่ถูกแทนที่ทั้งหมด ไม่ใช่ตัวสะสมสถานะบางส่วน ตรงนั้นให้ใช้ ReplacingMergeTree

เมื่อมันพลาด: การใช้ sum(fare_sum) แทน sumMerge(fare_sum) ตอนคิวรีจะตีความสถานะ aggregate แบบไบนารีเป็น Float64 และคืนตัวเลขขยะออกมา นี่เป็นข้อผิดพลาดด้านความถูกต้องแบบเงียบ ๆ

CollapsingMergeTree

ใช้เมื่อ: คุณต้องลบหรืออัปเดตแถวด้วยการ insert "แถว sign" (sign=1 สำหรับการเพิ่ม, sign=-1 สำหรับการยกเลิก) พบไม่บ่อยแต่มีประโยชน์กับรูปแบบ CDC ที่อิงอีเวนต์

ENGINE = CollapsingMergeTree(sign)

ระหว่างการ merge แถวคู่ที่มี sign=1 และ sign=-1 สำหรับคีย์เดียวกันจะหักลบกันหมดไป ไม่ได้ใช้ในแล็บแท็กซี่ NYC — ReplacingMergeTree ที่มีคอลัมน์เวอร์ชันเรียบง่ายกว่าสำหรับรูปแบบ insert แล้วรีทรายของเวิร์กโหลดนี้

MergeTree ที่มี TTL

เพิ่มการหมดอายุของข้อมูลตามเวลาให้กับ MergeTree รูปแบบใดก็ได้:

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 ทำงานระหว่างการ merge เบื้องหลัง แถวที่หมดอายุจะถูกลบออกจาก part เมื่อ part นั้นถูก merge ในแล็บนี้ไม่ได้ตั้งค่า TTL — ข้อมูลทั้ง 4 ปีถูกเก็บไว้ทั้งหมด ในการใช้งานจริง TTL จำเป็นต่อการควบคุมค่าสตอเรจ

การเลือกเอนจิน: แผนผังการตัดสินใจ

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

สำหรับแล็บแท็กซี่ NYC:

การออกแบบ ORDER BY

ORDER BY เป็นการตัดสินใจด้านประสิทธิภาพที่สำคัญที่สุดในตาราง ClickHouse มันกำหนด:

1. ประสิทธิภาพของ primary index — คิวรีที่กรองด้วยคอลัมน์ที่เป็น prefix ของ ORDER BY จะข้ามบล็อกที่ไม่เกี่ยวข้องไป
2. อัตราการบีบอัด — ข้อมูลที่เรียงแล้วบีบอัดได้ดีกว่า (ค่าที่คล้ายกันอยู่ติดกัน)
3. คีย์สำหรับกำจัดข้อมูลซ้ำ (สำหรับ RMT/AMT) — สองแถวจะถือว่าซ้ำกันก็เมื่อคอลัมน์ ORDER BY ของมันตรงกันเท่านั้น

กฎในการออกแบบ ORDER BY:

1. วาง คอลัมน์ที่มี cardinality ต่ำไว้ก่อน (เช่น borough, payment_type): แถวจำนวนมากกว่าใช้ค่าร่วมกัน อินเด็กซ์จึงข้ามบล็อกได้มากกว่า
2. วาง คอลัมน์ที่มี cardinality สูงไว้ท้าย (เช่น trip_id, UUID): มันช่วยจำกัดช่วงให้แคบลง แต่ถ้าอยู่หน้าสุดจะบีบอัดได้ไม่ดีเท่า
3. อนุมานคอลัมน์จาก ตัวกรองของคิวรีที่เกิดขึ้นจริง ไม่ใช่จากสคีมาต้นทาง
4. สำหรับตาราง RMT คอลัมน์สุดท้ายควรเป็น ตัวระบุแถวที่ไม่ซ้ำ (รับประกันหนึ่งแถวต่อหนึ่งคีย์ทางธุรกิจ)

แอนติแพตเทิร์น: คัดลอก primary key ของต้นทางมาเป็น ORDER BY ถ้า TRIPS_RAW ใน Snowflake ไม่มีการเรียงลำดับที่ระบุไว้ชัดเจน การคัดลอกลำดับของสคีมา Snowflake มา (trip_id อยู่ก่อน) ทำให้ ClickHouse ได้ ORDER BY แบบสุ่ม — ไม่มีการข้ามบล็อกให้คิวรีวิเคราะห์ใด ๆ เลย

ตัวอย่างการอนุมานสำหรับ fact_trips:

คิวรี Q1–Q7 ทั้งหมดกรองด้วย pickup_at ในรูปแบบใดรูปแบบหนึ่ง:

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

ดังนั้น pickup_at ต้องอยู่ใน ORDER BY และควรอยู่ใกล้หน้าสุด การใช้ toStartOfMonth(pickup_at) เป็นคอลัมน์แรกสร้าง prefix ที่หยาบกว่า ซึ่งเปิดทางให้ตัดข้อมูลในระดับพาร์ทิชันได้แม้ไม่มี clause PARTITION BY ส่วน trip_id ไปอยู่ท้ายสุดเพื่อความไม่ซ้ำของ RMT

ผลลัพธ์: ORDER BY (toStartOfMonth(pickup_at), pickup_at, trip_id)

PARTITION BY

PARTITION BY เป็นทางเลือกและแยกจาก ORDER BY มันสร้างพาร์ทิชันเป็นไดเรกทอรีทางกายภาพ — แต่ละพาร์ทิชันเป็นชุดของ part ที่เป็นอิสระจากกัน

PARTITION BY toYYYYMM(pickup_at)

ใช้ PARTITION BY เมื่อ:

• คุณต้อง DROP ช่วงเวลาทั้งช่วงอย่างมีประสิทธิภาพ (ALTER TABLE DROP PARTITION '202401')
• คุณต้องการให้ TTL ทำงานเป็นรายเดือนแทนที่จะเป็นรายแถว
• ตารางใหญ่มาก (>1TB) และเมทาดาทาระดับพาร์ทิชันจะช่วยการวางแผนคิวรี

อย่าใช้ PARTITION BY แทน ORDER BY ความผิดพลาดที่พบบ่อยคือใส่ toYYYYMM(date) ใน PARTITION BY แล้วละเว้นมันจาก ORDER BY — นี่ขัดขวางการข้ามบล็อกภายในพาร์ทิชัน

ในแล็บแท็กซี่ NYC ไม่จำเป็นต้องใช้ PARTITION BY — ดาต้าเซ็ตมี 50M แถว (~8GB เมื่อบีบอัด) อยู่ในช่วงประสิทธิภาพของพาร์ทิชันเดียวอย่างสบาย

สรุปจุดที่พลาดง่ายที่สำคัญ