G-gen の杉村です。Google Cloud (旧称 GCP) の BigQuery と BI ツールである Looker Studio のネイティブ統合機能である BigQuery native integration in Looker Studio が2023年10月2日に Private Preview (申込制) で公開されたため、その機能を活用して Looker Studio レポートごとの BigQuery 課金額を調査してみました。

はじめに

やること

当記事では、Looker Studio レポートごとに、そのレポートからのクエリが BigQuery 課金のうちのどれくらいを占めているかを調査する方法をご紹介します。

BigQuery をデータソースとする Looker Studio レポートからは、BigQuery に対してクエリ (SQL) が発行されます。BigQuery の利用コスト削減を考えるとき、どのレポートからのどのようなクエリで課金がかさんでいるのかを調査することが必要です。

2023年10月2日に Private Preview (申込制) として公開された BigQuery native integration in Looker Studio 機能の登場でこの調査が容易になったため、これを利用した調査方法をご紹介します。

調査方法

BigQuery で実行されたクエリは INFORMATION_SCHEMA.JOBS ビューに記録されます。

INFORMATION_SCHEMA は BigQuery が自動的に記録する一連のシステムビューであり、JOBS はその一つです。ここにはプロジェクト内で発行されたすべてのジョブ (QUERY、LOAD、COPY等) が記録され、クエリ内容や実行時間、スキャンデータ量、消費スロット数などを確認することができます。

参考 : BigQuery INFORMATION_SCHEMA の概要
参考 : JOBS ビュー

以前は INFORMATION_SCHEMA.JOBS のジョブ履歴を確認しても、発行元の Looker Studio レポートは分かりませんでした。しかし2023年10月2日に Private Preview になった BigQuery native integration in Looker Studio 機能により、ジョブ履歴に発行元のレポートやデータソース設定が記録されるようになりました。

参考 : BigQuery native integration in Looker Studio preview

当記事では、このジョブ履歴に記録されたラベルをもとに、レポートごとの課金額を調査します。

留意事項

当記事は2023年10月時点の Private Preview 時の仕様をもとに執筆しています。GA (Generally Available、一般公開) 後の仕様は違ったものになる可能性があることにご留意ください。

また当記事内で紹介するクエリは一部、前述の公式ドキュメントのクエリを流用しています。

Preview 中の機能の注意点については以下をご参照ください。

blog.g-gen.co.jp

情報取得方法を理解する

INFORMATION_SCHEMA.JOBS の構造

INFORMATION_SCHEMA.JOBS にはジョブの様々な情報が履歴として記録されます。スキーマは以下の通りです。

参考 : JOBS ビュー - スキーマ

BigQuery native integration in Looker Studio の登場により labels 列に looker_studio_report_id と looker_studio_datasource_id が記録されるようになります。

labels 列は「ネストされた繰り返し列」であり、配列形式かつ key-value で値が記録されます。その記録は以下のようになります (一部の列のみ抜粋)。

creation_time	job_id	labels.key	labels.value
2023-10-06 02:47:02.480000 UTC	job_xxxx1234	requestor	looker_studio
		looker_studio_report_id	ccb23aca-1234-1234-abcd-1234abcd1234
		looker_studio_datasource_id	e8820559-4321-4321-dcba-4321dcba4321

job_xxxx1234 というジョブのラベルとして、リクエスト元が looker_studio であることが記録されており、同時にレポート ID とデータソース ID が記録されています。レポート ID とデータソース ID は、Looker Studio のレポートとデータソースに一意に振られる ID です。これでレポートを特定することができます。

クエリ

まずは探索的に以下の SQL を実行してみて、理解を深めます。

必要に応じて対象リージョン (region-us の部分。東京リージョンなら region-asia-northeast1) や WHERE 句の対象期間を変更してください。

CREATE TEMP FUNCTION GetLabel(labels ANY TYPE, label_key STRING)
AS (
  (SELECT l.value FROM UNNEST(labels) l WHERE l.key = label_key)
);
  
SELECT
  DATETIME(creation_time, "Asia/Tokyo") AS creation_time_jst,
  job_id,
  GetLabel(labels, "looker_studio_report_id") AS report_id,
  GetLabel(labels, "looker_studio_datasource_id") AS datasource_id,
  user_email,
  cache_hit,
  query,
  referenced_tables,
  total_bytes_billed,
  total_slot_ms
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  DATE(creation_time, "Asia/Tokyo")
    BETWEEN DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY)
    AND     DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 0 DAY)
  AND
    GetLabel(labels, 'requestor') = 'looker_studio'
ORDER BY
  1 DESC

上記クエリは INFORMATION_SCHEMA.JOBS から、requestor が looker_studio であるジョブだけを抽出するクエリです。ネストされた繰り返し列を UNNEST() 関数で分解してクエリ・フィルタしています。

なお Private Preview で利用している場合、機能が有効化されて以降に Looker Studio から発行されたクエリだけにラベルが付与されますので、それ以前のクエリ結果は上記のクエリ結果には入りません。

このクエリの結果は以下のようなものになります (1行だけ抜粋)。

列の意味を解説します。

列名	意味
creation_time	クエリの発行時間。またこの列は JOBS ビューのパーティショニング列
job_id	BigQuery ジョブに振られる一意の ID
report_id	クエリ発行元の Looker Studio レポートの ID
datasource_id	クエリ発行元の Looker Studio データソースの ID
user_email	クエリ発行ユーザ。データソース設定により「レポートのオーナー」「レポートの閲覧者」「サービスアカウント」のいずれかのメールアドレスとなる
cache_hit	キャッシュが適用されたか否か (TRUE/FALSE)
query	SQL 文
referenced_tables	クエリ対象のテーブルに関する情報 (ネスト繰り返し列)
total_bytes_billed	課金対象バイト数 (キャッシュ適用時は0になる)
total_slot_ms	ジョブの消費スロット時間 (ms) (キャッシュ適用時は null になる)

report_id によりクエリ発行元の Looker Studio レポートが分かります。

BigQuery の課金モードがオンデマンド (デフォルト) の場合、total_bytes_billed が課金対象バイト数です。このバイト数の合計にオンデマンド単価をかけたものが、BigQuery の利用料金になります。

参考 : BigQuery pricing

もし BigQuery を Editions で利用している場合は、total_slot_ms を見て、このジョブがどのくらいスロットを消費したかを確認できます。ただし Editions の Autoscaler ではその同時間帯に発行されたすべてのジョブのために必要なスロットを確保しますので、単純にこの数値を料金計算に使うことはできません。Editions をお使いの場合は、あくまでこのクエリでは「どのジョブがその時間帯のスロットのスケーリングの原因となったか」を調べられるにすぎない、ということにご注意ください。

ID からのレポート特定

検索ボックス

先ほどの方法でレポート ID やデータソース ID が取れることは分かりました。

これらの ID から、実際にレポートやデータソースを特定してみます。

Web UI であれば、Looker Studio トップ画面 (https://lookerstudio.google.com/) の検索ボックスに ID を入れると、対象レポートが表示されます。

「最近」「共有アイテム」「自分がオーナー」「(Looker Studio Pro の場合のみ) チームワークスペース」など、検索対象の場所を選択してから検索してください。

データソースの場合は、検索ボックスの下の「レポート / データソース / エクスプローラ」と3つ並んでいるボタンから「データソース」を選んでから検索します。

URL

上記の方法以外にも簡単な方法があります。

実はレポート ID やデータソース ID は、Web UI でアクセスするときの URL になっています。

report_id が abcd の場合、以下のように URL 末尾に付与すると、当該レポートの閲覧・編集画面に飛ぶことができます。

https://lookerstudio.google.com/reporting/abcd

データソースの場合は以下です。datasource_id が 1234 の場合、以下の URL になります。

https://lookerstudio.google.com/datasources/1234

ただし、最低でも閲覧権限を持っていないとエラー画面になります。

集計クエリ

ここまでで、INFORMATION_SCHEMA.JOBS からレポート ID が特定できること、またレポート ID が分かればレポートを一意に特定できることが分かりました。

レポート ID ごとに処理バイト数を算出できる実用的なクエリもサンプルとして掲載しますので、ご活用ください (オンデマンド利用向け)。例のごとく、リージョン (11行目) や期間 (14〜15行目) は必要に応じて変更してください。

CREATE TEMP FUNCTION GetLabel(labels ANY TYPE, label_key STRING)
AS (
  (SELECT l.value FROM UNNEST(labels) l WHERE l.key = label_key)
);
  
WITH job_list AS (    
  SELECT
    GetLabel(labels, "looker_studio_report_id") AS report_id,
    total_bytes_billed,
  FROM
    `region-us`.INFORMATION_SCHEMA.JOBS
  WHERE
    DATE(creation_time, "Asia/Tokyo")
      BETWEEN DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 30 DAY)
      AND     DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 0 DAY)
    AND
      GetLabel(labels, 'requestor') = 'looker_studio'
)
  
SELECT
  report_id,
  SUM(total_bytes_billed)/1024/1024/1024 AS total_gb_billed
FROM
  job_list
GROUP BY
  report_id

過去30日間のレポート ID ごとの合計ギガバイト数を求めるクエリです。オンデマンド課金であれば、これにオンデマンド単価を掛け算することでレポートごとの BigQuery コストが計算できます。

以下のような結果が返ります。

report_id	total_gb_billed
abcd-1234	125.1953
efgh-5678	1026.224609375

BigQuery Editions をご利用の場合は total_bytes_billed を集計するのではなく、代わりに total_slot_ms を集計するなどして、スロットの Autoscale 要因のうちどれだけを占めているか、などの調査の参考にしてください。

繰り返しになりますが、Private Preview にお申し込み頂いた場合、機能が有効化されて以降のクエリにしか report_id ラベルがつきませんので、それ以前のクエリは report_id が null になる点にもご留意ください。

その他の Tips

Google Workspace の「監査と調査」ツールによって、以前から Looker Studio レポートの閲覧履歴を取ることはできました。

参考 : Looker Studio のログイベント

こちらの方法では BigQuery へのクエリに関する情報は取れませんが、Looker Studio レポートについて「いつ、誰が、どこから、どのレポートへアクセスしたか」等を確認することができます。当記事でご紹介した方法で合わせて、Looker Studio 利用状況の可視化にあたってご参考としてください。