Go to Qiita Advent Calendar 2024 Top
LoginSignup
6
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@C_HERO(ChiHaRu)

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

Last updated at Posted at 2022-11-01

先々月～先月（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 からのクエリ実行は無料だったり、ログ形式を変更しても、スキーマの不一致によるエラーは発生しない、などなど従来版と比べ、いろいろ改善されている様子。

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

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

Looker Studio

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

詳細は、

をご覧ください。

前提条件

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

手順

1. ログバケットの作成

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

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

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

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

image.png

①の入力内容詳細

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

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

image.png

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

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

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

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

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

①の選択内容詳細

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

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

image.png

①の入力内容詳細

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

補足事項

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

3. BigQueryの確認

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

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

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

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

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

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

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

image.png

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

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

以下、日別・ユーザー別でコストとクエリ実行回数を求めるです。
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を開き、「空のレポート」をクリック

image.png

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

image.png

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

image.png

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

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

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

image.png

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

image.png

②SETUPタブの編集内容

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

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

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

image.png

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

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

image.png

補足事項

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

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

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

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

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

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

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

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

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) レポート編集画面に戻ったら、「グラフを追加」をクリックし（①）、「積み上げ縦棒グラフ」を選択（②）

image.png

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

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

①SETUPタブの編集内容

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

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

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

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

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

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

image.png

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

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

image.png

終わりに

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とは

手順

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

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

6
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?