アドベントカレンダー日付を一日間違ったのは内緒
はじめに
最近、BigQueryに入門しました。インデックスとか関係なく問答無用でフルスキャン！ 結果として集計関数や正規表現で文字加工しても速度ペナルティなし！ ってのは便利ですね。さすぐー。
さて、そんなBigQueryですがSQLを生で投げるだけでは不便なのでBIツールと連携させて可視化する必要があります。
まず最初に思いつくのはデータポータル(旧Data Studio)です。ただ少し触った感じでは固定のサマリレポートを作るのには良さそうだったんですが、ドリルダウンやドリルスルーといった探索的な分析をするのにはちょっと使いづらそうでした。
Metabaseは前から触ってみようと思ってたので試してみたところ、BigQueryにも繋がるしフルスキャンSQLだしドリルスルーも簡単に出来て、以前Kibanaやデータポータルでした苦労が無く個人的に手に馴染んだので共有します。
なお今回は負荷テストのレポートを作っています。
GCEにMetabaseをインストール
たぶんマネージドサービスはないので自前で環境を作ります。
今回はGCEにDockerコンテナでMetabaseを放り込んで使って見ました。GAE FEに入れる方法もあるようですが、ちょっとカスタムが必要そうだったので今回はお手軽にGCEです。
コマンドは以下の通り。
# create instance
$ gcloud compute instances create-with-container metabase-vm \
--project ${PROJRCT_NAME} \
--subnet=projects/${PROJRCT_NAME}/regions/us-west1/subnetworks/${SUB_NET}\
--no-address \
--container-image metabase/metabase \
--container-env="MB_DB_FILE=/metabase-data/metabase.db" \
--container-mount-host-path mount-path=/metabase-data,host-path=/mnt/disks/data/data,mode=rw \
--zone us-west1-b \
--machine-type e2-standard-2 \
--tags http-traffic \
--preemptible
# create firewall rule
$ gcloud compute firewall-rules create allow-http-traffic --target-tags http-traffic --allow tcp:3000
簡単ですね。create-with-containerを使って直接Dockerイメージを指定しているので自分でDockerコンテナの起動とかを管理する必要は無いです。使いたいときだけ起動すれば十分なのでコスト圧縮のためにプリエンプティブインスタンスにしてあります。
Firewallは必要に応じてアクセス元の制限等をかけてください。以上でインフラの準備は完了です。
BigQueryに接続する
では立ち上げたMetabaseを使ってBigQueryに接続します。
まずは「設定」 -> 「管理者」 -> 「データベース」 -> 「データベースの追加」を選択します。
「Database Type」で「BigQuery」を選択します。
するとクライアントIDとクライアントシークレットを求められので以下をクリックして取得します。
Auth Codeも同様できます。ドメインを取ってないので「安全ではないページです」と出ますが今回は問題無いので先に進みましょう。
これでBigQueryとの接続も完了です。
まずは簡単なグラフを作成
まずは簡単なグラフを作成してみましょう。今回使ったのは負荷テストの結果ですので時系列のアクセス数の推移を作ります。
MetabaseはSQLを書かなくてもGUIで分析が出来るツールです。しかし、個人的にはUIで書くよりSQLの方が慣れてる分直観的なのでSQLで書いていきます。
メニュー右上の「SQLを書く」をクリックしてエディタを開きます。
SELECT
start_time,
response
FROM `${project_id}.load_gen_logs.http_response`
WHERE test_id='06f392033d864321a0e7f805ed3ec3cf'
{project_id}のところは自分の任意のプロジェクトです。検索結果は以下のようになります。
こちらを「ビジュアライゼーション」を選択してグラフの種類を決めます。とりあえず散布図にしておきます。
「保存」をするとカードの名前を付けるように要求されるので付けます。問題が無ければそのままダッシュボードへの登録もしましょう。
色んなグラフを追加する
もう少しグラフを追加してそれっぽい感じにしてみます。
SQLは以下の通りです。トランザクションDBはもちろん、例えDWHでも怒られそうな素敵なSQL群ですが大丈夫そうです。
Response Summary:
SELECT
TIMESTAMP_SECONDS(UNIX_SECONDS(end_time)) as timestamp,
COUNTIF(0 <= response AND response < 300*1000) as under300,
COUNTIF(300*1000 <= response AND response < 500*1000) as under500,
COUNTIF(500*1000 <= response AND response < 1000*1000) as under1000,
COUNTIF(1000*1000 <= response AND response < 2000*1000) as under2000,
COUNTIF(2000*1000 <= response) as over2000
FROM `${project_id}.load_gen_logs.http_response`
WHERE test_id='06f392033d864321a0e7f805ed3ec3cf'
GROUP BY timestamp
URL Distribution:
SELECT
REGEXP_REPLACE(REGEXP_REPLACE(url, "http://app:8080", ""), r"[0-9]+", "{key}") as url,
count(1) as count
FROM `${project_id}.load_gen_logs.http_response`
GROUP BY url
TPS:
SELECT PERCENTILE_CONT(count, 0.90) OVER() as tps FROM (
SELECT
UNIX_SECONDS(end_time) as sec,
count(1) as count
FROM `${project_id}.load_gen_logs.http_response`
WHERE response < 1000000 and http_status = 'http_ok' and test_id='06f392033d864321a0e7f805ed3ec3cf'
GROUP BY sec
)
LIMIT 1
Slow Response:
SELECT
end_time as timestamp,
url,
CAST(response / 1000 AS INT64) AS response,
CAST(PERCENTILE_CONT(response, 0.90) OVER(PARTITION BY REGEXP_REPLACE(url, r"[0-9]+", "{key}")) / 1000 AS INT64) AS response_09,
CAST(AVG(response) OVER(PARTITION BY REGEXP_REPLACE(url, r"[0-9]+", "{key}")) / 1000 AS INT64)AS response_avg
FROM `${project_id}.load_gen_logs.http_response`
WHERE http_status = 'http_ok' and test_id='06f392033d864321a0e7f805ed3ec3cf'
ORDER BY response DESC
LIMIT 20
ドリルスルー分析をする
BIの分析にはダッシュボードの中で情報を詳細に見ていくドリルダウンと別のダッシュボードに遷移するドリルスルーがあります。
例えば今回であれば負荷テスト毎にテストIDが振ってありますが、SQLの中に固定で書いてしまっています。テスト毎に毎回ダッシュボードを作成しなおすのはいくらなんでも不便です。
例えば今回であれば「テストの一覧ページ」があってそこからテストの詳細ダッシュボードに遷移して欲しいですよね？
Metabaseでは「変数」と「フィルタ」を使うことでこのような処理が実現できます。
まずは、SQLに変数を設定します。変数は{{変数名}}でSQLの中に記載すればOKです。
WHERE test_id={{test_id}}
こういった感じですね。これを全部のSQLに埋め込みます。
続いてダッシュボードにフィルタを追加します。
右上のメニューボタンを押すことでフィルタが左上にIDという名前で追加されました。こちらを先ほど変数化した各カードの値に関連付けます。
こうすると、フィルタに06f392033d864321a0e7f805ed3ec3cfを入れるとtest_idにこの値が格納されてSQLが実行されるのでテスト毎にダッシュボードを作る必要が無くなります。
ただ、これだけだと毎回自分でIDを入れないといけないので不便です。実はURLをよく見れば分かるのですが、このフィルタのパラメータはGETパラメータをとして渡しています。
http://${URL}/dashboard/3?id=06f392033d864321a0e7f805ed3ec3cf
つまりURLを組み立てることでドリルスルーが実現可能です。と言うわけでもう一つダッシュボードを作ります。
テストの一覧を表示するダッシュボードです。「View Details」をクリックすると「Load Test Details」のダッシュボードに遷移します。
SQLは以下の通り。
SELECT
MIN(start_time) as start_time,
MAX(end_time) as end_time,
CONCAT('http://${URL}/dashboard/3?id=',test_id) as test_page
FROM `${project_id}.load_gen_logs.http_response`
GROUP BY test_id
ORDER BY start_time DESC
${URL}の部分は自分のMetabaseのURLを入れてください。
まとめ
BIはDataStudioとかDOMOとかKibanaを大なり小なり使ったことあるのですが、個人的にはMetabaseが一番手に馴染みますね。BIというよりDHWのシンプルな可視化ツールという思想だと思いますが個人的にはそれこそが欲しいものだったので。
BigQueryとMetabaseの組み合わせは自分的にはベストコンビネーションですね。今のところ。あとはBigQueryで百万円溶かさないように気を付けようｗ
それではHappy Hacking!