Cloud Billing データのエクスポート クエリの例

このページでは、BigQuery にエクスポートして保存された Cloud Billing データをクエリする方法の例を紹介します。

クエリで使用するテーブル名の指定

以下の例では、BigQuery で Cloud Billing データをクエリするには、FROM 句でテーブル名を指定する必要があります。テーブル名は、project.dataset.BQ_table_name の 3 つの値を使用して決定されます。

  • project は、BigQuery データセットを含む設定した Cloud プロジェクトの ID です。
  • dataset は、エクスポートした Cloud Billing データを含む BigQuery テーブルを格納する設定した BigQuery データセットの名前です。
  • BQ_table_name は、クエリを実行するエクスポートされた Cloud Billing データを含む BigQuery テーブルの名前です。 Cloud Billing データを含む 2 つの BigQuery テーブルがあります。
    • 1 日の費用の詳細テーブル: BigQuery データセットでは、このテーブルには gcp_billing_export_v1_<BILLING_ACCOUNT_ID> という名前が付けられています。
    • 料金表: BigQuery データセットでは、このテーブルの名前は cloud_pricing_export です。

Cloud Billing データ型別のクエリの例

このページでは、1 日の費用の詳細データ料金データの両方に対するクエリの例を説明します。

1 日あたりの費用の詳細データ 料金データ
1 日あたりの費用の詳細クエリの例 料金データクエリの例

1 日あたりの費用の詳細クエリの例

このセクションでは、BigQuery にエクスポートされた Cloud Billing の 1 日あたりの費用の詳細データをクエリする方法のさまざまな例について説明します。

費用の詳細クエリの例で使用している共通の値

このセクションのクエリの例では、次の値を使用しています。

  • テーブル名: project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX

請求書の料金総額を取得する

次のクエリで、エクスポートされた課金データを使用して料金とクレジット額を表示する 2 つの方法を説明します。

  • total フィールドは、浮動小数点数の料金とクレジット額をそのまま合計します。そのため、浮動小数点の丸め誤差が生じる可能性があります。
  • total_exact フィールドは、料金とクレジット額をマイクロに変換してから合計します。その合計金額をドルに戻すことで、浮動小数点の丸め誤差を回避します。

例 1: 請求書あたりのすべての費用の合計

次のクエリでは、各月の請求総額が、通常料金、税金、調整額、および丸め誤差の合計として表示されます。

標準 SQL

SELECT
  invoice.month,
  SUM(cost)
    + SUM(IFNULL((SELECT SUM(c.amount)
                  FROM UNNEST(credits) c), 0))
    AS total,
  (SUM(CAST(cost * 1000000 AS int64))
    + SUM(IFNULL((SELECT SUM(CAST(c.amount * 1000000 as int64))
                  FROM UNNEST(credits) c), 0))) / 1000000
    AS total_exact
FROM `project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
GROUP BY 1
ORDER BY 1 ASC
;

たとえば、前のクエリの結果は次のようになります。

Row month total total_exact
1 201901 $1005.004832999999984 $1005.00
2 201902 $992.3101739999999717 $992.31
3 201903 $1220.761089999999642 $1220.76

例 2: 請求月ごとの料金タイプ別詳細を取得する

次のクエリでは、各月の cost_type ごとの合計が表示されます。料金タイプには、通常料金、税金、調整額、および丸め誤差があります。

標準 SQL

SELECT
  invoice.month,
  cost_type,
  SUM(cost)
    + SUM(IFNULL((SELECT SUM(c.amount)
                  FROM   UNNEST(credits) c), 0))
    AS total,
  (SUM(CAST(cost * 1000000 AS int64))
    + SUM(IFNULL((SELECT SUM(CAST(c.amount * 1000000 as int64))
                  FROM UNNEST(credits) c), 0))) / 1000000
    AS total_exact
FROM `project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
GROUP BY 1, 2
ORDER BY 1 ASC, 2 ASC
;

たとえば、前のクエリの結果は次のようになります。

Row month cost_type total total_exact
1 201901 regular $1000.501209987994782 $1000.50
2 201901 rounding_error –$0.500489920049387 –$0.50
3 201901 tax $10.000329958477891 $10.00
4 201901 adjustment –$5.002572999387045 –$5.00

ラベルを使用したクエリの例

次の例では、ラベルでデータのクエリを実行する別の方法を示しています。

このセクションの例では、次のことを前提とします。

  • アプリを 2 つ(grapefruit-squeezer と chocolate-masher)持っている。
  • アプリごとに、2 つの環境(開発と本番)がある。
  • アプリごとに、開発環境に 1 つの小さいインスタンスがある。
  • 本番環境では、アメリカに 1 つの小さいインスタンス、アジアに 1 つの小さいインスタンスがある。
  • 各インスタンスには、アプリと環境のラベルが付けられている。
  • 実験に使用するラベルのないインスタンスが 1 つある。

請求額合計は $24 で、内訳は次のとおりです。

インスタンス ラベル 合計費用
南北アメリカで動作する 1 VCPU 搭載小規模インスタンス なし $4
南北アメリカで動作する 1 VCPU 搭載小規模インスタンス app: chocolate-masher
environment: dev
$2
南北アメリカで動作する 1 VCPU 搭載小規模インスタンス app: grapefruit-squeezer
environment: dev
$3
南北アメリカで動作する 1 VCPU 搭載小規模インスタンス app: chocolate-masher
environment: prod
$3.25
1 つの VCPU がアジアで稼働している小規模インスタンス app: chocolate-masher
environment: prod
$3.75
南北アメリカで動作する 1 VCPU 搭載小規模インスタンス app: grapefruit-squeezer
environment: prod
$3.50
1 つの VCPU がアジアで稼働している小規模インスタンス app: grapefruit-squeezer
environment: prod
$4.50

グループ化せずにすべての行に対してクエリを発行

上記の費用を最も詳細に確認する方法は、グループ化せずにすべての行に対してクエリを実行することです。ラベルと SKU の説明を除き、すべてのフィールド(プロジェクト、サービスなど)が同じであるとします。

標準 SQL

SELECT
  sku.description,
  TO_JSON_STRING(labels) as labels,
 cost as cost
FROM `project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`;

レガシー SQL

TO_JSON_STRING not supported.
Row sku.description labels cost
1 南北アメリカで動作する 1 VCPU 搭載小規模インスタンス [] $4
2 南北アメリカで動作する 1 VCPU 搭載小規模インスタンス [{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"dev"}] $2
3 南北アメリカで動作する 1 VCPU 搭載小規模インスタンス [{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"dev"}] $3
4 南北アメリカで動作する 1 VCPU 搭載小規模インスタンス [{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"prod"}] $3.25
5 1 つの VCPU がアジアで稼働している小規模インスタンス [{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"prod"}] $3.75
6 南北アメリカで動作する 1 VCPU 搭載小規模インスタンス [{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"prod"}] $3.50
7 1 つの VCPU がアジアで稼働している小規模インスタンス [{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"prod"}] $4.50
合計 $24

ラベルマップ別に JSON 文字列としてグループ化

これは、ラベルの組み合わせごとに費用を素早く簡単に割り出す方法です。

標準 SQL

SELECT
  TO_JSON_STRING(labels) as labels,
  sum(cost) as cost
FROM `project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
GROUP BY labels;

レガシー SQL

TO_JSON_STRING not supported.
Row labels cost
1 [] $4
2 [{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"dev"}] $2
3 [{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"dev"}] $3
4 [{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"prod"}] $7
5 [{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"prod"}] $8
合計 $24

ラベル値を基準に特定のキーについてグループ化

特定のラベルキーの値についての費用を割り出すのは、一般的な使用例です。LEFT JOIN を使用し、JOIN 条件(WHERE ではなく)としてキーフィルタを設定すると、このキーを含まない費用が含まれるため、費用の全体像を把握できます。

標準 SQL

SELECT
  labels.value as environment,
  SUM(cost) as cost
FROM `project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
LEFT JOIN UNNEST(labels) as labels
  ON labels.key = "environment"
GROUP BY environment;

レガシー SQL

SELECT
  labels.value as environment,
  SUM(cost) as cost
FROM [project:dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX]
WHERE labels.key = "environment" OR labels.key IS NULL
GROUP BY environment;
Row environment cost
1 prod $15
2 dev $5
3 null $4
合計 $24

Key-Value ペア別にグループ化

これらの結果を解釈またはエクスポートするときは注意してください。ここでは各行が、二重カウントなしの有効な合計を示していますが、他の行と組み合わせることはできません(ただし、キーが同じである場合や、同じリソースに複数のキーが設定されていない場合などは除きます)。

標準 SQL

SELECT
  labels.key as key,
  labels.value as value,
  SUM(cost) as cost
FROM `project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
LEFT JOIN UNNEST(labels) as labels
GROUP BY key, value;

レガシー SQL

SELECT
  labels.key as key,
  labels.value as value,
  SUM(cost)
FROM [project:dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX]
GROUP BY key, value;
Row key value cost
1 null null $4
2 app chocolate-masher $9
3 app grapefruit-squeezer $11
4 environment dev $5
5 environment prod $15
合計 $44

合計が請求額より大きいことに注意してください。

確約利用割引のクエリ

次のクエリで、エクスポートされた課金データの確約利用割引に関連する料金とクレジット額を表示する方法を説明します。

確約料金の表示

課金データのエクスポートで確約利用割引の確約料金を表示するには、次のサンプルクエリを使用します。

標準 SQL

SELECT
    invoice.month AS invoice_month,
    SUM(cost) as commitment_fees
FROM 'PROJECT.DATASET.TABLE'
WHERE LOWER(sku_description) LIKE "commitment%"
GROUP BY 1

確約クレジット額の表示

課金データのエクスポートで確約利用割引を表示するには、次のサンプルクエリを使用します。

標準 SQL

SELECT
    invoice.month AS invoice_month,
    SUM(credits.amount) as CUD_credits
FROM 'PROJECT.DATASET.TABLE'
LEFT JOIN UNNEST(credits) AS credits
WHERE LOWER(credits.name) = "committed use discount%"
GROUP BY 1

その他のクエリ例

指定した請求月のプロジェクトごとの費用とクレジット額のクエリ

2020 年 6 月という特定の月を(YYYYMM 形式で)指定すると、このクエリでは、プロジェクト ラベルを示しながら、プロジェクトごとにグループ化された費用とクレジット額を表示します。

標準 SQL

SELECT
  project.name,
  TO_JSON_STRING(project.labels) as project_labels,
  sum(cost) as total_cost,
  SUM(creds.amount) as total_credits
FROM `project:dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
  LEFT JOIN UNNEST(credits) as creds
WHERE invoice.month = "202006"
GROUP BY 1, 2
ORDER BY 1;

レガシー SQL

TO_JSON_STRING not supported.
名前 project_labels total_cost total_credits
1 CTG - Dev [{"key":"ctg_p_env","value":"dev"}] 79.140979 -4.763796
2 CTG - Prod [{"key":"ctg_p_env","value":"prod"},{"key":"ctg_team","value":"eng"}] 32.466272 -3.073356
3 CTG - Sandbox [{"key":"ctg_p_env","value":"dev"}] 0 0
4 CTG - Storage [{"key":"ctg_p_env","value":"prod"},{"key":"ctg_team","value":"data"}] 7.645793 -0.003761

料金データクエリの例

このセクションでは、BigQuery にエクスポートされた Cloud Billing 料金データをクエリする方法のさまざまな例を示します。

料金クエリの例で使用している共通の値

このセクションのクエリの例では、次の値を使用しています。

  • テーブル名: project.dataset.cloud_pricing_export
  • SKU ID: 2DA5-55D3-E679(Cloud Run - リクエスト)

特定の SKU の正規価格を取得する

次の例は、指定された SKU の料金階層ごとに list_price を返す単純なクエリを示しています。

標準 SQL

SELECT sku.id,  sku.description, list_price.*
FROM `project.dataset.cloud_pricing_export`
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

_PARTITIONTIME は BigQuery によって自動的に生成されるフィールドで、データが属する日付を表します。_PARTITIONTIME の代わりに、pricing_as_of_time など、Cloud Billing エクスポートによって明示的に生成されるフィールドを使用できます。

pricing_as_of_time フィールドを使用するように構成された同じクエリを以下に示します。

SELECT sku.id,  sku.description, list_price.*
FROM `project.dataset.cloud_pricing_export`
WHERE DATE(pricing_as_of_time) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

クエリ結果

id 説明 pricing_unit aggregation_info.
aggregation_level
aggregation_info.
aggregation_interval
tiered_rates.
pricing_unit_quantity
tiered_rates.
start_usage_amount
tiered_rates.
usd_amount
tiered_rates.
account_currency_amount
1 2DA5-55D3-E679 リクエスト COUNT アカウント 1 か月あたり 1000000 0 0 0
          1000000 2000000 40% 40%

特定の SKU の正規価格を取得し、サービスの説明を含める

このセクションの 2 つの例は、指定された SKU の料金階層ごとに list_price を返し、SKU の説明とサービスの説明を含めるクエリを示しています。

  • 例 1 では、1 行ごとに 1 つの SKU が返され、料金階層はネストされたデータとして表示されています。
  • 例 2 は、データのネストを解除して、料金階層ごとに SKU あたり 1 行を返す様子を示しています。

例 1: ネストされたデータを返す

この例では、単一の SKU にクエリを実行して list_price データを返しています。この SKU には複数の料金階層があります。SKU ID の行の下にネストされている個別の行に表示された正規価格フィールドの値。

標準 SQL

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       list_price.*
FROM my-billing-admin-project.my_billing_dataset.cloud_pricing_export
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

クエリ結果:

sku_id sku_description service_id service_description aggregation_info.
aggregation_level
aggregation_info.
aggregation_interval
tiered_rates.
pricing_unit_quantity
tiered_rates.
start_usage_amount
tiered_rates.
usd_amount
tiered_rates.
account_currency_amount
1 2DA5-55D3-E679 リクエスト 152E-C115-5142 Cloud Run アカウント 1 か月あたり 1000000 0 0 0
            1000000 2000000 40% 40%

例 2: 同じテーブルに結合されたネストされていないデータを返す

この例では、単一の SKU にクエリを実行して、list price を返しています。SKU には複数の料金階層があります。このクエリでは、UNNEST 演算子を使用して tiered_rates 配列をフラット化し、フィールドを同じテーブルに結合して料金階層ごとに 1 行を生成する様子を示しています。

標準 SQL

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       tier.*
FROM `my-billing-admin-project.my_billing_dataset.cloud_pricing_export`, UNNEST (sku_pricing.list_price.tiered_rates) as tier
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

クエリ結果:

sku_id sku_description service_id service_description pricing_unit_quantity start_usage_amount usd_amount account_currency_amount
1 2DA5-55D3-E679 リクエスト 152E-C115-5142 Cloud Run 1000000.0 0.0 0.0 0.0
2 2DA5-55D3-E679 リクエスト 152E-C115-5142 Cloud Run 1000000.0 2000000.0 40% 40%

商品分類と地域分類を使用して SKU をクエリする

  • 商品分類は、サーバーレス、Cloud Run、オンデマンド VM などの SKU に適用される商品カテゴリのリストです。
  • 地域分類は、タイプとリージョンの値で構成され、SKU に適用される地理的メタデータです。

SKU の商品分類を取得する

次の例は、指定された SKU の product_taxonomy リストを返すクエリを示しています。ここで、SKU ID = 2DA5-55D3-E679(Cloud Run - リクエスト)です。

標準 SQL

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       product_taxonomy
FROM `project.dataset.cloud_pricing_export`
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

クエリ結果:

sku_id sku_description service_id service_description product_taxonomy
1 2DA5-55D3-E679 リクエスト 152E-C115-5142 Cloud Run GCP
        サーバーレス
        Cloud Run
        その他

特定の商品分類のすべての SKU を取得する

次の例は、指定した product_taxonomy と一致するすべての SKU を返すクエリを示しています。このクエリでは、product taxonomy 値として Serverless を指定しています。

標準 SQL

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       product_taxonomy
FROM `project.dataset.cloud_pricing_export`
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
     AND "Serverless" in UNNEST(product_taxonomy)
LIMIT 10
;

クエリ結果:

sku_id sku_description service_id service_description product_taxonomy
1 0160-BD7B-4C40 Cloud Tasks ネットワーク リージョン内下り F3A6-D7B7-9BDA CloudTasks GCP
        サーバーレス
        CloudTasks
        その他
2 FE08-0A74-7AFD Cloud Tasks GOOGLE-API の下り F3A6-D7B7-9BDA CloudTasks GCP
        サーバーレス
        CloudTasks
        その他
3 A81A-32A2-B46D タスクキュー ストレージ ソルトレイクシティ F17B-412E-CB64 App Engine GCP
        サーバーレス
        GAE
        その他
        TaskQueue

特定の地域分類と商品分類に関するすべての SKU を取得する

次の例は、指定した geo_taxonomy リージョン、および指定した product_taxonomy と一致するすべての SKU を返すクエリを示しています。ここで、region = us-east4product_taxonomy = オンデマンド VM です。

標準 SQL

SELECT sku.id AS sku_id,
       sku.description AS sku_description,
       service.id AS service_id,
       service.description as service_description,
       geo_taxonomy,
       product_taxonomy
FROM `project.dataset.cloud_pricing_export`
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND "VMs On Demand" in UNNEST(product_taxonomy)
      AND geo_taxonomy.type = "REGIONAL"
      AND "us-east4" in UNNEST (geo_taxonomy.regions)
;

クエリ結果:

sku_id sku_description service_id service_description geo_taxonomy.type geo_taxonomy.regions product_taxonomy
1 9174-81EE-425B バージニア州で動作する単一テナンシー インスタンス RAM の単一テナンシー プレミアム 6F81-5844-456A Compute Engine REGIONAL us-east4 GCP
            Compute
            GCE
            オンデマンド VM
            メモリ: 1 GB あたり
2 C3B9-E891-85ED バージニア州で動作する単一テナンシー インスタンス RAM 6F81-5844-456A Compute Engine REGIONAL us-east4 GCP
            Compute
            GCE
            オンデマンド VM
            メモリ: 1 GB あたり
3 6E2A-DCD9-87ED バージニア州で動作する N1 事前定義済みインスタンス RAM 6F81-5844-456A Compute Engine REGIONAL us-east4 GCP
            Compute
            GCE
            オンデマンド VM
            メモリ: 1 GB あたり

Google Cloud Console で利用可能な費用と料金のレポート