BigQuery利用状況ダッシュボードの要件定義

はじめに

Google CloudのBigQueryにデータ基盤を構築した後に、データ基盤の利用状況をモニタリングしたいという要望が出ることは容易に想像できます。この記事では、データ基盤の利用状況をモニタリングするためのダッシュボードの要件定義を行います。

要件定義は以下の関連記事に沿って整理していきます。

導入課題・目的

効果的なビジネスダッシュボードを作るための要件定義から構築までの流れ | Hakky Handbook

ビジネスダッシュボードを構築するための要件定義から構築、QA・レビューまでの流れを紹介します。

ダッシュボードで解決したい課題と目的

課題
- どれくらいの人がデータ基盤を利用しているかがわからない
- 誰がどのデータを参照してどんなクエリを叩いているかわからない
- 課金額を把握できていない
目的
- どれくらいの人がデータ基盤を活用しているかモニタリング
- 誰がどんなクエリを叩いているか探索
- クエリのスキャン量(≒課金額)もチェック
- 効率の悪いqueryを書いている人を支援することによるコストの効率化
- よく使われるテーブルや処理をDWHとして共通化することにより、データ基盤の利用促進

ダッシュボードの利用ユースケース


誰が見るか	データ基盤運用チーム
いつ見るか	データ基盤運用チームの定例会
どんな目的で見るか	・チームの成果を確認するため・利用者の傾向（人数、クエリ内容の両方）を確認するため・利用者が利用しやすいようなデータ基盤を整えるための情報収集のため
どれくらいの頻度で見るか	月1程度
どのようなアクションにつながるか	・利用者がデータをより活用できるような支援・新しいDWH / DMの構築

ダッシュボードに表示・分析可能にする指標

指標を整理

以下の2つの観点で整理します

利用者の傾向
実行クエリ・スキャン量

利用者の傾向

各指標と必要な補足情報を併せて整理します

MAU（月ごとの利用したユーザーのユニーク数）
利用者数の推移
利用者一覧と利用数
- メールアドレス
- 直近1ヶ月のクエリ実行日数
- 直近1ヶ月のクエリ実行数
脱落者一覧
- メールアドレス
- 最終クエリからの実行経過日数

実行クエリ・スキャン量

各指標と必要な補足情報を併せて整理します

ユーザーごとのクエリ実行数
- メールアドレス
- クエリ実行数
- 合計スキャン量
ユーザーごとのクエリ一覧
- メールアドレス
- クエリ実行日時
- スキャン量
- クエリ

ディメンション・メジャーで整理

整理した指標から、ディメンションとメジャーで整理します

実行されたクエリの集計テーブル

ディメンション
- 日付
- メールアドレス
メジャー
- クエリ数
- 合計スキャン量

これを実現するクエリの一例を以下に示します。

SELECT
  DATE(start_time, 'Asia/Tokyo') AS query_at,
  user_email,
  COUNT(*) AS queries,
  SUM(total_bytes_billed)/1024/1024/1024/1024 AS total_terabytes
FROM
  `<project-id>`.`region-<region>`.`INFORMATION_SCHEMA.JOBS`
WHERE
  job_type = 'QUERY'
  AND start_time >= 'xxxx-xx-xx'
GROUP BY
  start_time,
  user_email

FROMの指定について、例えばあるプロジェクトの東京リージョンからデータを取得したい場合は以下のフォーマットになります。

FROM
  `<project-id>`.`region-asia-northeast1`.`INFORMATION_SCHEMA.JOBS`

クエリ履歴テーブル

ディメンション
- クエリ実行日時
- メールアドレス
- クエリ
メジャー
- スキャン量

これを実現するクエリの一例を以下に示します。

SELECT
  DATETIME(start_time, 'Asia/Tokyo') AS query_at,
  user_email,
  query,
  total_bytes_billed/1024/1024/1024/1024 AS query_terabytes
FROM
  `<project-id>`.`region-<region>`.`INFORMATION_SCHEMA.JOBS`
WHERE
  job_type = 'QUERY'
  AND start_time >= 'xxxx-xx-xx'

ダッシュボードのページ設計とワイヤー

ダッシュボードでは以下2ページで構成することを想定します。

データ基盤活用者傾向サマリ
実行クエリ・スキャン量探索

それぞれのページでのワイヤーを描きます。

データ基盤活用者傾向サマリ

実行クエリ・スキャン量探索

まとめ

この記事では、BigQueryの活用状況を可視化したダッシュボードの要件定義をしました。

このダッシュボードにより、異常なコストがかかるクエリを特定できたり、支援すべき利用者を特定できたり、DWH拡張の手がかりにできるようになると思います。

ダッシュボードを使いながら、より活用されるデータ基盤へ進化させる施策を検討できるとよいでしょう。

参考資料

INFORMATION_SCHEMA.JOBS ビュー | BigQuery

備考

Hakky では社内のデータ活用やサービスとしてデータ提供を行うためのソリューションを展開しております。

「社内のデータを一元管理し、よりデータドリブンな組織を構築したい」など具体的な相談はもちろんのこと、「どんなことをお願いできるのか知りたい」や「こんなことをやりたい」など、ご検討段階でも構いませんので、ぜひお気軽にフォームよりお問い合わせくださいませ。