Kesinの知見置き場

知見を共有していきたいじゃないですか

BigQueryでinsert or updateを実現する

GCP

BigQueryにデータをインポートする基本的な手段はloadでCSVやJSONのデータからテーブルを作成したり、既存のテーブルへの追記です。
ただ、今回は要件的にRDBでいうところのinsert or update（またはupsertとも呼ばれる）を行いたかったので、BigQueryで可能なのか調べてみました。

tl;dl

MERGEを使いこなせば可能。ただし料金には注意。

INSERT/UPDATE

BigQueryはSQLのINSERT/UPDATE/DELETEによってテーブルのデータを直接編集することが可能です。
https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax

ただし、残念なことにMySQLにおけるINSERT ON DUPLICATE KEY UPDATEやpostgresqlのINSERT ON CONFLICE UPDATEに相当するいわゆるinsert or updateの機能は存在していません。

なんとか実現するとしたら、更新したいデータのうちBigQueryのテーブルに既に存在するものと、まだ存在していない2種類に分割してそれぞれをINSERT/UPDATEすることで一応可能でしょう。
しかし、データを分割するのに手間がかかるだけではなく、BigQueryの料金は1クエリ毎に計算される仕組み（料金については後述）なのでその点でも非効率です。

MERGE

BigQueryにはINSERT/UPDATE/DELETEに加えて、MERGEという操作が存在します。 詳しい説明はドキュメントにあるのですが、説明がなかなか複雑なのでどういう挙動をするのか初見では理解するのが難しいと思います。
自分の理解でざっくりと説明すると、MERGEは操作する対象のテーブル(target)と変更するためのデータのテーブル(source)をJOINして、その結果に応じてINSERT/UPDATE/DELETEが行える操作です。

という挙動であることを把握してからドキュメントのサンプル事例を見ると、これを活用することでinsert or updateの挙動が実現できることに気がつきます。
まず、変更したいテーブルをtarget、変更するためのデータを適当なテーブルにアップロードしてsourceとし、それらをPrimary Keyと考えたいカラムでJOINします。そして、既にPrimary Keyが存在する場合はUPDATE、存在しない場合はINSERTを実行します。

実際にBigQueryのSQLにすると以下になります。

#standardSQL
MERGE
  `blog.target` AS T
USING
  `blog.source` AS S
ON
  T.user_id = S.user_id
WHEN MATCHED  THEN
  UPDATE SET T.comment = S.comment
WHEN NOT MATCHED THEN
  INSERT (user_id, comment)
  VALUES (user_id, comment)

これを以下のtargetとsourceのテーブルに対して実行してみます。

targetテーブル

user_id comment
A null
B null

sourceテーブル

user_id comment
B 'Merge update'
C 'Merge insert'

MERGE実行後のtargetテーブル

user_id comment
A null
B 'Merge update'
C 'Merge insert'

狙い通り、1つのクエリだけでinsert or updateの挙動が実現できていますね。

料金について

MERGEは大変便利なのですが、BigQueryにデータを追加する基本手段のloadが無料であるのに対して、MERGEはSELECT同様に課金が発生しますのでここについても触れておきます。

INSERT/UPDATE/DELETEの料金は現在のドキュメントでは以下となっています。 https://cloud.google.com/bigquery/pricing#free-tier https://cloud.google.com/bigquery/pricing#on_demand_pricing

注意すべきは、どれだけテーブルのデータ数が少なかったとしても最低のデータ処理容量は10MBだということです。

料金は MB 単位のデータ処理容量（端数は四捨五入）で決まります。クエリが参照するテーブルあたりのデータ最小処理容量は 10 MB、クエリあたりのデータ最小処理容量は 10 MB とします。

BigQueryの無料枠は1GB/月ですので、100回を超えてクエリを実行するとそこから課金が発生します。ちなみに、SELECTも最小容量は10MBですがJOINすると倍の20MBになるので注意です。

そして、MERGEもJOINと同じく最小容量は20MBです。つまり50回を超えると課金発生です。1ヶ月に50回ですので案外少ない回数で無料枠を使い切ってしまいますね。

ちなみに先ほどのサンプルSQLを実行した結果はこのようになっており、Bytes Billedが実際に20MB分となっていることが分かります。

Bytes Processed  44 B
Bytes Billed    20.0 MB

20MBはあくまで最小容量ですので、操作対象となるテーブルのデータ量が多い場合は、当然1クエリあたりの料金は比例して多くなります。バッチ処理などで定期的にMERGEを使う想定の場合、一度クエリを実行してみてそのときの情報から一ヶ月単位での利用料を軽く見積もってみるといいでしょう。

注意

前述の料金の説明は2018/06/18日時点でのドキュメントを参考にしています。
また、MERGEについても執筆時点ではベータ版です。従って、将来的に使い方が変更される可能性もゼロではないです。

参考