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

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

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

![gsod_query](https://raw.githubusercontent.com/GoogleCloudPlatform/bigquery-fluentd-docker-sample/master/images/gsod_query.png)

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

![gsod_temperature](https://raw.githubusercontent.com/GoogleCloudPlatform/bigquery-fluentd-docker-sample/master/images/gsod_graph.png)

なお、BigQueryをまだ試したことない...って人は、[この記事](http://qiita.com/kazunori279/items/7367e725d7827e37a38f)を参照のこと。

## 設定方法

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

1. [このスプレッドシート](https://docs.google.com/spreadsheets/d/1Xwk2icyXH2DmVIZC33SAs5bs012ZIt0-goyX0dZZu7s/edit)を開き、`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デモ](http://qiita.com/kazunori279/items/7367e725d7827e37a38f)で集めた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回自動更新されることも確認する。

![access_log graph](https://raw.githubusercontent.com/GoogleCloudPlatform/bigquery-fluentd-docker-sample/master/images/access_log_graph.png)

- `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の使い方を説明した。[英語版](https://github.com/kazunori279/bigquery-fluentd-docker-sample)のドキュメントは俺の下手な英語を同僚のプレストンがしっかり直してくれた。プレストンあざす！！

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