本記事は2024年11月時点での内容になります。最新情報については公式サイトなどを確認願います。
Google Cloudの使用料をLooker Studioで可視化する手順
BigQueryのコストを可視化する方法については以前紹介しましたが、今回はGoogle Cloud Billing Exportを利用して、Google Cloud全体の使用料をBigQueryにエクスポートし、Looker Studioで可視化するための手順を紹介します。
ステップ 1: Google Cloud Billing Exportの設定
-
Google Cloud Consoleにログイン
Google Cloud Console にアクセスして、使用しているプロジェクトを選択します。 -
Billing Exportの有効化
- メニューから「課金」 > 「課金エクスポート」を選択。
- 「BigQueryエクスポート」をクリック。
- 使用データをエクスポートするBigQueryプロジェクトとデータセットを指定。
- 「エクスポートを有効にする」をクリックして設定完了。
※ 上記を行うには下記の権限（ロール）が必要です。- Billing Account Administrator（課金アカウント管理者）このロールは、課金アカウントの設定を変更するために必要です。
- Billing Account Viewer（課金アカウント閲覧者）課金情報を閲覧するために必要です。
- BigQuery Data Editor（BigQueryデータ編集者）データセットやテーブルを作成し、データを書き込む権限が必要です。
- BigQuery Job User（BigQueryジョブユーザー）BigQuery内でのジョブ（クエリやデータの読み書き）を実行するために必要です。
-
データ確認
数時間から24時間以内に、エクスポートされた使用料データがBigQueryの指定したデータセット内に保存されます。主に以下のテーブルが作成されます：-
gcp_billing_export_v1_<BillingAccountID>（リソース別の課金詳細）
-
ステップ 2: BigQueryでのデータ確認とカスタマイズ
-
BigQueryにアクセス
BigQueryコンソール で、課金データが正しくエクスポートされていることを確認します。 -
カスタムクエリの作成（必要に応じて）
使用状況データを整理し、Looker Studioでの可視化に適した形式にするためのクエリを作成します。例えば：SELECT TIMESTAMP_TRUNC(usage_start_time, DAY) AS usage_date, service.description AS service_name, sku.description AS sku_name, SUM(cost) AS total_cost FROM `your_project_id.your_dataset_id.gcp_billing_export_v1_*` WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20241231' GROUP BY usage_date, service_name, sku_name ORDER BY usage_date;
ステップ 3: Looker Studioでのデータ接続
-
Looker Studioにログイン
Looker Studio にアクセス。 -
新しいデータソースを追加
- 「データソースを追加」をクリック。
- 「BigQuery」を選択。
- 使用データを保存したプロジェクト、データセット、テーブルを選択。
-
スキーマ確認
フィールド（列）が正しく認識されているか確認。必要に応じてフィールドタイプを変更します（例: 日付フィールドは日付型に設定）。
ステップ 4: レポートの作成と可視化
-
新しいレポートを作成
- 「新しいレポートを作成」をクリック。
- 先ほど作成したデータソースをレポートに接続。
-
グラフやテーブルを追加
- 折れ線グラフ: 日付ごとの使用料を可視化。
- 円グラフ: サービスごとのコスト割合を表示。
- 棒グラフ: SKUごとの使用料比較。
-
フィルタとコントロールの追加
- フィルタ: 特定の期間やサービスに絞り込む。
- コントロール: ユーザーがインタラクティブにデータを操作できるようにする。
-
スタイルとフォーマット調整
見た目を整え、読みやすいデザインにする。
ステップ 5: レポートの共有
-
共有リンクを生成
「共有」 > 「リンクを共有可能にする」でレポートへのリンクを生成。 -
アクセス権の設定
特定のユーザーにのみアクセスを許可するか、リンクを知っているすべての人が閲覧できるように設定します。
クエリの詳細解説
以下は、Google Cloud BillingデータをBigQueryで加工する際のクエリ例とその解釈です。
WITH target AS(
SELECT
150 AS rate, -- ドル円レート（固定値）
6.25 AS fee_tb, -- 1TBあたりの料金（USD）
PARSE_DATE("%Y%m%d", @DS_START_DATE) AS start_date,
PARSE_DATE("%Y%m%d", @DS_END_DATE) AS end_date
)
,billing as(
SELECT
service.description AS service,
TRIM(REGEXP_EXTRACT(TRIM(COALESCE(REGEXP_EXTRACT(REPLACE(sku.description,'Long-Term','Long Term'),r'^(.*?)[(-]'), sku.description)), r'^[A-Z][A-Za-z]*(?:\s[A-Z][A-Za-z]*)*')) AS sku_description,
sku.description AS original_sku_description,
DATE(usage_start_time,'Asia/Tokyo') AS usage_date,
project.id AS project_id,
project.name AS project_name,
CASE WHEN sku.description LIKE '%Replication%' THEN REGEXP_EXTRACT(resource.name, r'/datasets/([^\/]+)/') ELSE resource.name END AS resource_name, -- sku_descriptionがAnalysisの場合はジョブID、Storageの場合はデータセット名
ROUND(SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)),2) AS cost_jpy,
MIN(DATE(DATETIME(usage_start_time,'America/Los_Angeles'))) AS usage_start_date,
MAX(DATE(DATETIME(usage_start_time,'America/Los_Angeles'))) AS usage_end_date
FROM `<project_id>.all_billing_data.gcp_billing_export_resource_v1_<billing_id>`
CROSS JOIN target
WHERE
DATE(DATETIME(usage_start_time,'Asia/Tokyo')) BETWEEN start_date AND end_date
GROUP BY ALL
HAVING cost_jpy > 0
)
SELECT
project_id,
resource_name,
service,
sku_description,
usage_date,
cost_jpy
FROM billing;
クエリの目的
- BigQueryの使用料（ストレージ、クエリ、レプリケーションなど）を日付、リソース、プロジェクト単位で集計。
- ドル円レートを考慮し、費用を日本円で計算。
- クレジットを適用して実際のコストを反映。
重要なポイント
-
為替レート:
クエリ内でrateを固定値として設定していますが、Googleスプレッドシートなど外部ソースと連携することで動的な為替レートも可能。 -
期間指定
@DS_START_DATE、@DS_END_DATEとすることで、Looker Studioの期間フィルターで指定した期間が対象となる -
リージョン:
リージョンに応じてusage_start_timeのタイムゾーンを切り替える仕組みがあります（日本時間またはアメリカ時間）。 -
クレジットの考慮:
UNNEST(credits)を利用して適用済みのクレジット額を費用に反映。
これで、Google Cloudの使用料をLooker Studioで可視化し、プロジェクトごとの費用をより詳細に分析できます！