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.
To get an accurate export that includes changes applied to your billing data by DoiT (for example, DoiT discounts or custom pricing), you 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 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. For example,doitintl-cmp-gcp-data-views
.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 is123456-123456-123456
, the dataset name should begcp_billing_123456_123456_123456
.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
, whereXXXXXX_XXXXXX_XXXXXX
is your billing account ID with dashes being replaced by underscores.
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 |
VIEW schema
The schema of the DoiT billing export table matches Google Cloud's billing export table. You can find the detailed information at Google Cloud Docs: Structure of Detailed data export.
Fields that may have different values from Google's billing export table are listed below.
Field | Type | Description |
---|---|---|
cost | float | The cost of the usage after applying DoiT discounts, custom pricing, and credits. |
credits.amount | float | The amount of the credit applied to the usage after DoiT discounts. |
cost_type | string | The type of cost the line item represents. If you've enabled Flexsave, you'll see a separate line item of Flexsave for the Flexsave costs/savings. |
sku_description | string | A description of the resource type used by the service. If you've enabled Flexsave, you'll see additional rows related with Flexsave. |
sku_id | string | The ID of the resource used by the service. If you've enabled Flexsave, you'll see additional rows related with Flexsave. |
project.ancestors | record | Empty. Currently not supported in the DoiT billing export table. |
Sample queries
This section provides examples of how to query the DoiT billing export table.
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
.
Daily cost per project
The query below returns the daily cost per project for January 2023.
DECLARE tz STRING DEFAULT "America/Los_Angeles";
SELECT
DATE(usage_start_time, tz) AS usage_date,
project.id AS project_id,
project.name AS project_name,
SUM(cost) AS cost,
SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) AS c), 0)) AS credits
FROM
`doitintl-cmp-gcp-data-views.gcp_billing_XXXXXX_XXXXXX_XXXXXX.doitintl_billing_export_resource_v1_XXXXXX_XXXXXX_XXXXXX`
WHERE
DATE(export_time) >= "2023-01-01" AND DATE(export_time) >= "2023-01-31"
AND DATE(usage_start_time, tz) BETWEEN "2023-01-01" AND "2023-01-31"
GROUP BY usage_date, project_id, project_name
ORDER BY 1, 2
Query with labels
The query below returns the cost breakdown for resources with the label environment
for January 2023.
DECLARE tz STRING DEFAULT "America/Los_Angeles";
SELECT
labels.value AS environment,
SUM(cost) AS cost,
SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) AS c), 0)) AS credits
FROM
`doitintl-cmp-gcp-data-views.gcp_billing_XXXXXX_XXXXXX_XXXXXX.doitintl_billing_export_resource_v1_XXXXXX_XXXXXX_XXXXXX`
LEFT JOIN UNNEST(labels) AS labels
ON labels.key = "environment"
WHERE
DATE(export_time) >= "2023-01-01" AND DATE(export_time) >= "2023-01-31"
AND DATE(usage_start_time, tz) BETWEEN "2023-01-01" AND "2023-01-31"
GROUP BY environment
HAVING
environment IS NOT NULL
ORDER BY 2 DESC
Google Cloud Docs: Understand the Cloud Billing data tables in BigQuery
Google Cloud Docs: Query partitioned tables
Google Cloud Docs: Differences between exported data and invoices