[GCP]Log AnalyticsとLooker StudioでBigQueryのクエリコストをユーザー別に見える化する

先々月～先月（2022年9, 10月）頃、Cloud LoggingシリーズにLog Analyticsの公開プレビュー版が利用可能になったので、使ってみつつ、Looker Studio（旧Google Data Portal）でBigQueryのクエリコストをユーザー別に見える化するところまで実施してみたので、覚書きを残しておきます。

※本記事は、2022年11月1日に執筆しました。

用語解説

Log AnalyticsとLooker Studioについて、簡単に解説しておきます。

Log Analytics

Cloud Loggingシリーズの機能セットのひとつで、BigQueryの機能を活用してログ分析できる。
ログの一元管理ができ、データの重複コピーが不要なため、低コスト。
他、Log Analytics からのクエリ実行は無料だったり、ログ形式を変更しても、スキーマの不一致によるエラーは発生しない、などなど従来版と比べ、いろいろ改善されている様子。

詳細や従来版との比較については、

• Cloud Logging のご紹介 - BigQuery を活用した Log Analytics | Google Cloud 公式ブログ
• BigQuery Export ユーザーの Log Analytics への移行 | Google Cloud 公式ブログ

辺りにまとまっているので、そちらをご覧ください。

Looker Studio

2022年9月頃まで、Data Portalと呼ばれていたもので、レポーティングやデータの可視化に使う。
800以上のデータソースに接続でき、直感的な操作が可能。
通常版（Proじゃないやつ）の利用料は基本無料（ただし、データソース側に課金される場合あり）

詳細は、

• Looker Studio: ビジネス分析の可視化 | Google Cloud

をご覧ください。

前提条件

• 使用ユーザーにCloud LoggingやBigQueryに関する適切なロールが付与されていること（今回、オーナー権限のあるユーザーで実施しました）

手順

1. ログバケットの作成

まず、ログバケットを作成します。

(1) コンソールにて、「ロギング」（①） > 「ログストレージ」（②）の順でクリック

(2) 「ログストレージ」の画面が開いたら、「ログバケットを作成」をクリック

(3) バケットの詳細に必要事項を入力して（①）、「NEXT」をクリック（②）

①の入力内容詳細

項目名	入力内容	備考
名前	任意の名前を入力（必須）
説明	説明を入力
Upgrade to use Log Analytics	チェックを入れる
このバケットにリンクする新しいBigQueryデータセットを作成する	チェックを入れる	「Upgrade to use Log Analytics」にチェックを入れると出現
このバケットにリンクする新しいBigQueryデータセットを作成する	任意の名前を入力（必須）

(4) ログデータの保持期間の設定を実施。プレビュー版のためか、30日から変更できず。そのまま「バケットを作成」をクリック。

2. ログ ルーティング シンクの作成

手順1で作成したログバケットに保管するログのルーティングを実施します

(1) 左ペインにて、「ログルーター」をクリックし（①）、「シンクの作成」をクリック（②）

(2) 「シンクの詳細」にて、「シンク名」（必須）と「シンクの説明」を入力し（①）、「次へ」をクリック

(3) 「シンクの宛先」にて、必要事項を選択し（①）、「次へ」をクリック（②）

①の選択内容詳細

項目名	選択内容
シンクサービスの選択（必須）	Cloud Loggingバケット
ログバケットの選択（必須）	手順1で作成したログバケット

(4) 「シンクに含めるログの選択」にて、BigQueryのログ取得に関する内容を入力し（①）、「シンクを作成」をクリック（②）

①の入力内容詳細

項目名	入力内容
包含フィルタの作成	resource.type = ("bigquery_project" OR "bigquery_dataset")

補足事項

BigQueryの監査ログとして、「AuditData」と「BigQueryAuditMetadata」を使う方法とがありますが、前者のほうが古く、後者を使うことが推奨されているようです。
今回は上記に則り、「BigQueryAuditMetadata」を用いてログ収集することとしました。

3. BigQueryの確認

(1) ここで、「BigQuery」も見てみます。コンソールの左ペインにて、「BigQuery」（①） > 「SQLワークスペース」をクリック（②）

(2) 以下、対象プロジェクト下のデータセット一覧を表示した状態です。

①が従来版（監査ログとしては、「AuditData」を用いています）で、②が今回の手順で作成されたものです。
②のほうが構造も随分シンプルになっていますね。
そして、②は実体はログバケット上に存在するviewなので、リンクマークが付いています。

4. Log Analyticsからのクエリ発行

手順2までで、ログ収集の設定が一通り完了しました！
これによって、「Log Analytics」からログの分析クエリを発行できるようになります。なお、「Log Analytics」からのクエリ発行は無料とのこと！

簡単にですが、操作方法を書いておきます。

(1) 「Log Analytics」（①）> 作成したログをクリック（②）すると、デフォルトでクエリが出現するので編集し（③）、「クエリの発行」をクリック（④）

いくつかコスト分析クエリも載せておきます。

①日別・ユーザー別でコストとクエリ実行回数を求める

以下、日別・ユーザー別でコストとクエリ実行回数を求めるです。
FROM句には、(1)にてデフォルトで出現したテーブル名を用いています。

WITH logs AS(
 SELECT
  DATE(TIMESTAMP(JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata,"$.jobChange.job.jobStats.endTime")),"Asia/Tokyo") AS day,
  proto_payload.audit_log.authentication_info.principal_email AS email,
  cast(JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata, "$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS int64) AS total_billed_bytes
 FROM
  `logs_test_log_analytics_2_US_000._AllLogs`
 WHERE
  JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata,"$.jobChange.job.jobConfig.type") = "QUERY"
  AND DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 6 day) <= DATE(TIMESTAMP(JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata,"$.jobChange.job.jobStats.endTime")),"Asia/Tokyo" -- 6日前以降（クエリ実行日を含め7日間）を対象)

)
SELECT
  L.day,
  SUBSTR(L.email,1,STRPOS(L.email,'@')-1) AS user,  -- emailだと長いので@移行をカット
  FORMAT('%9.2f',5.0 * (SUM(L.total_billed_bytes)/POWER(2, 40))) AS estimated_usd_cost,-- USリージョンの場合
  count(L.email) AS query_count
FROM
  logs L
GROUP BY
 L.day, L.email
ORDER BY
 L.day, L.email;

日付は日本時間（Asia/Tokyo）で算出しています
今回は6日前以降（クエリ実行日を含め7日間）を対象という条件も入れてみました

コスト（estimated_usd_cost）はUSリージョンベースとなっているため、データセットが他リージョンにある場合は、BigQuery の料金 | BigQuery - Google Cloudを参照の上、数値の5.0（USリージョンの場合、クエリ実行料金は、5.00USD/TB）を適切な値に変更してください

②月別・ユーザー別でコストを求める

以下、月別・ユーザー別でコストを求めるクエリです。

WITH logs AS(
  SELECT
   FORMAT_DATE('%Y/%m',DATE(TIMESTAMP(JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata,"$.jobChange.job.jobStats.endTime")),"Asia/Tokyo")) AS year_month,
   proto_payload.audit_log.authentication_info.principal_email AS email,
   cast(JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata, "$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS int64) AS total_billed_bytes,
  FROM
   `logs_test_log_analytics_2_US_000._AllLogs`
  WHERE
   JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata,"$.jobChange.job.jobConfig.type") = "QUERY"
   AND DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 12 MONTH), MONTH) <= DATE(TIMESTAMP(JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata,"$.jobChange.job.jobStats.endTime")),"Asia/Tokyo") -- 過去12ヶ月前以降（当月含め過去13か月）
 )
 SELECT
   L.year_month,
   SUBSTR(L.email,1,STRPOS(L.email,'@')-1) AS user,
   FORMAT('%9.2f',5.0 * (SUM(L.total_billed_bytes)/POWER(2, 40))) AS estimated_usd_cost
 FROM
   logs L
GROUP BY
 L.year_month, L.email
ORDER BY
 L.year_month, L.email;

月別・ユーザー別でコストを求めるクエリです。
12ヶ月前以降（当月含め過去13か月）を対象としました。

5. Looker Studioでクエリコストをグラフ化

最後に、Looker Studioでクエリコストをグラフ化していきます。
Looker Studioは最近（2022年9月頃）まで、Google Data Portalと呼ばれていたものになります。

5-1. 月別・ユーザー別でクエリコストをグラフ化

まずは、月別・ユーザー別でクエリコストをグラフ化します。
今回は、12ヶ月前以降＋当月（当月含む13か月間）のコストをグラフ化する想定で進めます。
（手順4とは記載順序が逆ですが、ご容赦を）

(1) Looker Studioを開き、「空のレポート」をクリック

(2) 「データのレポートへの追加」が開いたら、「BigQuery」をクリック

(3) 「カスタムクエリ」（①） > 対象プロジェクト と選択し（②）、「カスタムクエリを入力」にクエリを入力したら（③）、「追加」をクリック（④）

③の「カスタムクエリを入力」欄で入力するクエリ

以下、「カスタムクエリを入力」に入力するクエリです。

WITH logs AS(
SELECT
  DATE(TIMESTAMP(JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata,"$.jobChange.job.jobStats.endTime")),"Asia/Tokyo") AS day,
  proto_payload.audit_log.authentication_info.principal_email AS email,
  cast(JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata, "$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS int64) AS total_billed_bytes,
 FROM
  `test_log_analytics_2._AllLogs`
 WHERE
  JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata,"$.jobChange.job.jobConfig.type") = "QUERY"
  AND DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 12 MONTH), MONTH) <= DATE(TIMESTAMP(JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata,"$.jobChange.job.jobStats.endTime")),"Asia/Tokyo")
)
SELECT
  FORMAT_DATE('%Y/%m', L.day) AS year_month,
  SUBSTR(L.email,1,STRPOS(L.email,'@')-1) AS user,
  5.0 * (L.total_billed_bytes/POWER(2, 40)) AS estimated_usd_cost
FROM
  logs L;

ここで、

• FROM句には、BigQuery上でのデータセット＋テーブル名を入れる（手順1-(3)で入力、3-(2)で確認したものです）
• 集計、ソートは実施しない（GROUP BY＋SUM, ORDER BYはしない） → Looker Studio側で操作するため
• Looker Studio側で集計するため、estimated_usd_costのフォーマットは実施しない → フォーマットすると、string型になり、集計できなくなる

点、注意してください。

(4) レポート編集画面が開き、初期状態でひとつグラフが作成＆選択された状態になるので、そのまま右側の「グラフ」をクリック

(5) 「積み上げ縦棒グラフ」を選択し（①）、その下「SETUP」タブを以下の通り編集します（②）

②SETUPタブの編集内容

項目名	選択内容	備考
ディメンション	year_month
内訳ディメンション	user
指標	estimated_usd_cost	デフォルトでSUMが選択される（はずな）ので、そのままにする
並べ替え	year_month / 昇順	グラフ横軸のソート順
予備の並べ替え	estimated_usd_cost / 降順	グラフ棒内（?）の並べ替え。 左記の設定で使用料の高いユーザーが下に来る

(6) (5)で選択した内容はデフォルトで、カスタムクエリのカラム名になっているので、必要に応じて編集するとよいでしょう。
カラム名左側のデータタイプ（?）の部分にカーソルを合わせると鉛筆マークが出るので、クリック

(7) 「名前」欄に表示させたい名前を入力します。また、「集計方法」を変更したい場合もここを編集すればOKです。
今回は後続の手順で、グラフのY軸を「推定コスト（USD）」としたいので、そのように編集しました。

(8) 「スタイル」タブをクリックし、「左Y軸」の「軸タイトルを表示」をクリック。
すると、(7)で編集した内容がY軸に表示されるようになります。

(9) 一通り編集した後の画面です。グラフタイトルも編集して、「ユーザー別BigQueryコスト」としてみました。
作成直後でデータ量が少ないため、不格好になってしまっていますが、月が変わればもう1本右隣に縦棒が出るはずです。

補足事項

2022年11月1日現在では、手順1-(4)の通りログの保持期間が30日から動かせないので、表示されたとしても2か月分までになります（しかも、日がたつと期限を向かえ、前月分がどんどん減っていく可能性あり）。
それ以上のログを取りたい場合は別途データを保持する仕組みを構築するか、後続のリリースを待つかどちらかでしょうか。
なお、ログの保持期間が30日を超える場合には0.01USD/GiBのストレージ料金がかかるので注意しましょう。

5-2. 日別・ユーザー別でクエリコストをグラフ化

続いて、日別・ユーザー別でクエリコストをグラフ化します
今回、当日を含む過去7日間（6日前以降）のデータを表示することとします

(1) Looker Studioの編集画面にて、「データを追加」をクリック

(2) 「データのレポートへの追加」が開くので、「BigQuery」をクリック

(3) 「カスタムクエリ」（①） > 対象プロジェクト と選択し（②）、「カスタムクエリを入力」にクエリを入力したら（③）、「追加」をクリック（④）

③の「カスタムクエリを入力」欄で入力するクエリ

以下、「カスタムクエリを入力」に入力するクエリです。

WITH logs AS(
  SELECT
   DATE(TIMESTAMP(JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata,"$.jobChange.job.jobStats.endTime")),"Asia/Tokyo") AS day,
   proto_payload.audit_log.authentication_info.principal_email AS email,
   cast(JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata, "$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS int64) AS total_billed_bytes,
  FROM
   `test_log_analytics_2._AllLogs`
  WHERE
   JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata,"$.jobChange.job.jobConfig.type") = "QUERY"
   AND DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 6 day) <= DATE(TIMESTAMP(JSON_EXTRACT_SCALAR(proto_payload.audit_log.metadata,"$.jobChange.job.jobStats.endTime")),"Asia/Tokyo")
 )
 SELECT
   L.day,
   SUBSTR(L.email,1,STRPOS(L.email,'@')-1) AS user,
   5.0 * (L.total_billed_bytes/POWER(2, 40)) AS estimated_usd_cost
 FROM
   logs L;

(4) レポート編集画面に戻ったら、「グラフを追加」をクリックし（①）、「積み上げ縦棒グラフ」を選択（②）

(5) 初期状態だと想定の内容でないので、編集していきます。画面右方の「グラフ」をクリック。

(6) 「SETUP」タブにて、以下の通り項目の編集を行い（①）、必要に応じて、選択内容の名前変更や、グラフの軸の表示設定も実施しましょう。

①SETUPタブの編集内容

項目名	選択内容	備考
期間のディメンション	day	月別のグラフと違いフォーマット(string型になる）していないからか、この項目も出現する
ディメンション	day
内訳ディメンション	user
指標	estimated_usd_cost	デフォルトでSUMが選択される（はずな）ので、そのままにする
並べ替え	day / 昇順	グラフ横軸のソート順
予備の並べ替え	estimated_usd_cost / 降順	グラフ棒内（?）の並べ替え。 左記の設定で使用料の高いユーザーが下に来る

(7) データの更新頻度も編集してみます。画面いちばん右側の「データ」欄にて、「BigQueryカスタムSQL」（日別のほう: カラム名に「day」があるのですぐわかると思います）の左側のマークをクリック

(8) 画面下方から、以下のようなモーダルが出現するので、「データの更新頻度」をクリック

(9) 「デフォルトの更新頻度」は12時間なのですが、日別かつ当日分もできるだけリアルタイムで表示させたいので、もう少し頻繁に更新されるようにします。
今回は「時間ごと」を選択しましたが、状況に応じてそのまま（12時間）、あるいは、15分毎や4時間ごと、でもよいでしょう。
（手順: お好みの更新頻度のラジオボタンにチェックを入れ（①）、「データの更新頻度を設定」をクリック（②））

ただし、更新頻度が高いほどBigQueryに対してクエリコストが発生する 点は注意してください。

(10) 完成です！こちらもまだ1日分しかデータがなく、不格好な感じですが、日がたつと縦棒が増え、らしくなっていくはずです

5-3. オマケ: 3日経過後

以下、作成から3日経過後のグラフです。随分それっぽくなりました（笑）

終わりに

Log Analyticsの使い方としては、従来版と大きな違いはない印象でした。
低コストでエラーが発生しにくい点、魅力的ですね（個人的には、Log Analyticsのワークスペースからクエリ発行し放題なのが嬉しい！）

早くGAになってほしいです♪

※2023年1月17日追記:
　ページ:ログ分析 | Cloud Logging | Google Cloudによれば2023年1月17日現在も公開プレビュー版のままのようです。
　Cloud Logging の料金概要| オペレーションスイート | Google Cloudに「2023 年 3 月 1 日までは、ログの保持期間は 30 日間」とあるので、2023年3月あたりにGAにならないかなと期待。

参考

Log Analyticsとは

• Cloud Logging のご紹介 - BigQuery を活用した Log Analytics | Google Cloud 公式ブログ
• BigQuery Export ユーザーの Log Analytics への移行 | Google Cloud 公式ブログ
• GoogleCloud のオペレーション スイートの料金 | オペレーションスイート - Google Cloud
• Looker Studio: ビジネス分析の可視化 | Google Cloud

手順

2. ログ ルーティング シンクの作成

• BigQuery audit logs overview | BigQuery - Google Cloud
• Audit logs migration guide | BigQuery - Google Cloud
• Support the newer BigQueryAuditMetadata format #8 | teamdatatonic/looker-gcp-control

5. Looker Studioでクエリコストをグラフ化

• Looker Studio を使用したクイックスタート | BigQuery - Google Cloud
• Looker Studio を使用してデータを可視化する | BigQuery - Google Cloud
• GoogleCloud のオペレーション スイートの料金 | オペレーション スイート - Google Cloud
• データの更新頻度を管理する | データポータルのヘルプ
• BigQueryのコスト可視化ダッシュボードを10分で作る | 下町柚子黄昏記 by @yuzutas0