Skip to main content

Billing data export

DoiT Cloud Navigator: Premium and above

Billing data​

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.

To access Google-provided base billing data, you can set up Cloud Billing data export to BigQuery if you have the Billing Account Administrator role on the target Cloud Billing account.

DoiT recalculates the Google billing data to apply DoiT discounts, custom pricing, and other relevant items. To access the recalculated billing data, you can choose one of the following approaches:

DoiT billing export table​

Required permissions​

When requesting access to the DoiT billing export table, provide the account you'll use for querying. It could be a Google user account, a Google group account, or a service account.

  • If the DoiT billing export table hasn't been configured yet, you need the Billing Account Administrator role on the target Cloud Billing account.

Data availability​

We create a view in Google BigQuery for the target Cloud Billing account at your request and share it with you. If you have multiple target billing accounts, we create multiple views, one for each 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 is gcp_billing_ concatenated with the name of the target Google Billing account (dashes in the billing account ID being replaced by underscores). For example, if the target Google Billing Account ID is 123456-123456-123456, the dataset name should be gcp_billing_123456_123456_123456.

  • BQ_table_name: The name of the BigQuery table that contains the exported Cloud Billing data. It is doitintl_billing_export_resource_v1_XXXXXX_XXXXXX_XXXXXX, where XXXXXX_XXXXXX_XXXXXX is the target Google Billing account ID with dashes being replaced by underscores.

Table configuration​

Review the table partitioning and clustering schema to allow query optimization.

ItemSetting
Table typePartitioned
Partitioned byDay
Partitioned on fieldexport_time
Partition filterRequired
Clustered byproject_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 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.

FieldTypeDescription
costfloatThe cost of the usage after applying DoiT discounts, custom pricing, and credits.
credits.amountfloatThe amount of the credit applied to the usage after DoiT discounts.
cost_typestringThe type of cost the line item represents.
sku_descriptionstringA description of the resource type used by the service.
sku_idstringThe ID of the resource used by the service.
project.ancestorsrecordEmpty. Currently not supported in the DoiT billing export table.

Sample queries​

This section provides examples of how to query the DoiT billing export table.

Tip

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
See also
Last updated on