Cloud Billing 数据导出查询示例

本页面提供了一些示例,演示如何查询导出并存储在 BigQuery 中的 Cloud Billing 数据。

指定要在查询中使用的表名称

在这些示例中,要在 BigQuery 中查询 Cloud Billing 数据,您需要在 FROM 子句中指定表名称。表名称使用 project.dataset.BQ_table_name 这三个值确定。

  • project 是您设置的包含 BigQuery 数据集的 Cloud 项目 ID。
  • dataset 是您设置的包含所导出 Cloud Billing 数据的 BigQuery 数据集名称。
  • BQ_table_name 是您要查询的已导出 Cloud Billing 数据所在的 BigQuery 表的名称。有两个包含 Cloud Billing 数据的 BigQuery 表:
    • 每日费用详情表:在 BigQuery 数据集中,该表命名为 gcp_billing_export_v1_<BILLING_ACCOUNT_ID>
    • 价格表:在 BigQuery 数据集中,该表命名为 cloud_pricing_export

按 Cloud Billing 数据类型进行的查询示例

本页面提供了每日费用详情数据价格数据的查询示例。

每日费用详情数据 价格数据
每日费用详情查询示例 价格数据查询示例

每日费用详情查询示例

本部分提供了不同的示例,演示如何查询导出到 BigQuery 的 Cloud Billing 每日费用详情数据

费用详情查询示例中使用的常用值

本部分中的查询示例使用以下值:

  • 表名称project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX

返回帐单总费用

以下查询演示了使用导出的结算数据查看费用和赠金值的两种方法。

  • total 字段直接对浮点费用和赠金值求和,可能会产生浮点舍入误差。
  • total_exact 字段在求和之前将费用和赠金值转换为 micros,然后在求和后转换回美元,以避免产生浮点舍入误差。

示例 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
;

例如,上述查询的结果可能是:

总计 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
;

例如,上述查询的结果可能是:

费用类型 总计 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 个环境(dev 和 prod)。
  • 每个应用的 dev 环境中有 1 个小型实例。
  • Prod 环境在美洲和亚洲各有 1 个小型实例。
  • 每个实例都带有应用和环境标签。
  • 您有 1 个实例不带任何用于实验的标签。

您的总帐单费用为 $24,明细如下:

实例 标签 总费用
1 个在美洲运行的小型实例(有 1 个 vCPU) $4
1 个在美洲运行的小型实例(有 1 个 vCPU) 应用:chocolate-masher
环境:dev
$2
1 个在美洲运行的小型实例(有 1 个 vCPU) 应用:grapefruit-squeezer
环境:dev
$3
1 个在美洲运行的小型实例(有 1 个 vCPU) 应用:chocolate-masher
环境:prod
$3.25
1 个在亚洲运行的小型实例(有 1 个 vCPU) 应用:chocolate-masher
环境:prod
$3.75
1 个在美洲运行的小型实例(有 1 个 vCPU) 应用:grapefruit-squeezer
环境:prod
$3.50
1 个在亚洲运行的小型实例(有 1 个 vCPU) 应用:grapefruit-squeezer
环境: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.
sku.description 标签 cost
1 1 个在美洲运行的小型实例(有 1 个 vCPU) [] $4
2 1 个在美洲运行的小型实例(有 1 个 vCPU) [{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"dev"}] $2
3 1 个在美洲运行的小型实例(有 1 个 vCPU) [{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"dev"}] $3
4 1 个在美洲运行的小型实例(有 1 个 vCPU) [{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"prod"}] $3.25
5 1 个在亚洲运行的小型实例(有 1 个 vCPU) [{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"prod"}] $3.75
6 1 个在美洲运行的小型实例(有 1 个 vCPU) [{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"prod"}] $3.50
7 1 个在亚洲运行的小型实例(有 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.
标签 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;
environment cost
1 prod $15
2 dev $5
3 null $4
总计 $24

按键值对分组

解读或导出这些结果时要小心。结果中单独的一行显示了系统未进行重复统计时的有效总费用,但不应与其他行合并在一起(除非对应的键相同,或者您确定绝对没有对同一资源设置相应键)。

标准 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;
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 ID2DA5-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 自动生成的字段,表示数据所属的日期。您可以使用 Cloud Billing 导出作业明确生成的字段(例如 pricing_as_of_time),而不是使用 _PARTITIONTIME

以下是配置为使用 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 description 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 ACCOUNT MONTHLY 1000000 0 0 0
          1000000 2000000 0.4 0.4

获取特定 SKU 的定价,并包含服务说明

本部分中的两个示例演示了针对指定 SKU 的每个价格层级返回 list_price 的查询,并且包含 SKU 说明和服务说明。

  • 示例 1 针对每行返回一个 SKU,其价格层级显示为嵌套数据。
  • 示例 2 演示了如何将数据解除嵌套,以针对每个价格层级为每个 SKU 返回一行。

示例 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 ACCOUNT MONTHLY 1000000 0 0 0
            1000000 2000000 0.4 0.4

示例 2:返回与同一表联接的未嵌套数据

此示例查询单个 SKU 以返回 list price。SKU 有多个价格层级。该查询演示了如何使用 UNNEST 运算符来展平 tiered_rates 数组,并将各个字段与相同表联接,以便每个价格层级各占一行。

标准 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 0.4 0.4

使用商品类目和地理类目查询 SKU

  • 产品类目是适用于该 SKU 的产品类别列表,例如“无服务器”、Cloud Run 或“按需虚拟机数”
  • 地理数目是应用于 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 值。

标准 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 Cloud Tasks GCP
        无服务器
        Cloud Tasks
        其他
2 FE08-0A74-7AFD Cloud Tasks GOOGLE-API 出站流量 F3A6-D7B7-9BDA Cloud Tasks GCP
        无服务器
        Cloud Tasks
        其他
3 A81A-32A2-B46D 任务队列存储空间盐湖城 F17B-412E-CB64 App Engine GCP
        无服务器
        GAE
        其他
        TaskQueue

获取特定地理类目和商品类目的所有 SKU

此示例演示了一个查询,该查询返回与指定的 geo_taxonomy 地区和指定的 product_taxonomy 匹配的所有 SKU。其中 region = us-east4product_taxonomy = 按需虚拟机数

标准 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
            计算
            GCE
            按需虚拟机数
            内存:每 GB
2 C3B9-E891-85ED 在弗吉尼亚州运行的单租户实例 RAM 6F81-5844-456A Compute Engine REGIONAL us-east4 GCP
            计算
            GCE
            按需虚拟机数
            内存:每 GB
3 6E2A-DCD9-87ED 在弗吉尼亚运行的 N1 预定义实例 RAM 6F81-5844-456A Compute Engine REGIONAL us-east4 GCP
            计算
            GCE
            按需虚拟机数
            内存:每 GB

Google Cloud Console 中提供的费用和价格报告