BigQueryでUPDATE？？

はじめに

先日のGCP ja Nightはどーもありがとうございました。
『運用がQiitaだと？』と言うつぶやきもありましたが、まぁそれはそれで良いと思うんですよね。世の中の常識の運用なんてぶっ飛ばしていいと思う。運用から見えてくるいろんなモノを新しいテクノロジーで解決するってのも積極的にやらないとね。じゃないと開発だけにお株を奪われてつまんないでしょ？（笑）
で、その中でうちの子が言ってた『データをアップデート出来ない？気にしなきゃいいんだ！』を詳しく解説したいと思います。

どういうデータをアップデートするのか？

そもそもBigQueryに入れているデータなんてアップデートするもの入れなきゃ良いんじゃない？ということもあります。が、うちの場合だと商品マスタなどを入れて他のデータとJOINすることがあります。
なんで商品マスタをアップデートする必要があるか？例えば某48人居るアイドルグループの場合、総選挙で結果が出るとすぐにCDの予約が始まったりします。でも、その時はタイトルが『タイトル未定（仮）』とかになるんですね。そして、後ほどタイトルが決まり発売になるという流れです。その際にずっと『タイトル未定（仮）』だとカッコ悪いし？かと言って商品マスタを毎日全部入れ替えるのなんてなんだかなぁ。。だからアップデートしたいということになります。

じゃ、どうする？

と、言うことで考えました。どーすっかな？と。

吐き出すレコードにタイムスタンプを持たせる。

で答えが『レコードにタイムスタンプを持たせる』ということにしました。まずはデータ吐き出しのところを改修しました。基準としては商品マスタが書き換わったものを差分データとして吐き出します。その際に吐き出した時間を各レコードの最後にタイムスタンプとして持たせます。Oracleだとsysdateという感じで。

インポートするときもTIMESTAMP型で。

BigQueryに差分データをインポートする際に、タイムスタンプも入れ込みます。その際にTIMESTAMP型でインポートしましょう。

Viewを作ります。（これが一番のポイント！）

で、Viewを作ります。どうやって作るか？
まずはBigQuery ConsoleにアクセスしてSQLを入力します。
SQLは簡単に言うとselectのところでタイムスタンプのmax値を取ってくるってわけです。

例としてカラムがshohin_name,shohin_code,shohin_price,update_dateとしましょう。
update_dateがデータ差分入れ込み時のタイムスタンプとします。
データセットがhoge、テーブル名がshohin_masterとします。

SELECT
 shohin_name,
 shohin_code,
 shohin_price,
 max(update_date)
FROM
 [hoge.shohin_master]
GROUP EACH BY
 shohin_name,
 shohin_code,
 shohin_price

こんなSQLを入力して最後に『Save View』を押下して、Viewを作ります。

これで、このViewを参照することで最新のデータを取ることが出来ます。

アップデートじゃないじゃん！

まぁ、そう言うなよ（笑）
でも、これで最新のデータとって来れるわけでしょ？うちで調べてみると約3%ぐらいがマスタデータ変わってるのでこれで十分です。
だがしかーし！これだと約1か月ほどで倍の容量になるわけで。それはそれでもったいない。
じゃ、どーする？？？？

たまには洗い替え

と言ってもわざわざデータをまた吐き出してやるわけじゃない！BigQueryでやればいいわけですよ。
どうやるか、一時テーブルを作ってそこにViewのデータを突っ込む⇒一時テーブルのデータを元のテーブルに戻す、という感じで。
これもBigQueryConsole上でやっちゃいます。（コマンドでも出来るけどね）

SELECT
 *
FROM
 [hoge.view_master]

これを実行するときに以下のようなオプションにします。
『Destination Table』を一時テーブルの名前を入力。この場合は[TMP_TABLE.tmp_master]。
（すでにある場合は『Write Preference』を『Overwrite table』を選択）
『Results Size』の『Allow Large Results』にチェックを入れる。

あとはこれを元のテーブルに戻してやります。同じですが、すでにテーブルがあるのでデータ入れ替えということで『Write Preference』を『Overwrite table』に必ずチェックを入れます。

SELECT
 *
FROM
 [TMP_TABLE.tmp_master]

『Destination Table』を商品マスターの名前を入力。この場合は[hoge.shohin_master]。
『Write Preference』を『Overwrite table』を選択。
『Results Size』の『Allow Large Results』にチェックを入れる。
これでデータ入れ替えが完了です。
ちなみにテーブルをdropしてからcreateをするのは厳禁です！これだとViewの作り替えをしなければならないからです！絶対にやめましょう！これで何度泣いたことか！

応用編として

これと同じようなことでカラム追加なんかも出来ますよ～っと。

最後に

どーでしたか？工夫次第でアップデートみたいなことも出来るわけです。
洗い替えもBigQueryのパワーを使えば、一からデータ吐き出して作り替えないでも簡単に恐ろしく短時間でできます。これは体感してみないとわかんないですね。
ちなみに5千万件、23GBぐらいのデータで2分20秒。これってすごくないですか？
このあたりのお話は第一回GCPUG in Tokyo！でやりますね。興味がある方は是非！