この記事はGoogle Cloud Platform その１ Advent Calendar 2018 - 18日目の記事です。

以前こういうブコメをしたのですが、言いっ放しだとダサいので、具体的なやり方を書きます。

"アカウントごと/日付ごとのBigQueryコストを可視化できるダッシュボード" なら Stackdriver + BQ + Datastudio を使うと作業時間10分・ほぼ無料で作れるので、うちはそのやり方を採用していますね。… https://t.co/9yoni22iw7
— ゆずたそ (@yuzutas0) August 17, 2018

はじめに

この記事のゴール

BigQueryにおいて「どのユーザーが、どのくらいクエリを叩いているか、どのくらいコストを使っているのか」を可視化します。

（情シス部門に権限を縛られていなければ）10分で出来ます。
（BQのストレージやクエリ料が多少掛かりますが）ほぼ無料です。

解決したい課題

GCPのコンソール画面ではトータルのコストしか閲覧できません。ドリルダウン分析ができないので、具体的なアクションに繋がりません。

課題の背景

コストは多すぎてもダメ、少なすぎてもダメだと思っています。

コストが多すぎる場合。ワイルドクエリを投げているユーザーに「こういうSQLを書くと財布に優しいですよ」と案内します。同じような集計処理が繰り返されている場合、集計済みの中間テーブルを設けるなどの節約施策を打ちます。

コストが少なすぎる場合。データが活用されていないということになります。多くの部署に使われることがデータ基盤の意義だと思います。そのため「データの民主化」を促進する施策を打つ必要があります。

詳しくはBQ Sushiの登壇資料やデブサミの登壇資料を参照ください。

実行方針

BigQueryのクエリ実行記録を使う
Stackdriver Loggingで上記のデータを収集する
収集したデータをBigQueryに保存する
BigQueryのデータをDataStudio（名前がDataPortalに変わったらしい）で表示する

完成イメージ

f:id:yuzutas0:20181218160434p:plain

作業手順

1: BQクエリ実行ログを流す

GCPコンソールから「Stackdriver Logging」>「Logs」を開きます。

resource.type="bigquery_resource"

上記の条件を記入して「Submit Filter」を押すと、ログが絞り込まれます。その状態で「CREATE EXPORT」を押します。エクスポートの設定を聞かれるので、以下のように入力しましょう。

Sink Name: export_audit_logs
- お好きな名前でOK
Sink Service: BigQuery
Sink Destination: source__cloudaudit__bigquery
- これが出力先データセット名になる
- お好きな名前でOK

全て埋めたら「Create Sink」を押します。

f:id:yuzutas0:20181218160524p:plain

これでBigQueryのクエリ実行記録（ログ）がBigQueryに流れるようになります。「Sink Destination」で定義したデータセットの下に、2つのテーブル「cloudaudit_googleapis_com_activity_YYYYMMDD」「cloudaudit_googleapis_com_data_access_YYYYMMDD」が生成されます。

f:id:yuzutas0:20181218160616p:plain

このデータをもとにコスト可視化ダッシュボードを作ります。

2: DataStudioでダッシュボードに表示する

Google DataStudio の新規レポート作成画面を開きます。

f:id:yuzutas0:20181218160630p:plain

「CREATE NEW DATA SOURCE」＞「BigQuery」＞「SELECT」＞「CUSTOM QUERY」＞「対象プロジェクト名」を押下して、SQLコンソールを開きます。もし「Use Legacy SQL」にチェックが入っていたら外しましょう。

コンソールには以下のSQLを貼り付けます。最初のFROM節は「Sink Destination」で定義したデータセット名を入れてください。

WITH
log AS (
  SELECT
    protopayload_auditlog.authenticationInfo.principalEmail AS user,
    SUM(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes) AS total_bytes,
    COUNT(protopayload_auditlog.authenticationInfo.principalEmail) AS query_count,
    PARSE_DATE(‘%Y%m%d’, _table_suffix) AS day
 FROM
    `source__cloudaudit__bigquery.cloudaudit_googleapis_com_data_access_*`
 WHERE TRUE
    AND protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName = ‘query_job_completed’
GROUP BY
    protopayload_auditlog.authenticationInfo.principalEmail,
    day
)
SELECT
    L.user AS User,
    ROUND((L.total_bytes*5)/1000000000000, 2) AS Cost,
    L.query_count AS Query_Count,
    L.day AS Day
FROM
    log L
ORDER BY
    2 DESC

ちなみに、WHERE句を TRUE で始めているのは、条件を変更するときに最初の1行で AND をいちいち追加・削除せずに済むからです。

SQLを書いたら「CONNECT」を押して接続します。

f:id:yuzutas0:20181218160647p:plain

ここから先はお絵かきタイムになります。上記のようなダッシュボードを設定しましょう。

DataStudioの仕様は頻繁に変わるので、キャプチャでの詳細解説は省略します。以下の4つはボタンを押せばすぐにできるはずです。もし分からない点があればお気軽に@yuzutas0にメンションください。

「テキスト」で、タイトルを表示させる
「期間」で、当月（デフォルト）を表示させる
「数値」で、合計コストを表示させる
- SQL結果の「Cost」を「SUM」したものが合計コストです
- 詳しい設定は後述します
「グラフ」で、ユーザーごとのコストを折れ線で表示させる
- データソース: BigQuery
- 期間のディメンション: Day
- ディメンション > 時間ディメンション: Day
- ディメンション > 内訳ディメンション: User
- 指標: SUM - Cost
- 内訳ディメンションの並べ替え: SUM - Cost

テキストだと分かりにくいので、以下の設定キャプチャを参考にしてください。

f:id:yuzutas0:20181218160704p:plain

これで完成です。必要な権限が付与されていて、なおかつツールに慣れていれば、10分で完成します。

応用編

1: 全体コストを可視化する

ユーザーごとのコストを可視化しましたが、これだけでは本当に数字があっているのか分かりません。実装のあとにはテストをして、データの品質を担保することが大事です。そこで、全体コストがGCPコンソールの数字と合致するかを見てみましょう。

f:id:yuzutas0:20181218160724p:plain

SQL結果の「Cost」を「SUM」したものが合計コストとなります。以下のような設定をすれば適切な数字が出るはずです。

データソース: BigQuery
期間のディメンション: Day
ディメンション > 時間ディメンション: Day
ディメンション > 内訳ディメンション: なし
指標: SUM - Query_Count
内訳ディメンションの並べ替え: なし

「内訳ディメンション」を「なし」にすることがポイントです。「User」を指定するとユーザー別コストになります。

2: コストではなく実行回数を計測する

BigQueryの請求料金はデータ量に依存します。そのため「実際にデータを活用されているか」をモニタリングする場合、必ずしもコストだけを見ればいいわけではありません。そこで、コストではなくSQLの実行回数もダッシュボードに出してみましょう。

f:id:yuzutas0:20181218160739p:plain

データソース: BigQuery
期間のディメンション: Day
ディメンション > 時間ディメンション: Day
ディメンション > 内訳ディメンション: User
指標: SUM - Query_Count
内訳ディメンションの並べ替え: SUM - Query_Count

「指標」で「SUM - Query_Count」を指定することがポイントです。ここで代わりに「SUM - Cost」を指定すると、前述したコストの計測になります。

3: チーム単位のコストを計測する

ユーザー別ではなくチーム別で管理する方法となります。どのチームがデータ基盤を積極的に使っているのか。どのチームは使っていないのか。チーム単位で区切って「データの民主化」の状況を分析することができるようになります。

前提として、BigQueryに「ユーザー（メールアドレス）」と「所属チーム」を紐づけるテーブルを用意する必要があります。データを用意する方法は色々ありますが、オススメなのは「Google Spreadsheet」でチーム・メンバーの管理簿を作ってBQに定期反映するやり方です。他の方法でも問題ありません。

さて、データを用意できたら、SQLを以下にように書き換えましょう。最後の数行で「INNER JOIN」をしている箇所、その結果を「SELECT」している箇所が差分となります。ユーザー情報を結合キーにして、クエリ実行履歴とチーム名を紐づけています。

WITH
log AS (
  SELECT
    protopayload_auditlog.authenticationInfo.principalEmail AS user,
    SUM(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes) AS total_bytes,
    COUNT(protopayload_auditlog.authenticationInfo.principalEmail) AS query_count,
    PARSE_DATE(‘%Y%m%d’, _table_suffix) AS day
 FROM
    `source__cloudaudit__bigquery.cloudaudit_googleapis_com_data_access_*`
 WHERE TRUE
    AND protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName = ‘query_job_completed’
GROUP BY
    protopayload_auditlog.authenticationInfo.principalEmail,
    day
)
SELECT
  L.user User,
  M.team AS Team,
  ROUND((L.total_bytes*5)/1000000000000, 2) AS Cost,
  L.query_count AS Query_Count,
  L.day AS Day
FROM
  log L
INNER JOIN
  `source__spreadsheet.member` M
ON
  L.user = M.mailAddress
ORDER BY
  2 DESC

このデータをもとに、チームごとのクエリ料金を表示するには、以下のような設定をします。

データソース: BigQuery
期間のディメンション: Day
ディメンション > 時間ディメンション: Day
ディメンション > 内訳ディメンション: Team
指標: SUM - Cost
内訳ディメンションの並べ替え: SUM - Cost

「内訳ディメンション」で「Team」を指定することがポイントです。ここで代わりに「User」を指定すると、前述したユーザーごとのコストモニタリングになります。

4: BigQueryにテストデータを入れる

BigQueryを使い始めたばかり・GCPプロジェクトを作ったばかりで、まだクエリを叩いていないよ！という状態かもしれませんね。そうなるとダッシュボードは0円のままで、本当に動いているのか確認することはできません。

何回かクエリを叩くためにも、適当なテストデータを投入してみましょう。以下のSQLをBigQuery UIで実行してください。

WITH item AS (
  SELECT
    CAST(RAND() * 100000000 AS INT64) AS purchaseId, -- 購入IDをランダムに生成する（ユニークの保証はないが重複の確率は低い）
    CAST(RAND() * 5 AS INT64) AS itemId, -- 購入した商品ID（0〜5）をランダムに生成する
    CAST(RAND() * 120 AS INT64) AS advertiseId, -- 流入元の広告ID（0〜120）をランダムに生成する
    CAST((RAND() * 40) + 20 AS INT64) AS age, -- ユーザーの年齢（20歳〜60歳）をランダムに生成する
    CAST(RAND() * 1 AS INT64) AS gender, -- ユーザーの性別（0〜1）をランダムに生成する
    DATE_SUB(CURRENT_DATE(), INTERVAL CAST((RAND() * 50) AS INT64) DAY) AS purchasedAt, -- : 購入日付（現在日付から50日前まで）をランダムに生成する
    CASE
      WHEN 10 * RAND() < 8 THEN 0
      ELSE CAST(RAND() * 1800 AS INT64)
    END AS discountAmount -- 割引金額（80%の確率で0円、20%の確率で0〜1800円）をランダムに生成する
  FROM
    UNNEST(GENERATE_ARRAY(1, 100000)) -- 100,000レコードを生成する
)
SELECT
  item.purchaseId AS purchaseId,
  item.itemId AS itemId,
  (item.itemId + 1) * 2000 AS price, -- 商品Idを元に商品価格を決める
  item.discountAmount AS discountAmount,
  ((item.itemId + 1) * 2000) - item.discountAmount AS paymentAmount, -- 商品価格から割引金額を差し引いた結果が支払い金額
  item.advertiseId AS advertiseId,
  item.age AS age,
  item.gender AS gender,
  item.purchasedAt AS purchasedAt
FROM item

このSQLによって以下のようなデータをランダムに10万レコード生成します。

個人情報マスキング済みのユーザー情報（年齢・性別）
アクション情報（広告・購買）
購買情報（商品・金額）

SQLの実行結果を適当なテーブルに保存しましょう。私は慣習的に workspace データセットの test__records に出力することが多いです。

あとは適当にこのデータを分析するためのSQLを叩いてみてください。どの商品が一番人気なのか。どのようなユーザーが多いのか。どの広告経由での売上が高いのか。何回かクエリを実行するとコストダッシュボードに変化が生じるはずです。

※上記ランダム生成クエリについて補足します。

例として CAST(RAND() * 5 AS INT64) AS itemId の1行を取り上げます。

まず RAND() * 5 は 0.00 〜 4.99... となる FLOAT64 値を返します。
- 参考: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#rand
そして CAST( [FLOAT64 値] AS INT64) は、最も近い INT64 値である 0 〜 5 を返します。
- 参考: https://cloud.google.com/bigquery/sql-reference/functions-and-operators#casting
結果として -- 購入した商品ID（0〜5）をランダムに生成する ことになるわけです。

5: 毎朝Slackに通知する

豪華なダッシュボードを作っても、すぐに見なくなるのが人間というものです。既存の業務フロー（例:毎朝Slackの未読チャンネルをざっと眺める）に上手く乗せることができると、実運用として回るはずです。画像ファイルでSlackに自動配信できるのが理想ですね。

しかし、いまのDataStudioの仕様では実現できません。 Puppeteerでスクリーンショットを取ってSlackに投げる、といった手段を取ることになります。「10分でお手軽にやろうぜ！」というコンセプトからは少し逸脱しそうです。

そこでおすすめなのが、純粋なSlack botの定期ポストです。今回作ったダッシュボードのURLを毎朝垂れ流すだけ。 /reminder #bq_report コスト確認 https://xxxxxx every weekday といったコマンドを打つだけなので、30秒で動かせます。

f:id:yuzutas0:20181218160759p:plain

まぁ、ぶっちゃけこれだと、利用者はほとんど見ないです。システムリソースやコストに興味ないですからね。代わりに、データ基盤エンジニア、システム管理者、エンジニアリングマネージャーといった、お金や負荷を見る人たち。彼らが時々見てコスト・キャパシティの予実を確認します。このユースケースには十分に対応できるかなと思います。

おわりに

このようにGCPはソリューションを組み合わせるだけで色々実現できるので超絶おすすめです。

実は最近はじめてフルGCPの案件を担当しました。インフラやデータ整備があまりにも簡単で驚きました。 1週間で見積もっていた作業だったのに、ボタンをポチポチしたら一瞬で終わってしまいました。これまで他クラウドやオンプレとのハイブリッド構成で苦しんだのは何だったのかという感じです。

新規事業であっても、むしろ新規事業なら尚更GCPがおすすめです。今時のプロダクト開発だと、データ活用の重要性が高まっているので、いわゆるMLOps・DataOpsの取り組みやすさは重要となります。また、システムだけでなく、ビジネスやマーケティングという観点でも、Google Adwards、Google Analytics、FirebaseがBigQueryと連携できるため、非常に魅力的です。

f:id:yuzutas0:20181218161321p:plain

https://datastudio.google.com/navigation/reporting

ということで、エンジニアにとっても、サイエンティストにとっても、ビジネスデベロッパーにとってもGCPは最高だと思います。めでたしめでたし。

下町柚子黄昏記 by @yuzutas0

したまち発・ゆずたそ作・試行錯誤の瓦礫の記録

BigQueryのコスト可視化ダッシュボードを10分で作る

もくじ