[[BigQuery]] の `MERGE` ステートメントは、更新したいテーブル (ターゲット) と反映するデータ (ソース。実テーブルでもサブクエリでもよい) を `ON` 条件で照合し、行ごとに INSERT / UPDATE / DELETE を出し分ける DML である。
ステートメント全体は 1 つのまとまった処理 (アトミックなトランザクション) として実行され、すべて成功するか何も変えないかのどちらかになる。内部ではソースとターゲットの JOIN として動くため、スキャン量と課金は、結合で読む列とパーティションの量で決まる。
## 5 つのアクションパターン
句は 3 種類あり、各句で使えるアクションは MATCHED が 2 つ、NOT MATCHED BY TARGET が 1 つ、NOT MATCHED BY SOURCE が 2 つで、合計 5 パターンが原子的な構成要素になる。
| # | 句 | アクション | 対象になる行 |
| --- | ---------------------------- | ------ | ---------- |
| 1 | WHEN MATCHED | UPDATE | ON が一致した行 |
| 2 | WHEN MATCHED | DELETE | ON が一致した行 |
| 3 | WHEN NOT MATCHED [BY TARGET] | INSERT | ソースのみに存在 |
| 4 | WHEN NOT MATCHED BY SOURCE | UPDATE | ターゲットのみに存在 |
| 5 | WHEN NOT MATCHED BY SOURCE | DELETE | ターゲットのみに存在 |
各パターンの挙動は、ターゲットを固定し、ソースだけを変えて MERGE を実行すると 1 つずつ確かめられる。次の 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 |
```
以降の各例は、このターゲットに対してソースだけを差し替え、当てたい句を 1 つだけ書いた MERGE を実行した結果 (After) を示す。
### 1. MATCHED → UPDATE
```
ソース
| id | value | status | load_ts |
| -- | ----- | ------ | ---------- |
| 1 | new1 | active | 2020-01-03 |
| 2 | new2 | active | 2020-01-03 |
```
```sql
merge dataset.target_table as t
using dataset.source_table as s
on t.id = s.id
-- ONが一致した行を更新する
when matched then
update set
t.value = s.value,
t.status = s.status,
t.load_ts = s.load_ts
```
```diff
ターゲット (After)
| id | value | status | load_ts |
| -- | ----- | ------ | ---------- |
- | 1 | old1 | active | 2020-01-01 |
- | 2 | old2 | active | 2020-01-01 |
+ | 1 | new1 | active | 2020-01-03 |
+ | 2 | new2 | active | 2020-01-03 |
| 3 | old3 | active | 2020-01-01 |
| 4 | old4 | active | 2020-01-02 |
| 5 | old5 | active | 2020-01-02 |
```
### 2. MATCHED → DELETE
```
ソース
| id | value | status | load_ts |
| -- | ----- | ------ | ---------- |
| 1 | old1 | active | 2020-01-01 |
| 2 | old2 | active | 2020-01-01 |
```
```sql
merge dataset.target_table as t
using dataset.source_table as s
on t.id = s.id
-- ONが一致した行を削除する
when matched then delete
```
```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 |
```
### 3. NOT MATCHED BY TARGET → INSERT
```
ソース
| id | value | status | load_ts |
| -- | ----- | ------ | ---------- |
| 6 | new6 | active | 2020-01-03 |
| 7 | new7 | active | 2020-01-03 |
```
```sql
merge dataset.target_table as t
using dataset.source_table as s
on t.id = s.id
-- ソースのみに存在する行を挿入する
when not matched by target then
insert (id, value, status, load_ts) values (s.id, s.value, s.status, s.load_ts);
```
```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 |
+ | 6 | new6 | active | 2020-01-03 |
+ | 7 | new7 | active | 2020-01-03 |
```
### 4. NOT MATCHED BY SOURCE → UPDATE
```
ソース
| id | value | status | load_ts |
| -- | ----- | ------ | ---------- |
| 1 | old1 | active | 2020-01-01 |
| 2 | old2 | active | 2020-01-01 |
```
```sql
merge dataset.target_table as t
using dataset.source_table as s
on t.id = s.id
-- ターゲットのみに存在する行を更新する
when not matched by source then update set t.status = 'inactive';
```
```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 |
+ | 3 | old3 | inactive | 2020-01-01 |
+ | 4 | old4 | inactive | 2020-01-02 |
+ | 5 | old5 | inactive | 2020-01-02 |
```
### 5. NOT MATCHED BY SOURCE → DELETE
```
ソース
| id | value | status | load_ts |
| -- | ----- | ------ | ---------- |
| 1 | old1 | active | 2020-01-01 |
| 2 | old2 | active | 2020-01-01 |
```
```sql
merge dataset.target_table as t
using dataset.source_table as s
on t.id = s.id
-- ターゲットのみに存在する行を削除する
when not matched by source then delete;
```
```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 |
```
## UPSERT
実務で最もよく使う MERGE は、キーが一致すれば更新し、無ければ挿入する UPSERT である。パターン 1 (UPDATE) と 3 (INSERT) を 1 文に組み合わせる。
```
ソース
| 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 t.id = s.id
-- 一致すれば更新する
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);
```
id1 は一致するので更新され、id6 はターゲットに無いので挿入される。
```diff
ターゲット (After)
| id | value | status | load_ts |
| -- | ----- | ------ | ---------- |
- | 1 | old1 | active | 2020-01-01 |
+ | 1 | new1 | active | 2020-01-03 |
| 2 | old2 | active | 2020-01-01 |
| 3 | old3 | active | 2020-01-01 |
| 4 | old4 | active | 2020-01-02 |
| 5 | old5 | active | 2020-01-02 |
+ | 6 | new6 | active | 2020-01-03 |
```
## 関連ノート
- **[深掘り]** [[BigQuery MERGE のコストはソースとターゲット双方のスキャン量で決まる]]: 本ノートのスキャン量と課金の仕組みを、プルーニングが効く条件まで掘り下げる
- **[深掘り]** [[BigQuery MERGE の ON 句を FALSE にするとソースとターゲットを照合しない]]: 本ノートの `ON` 条件を FALSE にしたときの挙動とパーティション洗い替えへの応用を扱う
- **[深掘り]** [[BigQuery のダミーデータ記法は typed UNNEST を第一選択にする]]: 本ノートのラボ表で使うインラインのテストデータ記法を詳しく扱う