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 機能により、ジョブ履歴に発行元のレポートやデータソース設定が記録されるようになりました。
当記事では、このジョブ履歴に記録されたラベルをもとに、レポートごとの課金額を調査します。
留意事項
当記事は2023年10月時点の Private Preview 時の仕様をもとに執筆しています。GA (Generally Available、一般公開) 後の仕様は違ったものになる可能性があることにご留意ください。
また当記事内で紹介するクエリは一部、前述の公式ドキュメントのクエリを流用しています。
Preview 中の機能の注意点については以下をご参照ください。
情報取得方法を理解する
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 レポートの閲覧履歴を取ることはできました。
こちらの方法では BigQuery へのクエリに関する情報は取れませんが、Looker Studio レポートについて「いつ、誰が、どこから、どのレポートへアクセスしたか」等を確認することができます。当記事でご紹介した方法で合わせて、Looker Studio 利用状況の可視化にあたってご参考としてください。
杉村 勇馬 (記事一覧)
執行役員 CTO / クラウドソリューション部 部長
元警察官という経歴を持つ現 IT エンジニア。クラウド管理・運用やネットワークに知見。AWS 12資格、Google Cloud認定資格11資格。X (旧 Twitter) では Google Cloud や AWS のアップデート情報をつぶやいています。
Follow @y_sugi_it