BigQueryに蓄積されたデータをSpreadsheetに読み込んで、手軽にちょっとした分析をできる環境を作りましょう。

背景

BigQueryに蓄積された大量のログデータは、サービス・商品のヘルスチェックやマーケティング・企画のための分析など、様々な場面で役立てられます。分析したい内容によってはGoogle Data StudioやTableau等のBIツールを使うことで、誰でも効率的に指標をチェックできる状態を作ることができますが、非定型の分析作業ではどうしても生のログ(もしくはそれに近いデータ)を分析したい場面が出てきます。

しかし「クエリーを直接書いてBigQueryから目的のログデータを取得する」という作業は、誰でも手軽にできるものではありません。正しく目的のデータを取得するには、ログのデータ構造(テーブル構造・テーブル間の関係性・その他のルール)を正確に理解し、適切なクエリーを構築できるスキルが求められます。日常的に業務でクエリーを利用しないメンバーにとっては、この作業は非常に難易度が高い作業になってしまいます。

データの抽出を都度エンジニアやデータサイエンティストに作業を依頼する形では、その分開発や他の作業に当てられる予定だったエンジニア/データサイエンティストの工数が失われてしまう上に、エンジニアの手が空いていないタイミングでは分析を進めることができず、色々な点でビジネスのスピードを損なうことになってしまいます。

そこで、ビジネス上で参照される場面が多いログデータに関しては、予め扱いやすい形でSpreadsheet上にインポートしておく方法を考えます。Spreadsheetにデータがあれば、クエリーを叩けるメンバーの手が空いていなくても、誰でも手軽に好きなタイミングで分析を進めていくことができるようになります。

BigQuery上のデータをSpreadsheetから参照できるようにする方法は多数存在します。それぞれ一長一短があるので、どのような場面でどの方法をとるべきか、考えていきます。

Connected Sheetsを使う

Spreadsheetに用意されているConnected Sheetsという機構を使うと、BigQueryでのクエリー結果をシートとして参照できるようになります。クエリー以外にコードを書く必要がなく、インフラの用意も不要という大きなアドバンテージがあります。

Connected Sheetで設定したクエリーは、設定を行ったユーザの権限で実行されます。異動や退職などで設定したメンバーのアカウントが無効化されると、以降データの更新ができなくなってしまうという問題があります。

また、インポートできるデータはクエリーで表現できる内容に限定されます。複雑なJSONのパースや、結合・加工を必要とするようなデータのインポートには限界があります。

インポートしたいデータがクエリーで表現可能で、かつ短期的にしか使われないような用途のシートであればこの方法を使うのが一番簡潔です。

ポイント

コーディングが不要
インフラの準備も不要
インポートできるデータはクエリーで表現できる範囲に限る
設定したユーザの権限で動く(サービスアカウントは使えない)ので、人の異動や退職に弱い

Google Apps Scriptを使う

SpreadsheetでConnected Sheets/データコネクタの機能が提供される以前は、Google Apps Scriptを使ってBigQueryからシートにデータをインポートするやり方が広く使われていました。
Google Apps ScriptにはBigQuery Serviceというサービスが用意されており、これを使うとGoogle Apps Script上でBigQuery APIを使うことができます。

自分でコードを書く必要があるため手軽さはConnected Sheetsには劣りますが、BigQueryから取得したデータを加工したり、状況に応じてクエリーの内容を変更するなど、複雑な用件にも対応しやすくなります。スクリプトの編集や実行は全てブラウザ上で作業可能で、またトリガーの機能を使って定期的に自動でスクリプトを実行することも出来るので、自分でゼロからスクリプトを書いて自前のサーバーにデプロイして実行するよりは手軽です。ただし、Google Apps Scriptの実行時間には制限があるため、長時間かかるような処理だと実行に失敗する場合があります。

Google Apps ScriptのBigQuery Serviceを使ったBigQuery APIの呼び出しでは、基本的にスクリプトを実行したユーザの認証情報が使用されることになります。この部分の処理、UI制御をGoogle Apps Script / BigQuery Service側が自動的にやってくれるのは非常に便利な反面、この機構ではサービスアカウントを使用することができません。

残念ながらBigQuery Serviceでサービスアカウントを使って認証する方法については、2020年12月現在ドキュメント上で言及が見当たりません。BigQuery Serviceの代わりにUrlFetchAppを使うことで、サービスアカウントの認証情報でBig Queryを使用することは不可能ではないと思われますが、リクエストの生成からレスポンスのパースまでを全て自前で実装する必要が出てきてしまうため、効率的ではありません。また、この方法では必然的にサービスアカウントの認証情報をGoogle Apps Scriptのプロジェクト内にアップロードしておく必要が出てくるため、セキュリティ面での注意も必要になってきます。

また、前述のトリガーに関しても、基本的には設定したユーザの権限で動作します。トリガーを設定したユーザのアカウントが無効化された場合には処理が走らなくなったり、エラーになってしまいますが、他のユーザからはそのことに気が付きにくいという問題もあります。

上記の点から、Google Apps Scriptを使ったインポートは、Connected Sheetsを使ったインポートより自由度は高くなるものの、長期的に安定的に使用していくには不安が残る方法となります。

ポイント

インフラの準備が不要
データの加工や状況に応じたクエリーの変更など、自由度が高い
基本的には設定したユーザの権限で動く(サービスアカウントは使えない)ので、人の異動や退職に弱い
頑張ればサービスアカウントを使用できるかもしれないが、手軽さは失われる

ETL/ELTツール、サービスの検討

サービス間のデータの転送という意味では、ETLツール/サービスの利用も検討すべき選択肢の一つです。インポートの最中におけるエラーハンドリング、リトライ処理などのケアを考慮すると、ETL/ELTツールを使った方が自分で転送用のプログラムを書くより簡単に堅牢なシステムを構築できます。

Spreadsheetへの書き出しに対応したETL/ELTサービスはほとんどなし

ETLサービスを使えば、自分で転送用のサーバーやプログラムを用意することなく、簡単な設定だけでデータの転送を実現できます。しかし2020年12月時点では、Spreadsheetからの読み込みに対応しているサービスは多いものの、Spreadsheetへの書き出しに対応しているサービスはほとんどありません。扱えるデータの量や性質を考えると、Spreadsheetへの書き込みは他のサービスに比べて制約が非常に大きいため、今後対応サービスが増えることはあまり期待できません。

Embulkはプラグインがあるものの安定して動かず

オープンソースのバルクデータローダーEmbulkはどうでしょうか?自分でサーバーを用意する必要はありますが、Embulkには並列処理による高速化やエラー発生時のリカバリー、冪等なリトライの機能などが用意されており、手軽に堅牢なデータ転送の仕組みを構築できます。

Embulkには、それぞれBigQueryからデータを読み込むプラグインとSpreadsheetにデータを書き込むプラグインが存在します。ただし、Spreadsheetに書き込むプラグイン(embulk-output-google_spreadsheets)は2016年以降開発が止まってしまっています。サービスアカウントの認証情報はP12にしか対応していなかったり、書き込む先のシート名を指定できないなどの問題に加え、SpreadsheetのAPI呼び出しでエラーが発生する場合もあり、2020年12月現在残念ながら安定して使えるとは言い難い状態です。

プラグインの開発/メンテナンスが再開される可能性はゼロではないですが、残念ながら現時点では選択肢から除外せざるを得ないでしょう。

BigQuery APIとSpreadsheet APIを使う

最終手段としては、BigQuery APIとSpreadsheet APIを使って自前でデータ転送のプログラムを書く方法が残されています。Google Apps Scriptに比べるとコーディング量が増え、またサーバーインフラを自分で管理する必要がでてくるため、決して手軽とはいえませんが、代わりに自由度は高くなります。

APIを使う方法では、サービスアカウントを使用できます。サービスアカウントを使うことで、複数人での開発・管理がしやすくなり、担当者の異動や退職に伴うアカウント停止によるトラブルを避けることが可能となります。

BigQuery APIもSpreadsheet APIも、GoやJavaをはじめ様々な言語環境用のクライアントライブラリが用意されています。ほとんど言語による制限はないため、都合の良い言語を使ってプログラムを書くことができます。大量のクエリー結果をプログラム内で変換したりチェックする場合には、Pandas/NumPyによる高速で強力なデータ処理が可能なPythonがおすすめです。

ポイント

コーディング量が多く、サーバーインフラの準備も必要となるため、手軽ではない。
ただし、自由度は高く比較的どんなことでもできる。
サービスアカウントを使うことができるため、複数人での開発・保守がしやすく、属人性の低いシステムを作れる。

まとめ

短期的でシンプルなデータのインポートはConnected Sheetsを使うのが最も簡単です。Google Apps Scriptを使えば、クエリーだけでは表現できないようなデータの加工ができ、より集計しやすい形でスプレッドシートにデータを読み込むことができます。

長期的な運用を目指し、担当者が変わっても安定して動作するような仕組みを作る場合は、サービスアカウントを使ってデータの転送を行うべきですが、残念ながら現状ではこの方法は自分でサーバーを立ててBigQuery、SpreadsheetのAPIを叩くプログラムを用意する必要があり、手軽な方法とはいえません。

どの方法も一長一短なので、用途によって適宜使うものを選んでいきましょう。