BigQueryを試してみました

おひさしぶりのt_konoです。

最近、会った人からBigQueryが速い！安い！旨いとは言ってませんが、とにかく速いとべた褒めだったので、自分でも使ってみました。

始め方は色んな所で説明されているので割愛して、いきなりやってみます。

BigQueryでは、サンプル用のテーブルが存在していて、数億件のデータなども用意されています。
そのデータを使って試してみても良かったのですが、今回僕がやってみようと思っていたことは
通常、集計バッチなどで作成しているレポートデータを作成することなので、サンプルデータではなく自前で用意しました。

用意にはGenerateDataというPHPのアプリケーションを使いました。
テスト用データ作るのに結構便利なアプリケーションなのですが、一度に作成出来るデータが10万件（Webサイト上では100件だけ）だったのでコードを書き換えて1000万件作成出来るようにしました。

SQL文を出力してくれるのですが、コピペしようとしたらクリップボードに乗っからず、ブラウザがお亡くなりになってしまうので、更にいじって直接ローカルのMySQLに生成したデータをINSERTするようにしました。

それだけでも2日くらいデータ作成にかかったので、素直にサンプルデータを使うことを検討すれば良かったです。

では、出来たデータをBigQuery上に登録するとします。

■tableとデータの準備
datasetの作成は説明するまでもないので省略します。

tableの準備ですが、データを同時に登録出来るので先にデータを準備しておきます。
データはCSV、JSON、Cloud Datastore Backupのいずれかの形式で指定出来ます。
ただし、この場合10MBまでのファイルしかアップロードできません。
※ Cloud Datastore Backupはごめんなさい。調べてません。

10MB超の大量のデータを登録したい場合はCloud Storageにファイルを用意することで可能となります。

今回は400MB程度のファイルも含まれていたので、まずはCloud Storageにファイルを用意することにします。
こちらも説明するほどのこともないので省略します。

とりあえず、以下のデータをCloud Storage上に用意しました。

user.csv gs://backet_name/user.csv
history.csv gs://backet_name/history.csv
merchant.csv gs://backet_name/merchant.csv

Cloud Storage上のファイルはgs://[バケット名]/[ファイル名]と指定します。

table作成とデータ登録はこんな感じになります。

「Load data from」でGoogle Cloud Storageを選択し、Cloud Storage上のパスを指定します。

次にtableの構造を指定します。
選べる型はSTRING、INTEGER、FLOAT、TIMESTAMPの4つです。

最後にimportするデータのデリミタや読み飛ばすヘッダ行の行数などを指定して「Submit」を押すとtable作成（データ登録）が実行されます。

history.csvには約1000万レコード分のデータがあるのですが、Importには5分程度かかりました。
ローカルPCのMySQLにInsertした時は10分程度だったので、Importは思ったより速くないのかもしれません。

過去のJobの結果はこんな感じで見れます。

tableを作成し各CSVファイルをImportしたところ、こんな感じになりました。
センスのないテーブル名ですが気にしないでください。

・user table

ユーザー情報です。
情報として、ユーザー名、email、カテゴリーIDを持っています。
レコード件数は1万件です。

・merchant table

商品情報です。
情報として、商品名、価格、製造日を持っています。
レコード件数は1万件です。

・history table

購入履歴です。
情報として、ユーザーID、商品ID、個数、購入日を持っています。
レコード件数は1000万件です。

■それっぽいデータを作成するクエリを投げてみる

想定しているストーリーとして、ユーザーのカテゴリー毎に購入日別の総購入金額を集計したいということとします。

出力されるデータはこんなカラムになります。
カテゴリーID | 購入日（購入履歴テーブルの購入日時を日で集計したもの） | 購入金額（価格 x 個数）

購入履歴テーブル（history table）には購入日時が保存してあるのですが、購入日は保存していないので、変換して集計してあげる必要があります。

実際のクエリはこんな感じになりました。
1000万件 x 1万件 x 1万件のJOINになります。

SELECT u.category_id,
  STRFTIME_UTC_USEC(PARSE_UTC_USEC(cast(h.date as String)),"%Y%m%d") AS ymd, 
  SUM(h.count) * SUM(m.cost) AS total_cost
FROM test001.history h JOIN test001.merchant m
  ON h.merchant_id = m.id
JOIN test001.user u
  ON h.user_id = u.id
GROUP EACH BY u.category_id, ymd
ORDER BY 1, 2;

そして、上記のクエリの実行結果が以下です。

わずか19.2秒で完了しました。
今までの経験上、オンプレ上のRDBMSで同じ様なことをやると、結果が返ってくるまでに膨大な時間がかかります。
と言うかスペックにもよりますが返ってこないんじゃないかと思います。

ちなみに自分のローカルPCのMySQLに対して似たようなクエリを投げてみたところ、結果が返ってくることはありませんでした。
スペックがあまりにも違いすぎるので、参考にはならないのですが。

■感想
1000万件 x 1万件 x 1万件程度だとデータとしては足りない感じがしますが、それでもわずか数十秒程度で結果が返ってくるのは、とんでもなく速いと思います。
実際に他の方が12億 x 8億件という途方も無い量のデータのJOINを実行したところ、それでも1分程度で返ってくるという驚異的な速度です。

また、SQLに近い感じで記述出来るので、Hadoopなどと違ってとても簡単です。

コスト的にもデータの保管に$0.020（GB 単位/月）、クエリの実行に$5（処理容量単位：TB）なので、とてもお安い価格となっております。
クエリの実行の処理容量単位はスキャンしたデータのサイズということになります。

実際にBigQuery使うと、今までバッチで定期的に集計データ作ったりしていたことが、ソースデータからダイレクトに画面に表示させることも出来てしまいます。
データの挿入については、Streaming Insertを行えば、秒単位程度のリアルタイムのデータのInsertが行えるので、ログをパースしてBigQueryに都度入れるような使い方も出来ます。

速い！安い！簡単！ってところですかね。