こんにちは！
2017年新卒SEの渡辺です！
社内で扱っている様々なサービスの改善、新技術の取り入れ等を行う部署で日夜勉強しています！！
今回はGoogleのクラウドサービスであるGCP(Google Cloud Platform)の利用法のひとつについて話したいと思います。
目的
GCPには様々なサービスが存在するなか今回はBigQueryの利用状況をイケてる感じに視覚化したいです。
その実現のために
- Stackdriver LoggingからBigQueryへの連携
- BigQueryからData Studioへの連携
を行う方法をハンズオン形式で紹介します。
やること
BigQueryの利用状況を見る(特にクエリに関して調べる)ためにはクエリを発行した履歴を見る必要があります。
そこで、BigQueryのクエリジョブのログを収集してそこから情報を引っ張り出します。
それらの情報を自分好みに並べることで情報を一目で確認することができるようになります。
必要なもの
- GCPのプロジェクト
- BigQueryの読み込み権限
- Stackdriver Loggingのシンクを作成する権限
シンクを作成する
BigQueryでのログを取得して保存するにはStackdriver Loggingを使います。
Loggingにはログのエクスポート機能が備わっていて、任意の媒体にログを流すことができます。
今回はBigQueryでクエリが実行された際のログを取得し、そのログをBigQueryに送るようにします。
Stackdriver Loggingの画面に遷移したらまず色々なログが垂れ流しの状態になっていると思うので、
フィルターを変えてBigQueryのみのログを出すように設定します。
対象に BigQuery を選択 種類に data_access を選択 その他の項目はデフォルトで問題なし
フィルターの設定ができたらそのままエクスポート先の設定を行います。
エクスポートを作成 を選択 シンク名 を好きなように命名 (このエクスポートを行うジョブの名前です) シンクサービス に BigQuery を選択 シンクのエクスポート先 にエクスポートしたいBigQueryのデータセットを選択 (ここで作成することも可)
入力を終えて シンクを作成 を押すことでシンク作成が完了です。
こうして、BigQueryで実行されたクエリの情報が指定したデータセットの
cloudaudit_googleapis_com_data_access_YYYYMMDD
テーブルに格納されるようになりました。
なお、ログのエクスポートはリアルタイムに行われるので常に最新の情報が貯まっていくことになります。
Data StudioでBigQueryをデータソースとして登録する
ログがBigQuery上に格納されるようになり、あとは視覚化するだけなので様々な方法がありますが、
今回お勧めするのは Google Data Studio ！
Google Data Studio は様々なデータを用いて容易にレポートを作ることができるBIツールです。
メリットとしては、現在はベータ版で無償利用することができることに加え、
様々なデータソースのサポートがされている(もちろんBigQueryも)ので面倒な手間いらずに導入することができます。
さっそく先ほどエクスポートしたBigQuery情報をデータソースとして利用するための準備を行います。
Data Studioのホーム画面にアクセスしたら、
サイドメニューからデータソースを選択し、右下の+から新しいデータソースを作成します。
新規のデータソース作成画面に移ったらBigQueryのデータを利用するため設定を行っていきます。
左上のデータソース名には自分で分かりやすい名前をつける コネクタ で BigQuery を選択 方法 に カスタムクエリ を選択 プロジェクト で 先ほどエクスポート先に指定したデータセットのプロジェクト を選択 クエリオプション の 以前のSQLを使用する のチェックボックスを オフ に切り替える
カスタムクエリとは、その場でクエリを実行しその結果をデータソースとして用いる方法です。
既にテーブル上に必要なデータが揃っている場合は マイプロジェクト を選択してデータセットを選べばクエリを書く必要はありません。
これが今回実行するカスタムクエリです。
SELECT protopayload_auditlog.authenticationInfo.principalEmail as Email, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes as Bytes, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes * 5 / (2^40) as Cost_In_Dollars, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes / 1024 / 1024 as MB, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query as Query, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.createTime FROM `<MY-GCP-PROJECT>.bigquery_audit_logs.cloudaudit_googleapis_com_data_access_*` WHERE protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName = 'query_job_completed'
上記を入力し、画面右上から接続を選択すればデータソースとして登録できます。
無事にBigQueryとの接続ができればフィールドの編集画面へと遷移することができ、
データソースを利用することができます。
グラフを作る
カスタムクエリによってBigQueryのデータがData Studio上のフィールドに出すことができました。
ここからは、このフィールドを色々な形に変えて利用することになります。
ここで詳しく説明をしてしまうと日が暮れてしまうので、
一例としてカスタマイズしたフィールドとそれによって作成したレポートです。
ほらできた！ね、それっぽいでしょ！
日付を持つフィールドを作っておけば期間フィルターを使うことができ、(レポート右上)
任意のフィールドを指定すればそれによるフィルターをつけることもできます。(レポート右)
おわりに
今回のGoogle Cloud Platformをふんだんに利用した便利なレポート作成について紹介しました。
データを視覚化するために利用したData Studio。このサービスの可能性は無限に広がりますよ！
今回の例であれば、クエリにかかるコストを視覚的に知ることができるので、これを見てチューニング対象のクエリを決めよう！ってなるかも？
また、BigQueryの情報だけでなく、様々なデータ・データソースと連携することができるので
自由にレポートを作ることができます。
さらに！データソースの登録は少し複雑な面もありますが、登録ができたあとの操作はエクセル並みに簡単！
Data Studioを使うことで誰でもレポートを作ることができてしまいます。
簡単に導入できて、素早くデータを視覚化。
みなさんも始めてみませんか？
