Example queries for Cloud Billing data export (original) (raw)

This document 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 theoverviewand limitations.

Generate a SQL query from a Billing Report

To quickly write a SQL query that returns the equivalent results in BigQuery as the results in a Billing Report, you can use theGenerate queryfeature in theBilling Reports, available in the Google Cloud console.

Specify the table name to use in your queries

In these examples, to query your exported Cloud Billing data in BigQuery, you need to specify the full path to the table name in the FROM clause. The table name is determined using three values separated by dots:

project-ID.dataset_name.BQ_table_name

• project-ID is the ID of theGoogle Cloud project you set upthat contains your BigQuery dataset.
• dataset_name is the name of theBigQuery dataset you set upto contain the BigQuery tables with your exported Cloud Billing data.
• BQ_table_name is the name of theBigQuery tablethat contains the exported Cloud Billing data that you want to query.
  The BigQuery tables that contain the exported Cloud Billing data are automatically assigned a name, based on thetype of export you enable:
  • Standard usage cost table: In your BigQuery dataset, this table is namedgcp_billing_export_v1_<BILLING-ACCOUNT-ID>.
  • Detailed usage cost table: In your BigQuery dataset, this table is namedgcp_billing_export_resource_v1_<BILLING-ACCOUNT-ID>.
  • Pricing table: In your BigQuery dataset, this table is namedcloud_pricing_export.

For example, assume you have a Cloud Billing account that'sconfigured to export detailed usage cost data and the components of the billing export configuration are as follows:

• project-ID: my-billing-project
• dataset_name: my_billing_dataset
• BQ_table_name: gcp_billing_export_resource_v1_<BILLING-ACCOUNT-ID>
• BILLING-ACCOUNT-ID: 010101-F0FFF0-10X10X – this is the ID of the Cloud Billing account that contains the billing data that you're exporting. Learn how tofind your billing account ID.

Based on the example values, you'd write the FROM clause as follows:

my-billing-project.my_billing_dataset.gcp_billing_export_resource_v1_010101-F0FFF0-10X10X

FOCUS usage cost data

For tips and guidance about using SQL to run queries on yourFOCUS billing data export, refer to the sample FOCUS use cases that are available on the FinOps foundation site:

FOCUS v1.2 use cases | FOCUS v1.1 use cases | FOCUS v1.0 use cases

Standard usage cost, detailed usage cost, and pricing data

This section provides query examples for yourstandard usage cost data,detailed usage cost data, andpricing data.

If you opted to exportdetailed usage cost datathen you can use the Standard usage cost query examples in addition to the_Detailed usage cost_ query examples.

Standard usage cost query examples

This section provides examples of how to query theCloud Billing standard usage cost dataexported to BigQuery.

These query examples also work with thedetailed usage cost dataexported to BigQuery, although they aren't written to retrieve any of the resource-level information that's provided with the 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-ID.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 AS NUMERIC)) + SUM(IFNULL((SELECT SUM(CAST(c.amount AS NUMERIC)) FROM UNNEST(credits) AS c), 0))) AS total_exact FROM project-ID.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:

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 AS NUMERIC)) + SUM(IFNULL((SELECT SUM(CAST(c.amount AS NUMERIC)) FROM UNNEST(credits) AS c), 0))) AS total_exact FROM project-ID.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:

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 two apps (grapefruit-squeezer and chocolate-masher).
• For each app, you have two environments (dev and prod).
• The dev environment has one small instance per app.
• The prod environment has one small instance in Americas and one small instance in Asia.
• Each instance is labeled with the app and environment.
• You have one instance with no labels that you use for experimentation.

Your total bill is $24 with the following breakdown:

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-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX;

Legacy SQL

TO_JSON_STRING not supported.

Group by label map as a JSON string

This is a basic way to break down cost by each label combination.

Standard SQL

SELECT TO_JSON_STRING(labels) as labels, sum(cost) as cost FROM project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX GROUP BY labels;

Legacy SQL

TO_JSON_STRING not supported.

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 doesn't 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-ID.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-ID:dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX] WHERE labels.key = "environment" OR labels.key IS NULL GROUP BY environment;

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 shouldn't be combined with other rows (except possibly if the key is the same, or if you're 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-ID.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-ID:dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX] GROUP BY key, value;

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 withcommitted use discountsin exported billing data. To understand how your commitment fees and credits are attributed to your Cloud Billing account and projects, seeAttribution of committed use discounts.

Viewing commitment fees

To view the commitment fees for yourcommitted use discountsin your billing data export, use the following sample query.

Standard SQL

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

Viewing commitment credits

To view yourcommitted use discountcredits 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-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX LEFT JOIN UNNEST(credits) AS credits WHERE credits.type = "COMMITTED_USAGE_DISCOUNT" GROUP BY 1

Use resource hierarchy filters to review ancestry

You can use resource hierarchy filters to aggregate costs by hierarchy elements such as projects, folders, and organizations. These query examples show methods for summing costs filtered by resource hierarchy elements and displaying project ancestries.

Example 1: Filter by resource name

This example demonstrates queries that group costs by project ancestry and filter for only costs generated under a specified hierarchy element, identified by the relative resource name.

String method

SELECT invoice.month AS invoice_month, TO_JSON_STRING(project.ancestors) as ancestors, SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) AS net_cost FROM project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX as bq WHERE TO_JSON_STRING(project.ancestors) like "%resource_name":"folders/1234" GROUP BY invoice_month, ancestors ORDER BY invoice_month, ancestors

UNNEST method

SELECT invoice.month AS invoice_month, TO_JSON_STRING(project.ancestors) as ancestors, SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) AS net_cost FROM project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX as bq, UNNEST(project.ancestors) as ancestor WHERE ancestor.resource_name = "folders/1234" GROUP BY invoice_month, ancestors ORDER BY invoice_month, ancestors

Example 2: Filter by display name

This example demonstrates queries that group costs by project ancestry and filter for only costs generated under a specified hierarchy element, identified by the user-provided display name.

String matching method

SELECT invoice.month AS invoice_month, TO_JSON_STRING(project.ancestors) as ancestors, SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) AS net_cost FROM project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX as bq WHERE TO_JSON_STRING(project.ancestors) like "%display_name":"MyFolderName%" GROUP BY invoice_month, ancestors ORDER BY invoice_month, ancestors

UNNEST method

SELECT invoice.month AS invoice_month, TO_JSON_STRING(project.ancestors) as ancestors, SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) AS net_cost FROM project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX as bq, UNNEST(project.ancestors) as ancestor WHERE ancestor.display_name = "MyFolderName" GROUP BY invoice_month, ancestors ORDER BY invoice_month, ancestors

The following examples illustrate ways to query your data with tags.

Calculate costs by invoice month with tags

The following query demonstrates how you can use return costs by invoice month for the cost_center tag.

SELECT invoice.month AS invoice_month, tag.value AS cost_center, ROUND((SUM(CAST(cost AS NUMERIC)) + SUM(IFNULL((SELECT SUM (CAST(c.amount AS NUMERIC)) FROM UNNEST(credits) AS c), 0))), 2) AS net_cost FROM project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX, UNNEST(tags) AS tag WHERE tag.key = "cost_center" AND tag.namespace = "821092389413" GROUP BY invoice.month, tag.value ORDER BY invoice.month, tag.value;

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

View costs of untagged resources

This query shows the invoice total for untagged resources, grouped by invoice month.

SELECT invoice.month AS invoice_month, ROUND((SUM(CAST(cost AS NUMERIC))

• SUM(IFNULL((SELECT SUM(CAST(c.amount AS NUMERIC)) FROM UNNEST(credits) AS c), 0))), 2)

AS net_cost FROM project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX WHERE "color" NOT IN (SELECT key FROM UNNEST(tags)) GROUP BY invoice_month ORDER BY invoice_month;

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

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 returns 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-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX WHERE invoice.month = "202006" GROUP BY 1, 2 ORDER BY 1;

Query costs to view corrections or late-monetized usage for a specified invoice month

By providing a specific invoice month and filtering by date where the_usage date_ occurred before the invoice month, this simplified query returns cost totals for corrections or late-monetized usage (charges that should have been on a previous invoice).

Standard SQL

SELECT SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) AS total FROM project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX WHERE invoice.month = '202403' AND DATE(TIMESTAMP_TRUNC(usage_start_time, Day, 'US/Pacific')) < '2024-03-01';

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

Query cost details to view corrections or late-monetized usage by service for a specified invoice month

This example demonstrates a query that returns a cost breakdown by service, for_invoice_ charges, where the usage date occurred before the invoice month. This query returns cost and credit totals by service for corrections or late-monetized usage (charges that should have been on a previous invoice).

In this query example, the WHERE clause filters for all charges with an_invoice month_ of March 2024 (in the format YYYYMM), then further refines the results to return only those invoice month charges that have a _usage date_before March 1, 2024.

SELECT DATE(TIMESTAMP_TRUNC(usage_start_time, Day, 'US/Pacific')) AS Day, service.description AS Service Description, SUM(CAST(cost_at_list AS NUMERIC)) AS List cost, SUM(CAST(cost AS NUMERIC)) - SUM(CAST(cost_at_list AS NUMERIC)) AS Negotiated savings, SUM(IFNULL((SELECT SUM(CAST(c.amount AS numeric)) FROM UNNEST(credits) c WHERE c.type IN ('SUSTAINED_USAGE_DISCOUNT', 'DISCOUNT', 'SPENDING_BASED_DISCOUNT', 'COMMITTED_USAGE_DISCOUNT', 'FREE_TIER', 'COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE', 'SUBSCRIPTION_BENEFIT', 'RESELLER_MARGIN')), 0)) AS Discounts, SUM(IFNULL((SELECT SUM(CAST(c.amount AS numeric)) FROM UNNEST(credits) c WHERE c.type IN ('CREDIT_TYPE_UNSPECIFIED', 'PROMOTION')), 0)) AS Promotions and others, SUM(CAST(cost_at_list AS NUMERIC)) + SUM(IFNULL((SELECT SUM(CAST(c.amount AS numeric)) FROM UNNEST(credits) c WHERE c.type IN ('SUSTAINED_USAGE_DISCOUNT', 'DISCOUNT', 'SPENDING_BASED_DISCOUNT', 'COMMITTED_USAGE_DISCOUNT', 'FREE_TIER', 'COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE', 'SUBSCRIPTION_BENEFIT', 'RESELLER_MARGIN')), 0)) + SUM(CAST(cost AS NUMERIC)) - SUM(CAST(cost_at_list AS NUMERIC))+ SUM(IFNULL((SELECT SUM(CAST(c.amount AS numeric)) FROM UNNEST(credits) c WHERE c.type IN ('CREDIT_TYPE_UNSPECIFIED', 'PROMOTION')), 0)) AS Subtotal FROM project-ID.dataset.gcp_billing_export_v1_XXXXXX-XXXXXX-XXXXXX WHERE invoice.month = '202403' AND DATE(TIMESTAMP_TRUNC(usage_start_time, Day, 'US/Pacific')) < '2024-03-01' GROUP BY Day, service.description ORDER BY Day DESC, Subtotal DESC;

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

Detailed usage cost query examples

This section provides examples of how to query theCloud Billing detailed usage cost dataexported to BigQuery.

Because the detailed usage cost schema includes all of the fields from the_standard_ usage cost schema, the query examples provided for thestandard dataexported to BigQuery also work with the detailed data that's exported. The standard query examples aren't written to retrieve any of the resource-level information that's 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 thedetailed 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-ID.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 nullfor 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 AS NUMERIC)) + SUM(IFNULL((SELECT SUM(CAST(c.amount AS NUMERIC)) FROM UNNEST(credits) AS c), 0))) AS total_exact FROM project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX GROUP BY 1, 2 ORDER BY 1 ASC, 2 ASC ;

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

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 AS NUMERIC)) + SUM(IFNULL((SELECT SUM(CAST(c.amount AS NUMERIC)) FROM UNNEST(credits) AS c), 0))) AS total_exact FROM project-ID.dataset.gcp_billing_export_resource_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:

Get breakdown of Google Kubernetes Engine (GKE) cluster costs

This section provides examples of filtering GKE cluster costs in your BigQuery export reports. To learn more about GKE cluster costs, visitView breakdown of cluster costs.

Filter GKE costs

The following example queries show you how to filter and group your GKE costs for supported resource types by cluster name, namespace, and label.

GKE cluster costs before credits

SELECT SUM(cost) AS cost_before_credits, labels.value AS cluster_name FROM project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX LEFT JOIN UNNEST(labels) as labels ON labels.key = "goog-k8s-cluster-name" GROUP BY labels.value ;

GKE costs after credits by namespace

SELECT labels.value as namespace, SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)) AS cost_after_credits, FROM project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX LEFT JOIN UNNEST(labels) as labels ON labels.key = "k8s-namespace" GROUP BY namespace ;

GKE costs by SKU

SELECT project.id AS project_id, labels.value AS cluster_name, sku.id AS sku_id, sku.description AS sku_description, SUM(cost) AS cost FROM project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX JOIN UNNEST(labels) AS labels ON labels.key = "goog-k8s-cluster-name" GROUP BY cluster_name, project_id, sku_description, sku_id ;

Pricing data query examples

This section provides different examples of how to query theCloud Billing pricing dataexported to BigQuery.

Common values used in the example pricing queries

The query examples in this section use the following values:

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

Get list prices for a specific SKU

This example demonstrates a basic query that returns thelist_pricefor eachpricing tierfor a specified SKU.

Standard SQL

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

_PARTITIONTIME is a field auto-generated 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 aspricing_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-ID.dataset.cloud_pricing_export WHERE DATE(pricing_as_of_time) = "2020-07-20" AND sku.id = "2DA5-55D3-E679" ;

Query results

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

The two examples in this section demonstrate queries that return thelist_pricefor eachpricing tierfor 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 thelist_price data. This SKU has multiplepricing 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 project-ID.dataset.cloud_pricing_export WHERE DATE(_PARTITIONTIME) = "2020-07-20" AND sku.id = "2DA5-55D3-E679" ;

Query results:

Example 2: Returns unnested data joined with the same table

This example queries a single SKU to return thelist price. The SKU has multiplepricing tiers. The query demonstrates using theUNNESToperator toflatten the tiered_rates arrayand 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 project-ID.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:

Use product taxonomy and geo taxonomy to query SKUs

• Product taxonomyis a list of product categories that apply to the SKU, such as_Serverless_, Cloud Run, or VMs On Demand.
• Geo taxonomyis 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 theproduct_taxonomylist 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-ID.dataset.cloud_pricing_export WHERE DATE(_PARTITIONTIME) = "2020-07-20" AND sku.id = "2DA5-55D3-E679" ;

Query results:

Get all SKUs for a specific product taxonomy

This example demonstrates a query that returns all SKUs that match a specifiedproduct_taxonomy. In this query, set 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-ID.dataset.cloud_pricing_export WHERE DATE(_PARTITIONTIME) = "2020-07-20" AND "Serverless" in UNNEST(product_taxonomy) LIMIT 10 ;

Query results:

Get all SKUs for a specific geo taxonomy and product taxonomy

This example demonstrates a query that returns all SKUs that match a specifiedgeo_taxonomyregion and a specifiedproduct_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-ID.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:

Join pricing data with detailed usage cost data

This query shows how to join Price and Cost Data exports, to see detailed pricing information in line with your costs. You can configure this query to pull exported data from yourdetailed usage cost data (as Exports), and join your usage cost data with your exportedpricing data(as Prices).

Use yourdetailed usage cost table nameto pull the Exports data:gcp_billing_export_resource_v1_<BILLING_ACCOUNT_ID>

Use yourpricing table namefor the Prices data:project-ID.dataset.cloud_pricing_export

WITH Exports AS ( SELECT * FROM project-ID.dataset.gcp_billing_export_resource_v1_XXXXXX-XXXXXX-XXXXXX ), Prices AS ( SELECT * FROM project-ID.dataset.cloud_pricing_export ) SELECT Exports.sku.description AS sku_description, Exports.cost, Exports.usage, FlattenedPrices.pricing_unit_description, FlattenedPrices.account_currency_amount, FlattenedPrices.account_currency_code, FROM Exports JOIN (SELECT * FROM Prices CROSS JOIN UNNEST(Prices.list_price.tiered_rates)) AS FlattenedPrices ON Exports.sku.id = FlattenedPrices.sku.id AND Exports.price.tier_start_amount = FlattenedPrices.start_usage_amount WHERE DATE(Exports.export_time) = '2023-06-30' AND DATE(FlattenedPrices.export_time) = '2023-06-30' AND cost > 0 LIMIT 1000

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

Query using system labels

System labels for Google Cloud reservations provide enhanced visibility into your reservation usage. They allow you to track which instances are consuming which reservations, and analyze the costs associated with both used and unused capacity directly within BigQuery.

These examples use two system labels:

• compute.googleapis.com/reservation_name
• compute.googleapis.com/reservation_project_id

These labels are present on Cloud Billing records from both instances consuming a reservation and the unused portion of a reservation.

The following examples demonstrate how to query the BigQuery billing export to analyze reservation consumption using system labels.

Before you begin

To query using system labels, you must have Cloud Billing export to BigQuery enabled, specifically the Detailed usage cost dataexport. This export contains the resource-level information, including system labels, necessary to see the new reservation labels.

To set up or verify your BigQuery export, see the following documentation:

1. Overview of billing data export to BigQuery
2. Set up billing data export to BigQuery
3. Understand the Detailed data export schema

Ensure your BigQuery dataset is configured to receive thedetailed export. The table name will typically be in the formatgcp_billing_export_resource_v1_<BILLING_ACCOUNT_ID>.

View consumption for a given instance

This query lets you check if a given instance utilized a reservation during a particular period and, if so, which reservation it was.

SELECT
  t.usage_start_time,
  sl_reservation.value AS reservation_name,
  t.sku.description AS sku_description,
  SUM(t.usage.amount) AS total_usage_amount
FROM
  project-ID.dataset.gcp_billing_export_resource_v1_X AS t
  LEFT JOIN
  UNNEST(t.system_labels) AS sl_reservation
  ON sl_reservation.key = 'compute.googleapis.com/reservation_name'
WHERE
  t.service.id = '6F81-5844-456A' AND DATE(TIMESTAMP_TRUNC(t._PARTITIONTIME, DAY)) = '2025-09-16' AND t.sku.description LIKE '%Core%' AND
  t.resource.name LIKE '%tc-billing-case1-instance-1757983787%'
GROUP BY t.usage_start_time, reservation_name, t.sku.description
ORDER BY t.usage_start_time, reservation_name, t.sku.description;

Sample Result:

Note:

• SKU Filtering: Limiting the SKU to CPU cores offers clearer resource distribution. This can be adjusted to other reserved resources, such as RAM, LocalSSD, TPU, or GPU.
• On-Demand Consumption: When both reservation_name and reservation_project_id are null, the instance is consuming on-demand resources.

When multiple rows with differing reservation_name and reservation_project_id correspond to a specific instance within a given timeframe, it indicates that the instance's consumed reservation changed during that period. The usage portion will detail the consumption.

Analyze usage distribution for a given reservation

This query helps you understand the distribution of usage for a given shared reservation, showing how much capacity was used or unused among different Google Cloud projects that can consume the given reservation.

SELECT
  t.project.id AS consumer_project_id,
  t.sku.description AS sku_description,
  CASE
    WHEN t.resource.global_name NOT LIKE '%instances/%' THEN 'Unused Capacity'
    WHEN t.resource.name IS NOT NULL AND t.resource.name != '' THEN 'Instance Usage'
    ELSE 'Other Reservation Cost'
  END AS usage_category,
  SUM(t.usage.amount) AS total_usage_amount,
  t.usage.unit AS usage_unit,
  SUM(t.cost) AS total_cost_usd
FROM
  project-ID.dataset.gcp_billing_export_resource_v1_X AS t
  LEFT JOIN
  UNNEST(t.system_labels) AS sl_reservation
  ON sl_reservation.key = 'compute.googleapis.com/reservation_name'
  LEFT JOIN
  UNNEST(t.system_labels) AS sl_project_id
  ON sl_project_id.key = 'compute.googleapis.com/reservation_project_id'
WHERE
  t.service.id = '6F81-5844-456A' AND DATE(TIMESTAMP_TRUNC(t._PARTITIONTIME, DAY)) = '2025-09-16' AND t.sku.description LIKE '%Core%' AND
  sl_reservation.value LIKE '%tc-billing-case2-res-1757983801%'
GROUP BY consumer_project_id, t.sku.description, usage_category,
  usage_unit
ORDER BY consumer_project_id, usage_category;

Sample Results:

List all the instances consuming a reservation

Similarly, we can write a query to understand all the instances, including first-party (1P) instances, that have consumed a given reservation during a given time period.

SELECT
  t.usage_start_time,
  t.usage_end_time,
  t.project.id AS consumer_project_id,
  t.resource.name AS instance_name,
  t.usage.amount AS usage_amount
FROM
  project-ID.dataset.gcp_billing_export_resource_v1_X AS t
  LEFT JOIN
  UNNEST(t.system_labels) AS sl_reservation
  ON sl_reservation.key = 'compute.googleapis.com/reservation_name'
WHERE
  t.service.id = '6F81-5844-456A' AND DATE(TIMESTAMP_TRUNC(t._PARTITIONTIME, DAY)) = '2025-09-16' AND t.sku.description LIKE '%Core%' AND
  sl_reservation.value LIKE '%tc-billing-case2-res-1757983801%' AND t.resource.global_name NOT LIKE '%allocation/%' AND
  t.resource.name IS NOT NULL AND t.resource.name != ''
ORDER BY t.usage_start_time, t.project.id, t.resource.name;

Sample results:

Analyze Vertex AI reservation usage using BigQuery

To analyze how first-party (1P) products, such as Vertex AI, consume your shared reservations across projects where Vertex AI jobs are active, use the following system labels in BigQuery:

• compute.googleapis.com/reservation_name
• compute.googleapis.com/reservation_project_id

For more information about how to share reservations with first-party (1P) products in Vertex AI, see the following:

• Use reservations with training
• Use reservations with online inference
• Use reservations with Vertex AI Workbench instances

The following example queries show how to use these labels to get detailed visibility into your reservation usage, monitor how instances consume these reservations, and perform in-depth cost analysis.

List SKU types consuming a reservation for a 1P product in Vertex AI

If you create and share a reservation with Vertex AI for a training job, the following query returns the list of SKU types for the current date.

SELECT DISTINCT
  t.sku.description AS sku_description
FROM
  project-ID.dataset.gcp_billing_export_resource_v1_X AS t
LEFT JOIN
  UNNEST(t.system_labels) AS sl_reservation
  ON sl_reservation.key = 'compute.googleapis.com/reservation_name'
WHERE
  TRUE
  AND t.service.id = '6F81-5844-456A'
  AND DATE(t.usage_start_time) = CURRENT_DATE()
  AND EXISTS(
    SELECT 1
    FROM UNNEST(t.Tags) AS tag
    WHERE
      tag.key = 'gcp-product'
      AND tag.value = 'vertex_ai'
      AND tag.inherited = TRUE
  )
  AND sl_reservation.value = 'RESERVATION_NAME'
  AND t.resource.name IS NOT NULL
  AND t.resource.name != ''
ORDER BY sku_description ASC;

Sample results

The results show that the training job uses three SKU types within the Vertex AI service.

Analyze SKU statistics by reservation for a 1P product in Vertex AI

The following query helps you understand the distribution of instances and their usage costs associated with a reservation for a first-party (1P) product in the current month.

SELECT
  t.sku.description AS sku_description,
  t.usage_start_time AS usage_start_time,
  t.usage_end_time AS usage_end_time,
  SUM(t.usage.amount) AS total_usage_amount
FROM
  project-ID.dataset.gcp_billing_export_resource_v1_X AS t
LEFT JOIN
  UNNEST(t.system_labels) AS sl_reservation
  ON sl_reservation.key = 'compute.googleapis.com/reservation_name'
LEFT JOIN
  UNNEST(t.system_labels) AS sl_project_id
  ON sl_project_id.key = 'compute.googleapis.com/reservation_project_id'
WHERE
  TRUE
  AND t.service.id = '6F81-5844-456A'
  AND t.invoice.month = '202602'
  AND EXISTS(
    SELECT 1
    FROM UNNEST(t.Tags) AS tag
    WHERE
      tag.key = 'gcp-product'
      AND tag.value = 'vertex_ai'
      AND tag.inherited = TRUE
  )
  AND sl_reservation.value = 'RESERVATION_NAME'
  AND sl_project_id.value = 'PROJECT_ID'
  AND t.resource.name IS NOT NULL
  AND t.resource.name != ''
GROUP BY sku_description, usage_start_time, usage_end_time;

Sample results

The following output shows how SKUs use the reservation and provides a breakdown of the corresponding usage costs.

Analyze reservation usage for Vertex AI in shared projects

If you use a reservation shared across multiple owner projects where Vertex AI jobs are active, you can use the following query to analyze and break down your usage costs by individual project.

SELECT
  t.project.id AS consumer_project_id,
  t.sku.description AS sku_description,
  t.usage.unit AS usage_unit,
  SUM(t.usage.amount) AS total_usage_amount
FROM
  project-ID.dataset.gcp_billing_export_resource_v1_X AS t
LEFT JOIN
  UNNEST(t.system_labels) AS sl_reservation
  ON sl_reservation.key = 'compute.googleapis.com/reservation_name'
LEFT JOIN
  UNNEST(t.system_labels) AS sl_project_id
  ON sl_project_id.key = 'compute.googleapis.com/reservation_project_id'
WHERE
  TRUE
  AND t.service.id = '6F81-5844-456A'
  AND EXISTS(
    SELECT 1
    FROM UNNEST(t.Tags) AS tag
    WHERE
      tag.key = 'gcp-product'
      AND tag.value = 'vertex_ai'
      AND tag.inherited = TRUE
  )
  AND sl_reservation.value = 'RESERVATION_NAME'
  AND sl_project_id.value = 'PROJECT_ID'
GROUP BY
  consumer_project_id, sku_description, usage_unit
ORDER BY consumer_project_id, sku_description, usage_unit;

Sample results

The following output shows the contribution of each project to total usage costs while consuming the reservation.

• Set up Cloud Billing data export to BigQuery
• Understanding the Cloud Billing data tables in BigQuery
• View your Cloud Billing reports and cost trends
• View and download the cost details of your invoice or statement
• View and download prices for Google's cloud services
• Understand your savings with cost breakdown reports
• Analyze the effectiveness of your committed use discounts
• View your cost and payment history