データ周りのお仕事をしています!モリヤス(@_moriyas)です!
最近dbtやらDataformやらを使って差分更新の処理を実装することがあります。
差分更新の際に実行されるのがMERGE文なのですが、そもそもMERGE文に馴染みがなかったので、調べたり挙動を確認したりして、自分なりの理解をまとめてみました。
環境
- BigQuery
準備
下記クエリでテーブルを作成します。
CREATE TABLE `yourprojectname.yourdatasetname.product` (
sku STRING,
description STRING,
quantity FLOAT64,
price FLOAT64
);
CREATE TABLE `yourprojectname.yourdatasetname.product2` (
sku STRING,
description STRING,
quantity FLOAT64,
price FLOAT64
);
CREATE TABLE `yourprojectname.yourdatasetname.product3` (
sku STRING,
description STRING,
quantity FLOAT64,
price FLOAT64
);
INSERT
`yourprojectname.yourdatasetname.product` (sku, description, quantity, price)
VALUES
('GCH635354', 'Chair', 4, 345.7),
('GRD828822', 'Gardening', 2,9.5),
('ABC123456', 'Furniture', 3, 36.3),
('TBL535522', 'Table', 6, 878.4),
('CHR762222', 'Chair', 4, 435.6);
INSERT
`yourprojectname.yourdatasetname.product2` (sku, description, quantity, price)
VALUES
('POT76456', "", 2, 32),
('FPL29921', "", 10, 780),
('ABC123456', "", 5, 250);
INSERT
`yourprojectname.yourdatasetname.product3` (sku, description, quantity, price)
VALUES
('ABC123456', "", 5, 100),
('POT76456', "", 5, 80);
そもそもMERGEって何?
公式には下記のような説明があります。
A MERGE statement is a DML statement that can combine INSERT, UPDATE, and DELETE operations into a single statement and perform the operations atomically.
MERGEステートメントは、INSERT、UPDATE、および DELETE 操作を1つのステートメントに結合し、1つの操作で実行できるDMLステートメントです。
Data manipulation language (DML) statements in GoogleSQL | BigQuery | Google Cloud
構文は下記のとおりです。
MERGE target_table
USING source_table
ON merge_condition
WHEN MATCHED [AND search_condition]
THEN update_statement | delete_statement
WHEN NOT MATCHED [AND search_condition]
THEN insert_statement
WHEN NOT MATCHED BY SOURCE [AND search_condition]
THEN update_statement | delete_statement;
- MERGE target_table:元テーブル
- USING source_table:結合(マージ)するテーブル
- ON merge_condition:結合条件
- WHEN MATCHED [AND search_condition] THEN update_statement | delete_statement:target_tableのレコードがsource_tableのレコードと一致する場合にそのtarget_tableのレコードをupdateかdeleteする
- WHEN NOT MATCHED [AND search_condition] THEN insert_statement:source_tableのレコードがtarget_tableのどのレコードとも一致しない場合に、そのsource_tableのレコードをtarget_tableにinsertする
- WHEN NOT MATCHED BY SOURCE [AND search_condition] THEN update_statement | delete_statement:target_tableのレコードがsource_tableのどのレコードとも一致しない場合に、そのtarget_tableのレコードをupdateかdeleteする
- search_condition:結合条件のオプション
実際の挙動を確かめてみる
丁寧に文章を読めば理解はできるかと思います!
ただ、しっかり理解するにはやはり実際に動かすのが一番!
ということでMERGE文を試してみましょうじゃないですか٩( ᐛ )و
MERGE `yourprojectname.yourdatasetname.product` AS p1
USING `yourprojectname.yourdatasetname.product2` AS p2
ON p1.sku = p2.sku
WHEN MATCHED THEN
UPDATE SET
quantity = p1.quantity + p2.quantity,
price = p1.price + p2.price
WHEN NOT MATCHED THEN
INSERT (sku, description, quantity, price)
VALUES (p2.sku, p2.description, p2.quantity, p2.price);
上記のクエリを実行すると「このステートメントで、product内の3個の行が影響を受けました」というメッセージが表示されます。
productテーブルがどうなっているか確認をしてみると、下の画像のように更新されていました。
まず、skuカラムの値が「ABC123456」のレコードに関して、productテーブルとproduct2テーブルにskuカラムの値「ABC123456」のレコードがそれぞれあるので、
WHEN MATCHED THEN UPDATE SET quantity = p1.quantity + p2.quantity, price = p1.price + p2.price
で定義されているようにquantityカラムとpriceカラムが更新されているのがわかります。
skuカラムの値が「POT76456」と「FPL29921」のレコードについては、もともとproduct2テーブルにしか存在しないレコードだったので、
WHEN NOT MATCHED THEN INSERT (sku, description, quantity, price) VALUES (p2.sku, p2.description, p2.quantity, p2.price)
で定義されているようにレコードがproductテーブルにINSERTされているのがわかります。
そして、skuカラムの値が「CHR762222」、「GCH635354」、「GRD828822」、「TBL535522」の4つのレコードはproductテーブルにしか存在しないので、MERGE文を実行後もそのまま存在しているのがわかります。
続いて「search_condition」の挙動を下記のクエリで確かめてみます。
MERGE `yourprojectname.yourdatasetname.product` AS p1
USING `yourprojectname.yourdatasetname.product3` AS p3
ON p1.sku = p3.sku
WHEN MATCHED AND p1.description = 'Furniture' THEN
UPDATE SET
quantity = p1.quantity + p3.quantity,
price = p1.price + (p3.quantity * p3.price)
WHEN MATCHED THEN
UPDATE SET
quantity = p1.quantity + p3.quantity,
price = p1.price + p3.price;
productテーブルがどうなっているか確認をしてみると、下の画像のように更新されていました。
skuのカラムの値が「ABC123456」、「POT76456」のレコードはproductテーブルとproduct3テーブルに共に存在するので、何かしらの処理が実行されるはずです。
「ABC123456」について、descriptionカラムの値が「Furniture」であることに注目しましょう。
WHEN MATCHED AND p1.description = ‘Furniture’ THEN UPDATE SET quantity = p1.quantity + p3.quantity, price = p1.price + (p3.quantity * p3.price)
で定義されているようにquantityカラムとpriceカラムが更新されているのがわかります。これはWHEN MATCHEDの後にsearch_conditionである
AND p1.description = ‘Furniture’
が追加されており、この条件に一致するレコードであるため、このような更新になっていることがわかります。
「POT76456」について、descriptionカラムの値は「Furniture」でないため、
WHEN MATCHED THEN UPDATE SET quantity = p1.quantity + p3.quantity, price = p1.price + p3.price
で定義されているようにquantityカラムとpriceカラムが更新されているのがわかります。
最後に「DELETE」と「WHEN NOT MATCHED BY SOURCE THEN」の挙動も確認してみます。
まず下記クエリでproduct3テーブルに新しいレコードを追加しておきます。
INSERT
`yourprojectname.yourdatasetname.product3` (sku, description, quantity, price)
VALUES
('TTT635354', 'test', 4, 1345.7);
続いて下記のクエリを実行してみます。
MERGE `yourprojectname.yourdatasetname.product` AS p1
USING `yourprojectname.yourdatasetname.product3` AS p3
ON p1.sku = p3.sku
WHEN MATCHED THEN
DELETE
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET
description = "not matched test";
productテーブルがどうなっているか確認をしてみるとしたの画像のように更新されていました。
skuカラムの値が「ABC123456」と「POT76456」のレコードについて、productテーブルとproduct3テーブル共に存在するレコードなので
WHEN MATCHED THEN DELETE
で定義されているようにテーブルから削除されていました。
その他のproductテーブルのレコードについては、skuカラムの値はproduct3テーブルのレコードのskuカラムの値と一致しないので、
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET description = “not matched test”
で定義されているようにdescriptionカラムの値が「not matched test」に更新されていました。
終わりに
丁寧に公式ドキュメントを確認したり、挙動を確認したりするの
マージ大事!!d( ̄  ̄)
参考記事