Googleスプレッドシートをコピーして作るBigQuery Dashboard

BigQueryにはKibanaのような可視化ツールが付いてないのだけど、Googleスプレッドシートを使うとGoogle Apps ScriptからBigQueryのクエリを呼び出せるので、グラフを描いたり簡単なダッシュボードを作れる。しかし自分でApps Scriptを書くのも面倒！ という人のために、コピーしてぽちぽち設定すればすぐに使えるBigQuery DashboardというGoogleスプレッドシートのひな形を作ったので、使い方を簡単に紹介したい。

例えばこんなふうに、セルにSQLを書くと、

こんなグラフが表示されて、定期的に自動更新もできる。Apps Scriptを知らなくてもOK。

なお、BigQueryをまだ試したことない...って人は、この記事を参照のこと。

設定方法

設定方法は以下のとおり。なお、UI表記は英語版のGoogleスプレッドシートをベースとしているのであしからず。。

1. このスプレッドシートを開き、File-Make a copyを選択
2. 新たに作成されたスプレッドシートのURLを選択してクリップボードにコピーしておく
3. Tools - Script editor... メニューを選択
4. スクリプトエディタでbq_query.gsを開く。<<PLEASE PUT YOUR SPREADSHEET URL HERE>>とある部分にURLをペースト
5. BigQueryで使用しているプロジェクトIDを<<PLEASE PUT YOUR PROJECT ID HERE>>の部分にペーストする。File - Saveメニューを選択して保存
6. Resources - Advanced Google servicesメニューを選択し、BigQuery APIをオンにする
7. ダイアログ上のGoogle Developers Consoleリンクをクリック。APIリストが表示されるので、BigQuery APIをクリックし、OFFからONに変更する。するとEnabled APIsリスト上にBigQuery APIが表示される
8. コンソールを閉じ、ダイアログのOKボタンをクリック

サンプルクエリの実行

このシートにはサンプルクエリが付属しているので、以下の手順で試すことができる。

1. BQ Queriesシートを開く。ここにあるgsod_temparature_LINEは、米国の過去の気象情報データを集計するサンプルクエリだ
2. Dashboard - Run All BQ Queriesメニューを選択。初回実行時にはAuthorization Requiredダイアログが表示されるので、ContinueおよびAcceptボタンをクリック
3. gsod_templateシートが追加される。このシートを開いてクエリ結果が追加されていることを確認
4. BigQuery Dashboardシートを開いて、グラフが追加されたことを確認

これで準備はOK。

Fluentd+BigQueryデモでダッシュボードを試す

では、Fluentd+BigQueryデモで集めたnginxアクセスログを可視化するダッシュボードを作ってみよう。以下のクエリをBQ Queriesシートに追加し、query nameはaccess_log_LINE、intervalは1分に設定しておく。

SELECT
  STRFTIME_UTC_USEC(time * 1000000, "%Y-%m-%d %H:%M:%S") as tstamp, 
  count(*) as rps
FROM bq_test.access_log
GROUP BY tstamp ORDER BY tstamp DESC LIMIT 100;

自動更新の設定手順

グラフの自動更新は以下の手順で設定できる。

1. スクリプトエディタを選択し、Resources - Current project's triggersを選択
2. Click here to add one nowをクリック
3. RunメニューからrunQueriesを選び、EventsではTime-driven Minutes timer Every minuteをそれぞれ選択、Saveをクリック
4. BQ Queriesシートにて interval (min)カラムに1をセット

これにより、上記クエリが1分ごとに自動実行される。0をセットすれば自動更新を停止できる。

Apache Benchで負荷テスト

ここまでの設定によりnginxのアクセス数がグラフ表示されるが、ブラウザからぷちぷちアクセスしても面白いグラフは表示されないので、MacやLinuxから以下のコマンドでApache Benchを起動して負荷をかけてみる。なお、YOUR_EXTERNAL_IPの部分にはGCEインスタンスのExternal IPアドレスを入れる。

$ ab -c 100 -n 1000000 http://YOUR_EXTERNAL_IP/

BigQuery Dashboardを選択し、Dashboard - Run All BQ Queriesメニューを選択。access_logグラフがダッシュボード上に表示されるはずだ。また、1分に1回自動更新されることも確認する。

• Ctrl+Cを入力してApache Benchを停止する。

ダッシュボードの使い方

このダッシュボードのおもな使い方は以下のとおり。

• query nameで指定した名前のシートがない場合は、新しいシートが作成される
• query nameが_AREA, _BAR, _COLUMN, _LINE, _SCATTER, もしくは _TABLEで終わる場合は、それぞれに対応したグラフが作成される
• query nameが_AREA_STACKED, _BAR_STACKEDもしくは_COLUMN_STACKEDで終わる場合は、スタックされたグラフが作成される
• クエリの最後にLIMIT 100を付け、100行以下に制限する
• クエリ結果の最初のカラムはタイムスタンプもしくは日付である必要がある

以上、BigQuery Dashboardの使い方を説明した。英語版のドキュメントは俺の下手な英語を同僚のプレストンがしっかり直してくれた。プレストンあざす！！

---

Disclaimer この記事は個人的なものです｡ここで述べられていることは私の個人的な意見に基づくものであり､私の雇用者には関係はありません｡