Amazon Redshiftでpreview公開されたMERGE文を使ってみる

はじめに

先日行われたre:Invent 2022では、Amazon Redshiftに関する新機能も多く発表されました。Amazon Auroraのデータをニアリアルタイムで自動連携する機能やAmazon S3のデータの自動取り込み機能のプレビュー公開は、これまでETLのパイプラインを作るのが面倒だなと思っていた方には朗報でしょうか。ぜひGAまで行ってほしいですね。
そんな中、RedshiftのSQL構文も地味にアップデートされていました。こちらもまだプレビューではありますが、ROLLUP、CUBE、GROUPING SETSといったOLAP向けにクエリする際に便利な構文のほか、待望のMERGEも追加されました。今回はこのMERGEについて試してみたいと思います。

MERGEとは

MERGE文はデータ更新を行うDML文の一種です。ソース（更新元）テーブルに入っているレコードがターゲット（更新先）テーブルに存在していればそのレコードを更新し、存在していなければ挿入を行います。俗にUPSERTと呼ばれる処理を、１つのSQL文で実行可能とするものです。
私の手元にあるSQL解説書「プログラマのためのSQL第４版」では、ANSI SQLの標準規格に入ったのはSQL99（1999年公開）となっていましたが、SQL99を解説しているサイトなどをちょっと調べてもMERGEは見つからず、Wikipediaや他のブログ記事ではSQL:2003にて導入との記載がありました。私が初めてMERGE文を知ったのはOracle 9iにMERGEが採用された時で、その時は確かOracleの独自拡張と言われていた記憶があるので、標準化はSQL:2003の時ではないかとも思うのですが、どちらが正しいかは不明です。それにしても、当時はまだ彼女いない歴＝年齢の駆け出しエンジニアだった自分も、今は四十代半ばの子持ちの父親ですか。時が経つのは早いなあ。
それはともかく、MERGE文はSQLの歴史的には比較的新しい構文であることもあり、RDBMSによって採用状況はまちまちで、SQL言語体系ではPostgreSQLの流れを汲む（PostgreSQL 8.0系をベースとした独自拡張）Amazon Redshiftも長らくMERGEは採用していませんでした。余談ですが、PostgreSQL本家は今年リリースのPostgreSQL 15でMERGEを採用したようですね。

では、Amazon Redshiftでプレビュー公開されたバージョンのMERGE文の構文を見てみます。ドキュメントだとこちらになりますね（2022年12月10日時点では英語版のみ）: https://docs.aws.amazon.com/redshift/latest/dg/r_MERGE.html

MERGE INTO target_table
USING source_table [ [ AS ] alias ] ON match_condition
WHEN MATCHED THEN { UPDATE SET col_name = { expr } [,...] | DELETE }
WHEN NOT MATCHED THEN INSERT [ ( col_name [,...] ) ] VALUES ( { expr } [, ...] )

target_tableが更新対象のテーブルです。source_tableが更新情報をもつテーブル（一般的には差分データをロードしたステージングテーブルなどでしょう）で、ON以下のmatch_conditionで、対象となるレコードを特定するための条件を書きます。通常はこの２テーブルの主キーを等号で結ぶ形になると思います。この条件がマッチすればWHEN MATCHED THEN以下が実行され、マッチしなければWHEN NOT MATCHD THEN INSERTが実行されます。よって、該当レコードがあればUPDATEまたはDELETEが実行され、なければINSERTされるという動きとなります。

MERGEを使う準備をする

ではさっそくMERGEを試してみましょう。冒頭で書いた通り、MERGEはまだプレビュー公開となっており、プレビュートラックのクラスター（またはサーバーレスワークグループ）を立ち上げる必要があります。Redshiftの管理コンソール画面を開くと、画面上部にこんな表示があります。

"Create preview cluster"をクリックすると、プレビュー用クラスター画面に入ります。
クラスターの設定では、プレビュートラックを選びます。MERGEを試すことができるトラックは"preview_2022"なので、これを選びます。クラスター名やサイズなど、その他の項目は適宜任意の値を設定します。

クラスタが起動したら、テスト用のテーブルを作成します。今回はTPC-Hで使われているlineitemテーブルが別のクラスターにあったので、このテーブルを共有してINSERT SELCT文を使ってテーブルをコピーしました。Amazon Redshiftのデータ共有機能は通常のクラスターとプレビューのクラスターとの間でも利用できました。データ共有ができるようになって本当に便利になりましたね。ちなみによく間違えられがちですが、データ共有によって共有先のクラスターに設定される外部データベースには、DBユーザーが直接接続することはできません。ローカルのデータベースに接続してから、「DB名.スキーマ名.テーブル名」形式のテーブル指定をすることで使えるようになります。なお、今回使ったテーブルのレコード数は約60億行です（TPCH SF=1000)。

create_target_table.sql

create table lineitem 
as 
select * from tpch1000ext.public.lineitem;

では次に更新用のテーブルを作ります。ここでは1995年6月のテーブルを切り出しておきます。後で更新情報を確認できるように、l_commentカラムにメッセージを入れておきましょう。なお、こちらのテーブルのレコード数は約7,500万行となりました。

create_source_table.sql

create table lineitem199506 
as 
select * from lineitem 
where l_shipdate between '1995-06-01' and '1995-06-30';

update lineitem199506 set l_comment = 'MERGE test';

ターゲットのテーブルからは、1995年6月後半のレコードは削除しておきましょう。MERGEによって、1995年6月前半のレコードはUPDATE, 1995年6月後半のレコードはINSERTすることにします。

update_target_table.sql

delete from lineitem where l_shipdate between '1995-06-16' and '1995-06-30';

MERGEを他のUPSERT手段と比較する

それでは実際にMERGE文を実行してみます。今回は、MERGE文を使わない場合のSQL文と比較して、コードや実行時間がどのようになるのかをみてみます。
MERGE以外に考えられる処理としては、以下の２パターンがあるかと思います。

1. 更新対象のデータを１行ずつ取り出し、更新先テーブルに該当する行があればUPDATEを、なければINSERTを行う
2. まず更新対象のレコードを更新先テーブルから全件削除し、それから更新対象レコードを全件挿入する

パターン１は手続き型言語で素直にロジックを組んで実行する形になりますね。Amazon RedshiftだとPL/pgSQLを使ったストアドプロシージャが作成できるので、今回はこれを使ってみます。ただ、Amazon Redshiftは行単位での更新処理を高速に行うようには作られていないので（インデックスもないですし）、とっても遅そうなのは容易に想像がつきます。
パターン２は一般的にAmazon Redshiftで推奨されているパターンですね。ドキュメントでもこちらのやり方が紹介されています。
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_updating-inserting-using-staging-tables-.html
ただし、これが利用できるパターンは更新元テーブルにて、対象レコードに関する更新レコードが一件だけとなっていることが必要です。そうでなければ、INSERT時に本来１行である行が複数行挿入されてしまいます。つまり、同一行に対して複数回更新を行い、差分レコードが複数できた場合は最新情報の１レコードだけになるように事前に処理しなければなりません。
ではパターン１から実行してみましょう。エラーハンドリングなどは無視して以下のように雑に書いてみました。

upsert_pattern1.sql

create or replace procedure sp_merge_test()
language plpgsql
as $$
declare 
	src_table record;
begin 
	for src_table in select * from lineitem199506 order by l_shipdate loop 
		update lineitem 
			set 
				l_partkey = src_table.l_partkey,
				l_suppkey = src_table.l_suppkey,
				l_quantity = src_table.l_quantity,
				l_extendedprice = src_table.l_extendedprice,
				l_discount = src_table.l_discount,
				l_tax = src_table.l_tax,
				l_returnflag = src_table.l_returnflag,
				l_linestatus = src_table.l_linestatus,
				l_shipdate = src_table.l_shipdate,
				l_commitdate = src_table.l_commitdate,
				l_receiptdate = src_table.l_receiptdate,
				l_shipinstruct = src_table.l_shipinstruct,
				l_shipmode = src_table.l_shipmode,
				l_comment = src_table.l_comment
			where 
				l_orderkey = src_table.l_orderkey
				and l_linenumber = src_table.l_linenumber
		;
		if not found then
			insert into lineitem (
				l_orderkey,
				l_linenumber,
				l_partkey,
				l_suppkey,
				l_quantity,
				l_extendedprice,
				l_discount,
				l_tax,
				l_returnflag,
				l_linestatus,
				l_shipdate,
				l_commitdate,
				l_receiptdate,
				l_shipinstruct,
				l_shipmode,
				l_comment
			)
			values (
				src_table.l_orderkey,
				src_table.l_linenumber,
				src_table.l_partkey,
				src_table.l_suppkey,
				src_table.l_quantity,
				src_table.l_extendedprice,
				src_table.l_discount,
				src_table.l_tax,
				src_table.l_returnflag,
				src_table.l_linestatus,
				src_table.l_shipdate,
				src_table.l_commitdate,
				src_table.l_receiptdate,
				src_table.l_shipinstruct,
				src_table.l_shipmode,
				src_table.l_comment			
			);
		end if;
	end loop;	
end;
$$
;

call sp_merge_test();

やっていることは単純ですが、問題はこのストアドプロシージャを実行する際に発行されるSQL文の量です。今回の更新元テーブルlineitem199506には、先ほど記載した通り約7,500万行のデータが入っています。つまりUPDATEが7,500万回発行された上で、空振りしたものについてさらにINSERT文が発行される（データの半分はINSERTとなるはず）こととなります。一体どれくらいの時間がかかることやら...。ちなみにこれを書きながら今私の手元で実行していますが、１時間経っても終わる気配がありません。

次にパターン２を実行してみます。SQLとしてはこんな感じでしょうか。

upsert_pattern2.sql

delete from lineitem
using lineitem199506
where 
	lineitem.l_orderkey = lineitem199506.l_orderkey
and lineitem.l_linenumber = lineitem199506.l_linenumber
;

insert into lineitem 
select * from lineitem199506;

パターン１と比較するとスッキリしていますね。単に対象レコードをdeleteしてからinsertしているだけです。実際に実行してみると、１つ目のdeleteには1分45秒、２つ目のinsertは18秒で完了しました。

では、いよいよMERGE文を使ってみましょう。MERGE文だとこんな感じのSQLとなるでしょうか。

upsert_pattern3_merge.sql

merge into lineitem 
using lineitem199506
on 	lineitem.l_orderkey = lineitem199506.l_orderkey
and lineitem.l_linenumber = lineitem199506.l_linenumber
when matched then update set 
	l_partkey = lineitem199506.l_partkey,
	l_suppkey = lineitem199506.l_suppkey,
	l_quantity = lineitem199506.l_quantity,
	l_extendedprice = lineitem199506.l_extendedprice,
	l_discount = lineitem199506.l_discount,
	l_tax = lineitem199506.l_tax,
	l_returnflag = lineitem199506.l_returnflag,
	l_linestatus = lineitem199506.l_linestatus,
	l_shipdate = lineitem199506.l_shipdate,
	l_commitdate = lineitem199506.l_commitdate,
	l_receiptdate = lineitem199506.l_receiptdate,
	l_shipinstruct = lineitem199506.l_shipinstruct,
	l_shipmode = lineitem199506.l_shipmode,
	l_comment = lineitem199506.l_comment
when not matched then insert (
	l_orderkey,
	l_linenumber,
	l_partkey,
	l_suppkey,
	l_quantity,
	l_extendedprice,
	l_discount,
	l_tax,
	l_returnflag,
	l_linestatus,
	l_shipdate,
	l_commitdate,
	l_receiptdate,
	l_shipinstruct,
	l_shipmode,
	l_comment
)
values (
	lineitem199506.l_orderkey,
	lineitem199506.l_linenumber,
	lineitem199506.l_partkey,
	lineitem199506.l_suppkey,
	lineitem199506.l_quantity,
	lineitem199506.l_extendedprice,
	lineitem199506.l_discount,
	lineitem199506.l_tax,
	lineitem199506.l_returnflag,
	lineitem199506.l_linestatus,
	lineitem199506.l_shipdate,
	lineitem199506.l_commitdate,
	lineitem199506.l_receiptdate,
	lineitem199506.l_shipinstruct,
	lineitem199506.l_shipmode,
	lineitem199506.l_comment			
);

UPDATEの時とINSERTとの時との処理を双方記載する必要があるので、コードの長さだけだとパターン１に近いですね。でもここで実行されるSQLは１つだけです。実際に実行してみると、実行時間は3分43秒となりました。シンプルの極みとも言えるパターン２と比較するとやや遅いですが、ロジック的にパターン１と同じことを表現していること、まだプレビューであることを考慮すると、なかなかいい線行っているんじゃないかなと個人的には思います。

おわりに

このように、MERGEがサポートされたことによって、Amazon Redshift上でSQLで差分データを更新する処理も今後は書きやすくなるのではないかと思います。気になった方はぜひプレビューを試してみてはいかがでしょうか。といったところで、なんとか12月10日中にアップできそうなことにホッとしつつ、パターン１のクエリは実行中のまま放置して終わりたいと思います。

2022/12/16 補足

結局パターン１のぐるぐるバッチは3日たっても終わらず、更新対象を2日（1/15のデータ量）に減らしてもやっぱり３日たっても終わらずといった状況で、最終的には1,000件（更新500件、挿入500件）まで減らすことで、やっとまともな時間で終了しました（3分35秒）。上にも書きましたが、RedshiftはUpdate対象の一行を取得するのに適したB-tree indexを持たないので、どうしてもこうなってしまうのですよね。ただ、ぐるぐるバッチをぶん回したおかげで、Redshiftのテーブル自動最適化機能が働き、1,000件更新の処理を実行するときにはソートキーと分散キーが自動設定されていました(l_orderkeyがソートキーと分散キーに）。3分半という結果にはそれも貢献しているかもしれません。

あと、参考までに適切に更新できたかを確認するためのSQLも最後につけておきます。対象月とその前後の件数を見て、狙った日付が更新できているかを見ています。

select 
    l_shipdate, 
    case l_comment when 'MERGE test' then 1 else 0 end as upd, 
    count(*) 
from lineitem 
where l_shipdate between '1995-05-01' and '1995-07-31'
group by l_shipdate, upd order by l_shipdate;

パターン１の場合の更新前

 l_shipdate | upd |  count
------------+-----+---------
 1995-05-01 |   0 | 2493311
 1995-05-02 |   0 | 2492828
 1995-05-03 |   0 | 2493603
 1995-05-04 |   0 | 2493173
 1995-05-05 |   0 | 2489818
 1995-05-06 |   0 | 2495009
 1995-05-07 |   0 | 2495623
 1995-05-08 |   0 | 2493351
 1995-05-09 |   0 | 2494402
 1995-05-10 |   0 | 2490392
 1995-05-11 |   0 | 2493772
 1995-05-12 |   0 | 2493083
 1995-05-13 |   0 | 2492357
 1995-05-14 |   0 | 2493726
 1995-05-15 |   0 | 2491507
 1995-05-16 |   0 | 2496366
 1995-05-17 |   0 | 2491319
 1995-05-18 |   0 | 2493563
 1995-05-19 |   0 | 2496052
 1995-05-20 |   0 | 2490730
 1995-05-21 |   0 | 2495474
 1995-05-22 |   0 | 2492809
 1995-05-23 |   0 | 2491530
 1995-05-24 |   0 | 2492014
 1995-05-25 |   0 | 2494609
 1995-05-26 |   0 | 2493655
 1995-05-27 |   0 | 2493561
 1995-05-28 |   0 | 2496568
 1995-05-29 |   0 | 2494189
 1995-05-30 |   0 | 2495939
 1995-05-31 |   0 | 2496808
 1995-06-01 |   0 | 2493961
 1995-06-02 |   0 | 2494937
 1995-06-03 |   0 | 2492593
 1995-06-04 |   0 | 2493634
 1995-06-05 |   0 | 2493002
 1995-06-06 |   0 | 2494927
 1995-06-07 |   0 | 2495326
 1995-06-08 |   0 | 2491955
 1995-06-09 |   0 | 2493211
 1995-06-10 |   0 | 2493500
 1995-06-11 |   0 | 2493570
 1995-06-12 |   0 | 2496600
 1995-06-13 |   0 | 2492092
 1995-06-14 |   0 | 2496180
 1995-06-15 |   0 | 2493359
 1995-07-01 |   0 | 2492971
 1995-07-02 |   0 | 2494313
 1995-07-03 |   0 | 2491072
 1995-07-04 |   0 | 2494483
 1995-07-05 |   0 | 2493050
 1995-07-06 |   0 | 2493621
 1995-07-07 |   0 | 2491404
 1995-07-08 |   0 | 2494322
 1995-07-09 |   0 | 2494761
 1995-07-10 |   0 | 2490515
 1995-07-11 |   0 | 2493943
 1995-07-12 |   0 | 2493493
 1995-07-13 |   0 | 2494285
 1995-07-14 |   0 | 2492023
 1995-07-15 |   0 | 2494235
 1995-07-16 |   0 | 2493140
 1995-07-17 |   0 | 2492123
 1995-07-18 |   0 | 2494741
 1995-07-19 |   0 | 2490142
 1995-07-20 |   0 | 2494892
 1995-07-21 |   0 | 2493343
 1995-07-22 |   0 | 2494116
 1995-07-23 |   0 | 2493609
 1995-07-24 |   0 | 2494568
 1995-07-25 |   0 | 2489702
 1995-07-26 |   0 | 2492615
 1995-07-27 |   0 | 2494545
 1995-07-28 |   0 | 2493985
 1995-07-29 |   0 | 2498479
 1995-07-30 |   0 | 2492367
 1995-07-31 |   0 | 2491038

更新後

 l_shipdate | upd |  count
------------+-----+---------
 1995-05-01 |   0 | 2493311
 1995-05-02 |   0 | 2492828
 1995-05-03 |   0 | 2493603
 1995-05-04 |   0 | 2493173
 1995-05-05 |   0 | 2489818
 1995-05-06 |   0 | 2495009
 1995-05-07 |   0 | 2495623
 1995-05-08 |   0 | 2493351
 1995-05-09 |   0 | 2494402
 1995-05-10 |   0 | 2490392
 1995-05-11 |   0 | 2493772
 1995-05-12 |   0 | 2493083
 1995-05-13 |   0 | 2492357
 1995-05-14 |   0 | 2493726
 1995-05-15 |   0 | 2491507
 1995-05-16 |   0 | 2496366
 1995-05-17 |   0 | 2491319
 1995-05-18 |   0 | 2493563
 1995-05-19 |   0 | 2496052
 1995-05-20 |   0 | 2490730
 1995-05-21 |   0 | 2495474
 1995-05-22 |   0 | 2492809
 1995-05-23 |   0 | 2491530
 1995-05-24 |   0 | 2492014
 1995-05-25 |   0 | 2494609
 1995-05-26 |   0 | 2493655
 1995-05-27 |   0 | 2493561
 1995-05-28 |   0 | 2496568
 1995-05-29 |   0 | 2494189
 1995-05-30 |   0 | 2495939
 1995-05-31 |   0 | 2496808
 1995-06-01 |   0 | 2493461
 1995-06-01 |   1 |     500
 1995-06-02 |   0 | 2494937
 1995-06-03 |   0 | 2492593
 1995-06-04 |   0 | 2493634
 1995-06-05 |   0 | 2493002
 1995-06-06 |   0 | 2494927
 1995-06-07 |   0 | 2495326
 1995-06-08 |   0 | 2491955
 1995-06-09 |   0 | 2493211
 1995-06-10 |   0 | 2493500
 1995-06-11 |   0 | 2493570
 1995-06-12 |   0 | 2496600
 1995-06-13 |   0 | 2492092
 1995-06-14 |   0 | 2496180
 1995-06-15 |   0 | 2493359
 1995-06-30 |   1 |     500
 1995-07-01 |   0 | 2492971
 1995-07-02 |   0 | 2494313
 1995-07-03 |   0 | 2491072
 1995-07-04 |   0 | 2494483
 1995-07-05 |   0 | 2493050
 1995-07-06 |   0 | 2493621
 1995-07-07 |   0 | 2491404
 1995-07-08 |   0 | 2494322
 1995-07-09 |   0 | 2494761
 1995-07-10 |   0 | 2490515
 1995-07-11 |   0 | 2493943
 1995-07-12 |   0 | 2493493
 1995-07-13 |   0 | 2494285
 1995-07-14 |   0 | 2492023
 1995-07-15 |   0 | 2494235
 1995-07-16 |   0 | 2493140
 1995-07-17 |   0 | 2492123
 1995-07-18 |   0 | 2494741
 1995-07-19 |   0 | 2490142
 1995-07-20 |   0 | 2494892
 1995-07-21 |   0 | 2493343
 1995-07-22 |   0 | 2494116
 1995-07-23 |   0 | 2493609
 1995-07-24 |   0 | 2494568
 1995-07-25 |   0 | 2489702
 1995-07-26 |   0 | 2492615
 1995-07-27 |   0 | 2494545
 1995-07-28 |   0 | 2493985
 1995-07-29 |   0 | 2498479
 1995-07-30 |   0 | 2492367
 1995-07-31 |   0 | 2491038