Example queries for Cloud Billing data export

This page provides some examples of how to query the Cloud Billing data exported to and stored in BigQuery.

For more information about exporting your billing data to BigQuery, see the overview and limitations.

Specifying the table name to use in your queries

In these examples, to query the Cloud Billing data in BigQuery, you need to specify the table name in the FROM clause. The table name is determined using three values: project.dataset.BQ_table_name.

  • project is the ID of the Cloud project you set up that contains your BigQuery dataset.
  • dataset is the name of the BigQuery dataset you set up to contain the BigQuery tables with your exported Cloud Billing data.
  • BQ_table_name is the name of the BigQuery table that contains the exported Cloud Billing data that you want to query. There are three BigQuery tables that contain Cloud Billing data:
    • Standard usage cost table: In your BigQuery dataset, this table is named gcp_billing_export_v1_<BILLING_ACCOUNT_ID>.
    • Detailed usage cost table: In your BigQuery dataset, this table is named gcp_billing_export_resource_v1_<BILLING_ACCOUNT_ID>.
    • Pricing table: In your BigQuery dataset, this table is named cloud_pricing_export.

Example queries by Cloud Billing data type

This page provides query examples for your standard usage cost data, detailed usage cost data, and pricing data.

Standard usage cost data Detailed usage cost data Pricing data
Standard usage cost query examples Detailed usage cost query examples Pricing data query examples

Standard usage cost query examples

This section provides examples of how to query the Cloud Billing standard usage cost data exported to BigQuery.

These query examples also work with the detailed usage cost data exported to BigQuery, although they are not written to retrieve any of the resource-level information that is provided with that detailed usage cost export option.

Common values used in the example standard cost queries

The query examples in this section use the following value for Table name: project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX

Return the total costs on an invoice

The following queries demonstrate two ways of viewing cost and credit values using exported billing data.

  • The total field directly sums the floating point cost and credit values, which can result in floating point rounding errors.
  • The total_exact field converts costs and credit values to micros before summing, then converts back to dollars after summing, avoiding the floating point rounding error.

Example 1: Sum of all costs, per invoice

This query shows the invoice total for each month, as a sum of regular costs, taxes, adjustments, and rounding errors.

Standard 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
;

For example, the result of the preceding query might be:

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

Example 2: Return details by cost type, per invoice month

This query shows the totals for each cost_type for each month. Cost types include regular costs, taxes, adjustments, and rounding errors.

Standard 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
;

For example, the result of the preceding query might be:

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

Query examples with labels

The following examples illustrate other ways to query your data with labels.

For the examples in this section, assume the following:

  • You have 2 apps (grapefruit-squeezer and chocolate-masher).
  • For each app, you have 2 environments (dev and prod).
  • The dev environment has 1 small instance per app.
  • The prod environment has 1 small instance in Americas and 1 small instance in Asia.
  • Each instance is labeled with the app and environment.
  • You have 1 instance with no labels that you use for experimentation.

Your total bill is $24 with the following breakdown:

Instance Labels Total Cost
Small instance with 1 VCPU running in Americas None $4
Small instance with 1 VCPU running in Americas app: chocolate-masher
environment: dev
$2
Small instance with 1 VCPU running in Americas app: grapefruit-squeezer
environment: dev
$3
Small instance with 1 VCPU running in Americas app: chocolate-masher
environment: prod
$3.25
Small instance with 1 VCPU running in Asia app: chocolate-masher
environment: prod
$3.75
Small instance with 1 VCPU running in Americas app: grapefruit-squeezer
environment: prod
$3.50
Small instance with 1 VCPU running in Asia app: grapefruit-squeezer
environment: prod
$4.50

Query every row without grouping

The most granular view of these costs would be to query every row without grouping. Assume all fields, except labels and sku description, are the same (project, service, and so on).

Standard SQL

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

Legacy SQL

TO_JSON_STRING not supported.
Row sku.description labels cost
1 Small instance with 1 VCPU running in Americas [] $4
2 Small instance with 1 VCPU running in Americas [{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"dev"}] $2
3 Small instance with 1 VCPU running in Americas [{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"dev"}] $3
4 Small instance with 1 VCPU running in Americas [{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"prod"}] $3.25
5 Small instance with 1 VCPU running in Asia [{"key":"app","value":"chocolate-masher"},{"key":"environment","value":"prod"}] $3.75
6 Small instance with 1 VCPU running in Americas [{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"prod"}] $3.50
7 Small instance with 1 VCPU running in Asia [{"key":"app","value":"grapefruit-squeezer"},{"key":"environment","value":"prod"}] $4.50
TOTAL $24

Group by label map as a JSON string

This is a quick and easy way to break down cost by each label combination.

Standard 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;

Legacy 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
TOTAL $24

Group by label value for a specific key

Breaking down costs for values of a specific label key is a common use case. By using a LEFT JOIN and putting the key filter in the JOIN condition (rather than WHERE), you include cost that does not contain this key, and so receive a complete view of your cost.

Standard 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;

Legacy 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
TOTAL $24

Group by key/value pairs

Be careful when interpreting or exporting these results. An individual row here shows a valid sum without any double counting, but should not be combined with other rows (except possibly if the key is the same, or if you are certain the keys are never set on the same resource).

Standard 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;

Legacy 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
TOTAL $44

Note that the total sum is greater than your bill.

Committed use discount queries

The following queries demonstrate ways of viewing the fees and credits associated with committed use discounts in exported billing data. To understand how your commitment fees and credits are attributed to your Cloud Billing account and projects, see Attribution of committed use discounts.

Viewing commitment fees

To view the commitment fees for your committed use discounts in your billing data export, use the following sample query.

Standard SQL

SELECT
    invoice.month AS invoice_month,
    SUM(cost) as commitment_fees
FROM `project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
WHERE LOWER(sku.description) LIKE "commitment%"
GROUP BY 1

Viewing commitment credits

To view your committed use discount credits in your billing data export, use the following sample query.

Standard SQL

SELECT
    invoice.month AS invoice_month,
    SUM(credits.amount) as CUD_credits
FROM `project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX`
LEFT JOIN UNNEST(credits) AS credits
WHERE LOWER(credits.name) LIKE "committed use discount%"
GROUP BY 1

Additional query examples

Query costs and credits by project for a specified invoice month

By providing a specific invoice month of June 2020 (in the format YYYYMM), this query will return a view of the costs and credits grouped by project along with showing project labels.

Standard SQL

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

Legacy SQL

TO_JSON_STRING not supported.
Row name 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

Detailed usage cost query examples

This section provides examples of how to query the Cloud Billing detailed usage cost data exported to BigQuery.

Because the detailed usage cost schema includes all of the fields from the standard usage cost schema, the query examples provided for the standard data exported to BigQuery also work with the detailed data that is exported. Note that the standard query examples are not written to retrieve any of the resource-level information that is provided with the detailed usage cost export option. When creating queries for the detailed data, you can use a standard query example as a template, update the Table name, and add any of the fields that are available in the detailed usage cost schema.

For further requirements and limitations to the detailed data exported, see Schema of the detailed usage cost data.

Common values used in the example detailed cost queries

The query examples in this section use the following value for Table name: project.dataset.gcp_billing_export_resource_v1_XXXXXX_XXXXXX_XXXXXX

Return the resource-level costs on an invoice

The following queries demonstrate two ways of viewing resource-level cost and credit values on an invoice using exported billing data.

  • The total field directly sums the floating point cost and credit values, which can result in floating point rounding errors.
  • The total_exact field converts costs and credit values to micros before summing, then converts back to dollars after summing, avoiding the floating point rounding error.

Sum costs for each resource, per invoice

This query shows the invoice total for each resource.name per month, as a sum of regular costs, taxes, adjustments, and rounding errors. Any costs not associated with a resource-level item are aggregated under the name null for the month.

Standard SQL

SELECT
  invoice.month,
  resource.name,
  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
;

For example, the result of the preceding query might be:

Row month name total total_exact
1 201901 null $1005.004832999999984 $1005.00
2 201901 backend1 $781.8499760000028 $781.85
3 201902 null $953.0034923645475983 $953.03
4 201902 backend1 $992.3101739999999717 $992.31
5 201902 bitnami-launchpad-wordpress-1-wordpress $1.2817819999999998 $1.28

Return details by cost type for each resource, per invoice month

This query shows the totals for each cost_type for each resource.name per month. Cost types include regular costs, taxes, adjustments, and rounding errors. Any costs not associated with a resource-level item are aggregated under the name null for the month.

Standard SQL

SELECT
  invoice.month,
  cost_type,
  resource.name,
  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, 3
ORDER BY 1 ASC, 2 ASC, 3 ASC
;

For example, the result of the preceding query might be:

Row month cost_type name total total_exact
1 201901 regular null $1000.501209987994782 $1000.50
2 201901 rounding_error null –$0.500489920049387 –$0.50
3 201901 tax null $10.000329958477891 $10.00
4 201901 adjustment null –$5.002572999387045 –$5.00
5 201901 regular backend1 $410.998795012082947 $411.00
2 201901 rounding_error backend1 –$0.2404900489920378 –$0.24
3 201901 tax backend1 $4.105840329977189 $4.11

Pricing data query examples

This section provides different examples of how to query the Cloud Billing pricing data exported to BigQuery.

Common values used in the example pricing queries

The query examples in this section use the following values:

  • Table name: project.dataset.cloud_pricing_export
  • SKU ID: 2DA5-55D3-E679 (Cloud Run - Requests)

Get list prices for a specific SKU

This example demonstrates a simple query that returns the list_price for each pricing tier for a specified SKU.

Standard 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 is a field autogenerated by BigQuery and represents the date that the data belongs to. Instead of _PARTITIONTIME, you can use a field that Cloud Billing export explicitly generates, such as pricing_as_of_time.

Here's the same query configured to use the pricing_as_of_time field:

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"
;

Query results

Row 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 Requests COUNT ACCOUNT MONTHLY 1000000 0 0 0
          1000000 2000000 0.4 0.4

Get list prices for a specific SKU, and include service description

The two examples in this section demonstrate queries that return the list_price for each pricing tier for a specified SKU, and includes the SKU description and the service description.

  • Example 1 returns one SKU per row, with the pricing tiers displayed as nested data.
  • Example 2 demonstrates unnesting the data to return one row per SKU per pricing tier.

Example 1: Returns nested data

This example queries a single SKU to return the list_price data. This SKU has multiple pricing tiers. The list price field values display in individual rows that are nested under the SKU ID row.

Standard 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"
;

Query results:

Row 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 Requests 152E-C115-5142 Cloud Run ACCOUNT MONTHLY 1000000 0 0 0
            1000000 2000000 0.4 0.4

Example 2: Returns unnested data joined with the same table

This example queries a single SKU to return the list price. The SKU has multiple pricing tiers. The query demonstrates using the UNNEST operator to flatten the tiered_rates array and join the fields with the same table, resulting in one row per pricing tier.

Standard 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` as sku_pricing, UNNEST (sku_pricing.list_price.tiered_rates) as tier
WHERE DATE(_PARTITIONTIME) = "2020-07-20"
      AND sku.id = "2DA5-55D3-E679"
;

Query results:

Row sku_id sku_description service_id service_description pricing_unit_quantity start_usage_amount usd_amount account_currency_amount
1 2DA5-55D3-E679 Requests 152E-C115-5142 Cloud Run 1000000.0 0.0 0.0 0.0
2 2DA5-55D3-E679 Requests 152E-C115-5142 Cloud Run 1000000.0 2000000.0 0.4 0.4

Use product taxonomy and geo taxonomy to query SKUs

  • Product taxonomy is a list of product categories that apply to the SKU, such as Serverless, Cloud Run, or VMs On Demand.
  • Geo taxonomy is the geographic metadata that applies to a SKU, consisting of type and region values.

Get the product taxonomy of a SKU

This example demonstrates a query that returns the product_taxonomy list for a specified SKU, where the SKU ID = 2DA5-55D3-E679 (Cloud Run - Requests).

Standard 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"
;

Query results:

Row sku_id sku_description service_id service_description product_taxonomy
1 2DA5-55D3-E679 Requests 152E-C115-5142 Cloud Run GCP
        Serverless
        Cloud Run
        Other

Get all SKUs for a specific product taxonomy

This example demonstrates a query that returns all SKUs that match a specified product_taxonomy. In this query, we are specifying Serverless as the product taxonomy value.

Standard 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
;

Query results:

Row sku_id sku_description service_id service_description product_taxonomy
1 0160-BD7B-4C40 Cloud Tasks Network Intra Region Egress F3A6-D7B7-9BDA Cloud Tasks GCP
        Serverless
        Cloud Tasks
        Other
2 FE08-0A74-7AFD Cloud Tasks GOOGLE-API Egress F3A6-D7B7-9BDA Cloud Tasks GCP
        Serverless
        Cloud Tasks
        Other
3 A81A-32A2-B46D Task Queue Storage Salt Lake City F17B-412E-CB64 App Engine GCP
        Serverless
        GAE
        Other
        TaskQueue

...

Get all SKUs for a specific geo taxonomy and product taxonomy

This example demonstrates a query that returns all SKUs that match a specified geo_taxonomy region and a specified product_taxonomy, where region = us-east4 and product_taxonomy = VMs On Demand.

Standard 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)
;

Query results:

Row sku_id sku_description service_id service_description geo_taxonomy.type geo_taxonomy.regions product_taxonomy
1 9174-81EE-425B Sole Tenancy Premium for Sole Tenancy Instance Ram running in Virginia 6F81-5844-456A Compute Engine REGIONAL us-east4 GCP
            Compute
            GCE
            VMs On Demand
            Memory: Per GB
2 C3B9-E891-85ED Sole Tenancy Instance Ram running in Virginia 6F81-5844-456A Compute Engine REGIONAL us-east4 GCP
            Compute
            GCE
            VMs On Demand
            Memory: Per GB
3 6E2A-DCD9-87ED N1 Predefined Instance Ram running in Virginia 6F81-5844-456A Compute Engine REGIONAL us-east4 GCP
            Compute
            GCE
            VMs On Demand
            Memory: Per GB

...

Cost and pricing reports available in the Google Cloud Console