ใบงานที่ 3: การแปลงสคีมา

เวลาที่ใช้โดยประมาณ: 20–25 นาที เอกสารอ้างอิง: Snowflake เทียบกับ ClickHouse — ส่วนที่ 2 (ช่องว่างของสำเนียงภาษา SQL)

แนวคิด

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

หลักการสำคัญ:

1. ระบุความละเอียดให้ชัดเจน TIMESTAMP_NTZ(9) ของ Snowflake มีความละเอียดระดับ นาโนวินาที DateTime ของ ClickHouse มีความละเอียดเพียงระดับวินาที — อย่าใช้มันเป็น คอลัมน์เวอร์ชัน ใช้ DateTime64(3, 'UTC') เพื่อความละเอียดระดับมิลลิวินาที (ซึ่งตรงกับ ความต้องการในโลกจริงส่วนใหญ่) หรือ DateTime64(9, 'UTC') เพื่อระดับนาโนวินาที เรื่องนี้มีผลต่อความถูกต้อง: ถ้าคอลัมน์เวอร์ชันของ ReplacingMergeTree มีความละเอียด เพียงระดับวินาที การอัปเดตสองครั้งที่มาถึงในวินาทีเดียวกันจะไม่แน่นอน — ClickHouse ไม่สามารถระบุได้ว่าอันไหนใหม่กว่า

2. ใช้ชนิดข้อมูลจำนวนเต็มที่เล็กที่สุดที่ยังถูกต้อง INTEGER ของ Snowflake คือ NUMBER(38, 0) — ความละเอียดคงที่ 38 หลักที่จัดเก็บเป็นค่าขนาด 128 บิต ClickHouse มี จำนวนเต็มความกว้างคงที่: Int8, Int16, Int32, Int64, UInt8, UInt16, UInt32, UInt64 การเลือก UInt8 สำหรับ vendor_id (ค่า 1–3) ประหยัด 7 ไบต์ต่อแถวเทียบกับ Int64 ที่ 50M แถว นั่นคือ 350MB

3. VARIANT → String ClickHouse มีชนิดข้อมูล JSON ในตัว (พร้อมใช้ตั้งแต่ v25.3+ ใน ระดับพร้อมใช้งานจริง) แต่มันถูกออกแบบมาสำหรับสคีมาที่ไดนามิกจริง ๆ ซึ่งชื่อฟิลด์และ โครงสร้างยังไม่เป็นที่รู้ตอนสร้างตาราง สำหรับ trip_metadata ในแล็บนี้ โครงสร้างเป็นที่รู้ อยู่แล้ว (driver.rating, app.surge_multiplier และอื่น ๆ) — วิธีที่ดีกว่าคือแบนราบล่วงหน้า ให้เป็นคอลัมน์ที่มีชนิดข้อมูลชัดเจนระหว่างการย้ายข้อมูล หรือจัดเก็บเป็น String แล้วใช้ JSONExtract* ตอนคิวรี ใช้ชนิดข้อมูล JSON เมื่อคุณคาดเดาสคีมาไม่ได้จริง ๆ เช่น การนำเข้า payload ของอีเวนต์ลูกค้าตามอำเภอใจที่ทุกอีเวนต์มีฟิลด์ต่างกัน ("แบนราบ ล่วงหน้าให้เป็นคอลัมน์ที่มีชนิดข้อมูลชัดเจน" หมายถึงการดึงฟิลด์ออกมาเป็นคอลัมน์ระดับบนสุด แยกกันระหว่าง ETL — แบบเดียวกับที่ FACT_TRIPS.driver_rating ถูกผลิตขึ้นจาก trip_metadata — ไม่ใช่การห่อก้อน JSON นั้นเองไว้ใน Tuple คอลัมน์ Tuple ยังผูกมัดกับชุดฟิลด์คงที่ชุดเดียว จึงพังทันทีที่ metadata ของการเดินทางหนึ่งไม่ตรงรูปแบบนั้น)

4. ความละเอียดของ float FLOAT ของ Snowflake แมปไปเป็น Float64 ใน ClickHouse สำหรับ จำนวนเงินที่ต้องใช้เลขคณิตทศนิยมแบบเที่ยงตรง ให้ใช้ Decimal(18, 2) — แต่สำหรับ แล็บนี้ Float64 เพียงพอที่จะตรงกับต้นทาง (นี่เป็นค่าเริ่มต้น ไม่ใช่กฎว่าทุกคอลัมน์ FLOAT ต้องใช้ Float64 โดยไม่สนช่วงค่า: คอลัมน์อย่าง driver_rating ที่ค่าอยู่ระหว่าง 1.0–5.0 ที่ทศนิยมหนึ่งตำแหน่ง พอดีสบาย ๆ ในเลขนัยสำคัญราว 7 หลักของ Float32 — การเลือกที่นั่นขึ้นอยู่กับความเป็น nullable ไม่ใช่ความละเอียดที่กฎข้อ 4 กำลังปกป้องไว้ สำหรับจำนวนเงิน)

5. LowCardinality() — การปรับแต่งที่มีเฉพาะใน ClickHouse การห่อชนิดข้อมูลด้วย LowCardinality(String) (หรือ LowCardinality(UInt8) และอื่น ๆ) บอกให้ ClickHouse ใช้ การเข้ารหัสแบบพจนานุกรมสำหรับคอลัมน์นั้น — ค่าจะถูกจัดเก็บเป็นการอ้างอิงจำนวนเต็มไปยัง พจนานุกรมแทนที่จะเป็นสตริงซ้ำ ๆ โดยทั่วไปนี่ให้การบีบอัดดีขึ้น 2–5 เท่าและ GROUP BY เร็วขึ้นบนคอลัมน์สตริงที่มีค่าไม่ซ้ำน้อยกว่าราว 10,000 ค่า Snowflake ไม่มีสิ่งเทียบเท่า มันจัดการเรื่องนี้ให้อัตโนมัติ ตัวเลือกที่ดีในแล็บนี้: pickup_borough (6 ค่า), payment_type (6 ค่า), vehicle_type, vendor_name

แบบฝึกหัด: การแมปชนิดข้อมูลสำหรับ TRIPS_RAW

แมปแต่ละคอลัมน์จาก NYC_TAXI_DB.RAW.TRIPS_RAW ไปยังชนิดข้อมูลของ ClickHouse TRIP_ID ถูกกรอกไว้เป็นตัวอย่าง: String เป็นแนวทางที่เข้ากับภาษาเมื่อย้ายมาจาก VARCHAR(36) — มันไม่ต้อง cast รองรับทุกฟังก์ชันสตริง และหลีกเลี่ยงภาระการแปลง UUID ตอนเขียนข้อมูล แม้ว่า ClickHouse จะมีชนิดข้อมูล UUID ในตัวด้วยก็ตาม

แบบฝึกหัด: การแมปชนิดข้อมูลสำหรับ FACT_TRIPS

FACT_TRIPS เพิ่มคอลัมน์ที่คำนวณ/อนุมานขึ้นซึ่งถูกเพิ่มโดยไปป์ไลน์ dbt คอลัมน์ส่วนใหญ่ ใช้การตัดสินใจซ้ำจาก TRIPS_RAW ส่วน DRIVER_RATING และ UPDATED_AT เป็นของใหม่

DRIVER_RATING เป็น NULL บ่อยครั้ง (ไม่มีการให้คะแนน) ใน ClickHouse Nullable(Float64) มีภาระด้านประสิทธิภาพเล็กน้อยเมื่อเทียบกับคอลัมน์ที่ไม่เป็น nullable — จะมีบิตแมสก์แยก จัดเก็บควบคู่กับข้อมูลเพื่อติดตามว่าแถวใดเป็น null การเลือกสำหรับคอลัมน์นี้อยู่ระหว่าง Nullable(Float32) (ความหมายของ null ชัดเจน) กับ Float32 เปล่า ๆ พร้อมค่าเซนติเนล อย่าง -1.0 (เร็วกว่า แต่ตามแบบแผนน้อยกว่า) แล็บนี้ใช้ Nullable(Float32) เพื่อความถูกต้อง

แบบฝึกหัด: การแปลงฟังก์ชัน

แปลงแต่ละนิพจน์ของ Snowflake ให้เป็นสิ่งเทียบเท่าใน ClickHouse นิพจน์เหล่านี้มาจาก Q1–Q7 ใน 01-setup-snowflake/queries/ โดยตรง สามในแปดรายการ — QUALIFY, MERGE INTO และการอ่านสตรีม CDC — ไม่มีนิพจน์บรรทัดเดียวเป็นคำตอบ จึงถูกทำเป็นคำถามใต้ตารางแทน

คำถามทบทวน

เมื่อกรอกตารางด้านบนครบแล้ว ให้ทำข้อเหล่านี้ สามข้อมาจากการแปลงในแบบฝึกหัดที่ 3 ที่ต้องใช้ มากกว่าหนึ่งบรรทัดในการตอบ อีกสามข้อคือ "Non-Obvious Translation Decisions" ของใบงาน ต้นฉบับ — เหตุผลเบื้องหลังการเลือกชนิดข้อมูลของ TRIP_METADATA, FARE_AMOUNT และ PICKUP_LOCATION_ID ด้านบน

Loading worksheet...

ถ่ายลงใน migration-plan.md

คัดลอกการตัดสินใจเรื่องชนิดข้อมูลและบันทึกการแปลงที่ไม่ตรงไปตรงมาใด ๆ ไปยังส่วนที่ 5 ของ migration-plan.md และติ๊กช่อง:

- [ ] Schema translation: completed