BigQueryでDMLが扱えます。
2016年11月よりBigQueryでもStandardSQLがベータ版が提供され、同時にDMLも使えるようになりました。
詳しくはコチラ。あと、まだベータ版ですので制限もあります。
では、実際にどのぐらい性能が出るのかをみてみたいと思います。
なんでやるの？
BigQueryはそもそもデータ保管料金がすこぶる安く、またクエリ課金ということもあり過去に紹介した方法（中村式UPDATEって言われているｗ）でVIEWとtimestampを使って最新のデータのみを抽出して、UPDATEっぽいことをやっていました。
これはこれでデータがどのように育ったかを履歴として取ったりするのに、何気に便利で自分も活用しておりました。
しかし、業務データなどでは『データの洗い替え』ということもありますし、10億行×1億行とか膨大な場合はイチイチJOINしているとお金的に痛くなります。データを入れた後に『間違えた！』なんてこともしばしば。。。。
自分も業務で必要だなぁと思ったことがあったのでやってみようと思いました。
今回使うデータ
今回はBigQueryで公開されている世界の気象情報でやってみたいと思います。これには観測地点コードを持っており、そのコードを国名に書き換えるということをやってみます。
まずは、自分のProjectのDatasetに、このDatasetに入っているTableをコピーして使います。
1行のUPDATE
まずは、ピンポイントでデータを書き換えてみましょう。gsod1929は2,081行入っています。
どうやら成功したようです。では本当にUPDATEされているのか見てみましょう。
ちゃんとUPDATEされています。想定の範囲内ですね。
1行の更新に約4秒ほどかかっています。普通のクエリと同じような時間だと思われます。
データが多いテーブルの1行だけの場合は？
さきほどのデータは2000行ということでデータ量が少なかったです。では、データ量が多い場合はどうなのでしょうか？
gsod2016には約430万行ほど入っています。これで実験してみましょう。
なるほど。それなりに時間がかかっていますね。データ量に比例して時間もかかるということでしょうか。
また、興味深いのはQuery completeのところでデータ量を見るとテーブルがフルスキャンになっていますが、費用自体はこれではありません。まだベータ版ということもあり、ここは後ほど改善されるように思います。
複数行のUPDATE
実際には1行だけというのは『間違えた。えへっ。』な場合ぐらいしかなく、ほとんどは複数行を一気にアップデートするケースが多いとおもいます。色んなやり方があると思いますが、SET のところを指定でやるのとサブクエリを使ったものをテストしてみたいと思います。データ量が多い方（gsod2016）で実験していきます。
SETを指定する
SETを指定してやってみます。stn = '425000'は364行あります。
きちんと出来ているようですね。1行よりも若干時間がかかっているようにも見えますが、これ以外にwhere句のstnを何度か変えてやってみると1行とほぼ同等の時間でした。普通のSELECT文でもよくありますが、前後5秒ぐらいは幅があるようです。
次はWHERE句の値を複数指定して、UPDATEする行数を増やしてやってみましょう。stnが2から始まるもの（24万行）をすべて書き換えてみます。
ふむ。更新する行数が増えたところで、時間は変わらないようですね。
サブクエリを使って更新する
では実際によく利用されているSETをサブクエリを利用して更新してみましょう。stationにはusafとwbanいう情報があり、これは先程から更新しているgsod2016のstnとwbanにひもづいています。また、観測地点の情報が入っています。その中にcountryという列がありますので、stnをこれに書き換えていきたいと思います。
417万行ほど更新が行われました。サブクエリを行ったので、時間が増えたのかな？と思っています。
テーブルをまたいでのUPDATE
BigQueryにはデータを効率よく管理するためにワイルドカード テーブルというものがあります。この機能の詳細はここでは省きますが、正規表現や日付でテーブルを指定出来ますので非常に便利です。使われている人も多いのではないでしょうか？
しかし、これはまだ対応されていません。残念。。。。。
複数列もやってみる。
一気に複数列を書き換えるというのをやってみましょう。gsod2016のmaxには最高気温（華氏）が入っています。
これを摂氏に変換して上書きし、ついでにflag_maxがStringなので、そこには気温によって文言を入れてみましょう。
出来ました。1列の時間と特に変わりは無いように思います。
総括
今回はUPDATEを検証してきました。
注意する点としてUPDATEした場合はlong term storageのタイマーがリセットされることです。これは地味に痛いように思います。気をつけてください。
まだベータ版ということもあり、実際のプロダクトで利用するのは難しいかもしれません。しかし、パフォーマンスについては十分に使えるものになっていると思います。テーブルを作り直すよりも速いかもしれません。
また、ついでにやってみたのですがUPDATEしている最中でもクエリを投げて普通に結果が返ってきます。よくあるのが、UPDATEしている最中は触るなとか（RDBMSの場合はロックされてUPDATEが終わるまで結果が返ってこないとか）、変な結果が返ってくるとかあるかもしれませんが、そういうことはありませんでした。UPDATEが終わった直後から、それが反映されてきちんとクエリの結果も返ってきます。素晴らしいです。
ということで、色々といぢってExplanationで動きをみながらやっていたのですが、BigQueryらしい更新のやり方だなぁと思いました。
お試しあれ。
おまけ
複数列でやったクエリをgsodのデータすべて連結してみて2億行の更新を調子にのってやってみました。1分かかってない！
