Billing data export
Google Cloud allows using BigQuery to store and query Cloud Billing data. Exporting billing data to BigQuery is convenient if you need to conduct detailed analysis or use other tools for data visualization.
Billing data
As a Google Cloud customer, you have access to your own billing export table and can follow Google Cloud's instructions to Set up Cloud Billing data export to BigQuery.
However, if you need an accurate export that includes changes applied to your billing data by DoiT (for example, DoiT discounts or custom pricing), you'll need the billing data from the DoiT Platform.
There are two ways to access your Google Cloud billing data in the DoiT Platform:
Using DoiT Reports API.
Opening a support ticket to request access to the DoiT billing export table.
NoteWhen requesting access to the DoiT billing export table, make sure to provide the account that you'll use for querying. It could be a Google user account, a Google group account, or a service account. The account needs a Billing Account Administrator role for the target Cloud Billing account.
This page explains how to query the DoiT billing export table.
DoiT billing export table
Data availability
Once you provided the necessary information for querying the DoiT billing export table, we'll create a VIEW in Google BigQuery for the target Cloud Billing account and share it with you (if you have multiple billing accounts, we'll create multiple VIEWs, one per billing account).
Your billing export table contains Cloud Billing data incurred from the date you joined the DoiT International consolidated billing.
BigQuery load jobs are ACID-compliant, which means, if you query the BigQuery Cloud Billing export table while data is loading, you will not encounter partially loaded data.
Project, dataset, and table name
To query the Google Cloud Billing data in BigQuery, you need to specify the table name in the FROM clause. The table name consists of three parts: project
.dataset
.BQ_table_name
.
project
: The Cloud project that contains your BigQuery dataset. It is alwaysdoitintl-cmp-gcp-data
.dataset
: The name of the BigQuery dataset that contains the BigQuery tables with your exported Cloud Billing data. It isgcp_billing_
concatenated with the name of your Google Billing account (dashes in the billing account ID being replaced by underscores). For example, if your Google Billing Account ID is006C3F-3613C3-2A2169
, the dataset name should begcp_billing_006C3F_3613C3_2A2169
.BQ_table_name
: The name of the BigQuery table that contains the exported Cloud Billing data. It isdoitintl_billing_export_resource_v1_XXXXXX_XXXXXX_XXXXXX_view
, whereXXXXXX_XXXXXX_XXXXXX
is your billing account ID with dashes being replaced by underscores.
VIEW schema
The structure of the the DoiT billing export table is different from that of a regular GCP billing export table.
Below is the schema of the DoiT billing export table.
Most fields in the DoiT billing export table are the same as those described at Google Cloud Docs: Structure of Detailed data export. DoiT-specific fields are indicated in the field descriptions.
Field | Type | Description |
---|---|---|
billing_account_id | string | The Cloud Billing account ID that the usage is associated with. |
project_id | string | The ID of the Google Cloud project that generated the Cloud Billing data. The same as project.id . |
service_description | string | The Google Cloud service that reported the Cloud Billing data. |
service_id | string | The ID of the service that the usage is associated with. |
sku_description | string | A description of the resource type used by the service. |
sku_id | string | The ID of the resource used by the service. For the full list of SKUs, see Google Cloud SKUs. |
usage_start_time | timestamp | The start time of the hourly usage window within which the given cost was calculated. |
usage_end_time | timestamp | The end time of the hourly usage window within which the given cost was calculated. |
project | record | project contains fields that describe the Cloud Billing project. |
project.id | string | The ID of the Google Cloud project that generated the Cloud Billing data. |
project.number | string | A unique identifier for the Google Cloud project that generated the Cloud Billing data. |
project.name | string | The name of the Google Cloud project that generated the Cloud Billing data. |
project.labels | record (repeated) | project.labels contains key:value pairs that comprise the labels on the Google Cloud project where the usage occurred. |
project.labels.key | string | If project labels are present, the key portion of the key:value pair. |
project.labels.value | string | If project labels are present, the value portion of the key:value pair. |
project.ancestry_numbers | string | The ancestors in the resource hierarchy for the project identified by the specified project.id . |
project.ancestry_names | string | (DoiT specific) The same as project.ancestry_numbers but in the folder name instead of the folder number. |
labels | record (repeated) | labels contains key:value pairs that comprise the labels on the Google Cloud resource where the usage occurred. |
labels.key | string | If labels are present, the key portion of the key:value pair. |
labels.value | string | If labels are present, the value portion of the key:value pair. |
system_labels | record (repeated) | system_labels contains key:value pairs that comprise the system-generated labels on the resource where the usage occurred. |
system_labels.key | string | If system labels are present, the key portion of the key:value pair. |
system_labels.value | string | If system labels are present, the value portion of the key:value pair . |
location | record | location contains geographic information of usage. |
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. |
location.country | string | The country of usage. |
location.region | string | The region of usage. |
location.zone | string | The zone of usage. |
export_time | timestamp | A processing time associated with an append of Cloud Billing data. |
cost | float | The cost of the usage before any credits, to a precision of up to six decimal places. |
currency | string | The currency that the cost is billed in. |
currency_conversion_rate | float | The exchange rate from US dollars to the local currency. |
usage | record | usage contains fields that describe the quantity of usage units and usage pricing units. |
usage.amount | float | The quantity of usage.unit used. |
usage.unit | string | The base unit in which resource usage is measured. |
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 Google Cloud Billing Catalog API. |
credits | record (repeated) | credits contains fields that describe the structure and value of the credits associated with Google Cloud and Google Maps Platform SKUs. |
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. |
credits.full_name | string | The name of the credit associated with the product SKU. A human-readable description of an alphanumeric credits.id . |
credits.id | string | If present, indicates that a credit is associated with the product SKU. |
credits.type | string | This field describes the purpose or origin of the credits.id . See Google Cloud Docs for details. |
invoice | record | invoice contains fields that describe the general information about the invoice. |
invoice.month | string | The year and month (YYYYMM) of the invoice that includes the cost line items. |
cost_type | string | The type of cost this line item represents: regular, tax, adjustment, or rounding error. |
adjustment_info | record | 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.description | string | A description of the adjustment and its cause. |
adjustment_info.mode | string | How the adjustment was issued. See Google Cloud Docs for details. |
adjustment_info.type | string | The type of adjustment. See Google Cloud Docs for details. |
tags | record (repeated) | (DoiT specific) tags contains fields that describe the resource structure of tags attached to the Google Cloud resource where the usage occurred. See Google Cloud Docs for more information about GCP tags. |
tags.key | string | (DoiT specific) The key portion of the tag key:value pair. |
tags.value | string | (DoiT specific) The value portion of the tag key:value pair. |
tags.inherited | boolean | (DoiT specific) This field indicates whether the tag bindings are inherited from the parent resource in the Google Cloud resource hierarchy. |
resource_id | string | (DoiT specific) A service-specific identifier for the resource that generated relevant usage. This is the resource.name field in Google's detailed usage cost data export. |
resource_global_id | string | (DoiT specific) A globally unique service identifier for the resource that generated relevant usage. This is the resource.global_name field in Google's detailed usage cost data export. |
report | record (repeated) | (DoiT specific) report contains fields that describe changes applied to your billing data by DoiT. |
report.cost | float | (DoiT specific) The cost of the usage after applying DoiT discounts and/or custom pricing. |
report.usage | float | (DoiT specific) The same as usage.amount_in_pricing_units . |
report.credit | string | (DoiT specific) The same as credits.name . The amount of the credit is included in report.cost . |
Table configuration
Review the table partitioning and clustering schema to allow query optimization.
Item | Setting |
---|---|
Table type | Partitioned |
Partitioned by | Day |
Partitioned on field | export_time |
Partition filter | Required |
Clustered by | project_id , service_description , sku_description |
Sample queries
This section provides examples of how to query the DoiT billing export table.
Google Cloud Docs: Understand the Cloud Billing data tables in BigQuery
Google Cloud Docs: Query partitioned tables
Similar to Google Cloud Billing Reports, DoiT billing export table uses the Pacific Time Zone for time aggregation and observes daylight saving time shifts. The sample queries below use the IANA time zone identifier America/Los_Angeles
.
Return daily cost breakdown by project, service, and SKU
The query below returns the cost (including credits) from the last month to date, with daily breakdown by project, service, and SKU.
DECLARE from_date DATE DEFAULT DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH);
SELECT
DATE(usage_start_time, "America/Los_Angeles") AS usage_date,
project_id,
service_description,
sku_description,
SUM(report_val.cost) AS cost
FROM
`doitintl-cmp-gcp-data.gcp_billing_XXXXXX_XXXXXX_XXXXXX.doitintl_billing_export_resource_v1_XXXXXX_XXXXXX_XXXXXX_view`
LEFT JOIN
UNNEST(report) AS report_val
WHERE
DATE(export_time) >= from_date
AND DATE(usage_start_time, "America/Los_Angeles") >= from_date
GROUP BY
usage_date, project_id, service_description, sku_description
Query with labels
The query below shows how to filter costs using labels. It returns cost breakdown for resources with the environment
label of the previous month.
The query reads all data that was exported between 2022-07-01
and 2022-08-10
to ensure all the usage data that belongs to July is included. For more information, see Google Cloud Docs: Differences between exported data and invoices.
WITH rows_with_label AS (
SELECT
l.value AS label_value,
report
FROM
`doitintl-cmp-gcp-data.gcp_billing_XXX.doitintl_billing_export_resource_v1_XXX_view`
LEFT JOIN
UNNEST(labels) AS l
WHERE
DATE(export_time) BETWEEN "2022-07-01" AND "2022-08-10"
AND DATE_TRUNC(DATE(usage_start_time, "America/Los_Angeles"), MONTH) = "2022-07-01"
AND l.key = "environment" AND l.value IS NOT NULL
)
SELECT
label_value,
SUM(report_val.cost) AS cost
FROM
rows_with_label
LEFT JOIN
UNNEST(report) AS report_val
GROUP BY 1