notebook

都内でWEB系エンジニアやってます。

BigQueryのMERGE文で削除、更新、追加を同時に行う

GoogleCloudPlatform BigQuery

MERGE文はUPSERT的な操作をしたいときに取り上げられることが多いイメージだったがもう少し込み入った要件でも活用できそうということで試してみた

MERGE文についてのドキュメントは下記

データ操作言語の構文  |  BigQuery  |  Google Cloud

cloud.google.com

要件

  • 特定のキーをもとに次の判定をする
    • 変更があった場合は更新
    • レコードが存在しない場合は追加
    • 特定のstatusになっている場合は削除
      • サンプルではstatusをenable,disable,rejectedとし、rejectedの場合は削除する

準備

テーブル作成

サンプルとしてmetadataテーブルを用意する

bq mk -t 'metadata_id:INT64,classification:STRING,name:STRING,status:STRING,column1:STRING' --description 'metadata' sample_dataset.metadata
bq mk -t 'metadata_id:INT64,classification:STRING,name:STRING,status:STRING,column1:STRING' --description 'metadata' sample_dataset.metadata_tmp

サンプルデータを入れる

初期データを入れる

  • before.ndjson
{"metadata_id":"1","classification":"A","name":"hoge","status":"enable","column1":"c3"}
{"metadata_id":"2","classification":"B","name":"fuga","status":"enable","column1":"c3"}
{"metadata_id":"3","classification":"A","name":"piyo","status":"enable","column1":"c3"}
{"metadata_id":"4","classification":"B","name":"foo","status":"disable","column1":"c3"}
{"metadata_id":"5","classification":"A","name":"bar","status":"enable","column1":"c3"}
{"metadata_id":"6","classification":"C","name":"baz","status":"disable","column1":"c3"}

bq load --replace --source_format=NEWLINE_DELIMITED_JSON \
'sample_dataset.metadata' \
./before.ndjson

変更用のデータを入れる

  • changes.ndjson
{"metadata_id":"1","classification":"A","name":"hoge","status":"enable","column1":"c4"}
{"metadata_id":"3","classification":"A","name":"piyo","status":"enable","column1":"c4"}
{"metadata_id":"5","classification":"A","name":"bar","status":"rejected","column1":"c4"}
{"metadata_id":"7","classification":"A","name":"new","status":"enable","column1":"c4"}

bq load --replace --source_format=NEWLINE_DELIMITED_JSON \
'sample_dataset.metadata_tmp' \
./changes.ndjson

MERGE文

metadata_id,classification,nameをキーとして判定する

  • merge.sql
MERGE `sample_dataset.metadata` target USING `sample_dataset.metadata_tmp` tmp
ON(target.metadata_id = tmp.metadata_id AND target.classification = tmp.classification AND target.name = tmp.name)
WHEN MATCHED AND tmp.status = 'rejected' THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET metadata_id = tmp.metadata_id, classification = tmp.classification, name = tmp.name, status = tmp.status, column1 = tmp.column1
WHEN NOT MATCHED THEN
  INSERT ROW

MERGEの実行

$ bq query --format json < merge.sql
Waiting on bqjob_r619238db338a57fd_000001770cae55e8_1 ... (1s) Current status: DONE
Number of affected rows: 4
  • MERGE実行前

f:id:swfz:20210208194835p:plain

  • 実行した結果

f:id:swfz:20210208194842p:plain

ピンクが更新、青が追加、オレンジが削除

蛇足

パーティションデコレータを指定して特定のパーティションに対してMERGEを行うことができるのか試したができなかった

Table "sample.daily_data$20201101" cannot include decorator

他所感

  • 今回のサンプルではtargetとsourceのテーブルを同じスキーマにしているがMERGE文自体は別に同じテーブル構成である必要もないのでさらに色々できそう
  • NOT MATCHED BY SOURCEを使うことでソースになければ削除するといった操作もできそう

MERGE文使用時のSELECTに料金が発生するはずなのであまり大量のデータの更新には向いていない気もするがこういう操作がSQL文だけで行えるのはとても楽だと思う

特に更新、追加、削除をアトミックに処理できるのは大きい

スクリプト書いて更新、追加、削除を別々でやる場合このくらいの要件でも大分面倒な感じになるはずなので

まぁそもそもUPSERTとか頻繁にやるような要件だったら要件の方を見直したほうが良いのでは?と思うこともあるが…

また、ドキュメントに今回のサンプルケースに近しい例は載っていた（記事書いている最中に気付いた）が一度試してから読み直すまでピンとこなかったのでやはり一度触ってみるのが大事だなーと感じた

まとめ

MERGE文を用いて次のような要件の操作を行うことができた

  • 特定カラムをキーとして
    • 現在存在しない場合は追加
    • すでに存在する場合は更新
    • 特定のカラムの値によっては削除

BigQuery触れば触るほど便利でなんでもSQLで行けるっしょ!という気分になりつつある

あとはSQLを管理できるサーバレスなワークフローツールが欲しい…