あけましておめでとうございます。データサイエンティストの金田です。現在 iQON では、データ分析の基盤として BigQuery を利用しており、データ分析や計算負荷の高いバッチ処理等に活用しています。しかしながら、通常のデータベースとは若干異なる点があり、効率的な運用ができるまでに様々な試行錯誤がありました。今回はそれらの試行錯誤によって得られた知見をベストプラクティスとして紹介したいと思います。
ログデータのテーブル名に日付を入れる
BigQuery でログを保存する場合は、テーブル名の最後に yyyymmdd 形式で日付を入れることをお勧めします。理由は二つあり、１つ目はスキャン対象となるデータ量を抑えられるため、２つ目が Web UI でのテーブル管理が容易になるためです。
処理データ量の抑制
まず、スキャンするデータ量を抑えられるという点ですが、BigQuery ではクエリを発行した際、スキャンしたデータ量に応じて課金が決まってくるため、扱うデータ量が大量になってくると、いかに無駄なデータへのクエリの発行を減らせるかが重要になってきます。
その点、BigQuery では テーブルワイルドカード関数という関数が容易されており、テーブル名の最後に yyyymmdd 形式で日付を入れておくと、指定した期間のテーブルだけを参照するといったことが可能です。
具体的には、「nginx」というデータセットに、「access_yyyymmdd」といった形式でアクセスログのデータが保存されているとします。その際、11月分のログを分析したいといった場合は、TABLE_DATE_RANGE 関数を使って、下記のようにクエリを書くことで、簡単に指定した期間のログだけを処理対象とすることが可能です。
[sql] SELECT * FROM (TABLE_DATE_RANGE(nginx.access_, TIMESTAMP('2015-11-01'), TIMESTAMP('2015-11-30'))) [/sql]
これにより、必要な期間のログだけをクエリの対象とできるため、スキャンするデータ量を抑えることが可能になります。
Web UI でのテーブル管理
また、ログデータを日別で管理することで、Web UI でのテーブル管理も容易になります。
通常は、データセットに日別に多くのテーブルを作成してしまうと、すべてのテーブルが表示されてしまうため、Web UI での管理が煩雑になってしまいますが、テーブル名の最後に日付を入れておくことで、下記のようにまとめて表示をしてくれます。
.png "bigquery_table")
個別の日付のテーブルに対して操作を行いたいときは、右側のプルダウンで日付を選択すると、特定の日付データだけ操作することが可能です。
\
ビューを活用する
他のデータベースと同様に、BigQuery では仮想テーブルを作成するビューの機能を持っていますので、この機能を活用することで効率的に運用をすることができます。
通常、ログやデータベースのデータを分析に使う際は、JOIN 操作を行って、トランザクションテーブルとマスタデータを結合したり、SQLの関数を使って加工を行ったりしますが、そのような操作はビューとして保存することができます。具体的には、Web UI のクエリ作成画面の下に、「Save View」というボタンがありますので、こちらを押すことでクエリで作成したテーブルを仮想テーブルとして保存することができます。
保存されたテーブルは、下記のように緑色のアイコンと共に表示されるので、ビューであることがするわかります。
実際の運用においては、データを利用するユーザーは、分析用に加工されたビューに対してクエリを発行するようにすることで、煩雑なSQL文が量産されるという事態を防ぐことができます。
また、例えば上記で説明した TABLE_DATA_RANGE 関数と CURRENT_TIMESTAMP という関数を組み合わせて、任意の期間のデータだけを参照対象とするといったことも可能です。例えば、下記のクエリでは過去60日のログだけを計算対象にしていますが、このように期間を絞ったユーザー参照用のビューを用意することで、無駄なデータに対するクエリ発行を防ぐことができると同時に、ユーザーが意図せず大量のデータに対してクエリを発行してしまうというリスクを抑えることもできます。
[sql] SELECT * FROM (TABLE_DATE_RANGE(nginx.access_, DATE_ADD(CURRENT_TIMESTAMP(), -60, "day"), CURRENT_TIMESTAMP())) [/sql]
ただし、大量の JOIN が発生したりするビューを作成する場合は、クエリの結果が返ってくるのに時間がかかることがありますので、レスポンスタイムが気になる場合は、必要に応じてビューではなく、バッチで実体のテーブルを作成することも考慮に入れてください。
非構造化データをBigQueryで扱う
BigQuery には、JSON 関数というものが用意されており、文字列で保存されているJSONから、キーを指定して必要な値だけを抜き出すということが可能なため、非構造化データを扱うことも可能です。
特に、アクセスログ等は、ビジネス要件によって取得項目が変更になったりすることもありますので、BigQuery で非構造化データも扱うことができると知っておくと、設計の幅が広がります。具体的には、下記のような形式で文字列としてJSONのデータが入っているとした場合、
スキーマ
| name | type |
| timestamp | TIMESTAMP |
| log_json | STRING |
データ例
| timestamp | log_json |
| 2015-12-27 12:07:49 UTC | {"where":"item_detail","item_id":"3030910","model":"iPhone 6"} |
テーブルから item_id だけを抜き出したいときは、下記のように記述することで JSON 形式で保存してあるデータを取り出すことが可能です。
[sql] SELECT * FROM (TABLE_DATE_RANGE(nginx.access_, TIMESTAMP('2015-11-01'), TIMESTAMP('2015-11-30'))) [/sql]
ただし、この方法を使う場合は、JSON文字列として保存されている全てのデータをスキャンする形になりますので、データ量が大きく、頻繁にクエリを発行する必要があるものに関しては、予め必要な項目だけを抜き出して、構造化したデータを別テーブルとして保存しておいた方がよいと思います。
ストリーミングインサートは必要に応じて
BigQuery ではストリーミングインサートという機能があり、例えば Fluentd から直接ストリーミングで BigQuery へデータをインポートすることが可能です。
ただし、便利なストリーミングインサートですが、通常のバッチでのインポートに比べて、料金がかかるということと、データに欠損が生じる可能性があるという事があるため、特にリアルタイムに結果を把握必要のないものに関しては、日次バッチでのインポートも考慮した方がよいと思います。
また、ストリーミングインサートを使う場合でも、データに欠損が生じる場合がありますので、バックアップとしてS3やGCSにも保存しておくようにしておきましょう。
バッチ処理にAirflow を活用する
上記でも記載した通り、データ分析基盤として BigQuery を活用するためには、日次でデータを BigQuery へインポートしたり、分析用のテーブルを日次で作成したりする必要がでてきます。
通常、このようなバッチの処理には、cron を使ってバッチを起動する場合が多いと思いますが、cron だと、ジョブに依存関係をもたせたり、ジョブが失敗した場合に、どこで失敗したのか特定することが難しいということがありました。
そこで、VASILYでは、バッチの処理に AirBnB 社が開発した、Airflow というジョブ管理ツールを使うことで、バッチ処理にまつわる業務を効率化しています。このツールを使うことで、例えば下記のように複数のジョブに依存関係がある場合も、その依存関係を考慮して処理を実行してくれ、万が一失敗した場合でも、リトライ処理を自動で行ってくれたり、問題の特定がすぐにできるようになったため、データの管理に関する業務が大幅に改善されました。
最後に
今回は、データ分析基盤として BigQuery を活用するにあたり、どのような点に気をつけて設計や運用を行なったらよいかについて説明を行いました。通常のデータベースとは若干異なるところがありますので、その点は注意が必要ですが、使いこなせば従来では考えられなかった低コストでデータ分析の環境を整えることが可能です。
また、今回ご紹介した BigQuery 以外にも、VASILY では、Tableau を使ってビジネスユーザーがクエリを書くことなく自由にデータを取得できるような仕組みを構築したりと、様々な取り組みを行っていますので、次回はその辺りの仕組みも説明できればと思っています。
最後に、VASILY では、データサイエンティスト を募集しています。データの分析だけでなく、データエンジニアの役割も担う必要がありますので、幅広い経験を積むことができます。 ご興味のある方は是非こちらからご応募よろしくお願いいたします。
