Googleスプレッドシート+BigQueryで追加作業が必要なレポーティングを効率化

こんにちは、AIチームの友松です。本日はレポーティング作業の運用効率化のためにスプレッドシートとBigQueryの連携を行ったので、それについてブログを書こうと思います。

AI Shiftでは普段、レポーティングのためのデータの可視化にはTableauを使用しています。Tableauは毎日更新が必要なレポートを作るのに最適なツールであると考えています。

一方で、今回必要だった要件として、スプレッドシート上でデータが毎日更新されて、さらにデータを1行ずつ確認して各行にメモを残す必要があるという要件だったので、調査を行いました。

BigQueryとGoogle Spread Sheetが連携できるようになったのは知っていたのですが、まだ試せていなかったのでこれを機に利用してみたら非常に便利だったのでメモを残したいと思います

想定しているケースは以下になります

• エンジニアじゃないメンバーがデータにアクセスしてレポートを作成したい
• 単純にログを集計しただけではなく、そこにメモなどをつけてレポート化したい
• ログ集積にGoogle BigQueryを利用している

Spread SheetとGoogle BigQueryとの連携

1. データコネクタに接続
   (データ → データコネクタ → BigQueryに接続)

2. データ接続の追加の画面が表示されるので、自身のGoogleアカウントに紐づく接続したいBigQueryのプロジェクトを選択

3. 選択したプロジェクトのテーブル一覧が表示される。
   ここでは、カスタムクエリを用いて自由にデータをとってこれるようにします。

4. BigQuery クエリエディタが出てくるので任意のQueryを記述します。

5. 結果をプレビューを押すと画面下部に指定したqueryのプレビューが表示されます。結果を挿入をするとSpread Sheet上にデータが反映されます

6. 抽出したデータからグラフを作成したり、ピボットテーブルを作ったり更新の時間設定を行ったり、指定したシートの反映させたりできます。

毎朝新しいデータに更新する

BigQueryにはテーブル名に日付をもたせることでスキャン量を削減することができるパーティション分割テーブルというものが存在します。
BigQueryはスキャンしたデータ量に対する課金となりますが、テーブル名に日付を入れておくことでWhere句でテーブルの絞り込みを行うことができます

例)
・test.table_20201001
・test.table_20201002
・test.table_20201003

例えば、今日が2020年10月3日で前日分のデータを全件抽出したいという場合は以下のSQLで抽出可能です

SELECT
    *
FROM
    `test.table_*`
WHERE
    _TABLE_SUFFIX = FORMAT_DATETIME("%Y%m%d", DATETIME_ADD(CURRENT_DATETIME("Asia/Tokyo"), INTERVAL -1 DAY))

FROMで指定しているテーブル名の*部分はワイルドカードとして機能し、一致するテーブルをUNIONしたような状態で抽出することができます。

また、*で指定した部分は_TABLE_SUFFIXでアクセスすることができ、WHERE句で条件指定することによって対象のテーブルをスキャンすることなく抽出を行うことができます。

この状態で毎朝このクエリによって更新がかかるように設定をすると自動更新のレポートが完成します。

頻度は毎日、毎週、毎月が選択可能です。

設定シートの作成とパラメータの活用

Google Spread SheetとBigQueryの連携ではパラメータを使用することでQueryの中身をSpread Sheet上の特定のセルの値によって指定ができます。

これによって、非エンジニアの方でもセルの値を変更することによって欲しいデータを取得できるような仕組みを作ることができます。

今回はデータの取得開始日, 取得終了日およびテナントIDを欲しい人が変更してデータを取得できる仕組みを作りました。以下でパラメータを使用した更新の仕組みを作った手順を説明します

1. 設定シートを用意して、絞り込みに必要な項目を用意する

2. 日付データに関しては表示形式→数字→日付を選ぶことでパラメータ側もDATE型で受け取ることができます

3. パラメータの設定を行います。先程設定したセルを指定します。

• TO_DATE: 取得終了日
• FROM_DATE: 取得開始日
• TENANT_ID: テナントID

4. SQLを書きます。WHERE句にパラメータを埋め込むことで目的の抽出が可能になります。(先程指定したパラメータに@をつけます)
   パーティション分割テーブルは先程と同じ設定でカラムにtenant_idを持つと仮定します。

SELECT
    *
FROM
    `test.table_*`
WHERE
    PARSE_DATE("%Y%m%d", _TABLE_SUFFIX) >= @FROM_DATE
    AND PARSE_DATE("%Y%m%d", _TABLE_SUFFIX) <= @TO_DATE
    AND tenant_id = @TENANT_ID

5. 設定シートを変更して更新ボタンを押すと指定したデータが取得できます

おわりに

本日はGoogle Spread SheetとBigQueryを連携してお手軽レポートの作成手順を紹介しました。これまでTableauで手の届かなかったところやExcelやスプレッドシートでレポートを作りたい場合は今後このやり方でやっていこうと思っています。今回は説明していませんが、マクロなどを使ってより高度なレポートを実装している例もウェブ上で見受けられました。日々のレポーティング作業はどんどん効率化していきましょう。