GoogleBigQueryの問い合わせクエリー肥大化でメンテが大変なことになったので、人間のためのクエリーを書けるよう工夫する話です。データ収集基盤としてBigQueryを採用しています。スマホアプリやサーバで生成される全てのユーザ行動ログをfluent経由でBigQueryに投入してデータサイエンティストに自由に活用してもらっています。

データ収集基盤の仕様設計

BigQueryはGoogle様のクラウド技術を利用して100GByteのフルスキャンだって10秒で終わるイケてるサービスです。BigQueryをデータ収集基盤として運用すればReadが超高速なので、アプリエンジニアは行動ログを仕込むだけ、データサイエンティストは蓄積されたデータを利用してビジネスに活用とWin-Winの関係が築けるはずだ、と思っていたのですがそう上手くはいきませんでした。

問い合わせクエリー肥大化による業務効率低下

データサイエンティストと雑談していたときに、GoogleBigQueryへの問い合わせクエリーを書くのに時間が掛かって仕方が無いという話がありました。実際に問い合わせクエリーを見せてもらったら500行を超える肥大化してしまったクエリーが複数ありました。使い回しや、引き継ぎ、バグの観点で現状のままだと本来の業務に割く時間が、クエリーのメンテにばかり取られてしまいそうだなーと思ったので改善方法を探してみました。

クエリー肥大化の理由

複雑なことをしたり、出力単位や集計単位が異なるログを1クエリーで扱おうとするとクエリーが肥大化します。たとえばパズドラ型ゲームで、プレイしたクエストとプレイヤーレベルとアプリ継続率の観点で調査する場合のログ単位を表にまとめてみました。

■ ログ毎の出力単位

ログ種別	データ記録タイミング	1レコードの集計単位
プレイしたクエスト	クエスト終了毎にBigQueryに投入	User毎
ユーザのデイリーログイン情報	深夜バッチで1日1回一括でBigQueryに投入	User毎
7日間継続ユーザ	デイリーログイン情報テーブルからクエリーで生成	日付+User毎
ユーザのレベル帯	デイリーログイン情報テーブルからクエリーで生成。Lv1-10,Lv11-20等	Lv帯+User毎
※ あくまでパズドラ型ゲームのログ収集を自分がつくるならというサンプル実装です。

上記データがBigQueryにある状態で、7日間継続率や30日間継続率の観点で解析するためにクエリーを書くと、最終的にJoin句を多用した巨大クエリーを書く必要があります。

■ 解析用データを出力するクエリー

BigQueryのView機能を使って解決

良い解決方法ではないですがバッチで良く使う解析項目をBigQueryに投入しようかと思ったのですが、調べてみたらOracleDBやMySQLのView機能のように、BigQueryにもView機能が存在しました。Viewは指定したクエリー結果をテーブルとして利用するための機能です。1度Viewを作成してしまえば、以後テーブルと同じように扱えます。

Viewから情報を取得するクエリー

-- レベル帯毎のユーザデータ取得View
select * from view.user_level_summary;

-- 7日間継続率を取得
select * from view.fq7;

■ 改善後のクエリー

1度作れば他のクエリーでも使い回せて、汎用性が高いです。
珍しく銀の弾丸がありました( ･ㅂ･)و

BigQueryでViewを作成する方法

■ 1. BigQueryのコンソールでクエリーを書いてSaveViewボタンを押す

■ 2. データセットにViewが出来てる