ゆーすけべー日記

天然パーマです。

データ加工機としての Google BigQuery

2014-12-30

ボケてでは既に「お題」が110万レコード、「ボケ」が2,300万レコード、それに対するユーザーの「評価」が1億1,000万レコード数存在します。このところそれをちょいと利用して、楽しいことをしましょうっていうんで、直近1年間の範囲である条件下のデータをエキスポートする必要がありました。ボケての基本的なデータは全て Amazon RDS の MySQL ストレージに格納されていて、この件で出力する形式は CSV フォーマットです。この場合、一般ユーザーがボケてのWebやモバイルアプリを操作する際に発生するクエリーとはまたひと味違った「重たい」クエリーを投げないとデータの「加工」が難しそうです。そんな際。Google BigQuery が非常に役に立ったので「大雑把に」その工程を紹介します。

BigQuery

投げては返ってこないMySQLへのクエリー

今回は最終的に以下の3つの種類のデータをCSVで書きだすことになりました。取得範囲は2013年12月から1年間とします。

  • 評価に関すること
  • ボケに関すること
  • ユーザーに関すること

例えば「評価」が rating というテーブルで表せられるならば以下のようなクエリーを MySQL に投げれば今回の要件はある程度満たせます。

SELECT * FROM rating
WHERE rating.date >= '2013-12-01 00:00:00' AND rating.date < '2014-12-01 00:00:00'

しかし、2番目の「ボケに関すること」として「評価された対象のボケを集め、評価点数順にソートする」ってな要望があると JOIN が発生して、かつソートコストが高くなります。下は安直に書いた「例えば」のSQLクエリーです。

SELECT boke.*,SUM(rating.rate) as rate_sum FROM boke,rating
WHERE boke.id = rating.boke_id
AND rating.date >= '2013-12-01 00:00:00' AND rating.date < '2014-12-01 00:00:00'
GROUP BY boke.id ORDER BY rate_sum DESC

これがいわゆる「重たいクエリー」になって投げてはなかなか返ってこないことになります。また、数十分〜数時間待って返ってきたとしても希望する結果でなければ再度修正して、再び同じ時間立たなければ、それが正しいかを検証することが出来ません。もちろん並び替えはせずに評価値のスコアを持つテンポラリーなテーブルをつくりインデックスさせるなど、他に賢いやり方があるかもしれませんが、今回は「Google BigQueryに頼ってなるべく頭をひねらない」という趣旨なので割愛しました。

MySQLデータを BigQuery にのせる

さて今回の解決策は前述の通り、Google BigQuery を使うというもの。「一時的に何千台のマシンを使ってフルスキャンさせる」という「強引な」 Google ならではのやり方は今回のような「ある意味強引な」クエリーを試すには持ってこいです。

諸々諸事情を加味して、以下のような手順で希望する3つのデータ＝CSVファイルを得ることが出来ました。

  1. 一時的にEC2のハイメモリインスタンスを立ち上げる
  2. RDSから mysqldump を実行し手元にダンプファイルをつくる
  3. 上記インスタンス上のローカルに MySQL サーバを立てて、ダンプファイルをインポート
  4. ソートや JOIN などコストのかかるクエリーは抜いて指定範囲内のデータをCSVで書き出し
  5. Googleのコマンドラインツールから Google Cloud Storage へアップロード
  6. スキーマファイルをつくり、CSVのデータを Google BigQuery のテーブルへインポート
  7. MySQLでは待たないと返ってこなかったようなクエリーを投げる => 10s ちょいくらいで結果が出る！
  8. 結果を一旦別テーブルへ書き出す
  9. そのテーブルデータを Google Cloud Storage に CSV 形式でエキスポート
  10. URLをみんなで共有

冒頭の1〜3はしばらく待ってれば終わるとして、その後の作業を的確にやれば、半日、いや1時間くらいで作業が終わった！

BigQuery

ここで改めて、Google BigQuery の凄さというか便利さを「個人的に」まとめると以下の通り。

  • しかたのない「重たいクエリー」をマシンパワーで高速に処理してくれる
  • Browser Tool が充実していて、クエリーの実行から、結果の表示、履歴の保存まで用意されたWebインターフェースで出来る
  • Google Cloud Storage 等とも連動して便利
  • 今や誰もが持っているGoogleアカウントで権限を移譲可能
  • ストリームデータの取り込みもある
  • 何よりも安い！ ( 今回のデータ加工は無料範囲内で収まった )

いや〜 今回のケースはデータの出力と加工だったので、「楽出来ることは楽して」サッサと終わらせたかったので非常に BigQuery が重宝しました。上記フローは初めて試したものだったので、ある程度半自動化していけば、より簡単になるかもしれませんね。現在は他にも、行動ログに近いものをストリームで BigQuery に流し込んでいるのですが、それだけではなくデータ加工にも使えるよん♪ というお話でした。ちゃんちゃん