【Snowflake】Snowflake ～ MERGE の使用上の注意 ～

Snowflake

■ はじめに

https://dk521123.hatenablog.com/entry/2022/08/01/132611

の続き。

SnowflakeのMerge文において、
使用した際に文法的に制約があったので、
そのことと回避案をメモしておく

目次

【１】「一致する値」と「一致しない値」で指定できる更新文が異なる
　１）文法エラーになるケース
　２）回避案
　３）注意点
【２】エラー「Duplicate row detected during DML action」が表示
　１）原因
　２）解決案

【１】「一致する値」と「一致しない値」で指定できる更新文が異なる

Snowflake のMerge文の公式ドキュメントの冒頭に記載があるのだが

https://docs.snowflake.com/ja/sql-reference/sql/merge

このコマンドは、次のケースを処理するためのセマンティクスをサポートしています。

・一致する値（更新および削除用）
・一致しない値（挿入用）

１）文法エラーになるケース

https://docs.snowflake.com/ja/sql-reference/sql/merge#syntax

-- NOT MATCHED句（一致しない）なのに、
-- 「UPDATE」文を指定しているため、
-- 以下「エラー文言」のようなエラーになる
WHEN NOT MATCHED AND a.sub_id = b.sub_id  THEN UPDATE

エラー文言例 

Error: NOT MATCHED clause in MERGE statement must be followed by INSERT clause

# ! Memo !
# エラー文言から、
# Error: MATCHED clause in MERGE statement must be followed by UPDATE/DELETE clause
# とかもありそう、、、

２）回避案

Merge文の 内部の「ON <join_expr>」

https://docs.snowflake.com/ja/sql-reference/sql/merge#syntax

で、CASE文を使えるので、そこで
~~~~~~~~~~~~~
・一致する値（更新および削除用）
・一致しない値（挿入用）
~~~~~~~~~~~~~
に合うように調整する

修正イメージ 

...
ON
  -- ここで、
  -- 「一致する値（更新および削除用）」と「一致しない値（挿入用）」
  -- に合うようにCASE文で調整するイメージ
  CASE WHEN t.id IS NOT NULL THEN
    t.sub_id = s.sub_id AND t.xxx = s.xxxx
  ELSE
    t.id = s.id
  END
...

３）注意点

* データ件数にもよるが、OR句などでフルスキャンしてしまう場合
　パフォーマンス悪化する恐れがある
 => 「結合爆発 (Join Explosion)」っていうらしい
 => 以下のサイトに記載されている「ifnull」などを使って
　　パフォーマンス悪化しないように対策する

https://zenn.dev/indigo13love/articles/b3604502149b2f

イメージ例 

ON
  -- OR句でフルスキャンしてしまう場合、レスポンス遅延する可能性がある
  CASE WHEN t.id IS NOT NULL THEN
    t.sub_id = s.sub_id OR t.xxx = s.xxxx
  ELSE
    t.id = s.id
  END

４）補足：結合爆発 (Join Explosion)

https://zenn.dev/indigo13love/articles/b93ab72f34aa72

より抜粋
~~~~~~~~~~~~~~~
Join Explosion (結合爆発) は、
結合の左右それぞれのテーブルの行数)に対して
結合結果の行数が急激に大きくなってしまっている状況のこと
~~~~~~~~~~~~~~~

【２】エラー「Duplicate row detected during DML action」が表示

以下の「エラーが発生したSQL例」を実行した際に
エラー「Duplicate row detected during DML action」が表示した

エラーが発生したSQL例 

MERGE INTO target_table t
USING (
  select
    id,
    update_at
  from source_table
) as  s
ON
  t.id = s.id
WHEN MATCHED THEN
    UPDATE
      SET
        t.delete_flag = 1
;

１）原因

* USING句のSQL文において、重複行になっていたため

２）解決案

* USING句のSQL文を、DISTINCTなどにより重複行になることを対応する

エラー修正後のSQL文 

MERGE INTO target_table t
USING (
  select
    distinct -- ! ADD ! 今回は、distinct句を使って重複を排除した
    id,
    update_at
  from source_table
) as  s
ON
  t.id = s.id
WHEN MATCHED THEN
    UPDATE
      SET
        t.delete_flag = 1
;

関連記事

Snowflake ～ 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2021/11/02/130111
Snowflake ～ 入門編 / Hello world ～
https://dk521123.hatenablog.com/entry/2021/11/22/212520
Snowflake ～ MERGE INTO ～
https://dk521123.hatenablog.com/entry/2022/08/01/132611
Snowflake ～ SEQUENCE ～
https://dk521123.hatenablog.com/entry/2023/07/14/091918
Snowflakeのパフォーマンス改善 ～ クエリプロファイル / 実行計画 ～
https://dk521123.hatenablog.com/entry/2023/09/12/194705