ワークシート3: スキーマ変換

所要時間の目安: 20〜25分 参照: Snowflake と ClickHouse の比較 — セクション2（SQL 方言のギャップ）

概念

型のマッピングと関数の変換はマイグレーションで最も機械的な作業ですが、不注意にやると最も 間違いが起きやすい部分でもあります。Snowflake と ClickHouse は意味論の異なる型システムを持ち、 誤った型を使うと、精度の静かな喪失、過剰なストレージ、あるいは壊れたクエリロジックを招きます。

主要な原則:

1. 精度について明示的であること。 Snowflake の TIMESTAMP_NTZ(9) はナノ秒精度です。 ClickHouse の DateTime は秒精度しかありません — バージョンカラムには使わないでください。 ミリ秒精度（現実の要件のほとんどに合致します）には DateTime64(3, 'UTC') を、ナノ秒には DateTime64(9, 'UTC') を使ってください。これは正しさに関わります: ReplacingMergeTree のバージョンカラムが秒精度しかないと、同じ秒の中に届いた2つの更新は非決定的になります — ClickHouse はどちらが新しいのか判定できません。

2. 正しく表せる最小の整数型を使う。 Snowflake の INTEGER は NUMBER(38, 0) です — 128ビット値として保存される38桁の固定精度です。ClickHouse には固定幅の整数があります: Int8、Int16、Int32、Int64、UInt8、UInt16、UInt32、UInt64。 vendor_id（値は1〜3）に UInt8 を選べば、Int64 に比べて1行あたり7バイト節約できます。 5000万行なら350MB です。

3. VARIANT → String。 ClickHouse にはネイティブの JSON 型があります（v25.3以降で プロダクション安定）が、これはフィールド名や構造がテーブル作成時に分からない、真に動的な スキーマのために設計されたものです。このラボの trip_metadata では構造が分かっています （driver.rating、app.surge_multiplier など）— よりよいやり方は、マイグレーション中に 型付きカラムへ事前にフラット化するか、String として保存してクエリ時に JSONExtract* を 使うことです。JSON 型は、スキーマを本当に予測できないときに使ってください。例えば、 イベントごとにフィールドが異なる任意の顧客イベントペイロードを取り込む場合です。 （「型付きカラムへ事前にフラット化する」とは、ETL の途中でフィールドを個別のトップレベル カラムへ抽出することを意味します — FACT_TRIPS.driver_rating が trip_metadata から 生成されるのと同じやり方です — JSON の塊そのものを Tuple で包むことではありません。 Tuple カラムは依然として固定された1組のフィールドに縛られるため、ある trip の メタデータがその形に合わなくなった瞬間に壊れます。）

4. 浮動小数点の精度。 Snowflake の FLOAT は ClickHouse の Float64 に対応します。 厳密な10進演算が必要な金額には Decimal(18, 2) を使ってください — ただしこのラボでは、 ソースに一致させるには Float64 で十分です。（これはデフォルトであって、範囲に関係なく すべての FLOAT カラムが Float64 を取るというルールではありません。driver_rating の ように値が小数第1位で1.0〜5.0に収まるカラムは、Float32 の約7桁の有効数字に余裕をもって 収まります — そこでの選択を決めるのは null 許容性であり、ルール4が金額について守ろうと している精度ではありません。）

5. LowCardinality() — ClickHouse 固有の最適化。 型を LowCardinality(String)（あるいは LowCardinality(UInt8) など）で包むと、そのカラムに辞書エンコーディングを使うよう ClickHouse に指示します — 値は繰り返される文字列ではなく、辞書への整数参照として 保存されます。異なる値が約10,000未満の文字列カラムでは、これによって通常2〜5倍の圧縮改善と、 より高速な GROUP BY が得られます。Snowflake には同等の機能がなく、自動的に処理されます。 このラボでの有力な候補は pickup_borough（6値）、payment_type（6値）、vehicle_type、 vendor_name です。

演習: TRIPS_RAW の型マッピング

NYC_TAXI_DB.RAW.TRIPS_RAW の各カラムを ClickHouse の型に対応付けてください。TRIP_ID は 例として記入済みです: VARCHAR(36) から移行する場合、String が定石です — キャストが不要で、 あらゆる文字列関数が使えて、挿入時の UUID パースのオーバーヘッドも避けられます。ClickHouse に ネイティブの UUID 型があるにもかかわらず、です。

演習: FACT_TRIPS の型マッピング

FACT_TRIPS には、dbt パイプラインが追加した計算カラム・派生カラムが加わります。ほとんどの カラムは TRIPS_RAW での判断の繰り返しですが、DRIVER_RATING と UPDATED_AT は新しいものです。

DRIVER_RATING はしばしば NULL です（評価が付かなかった場合）。ClickHouse では Nullable(Float64) は null 非許容のカラムに比べてわずかに性能上のオーバーヘッドがあります — どの行が null かを追跡するために、データとは別にビットマスクが保存されます。このカラムの 選択肢は Nullable(Float32)（明示的な null の意味論）と、-1.0 のような番兵値を使う素の Float32（より高速だが慣例的でない）のどちらかです。このラボでは正しさのために Nullable(Float32) を使います。

演習: 関数の変換

各 Snowflake の式を ClickHouse の同等物に変換してください。これらは 01-setup-snowflake/queries/ の Q1〜Q7 から直接取ったものです。8つのうち3つ — QUALIFY、 MERGE INTO、そして CDC ストリームの読み取り — は1行の式が答えにならないため、表ではなく その下の問いとして扱います。

振り返りの問い

上の表を埋めたら、これらに取り組んでください。3つは演習3の変換のうち、答えるのに1行以上を 必要としたものです。3つは元のワークシートの「Non-Obvious Translation Decisions」— 上で扱った TRIP_METADATA、FARE_AMOUNT、PICKUP_LOCATION_ID の型の選択の背後にある理由です。

Loading worksheet...

migration-plan.md への転記

型の判断と、自明でない変換に関するメモを migration-plan.md のセクション5にコピーし、 次をチェックしてください。

- [ ] Schema translation: completed