[[BigQuery]] の [[BigQuery MERGE ステートメント|MERGE ステートメント]] は通常、`ON` 条件でソースとターゲットを照合し、行ごとに INSERT / UPDATE / DELETE を出し分ける。`ON` に `FALSE` を書くと、この照合そのものを行わず、すべての行が「一致しなかった」とみなされる。 照合しないため `WHEN MATCHED` 句は永久に発火せず、行の出し分けは「ターゲットに無い行を挿入する」「ソースに無い行を削除する」の 2 方向だけになる。キー一致を前提とする UPSERT は表現できなくなる代わりに、パーティション単位の洗い替えを 1 つのアトミックな文で書けるようになる。 ## ロジック ### 発火しうるアクション `ON FALSE` のもとでは、5 パターンのアクションのうち発火しうるのは `NOT MATCHED BY TARGET → INSERT` と `NOT MATCHED BY SOURCE → UPDATE / DELETE` だけになる。`MATCHED` 系は照合が起きない以上、対象になる行が存在しない。 | 句 | アクション | `ON FALSE` での対象 | | ---------------------------- | ---------- | ------------------- | | WHEN MATCHED | UPDATE | なし (発火しない) | | WHEN MATCHED | DELETE | なし (発火しない) | | WHEN NOT MATCHED BY TARGET | INSERT | ソースの全行 | | WHEN NOT MATCHED BY SOURCE | UPDATE | ターゲットの全行 | | WHEN NOT MATCHED BY SOURCE | DELETE | ターゲットの全行 | ### UPSERT 句を走らせた場合 この挙動は、キー一致時に更新し無ければ挿入する UPSERT 句を `ON FALSE` で走らせると鮮明になる。次の 5 行をターゲットの初期状態とする。 ``` ターゲット (初期状態) | id | value | status | load_ts | | -- | ----- | ------ | ---------- | | 1 | old1 | active | 2020-01-01 | | 2 | old2 | active | 2020-01-01 | | 3 | old3 | active | 2020-01-01 | | 4 | old4 | active | 2020-01-02 | | 5 | old5 | active | 2020-01-02 | ``` ``` ソース | id | value | status | load_ts | | -- | ----- | ------ | ---------- | | 1 | new1 | active | 2020-01-03 | | 6 | new6 | active | 2020-01-03 | ``` ```sql merge dataset.target_table as t using dataset.source_table as s on false -- 照合しないので MATCHED は発火しない when matched then update set t.value = s.value, t.status = s.status, t.load_ts = s.load_ts -- ソースの全行が「ターゲットに一致しない」とみなされ挿入される when not matched by target then insert (id, value, status, load_ts) values (s.id, s.value, s.status, s.load_ts); ``` `on t.id = s.id` なら id1 は UPDATE され id6 が INSERT される。`on false` では `WHEN MATCHED` が発火しないため、id1 も「ターゲットに一致しない行」として扱われる。 その結果、old1 を残したまま new1 が追加され、id1 が 2 行に重複する。 ```diff ターゲット (After) | id | value | status | load_ts | | -- | ----- | ------ | ---------- | | 1 | old1 | active | 2020-01-01 | | 2 | old2 | active | 2020-01-01 | | 3 | old3 | active | 2020-01-01 | | 4 | old4 | active | 2020-01-02 | | 5 | old5 | active | 2020-01-02 | + | 1 | new1 | active | 2020-01-03 | + | 6 | new6 | active | 2020-01-03 | ``` ## パーティション単位の洗い替えに使う ### 削除範囲の絞り込み `ON FALSE` を実用にするには、`NOT MATCHED BY SOURCE → DELETE` で削除する範囲を絞り込む。ただ挿入するだけでは既存行が残るため、削除句に `AND` で述語を足し、特定のパーティションだけを削除対象にする。 ```sql merge dataset.target_table as t using dataset.source_table as s on false -- 指定したパーティションの既存行だけを削除する when not matched by source and t.load_ts in ('2020-01-03') then delete -- ソースの全行を挿入する when not matched by target then insert (id, value, status, load_ts) values (s.id, s.value, s.status, s.load_ts); ``` `load_ts` がパーティション列であれば、`AND t.load_ts IN (...)` は BigQuery のパーティションプルーニングを効かせる。削除句はターゲット全体をスキャンせず、述語で指定したパーティションだけを読んで消す。 これにより「対象パーティションを消してソースを入れ直す」洗い替えが、1 つの MERGE 文の中で完結する。 ### プルーニングと噛み合う理由 `ON FALSE` がプルーニングと噛み合うのは、照合を捨てたことの裏返しである。`ON t.id = s.id` の結合では、BigQuery はソースの値からどのパーティションを読むべきか推定しきれず、ターゲットを広くスキャンしがちになる。 この照合をやめ、読む範囲を `AND` 述語で明示的に与えることで、スキャン量を書き手が制御できる。 ## 利点と注意点 照合しない MERGE には、キー結合に伴う制約から解放される利点がある。 - **ソースの重複キーでエラーにならない**: 通常の MERGE は 1 つのターゲット行が複数のソース行に一致するとエラーになる。`ON FALSE` は照合しないため、ソースに同一キーの行が複数あっても失敗しない。洗い替えに `unique_key` を用意しなくてよい - **洗い替えがアトミック**: 削除と挿入が 1 つの MERGE 文にまとまり、トランザクションとして実行される。`TRUNCATE` してから `INSERT` する手順と違い、途中で失敗しても中間状態 (消えただけで入っていない) が他のクエリに見えない > [!warning] > `NOT MATCHED BY SOURCE` の削除句に述語を書き忘れると、ターゲットの全行が削除対象になる。パーティションプルーニングも効かずフルスキャンになるため、洗い替えの範囲は必ず `AND` 述語で絞る。 > > また `WHEN MATCHED` が発火しない以上、キー一致での部分更新はできない。`ON FALSE` は洗い替え専用であり、UPSERT が必要なら `ON t.key = s.key` の通常の MERGE を使う。 ## 関連ノート - **[前提]** [[BigQuery MERGE のコストはソースとターゲット双方のスキャン量で決まる]]: 本ノートの洗い替えでプルーニングを効かせる前提となる、MERGE のスキャン範囲とコストの一般原則 - **[応用]** [[dbt の insert_overwrite はパーティション単位で洗い替えて増分更新する]]: この洗い替えを dbt のインクリメンタル戦略として使う実装事例