[Redshifit] MergeクエリがGAになったので、主キー重複時の挙動について調べてみた！

RedshiftのMergeクエリがGAとなりました。

Amazon Redshift が MERGE SQL コマンドの一般提供を開始

なお2023/05/15現在、日本語ドキュメントだとまだ「プレビュー」と書かれていますが、上記ページには

MERGE SQL コマンドは、 Amazon Redshift が利用可能なすべての商用リージョンと AWS GovCloud (米国) リージョンで利用できます。

とありますので、使用して問題ないかと思います。 （英語ドキュメントではプレビューとは書いていないです）

さて、Mergeクエリについて私はこれまで使ったことがなかったので、 動作の確認をしてみました。 特に、主キー的なものが重複している場合については挙動が予想できなかったので、そこを重点的に調べました。

検証環境

今回はRedshiftServerlessを使って検証を行いました。 従来のプロビジョニング版と差異があるかの確認は特に行っていませんが、 さすがに全く同じ挙動かと思います。

Mergeクエリ

Mergeクエリについてちょっと復習します。

厳密な構文は公式ドキュメントを確認して頂くとして、 実際に使うときのSQLはこんな感じになります。

merge into target_table
using source_table s on target_table.id = s.id
when matched then update set id = target_table.id, name = target_table.name, age = target_table.age
when not matched then insert values (s.id, s.name, s.age);

ポイントは以下です。

• on target_table.id = s.id
  • ここで「主キー」を決めます
• when matched then
  • targetに対してsourceに同一の「主キー」が見つかった時の処理を書きます
    • targetに対するupdateかdeleteができます。
• when not matched then
  • targetに対してsourceで同一の「主キー」が見つからなかった時の処理を書きます
    • targetに対するinsert valuesを行うことができます。

なお、ここでは「同一とみなすもの」の条件のことを「主キー」と表記しています。 「同一とみなすもの」の条件は任意に書くことができ、複数の条件もかけるので、 あくまでも直感的な理解重視での呼び方であることをご了承ください。

試してみる

テーブル

検証に利用したテーブルです。 今回は簡単のためにtargetとsourceは同じテーブル定義にしています。 特に主キーは設定していませんが、idが主キー相当となる想定です。

CREATE TABLE target_table (
    id integer,
    name text,
    age integer
);

CREATE TABLE source_table (
    id integer,
    name text,
    age integer
);

targetに重複がある時

まずはtarget_tableに重複がある場合を試してみます。

target_tableにidが重複した2レコードを入れます。

insert into target_table values
(123, 'abc', 18),
(123, 'def', 38);

insert into source_table values
(123, 'xxx', 28),

Mergeを行います。

MERGE INTO target_table
USING source_table s ON target_table.id = s.id
WHEN MATCHED THEN UPDATE SET id = s.id, name = s.name, age = s.age
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.name, s.age);

target_tableをselectした結果はこんな感じです。

id,name,age
123,xxx,28
123,xxx,28

Mergeによる更新の評価はレコードごとに独立して行われるため、 target_tableに重複があった場合はそれぞれの行が独立して更新され、 結果として全く同じレコードが複数入るという挙動だと理解できます。

sourceに重複があるとき

次にsource_tableに重複がある時を見てみます。

-- それぞれのテーブルは作り直して空になっている前提。

insert into target_table values
(123, 'abc', 18),

insert into source_table values
(123, 'xxx', 28),
(123, 'yyy', 28),

Mergeを行います。クエリは先ほどと全く同じです。

MERGE INTO target_table
USING source_table s ON target_table.id = s.id
WHEN MATCHED THEN UPDATE SET id = s.id, name = s.name, age = s.age
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.name, s.age);

これを実行してみると、次のようにエラーが出ました。

error: Found multiple matches to update the same tuple.

source_tableに重複があるとエラーとなるようです。 確かに、sourceに重複がある場合はどのレコードで更新するべきかわからないので、 エラーにされるのが一番しっくりくる気がします。 （どのレコードで更新されるかは不定とかじゃなくてよかった）

自分自身とのMerge

ここからはすこしおまけです。

targetやsourceに「主キー」重複が発生している時の挙動から考えると、 自分自身とのMerge（target=source）クエリを行うことで、 テーブル内で「主キー」が重複していないかを確認することができそうです。 こんなクエリです。

merge into target_table
using target_table s on target_table.id = s.id
when matched then update set id = s.id, name = s.name, age = s.age
when not matched then insert values (s.id, s.name, s.age);

しかしこのクエリは動きません。

ERROR: Target and source for MERGE statement must be different relations.

残念ながらtargetとsourceのテーブルは同一のものを指定できないようです。 ドキュメントによると、sourceテーブルとしては指定できるものにそこそこ条件があるようです。

The temporary or permanent table supplying the rows to merge into target_table. source_table can also be a Spectrum table, Postgres federated table, or MySQL federated table. source_table can't be a view or a subquery.

試してみましたが、ドキュメント通り、withで作成したテーブルや、 サブクエリは書くことはできませんでした。 しかし、create temp tableであれば使うことができます。 1クエリには収まらないのですが、tempテーブルであればかなり1クエリに近い感覚で使うことができそうです。

こんな感じです。

drop table if exists target_copy;
create temp table target_copy as
select * from target_table;

merge into target_table
using target_copy s on target_table.id = s.id
when matched then update set id = target_table.id, name = target_table.name, age = target_table.age
when not matched then insert values (s.id, s.name, s.age);

このクエリの時の挙動をまとめてみましょう。

• 重複なし
  • 重複がないので、全てのレコードがmatchedに該当
  • updateの内容としては、自分自身のレコードの内容で上書き¹
    • => 何も起こらない
• 重複あり
  • sourceとして重複があるので、エラー発生

ということで、どちらにしてもこのクエリでtargetテーブルへの影響は発生しないことになるかと思いますので、 これで特定のカラム群に重複があるかの確認がほぼ1クエリで可能になりそうです。

SyntaxErrorとの戦い

さらにおまけですが、Mergeクエリを書くときにつまずいた、 細かい書き方についての補足をしておきます。 ドキュメントを見ればわかるものもありますが、足跡として記載しておきます。

update内容の左辺はテーブル指定不能

update setに書く式については、左辺にテーブル名の指定ができません。 Mergeではsourceの内容でもってtarget側を更新するという動作なので、 左辺（代入先）は必ずtargetテーブルであることは決まっているため指定する必要がありません。 というか、指定できません。

set id = target_table.id

の左辺にテーブル名は書くことができないということです。

target_tableはエイリアスが使えない

target_tableに指定するテーブルについては、エイリアスの指定ができないようです。 さっきのクエリだと

merge into target_table

ここにas tのようには書けないようです。

まとめ

Mergeクエリにおいて、ONに指定する要素（「主キー」）が重複している場合の挙動について調べてみました。 Redshiftは基本的に「主キー」の重複を抑止することができず、 実際にデータを見てみないと重複があるかどうかがわかりません。 Mergeを使うことで、その確認がやりやすくなりそうです。

参考情報

• MERGE
• [アップデート] Amazon RedshiftがMERGE、ROLLUP、CUBE、GROUPING SETS等、SQL 機能の拡張をサポートしました！（Preview） #reinvent