BigQueryのMERGEステートメントの検証

BigQueryのMERGEステートメントはINSERTとUPDATEを同時にできるが、その使い方を理解するために検証してみました。

次の２つのテーブルがあったとします。

#####source_tbl

id	name	price	update_date
001	apple	150	2020/8/4
002	orange	150	2020/8/2
003	carrot	50	2020/8/2
004	cabbage	100	2020/8/3
005	pork	300	2020/8/3

#####target_tbl

id	name	price	update_date
001	apple	100	2020/8/1
002	orange	150	2020/8/2
003	carrot	50	2020/8/2

• source_tblが更新元テーブル
• target_tblが更新先テーブル
• source_tblとtarget_tblを比較し、source_tblに更新が発生したレコードはtarget_tblへ反映する
• その際、update_dateカラムはSQL実行日に設定する
• SQL実行日は 2020/8/5 とする

##特に条件は指定せず、MERGEをやってみる

MERGE
    target_tbl t
USING
    source_tbl s
ON
    t.id = s.id
WHEN MATCHED THEN
    UPDATE SET
        t.name = s.name
      , t.price = s.price
      , t.update_date = CURRENT_DATETIME('Asia/Tokyo')
WHEN NOT MATCHED THEN
    INSERT
    (
        id
      , name
      , price
      , update_date
    )
    VALUES
    (
        s.id
      , s.name
      , s.price
      , CURRENT_DATETIME('Asia/Tokyo')
    )

###実行結果
#####target_tbl

id	name	price	update_date
001	apple	150	2020/8/5
002	orange	150	2020/8/5
003	carrot	50	2020/8/5
004	cabbage	100	2020/8/5
005	pork	300	2020/8/5

全てのレコードが更新されてしまった。

##EXCEPT DISTINCT句を使用し、変更のあったレコードのみ抽出し、それをソーステーブルとしてMERGEしてみる

MERGE
    target_tbl t
USING
(
    SELECT
        id
      , name
      , price
      , update_date
    FROM source_tbl
    EXCEPT DISTINCT
    SELECT
        id
      , name
      , price
      , update_date
    FROM target_tbl
) s
ON
    t.id = s.id
WHEN MATCHED THEN
    UPDATE SET
        t.name = s.name
      , t.price = s.price
      , t.update_date = CURRENT_DATETIME('Asia/Tokyo')
WHEN NOT MATCHED THEN
    INSERT
    (
        id
      , name
      , price
      , update_date
    )
    VALUES
    (
        id
      , name
      , price
      , CURRENT_DATETIME('Asia/Tokyo')
    )

###実行結果
#####target_tbl

id	name	price	update_date
001	apple	150	2020/8/5
002	orange	150	2020/8/2
003	carrot	50	2020/8/2
004	cabbage	100	2020/8/5
005	pork	300	2020/8/5

変更があったレコードのみ更新・挿入されている。

##仮に、更新があったレコードは必ずupdate_dateカラムが更新される という前提があった場合、EXCEPT DISTINCT句を使用せず、WHEN MATCHED THENにその条件を追加してMERGE

MERGE
    target_tbl t
USING
    source_tbl s
ON
    t.id = s.id
WHEN MATCHED AND s.update_date != t.update_date THEN
    UPDATE SET
        t.name = s.name
      , t.price = s.price
      , t.update_date = CURRENT_DATETIME('Asia/Tokyo')
WHEN NOT MATCHED THEN
    INSERT
    (
        id
      , name
      , price
      , update_date
    )
    VALUES
    (
        id
      , name
      , price
      , CURRENT_DATETIME('Asia/Tokyo')
    )

###実行結果
#####target_tbl

id	name	price	update_date
001	apple	150	2020/8/5
002	orange	150	2020/8/2
003	carrot	50	2020/8/2
004	cabbage	100	2020/8/5
005	pork	300	2020/8/5

変更があったレコードのみ更新・挿入されている。
このやり方のほうがシンプル。
ただ、このやり方だとupdate_dateカラム以外が更新され、update_dateカラムは更新されない場合はそのレコードは変更なしと見做され、更新されない。