[[BigQuery]] の [[BigQuery MERGE ステートメント|MERGE ステートメント]] は内部でソースとターゲットの JOIN として動くため、課金対象のスキャン量は両側で読んだバイト数の合計になる。どちらの側も、パーティション列に静的な述語が効けば読む範囲を絞れるが、述語が動的だとそのテーブルの全パーティションを読む。 コスト事故は、ターゲットがソースと同等以上に大きいテーブルで、ターゲット側のプルーニングが効かないときに起きる。ソースが小さくてもターゲットを全走査するため、課金はフルスキャン相当になり、増分処理にした意味がなくなる。 ## スキャンが起きる場所 MERGE は `ON` でソースとターゲットを照合する JOIN なので、両側がスキャン対象になる。ソースは `USING` に書いたテーブルまたはサブクエリ、ターゲットは `ON` 句と各 `WHEN` 句の述語が読む範囲を決める。 | 側 | スキャン対象を決めるもの | | ---------- | ----------------------------------------------- | | ソース | `USING` のテーブル / サブクエリと、その中の述語 | | ターゲット | `ON` 句の述語と、各 `WHEN` 句の `AND` 述語 | `ON` 句にパーティション列の定数述語を書くと、BigQuery はその述語をソースとターゲットの両方の読み取り段階まで押し下げる。結果として両側が同じパーティションだけに絞られる。 ## プルーニングが効く条件・効かない条件 プルーニングの可否は、パーティション列を **静的な値** で比較しているかで決まる。動的な値 (別テーブルの列・サブクエリの結果) との比較は、実行時まで値が定まらないため全パーティションを読む。 | 述語の書き方 | プルーニング | 理由 | | ----------------------------------------------------------- | ------------ | ---------------------------------------- | | `t.dt = '2018-01-01'` (定数 / クエリパラメータ) | 効く | 静的な値で範囲が確定する | | `DATE_SUB(t.dt, INTERVAL 1 DAY) = '...'` (定数引数) | 効く | 対応関数 + 定数引数は静的に評価できる | | `t.dt = s.dt` (ソース列との比較) | 効かない | 別テーブルの列は動的な値 | | `t.dt = (SELECT max(dt) FROM ...)` (サブクエリ) | 効かない | 実行時まで値が定まらない | | `EXTRACT(MONTH FROM t.dt) = 1` / `t.dt + INTERVAL 1 DAY > x` | 効かない | パーティション列に非対応関数・算術を適用 | | `WHEN NOT MATCHED BY SOURCE` (述語なし) | 効かない | ターゲット全行を照合する必要がある | プルーニングに対応する日付関数と、逆にプルーニングを壊す書き方は次のとおり。 - **対応する日付関数**: 追加の引数が定数であれば使える。`DATE_ADD` / `DATE_SUB` / `DATE_DIFF` / `DATE_TRUNC` / `TIMESTAMP_ADD` / `TIMESTAMP_SUB` / `TIMESTAMP_DIFF` / `TIMESTAMP_TRUNC` / `EXTRACT` / `FORMAT_TIMESTAMP` が該当する - **壊す書き方**: `EXTRACT(MONTH FROM t.dt)` のようにパーティション列を関数で包む、パーティション列に算術を適用する (`t.dt + INTERVAL 1 DAY`)、述語を `OR` で結合する ## 対策 ターゲットのスキャン範囲を握るには、ターゲットのパーティション列に静的述語を効かせる。 - **動的述語を静的述語に変える**: ソースの最新パーティションを `max(dt)` で求めて条件にする場合、サブクエリのまま `ON` に書かず、先にクエリパラメータやスクリプティング変数へ確定させ、リテラルとして `ON ... AND t.dt >= {{ MAX_DT }}` のように渡す - **`ON` に定数のパーティション述語を足す**: `ON t.id = s.id AND t.dt = '2018-01-01'` のように書くと、定数述語が両側に押し下がりソースもターゲットも 1 パーティションに絞れる - **日付列でのフィルタは対応関数を定数引数で使う**: パーティション列を関数で包んだり、サブクエリ・別テーブルの列と比較したりしない - **着手前に dry-run でスキャンバイト数を確認する**: 想定どおりのパーティションだけを読んでいるか、実行前に見積もる > [!warning] > ターゲットがソース以上に大きいテーブルで、ターゲット側のプルーニングが効かない述語 (ソース列との比較・サブクエリ・非対応関数) を書くと、MERGE はターゲットを全走査する。 > > この全走査は、ソースが小さくても課金がフルスキャン相当になり、増分処理のコスト削減効果を消す。 > > `WHEN NOT MATCHED BY SOURCE` を使うときは特に、`AND` でパーティション述語を足して削除範囲を絞る。 ## 関連ノート - **[応用]** [[BigQuery MERGE の ON 句を FALSE にするとソースとターゲットを照合しない]]: 本ノートのプルーニング原則を、パーティション洗い替えで `NOT MATCHED BY SOURCE` のスキャン範囲を絞る文脈で使う - **[応用]** [[dbt の insert_overwrite はパーティション単位で洗い替えて増分更新する]]: 削除句のプルーニングを、dbt のパーティション洗い替えで効かせる事例