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.

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.

    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 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 always doitintl-cmp-gcp-data.

  • 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 006C3F-3613C3-2A2169, the dataset name should be gcp_billing_006C3F_3613C3_2A2169.

  • 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_view, where XXXXXX_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.

Tip

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.

FieldTypeDescription
billing_account_idstringThe Cloud Billing account ID that the usage is associated with.
project_idstringThe ID of the Google Cloud project that generated the Cloud Billing data. The same as project.id.
service_descriptionstringThe Google Cloud service that reported the Cloud Billing data.
service_idstringThe ID of the service that the usage is associated with.
sku_descriptionstringA description of the resource type used by the service.
sku_idstringThe ID of the resource used by the service. For the full list of SKUs, see Google Cloud SKUs.
usage_start_timetimestampThe start time of the hourly usage window within which the given cost was calculated.
usage_end_timetimestampThe end time of the hourly usage window within which the given cost was calculated.
projectrecordproject contains fields that describe the Cloud Billing project.
project.idstringThe ID of the Google Cloud project that generated the Cloud Billing data.
project.numberstringA unique identifier for the Google Cloud project that generated the Cloud Billing data.
project.namestringThe name of the Google Cloud project that generated the Cloud Billing data.
project.labelsrecord (repeated)project.labels contains key:value pairs that comprise the labels on the Google Cloud project where the usage occurred.
project.labels.keystringIf project labels are present, the key portion of the key:value pair.
project.labels.valuestringIf project labels are present, the value portion of the key:value pair.
project.ancestry_numbersstringThe ancestors in the resource hierarchy for the project identified by the specified project.id.
project.ancestry_namesstring(DoiT specific) The same as project.ancestry_numbers but in the folder name instead of the folder number.
labelsrecord (repeated)labels contains key:value pairs that comprise the labels on the Google Cloud resource where the usage occurred.
labels.keystringIf labels are present, the key portion of the key:value pair.
labels.valuestringIf labels are present, the value portion of the key:value pair.
system_labelsrecord (repeated)system_labels contains key:value pairs that comprise the system-generated labels on the resource where the usage occurred.
system_labels.keystringIf system labels are present, the key portion of the key:value pair.
system_labels.valuestringIf system labels are present, the value portion of the key:value pair .
locationrecordlocation contains geographic information of usage.
location.locationstringLocation of usage at the level of a multi-region, country, region, or zone; or global for resources that have no specific location.
location.countrystringThe country of usage.
location.regionstringThe region of usage.
location.zonestringThe zone of usage.
export_timetimestampA processing time associated with an append of Cloud Billing data.
costfloatThe cost of the usage before any credits, to a precision of up to six decimal places.
currencystringThe currency that the cost is billed in.
currency_conversion_ratefloatThe exchange rate from US dollars to the local currency.
usagerecordusage contains fields that describe the quantity of usage units and usage pricing units.
usage.amountfloatThe quantity of usage.unit used.
usage.unitstringThe base unit in which resource usage is measured.
usage.amount_in_pricing_unitsfloatThe quantity of usage.pricing_unit used.
usage.pricing_unitstringThe unit in which resource usage is measured, according to the Google Cloud Billing Catalog API.
creditsrecord (repeated)credits contains fields that describe the structure and value of the credits associated with Google Cloud and Google Maps Platform SKUs.
credits.namestringA description of the credit applied to the Cloud Billing account.
credits.amountfloatThe amount of the credit applied to the usage. Credits are always negative amounts.
credits.full_namestringThe name of the credit associated with the product SKU. A human-readable description of an alphanumeric credits.id.
credits.idstringIf present, indicates that a credit is associated with the product SKU.
credits.typestringThis field describes the purpose or origin of the credits.id. See Google Cloud Docs for details.
invoicerecordinvoice contains fields that describe the general information about the invoice.
invoice.monthstringThe year and month (YYYYMM) of the invoice that includes the cost line items.
cost_typestringThe type of cost this line item represents: regular, tax, adjustment, or rounding error.
adjustment_inforecordadjustment_info contains fields that describe the structure and value of an adjustment to cost line items associated with a Cloud Billing account.
adjustment_info.idstringIf present, indicates that an adjustment is associated with a cost line item.
adjustment_info.descriptionstringA description of the adjustment and its cause.
adjustment_info.modestringHow the adjustment was issued. See Google Cloud Docs for details.
adjustment_info.typestringThe type of adjustment. See Google Cloud Docs for details.
tagsrecord (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.keystring(DoiT specific) The key portion of the tag key:value pair.
tags.valuestring(DoiT specific) The value portion of the tag key:value pair.
tags.inheritedboolean(DoiT specific) This field indicates whether the tag bindings are inherited from the parent resource in the Google Cloud resource hierarchy.
resource_idstring(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_idstring(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.
reportrecord (repeated)(DoiT specific) report contains fields that describe changes applied to your billing data by DoiT.
report.costfloat(DoiT specific) The cost of the usage after applying DoiT discounts and/or custom pricing.
report.usagefloat(DoiT specific) The same as usage.amount_in_pricing_units.
report.creditstring(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.

ItemSetting
Table typePartitioned
Partitioned byDay
Partitioned on fieldexport_time
Partition filterRequired
Clustered byproject_id, service_description, sku_description

Sample queries

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

See also
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.

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.

Note

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