Skip to main content

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.

    Note

    When 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 is gcp_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 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 your 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 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.

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. If you've enabled Flexsave, you'll see a separate line item of Flexsave for the Flexsave costs/savings.
sku_descriptionstringA description of the resource type used by the service. If you've enabled Flexsave, you'll see additional rows related with Flexsave.
sku_idstringThe ID of the resource used by the service. If you've enabled Flexsave, you'll see additional rows related with Flexsave.
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