This page provides reference information for the schema of Cloud Billing detailed usage cost data that is exported to each table in BigQuery.
Schema of the detailed usage cost data
The detailed usage cost data provides all of the information included in the standard usage cost data, along with additional fields that provide resource-level cost data, like a virtual machine or SSD that generates service usage. The detailed export automatically includes resource-level information about Compute Engine. To view a breakdown of Google Kubernetes Engine (GKE) cluster costs in a detailed data export, you must also enable cost allocation for GKE.
In your BigQuery dataset, your detailed Google Cloud
usage cost data is loaded into a data table named
gcp_billing_export_resource_v1_<BILLING_ACCOUNT_ID>.
When you use the detailed usage cost data in BigQuery, note the following:
- When selecting or creating a BigQuery dataset for your detailed usage cost data, you must use a multi-region dataset located in the United States (US) or the European Union (EU).
- When you enable the detailed usage cost data export for the
first time, Cloud Billing data is available from the start of the previous
month.
- If you have enabled, disabled, and subsequently re-enabled the detailed usage cost data export, the Cloud Billing data might not be available for the period it was explicitly disabled.
- See other limitations that might impact exporting your billing data to BigQuery, such as datasets with customer-managed encryption keys (CMEK) enabled.
- Consider the additional data volume that your BigQuery tables might need and the additional cost when enabling detailed usage cost data instead of the standard usage cost data export. The increased granularity of resource-level information can increase the number of rows, which are aggregated in the standard usage cost format. We recommend that you review Control costs in BigQuery for further best practices in managing your BigQuery costs.
| Field | Type | Description |
|---|---|---|
billing_account_id |
String | The Cloud Billing account ID that the usage is associated with. For resellers: For usage costs generated by a Cloud Billing subaccount, this is the ID of the subaccount, not the ID of the parent reseller Cloud Billing account. |
invoice.month |
String | The year and month (YYYYMM) of the invoice that includes the cost line items. For example: "201901" is equivalent to January, 2019. You can use this field to get the total charges on the invoice. See Cloud Billing Export to BigQuery Query Examples. |
cost_type |
String | The type of cost this line item represents: regular, tax, adjustment, or rounding error. |
service.id |
String | The ID of the service that the usage is associated with. |
service.description |
String | The Google Cloud service that reported the Cloud Billing data. |
sku.id |
String | The ID of the resource used by the service. For the full list of SKUs, see Google Cloud SKUs. |
sku.description |
String | A description of the resource type used by the service. For example, a resource type for Cloud Storage is Standard Storage US. |
usage_start_time |
Timestamp | The start time of the hourly usage window within which the given cost was
calculated. The usage/costs for all services is displayed with hourly
granularity, which means long running service usage is spread across multiple
hourly windows.
For more information, see the BigQuery documentation on timestamp data types. See also, Differences between exported data and invoices below. |
usage_end_time |
Timestamp | The end time of the hourly usage window within which the given cost was
calculated. The usage/costs for all services is displayed with hourly
granularity, which means long running service usage is spread across multiple
hourly windows.
For more information, see the BigQuery documentation on timestamp data types. See also, Differences between exported data and invoices below. |
project |
Struct | project contains fields that describe the
Cloud Billing project, such as ID, number, name, ancestry_numbers,
and labels.
|
project.id |
String | The ID of the Google Cloud project that generated the Cloud Billing data. |
project.number |
String | An internally-generated, anonymized, unique identifier for the Google Cloud project that generated the Cloud Billing data. In your support cases and other customer communication, Google will refer to your projects by this project number. |
project.name |
String | The name of the Google Cloud project that generated the Cloud Billing data. |
project.ancestry_numbers |
String | The ancestors in the resource hierarchy for the project identified by the
specified project.id (for example, my-project-123).
For example: /ParentOrgNumber/ParentFolderNumber/. Learn more about the Resource Hierarchy. |
project.ancestors | Struct |
This field describes the structure and value of the resource hierarchy of a cost line item, including projects, folders, and organizations. Ancestors are ordered from node to root (project, folder, then organization). |
project.ancestors.resource_name | String | The
relative resource name for each ancestor in the format 'resourceType/resourceNumber'. Using project.ancestors.resource_name will offer a more complete view of
project.ancestry_numbers. |
project.ancestors.display_name | String | The name that you have created for your resource in your console. |
project.labels.key | String | If labels are present, the key portion of the key:value pair that comprises the label on the Google Cloud project where the usage occurred. For more information about using labels, see Using Labels. |
project.labels.value |
String | If labels are present, the value portion of the key:value pair that comprises the label on the Google Cloud project where the usage occurred. For more information about using labels, see Using Labels. |
labels.key |
String | If labels are present, the key portion of the key:value pair that comprises the label on the Google Cloud resource where the usage occurred. For more information about using labels, see Using Labels. |
labels.value |
String | If labels are present, the value portion of the key:value pair that comprises the label on the Google Cloud resource where the usage occurred. For more information about using labels, see Using Labels. |
system_labels.key |
String | If system labels are present, the key portion of the key:value pair that comprises the system-generated label on the resource where the usage occurred. See also, Available system labels. |
system_labels.value |
String | If system labels are present, the value portion of the key:value pair that comprises the system-generated label on the resource where the usage occurred. See also, Available system labels. |
location.location |
String | Location of usage at the level of a multi-region, country, region, or zone;
or global for
resources that have no specific location.
For more information, see
Geography and regions and
Google Cloud locations.
|
location.country |
String | When location.location is a country, region, or zone, this
field is the country of usage, e.g. US.
For more information, see
Geography and regions and
Google Cloud locations.
|
location.region |
String | When location.location is a region or zone, this field is
the region of usage, e.g. us-central1.
For more information, see
Geography and regions and
Google Cloud locations.
|
location.zone |
String | When location.location is a zone, this field is the zone of
usage, e.g. us-central1-a.
For more information, see
Geography and regions and
Google Cloud locations.
|
cost |
Float | The cost of the usage before any credits, to a precision of up to six
decimal places. To get the total cost including
credits, any credits.amount should be added to cost.
See
this example query for more information. |
currency |
String | The currency that the cost is billed in. For more information, see Local Currency for Billing and Payments. |
currency_conversion_rate |
Float | The exchange rate from US dollars to the local currency. That is,
cost ÷ currency_conversion_rate
is the cost in US dollars. |
usage.amount |
Float | The quantity of usage.unit used. |
usage.unit |
String | The base unit in which resource usage is measured. For example, the base unit for standard storage is byte-seconds. |
usage.amount_in_pricing_units
|
Float | The quantity of usage.pricing_unit used.
|
usage.pricing_unit
|
String | The unit in which resource usage is measured, according to the Cloud Billing Catalog API. |
credits |
Struct | credits contains fields that describe the structure and
value of the credits associated with Google Cloud and Google Maps
Platform SKUs. |
credits.id |
String | If present, indicates that a credit is associated with the product SKU.
credits.id values are either an alphanumeric unique identifier
(for example, 12-b34-c56-d78), or a description of the credit type
(such as
Committed Usage Discount: CPU).
If the |
credits.full_name |
String | The name of the credit associated with the product SKU. This is a
human-readable description of an alphanumeric credits.id.
Examples include
Free trial credit or
Spend-based committed use discount.
|
credits.type |
String | This field describes the purpose or origin of the credits.id.
Credit types include:
|
credits.name |
String | A description of the credit applied to the Cloud Billing account. |
credits.amount |
Float | The amount of the credit applied to the usage. Credits are always negative amounts. |
adjustment_info |
Struct | adjustment_info contains fields that describe the structure and
value of an adjustment to cost line items associated with a
Cloud Billing account.
|
adjustment_info.id |
String | If present, indicates that an adjustment is associated with a cost line
item. adjustment_info.id is the unique ID for all
the adjustments associated caused by an issue.
|
adjustment_info.description |
String | A description of the adjustment and its cause. |
adjustment_info.type |
String | The type of adjustment. Types include:
|
adjustment_info.mode |
String | How the adjustment was issued. Modes include:
|
export_time |
Timestamp | A processing time associated with an append of Cloud Billing data.
This will always increase with each new export. See also, Differences between exported data and invoices below. |
| Additional fields available to detailed usage cost data export | ||
resource |
Struct | The fields that describe the structure and value of information relevant to service resources (like a virtual machine or a SSD) that generate service usage. |
resource.global_name |
String | A globally unique service identifier for the resource that generated relevant usage. |
resource.name |
String | A service-specific identifier for the resource that generated relevant usage. This can be input generated by the user. |
Understand standard and detailed usage cost data
The following sections describe the standard and detailed usage cost data exported to BigQuery.
About labels
You'll see columns for labels in your BigQuery dataset, but for the current release some label values will be empty. Label export data will be populated at different times for different services, depending on when each service provides it.
Available system labels
System labels are key-value pairs for important metadata about the resource that generated the usage. The following system labels are automatically included on applicable usage.
system_labels.key |
Example system_labels.value |
Description |
|---|---|---|
compute.googleapis.com/machine_spec |
n1-standard-1, custom-2-2048 | Configuration of the virtual machine. See Machine Types for more information. |
compute.googleapis.com/cores |
for n1-standard-4 this is 4; for custom-2-2048 this is 2 | The number of vCPUs available to the virtual machine. |
compute.googleapis.com/memory |
for n1-standard-4 this is 15360 (i.e. 15 GB * 1024 MB/GB); for custom-2-2048 this is 2048 | The amount of memory (in MB) available to the virtual machine. |
compute.googleapis.com/is_unused_reservation |
true; false | Indicates usage that was reserved through Zonal Reservations but not used. |
Differences between exported data and invoices
Because Google Cloud services report usage and cost data to Cloud Billing processes at varying intervals, sometimes there is a slight delay (up to a few days) in usage reporting. At the end of a calendar month, late-reported usage might not be included on that month's invoice and instead might roll over to the next month's invoice. However, that same usage includes a timestamp by actual usage date and time when the usage is reported to Cloud Billing processes.
When you query your costs using timestamp fields, your returned data might pick up late-reported usage that was not originally included on the invoice that was generated for the same usage month. As a result, the Cloud Billing data returned might not map directly to that invoice.
Timestamp fields include:
usage_start_timeusage_end_timeexport_time
To return Cloud Billing data that maps directly to an invoice, query on
invoice.month
instead of timestamp fields.
Taxes
Starting on September 1, 2020, your usage cost data shows your tax liability for each of your projects, instead of as a single line item. If you have queries or visualizations that depend on tax data, you might need to update the queries to account for these changes.
For example, for costs recorded before September 1, your usage cost data looked similar to the following example, which shows a total tax liability of $10.
billing_account_id |
project.id |
cost_type |
cost |
|---|---|---|---|
| 123456-ABCDEF-123456 | example-project | Regular | $60 |
| 123456-ABCDEF-123456 | test-project | Regular | $40 |
| 123456-ABCDEF-123456 | [empty] | Tax | $10 |
For costs recorded after September 1, the $10 is broken down to $6 for
example-project, and $4 for test-project:
billing_account_id |
project.id |
cost_type |
cost |
|---|---|---|---|
| 123456-ABCDEF-123456 | example-project | Regular | $60 |
| 123456-ABCDEF-123456 | test-project | Regular | $40 |
| 123456-ABCDEF-123456 | example-project | Tax | $6 |
| 123456-ABCDEF-123456 | test-project | Tax | $4 |
Errors and adjustments
You should be aware that in the unlikely event that your Cloud Billing data contains an error or requires an adjustment, your Cloud Billing data will be appended with data that negates the error. All columns will remain the same, except for:
costcreditusage.amountexport_time
If you need to determine which data is new, you can do so by
querying export_time.
In your exported data, the invoice month for Cloud Billing adjustments and associated taxes reflects the month the adjustment was issued. However, the adjustment could be applied to an invoice in a different month than the issue month of the adjustment. To learn if and where an adjustment is applied, you'll need to review the invoice or credit/debit memo documents available in the documents page of the Google Cloud console. For more details on analyzing adjustments and how they are applied, see Understand memos and adjustments.
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.
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
totalfield directly sums the floating point cost and credit values, which can result in floating point rounding errors. - The
total_exactfield 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_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:
| 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_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:
| 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 |
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, visit View 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.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.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.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 ;
Related topics
Topics related to exported Cloud Billing data
- Set up Cloud Billing data export to BigQuery
- Example queries for Cloud Billing data export to BigQuery
- Visualize spend over time with Data Studio
Cost and pricing reports available in the Google Cloud console
- 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