Export Billing Data to BigQuery

Tools for monitoring, analyzing and optimizing cost have become an important part of managing development. Billing export to Google BigQuery enables you to export your daily usage and cost estimates automatically throughout the day to a BigQuery dataset you specify. You can then access your billing data from BigQuery. You can also use this export method to export data to a JSON file.

Regular file export to CSV and JSON is also available. However, if you use regular file export, you should be aware that regular file export captures a smaller dataset than export to BigQuery. For more information about regular file export and the data it captures, see Export Billing Data to a File.

How to enable billing export to BigQuery

To enable billing export to BigQuery:

  1. Go to the Google Cloud Platform Console.
  2. Open the left side menu and select Billing.
  3. If you have more than one billing account, select Go to linked billing account to manage the current project's billing. To locate a different billing account, select Manage billing accounts.
  4. Click Billing export.
  5. Select BigQuery export.
  6. From the Project list, select the project where your BigQuery dataset is stored. If you don't have a BigQuery dataset created yet, you'll be prompted to create one.
  7. If necessary, to create a new dataset:

    1. In the BigQuery web UI, click the down arrow next to your project name, then click Create new dataset.
    2. Specify the Dataset ID, Data location, and Data expiration for your dataset, then click OK.
  8. In the console, from the Billing export dataset list, specify a dataset to export data to. If you just created a dataset, choose the name of your new dataset.

  9. Click Enable BigQuery export.

After you enable BigQuery export, it might take a few hours to start seeing your data. Billing data automatically exports your data to BigQuery in regular intervals, but the frequency of updates in BigQuery varies depending on the services you're using.

About billing data export table versions: original and v1

During the beta stage of data export to BigQuery, your exported billing data will flow to two different tables in your dataset. The tables contain two different versions of the same data: the original version and the new version, v1.

We provide both formats so that you can continue analyzing your billing data during this transition. We will automatically export new billing data to both of the BigQuery tables as it flows in.

The data version is indicated by the table names:

  • original version: gcp_billing_export_BILLING_ACCOUNT_ID
  • v1 version: gcp_billing_export_v1_BILLING_ACCOUNT_ID

Differences between the original version and v1

We made several changes to the export schema. Some columns were renamed to stay consistent with the rest of Google Cloud Platform, and some columns were added to include additional useful information about your costs.

Summary of schema changes:

  • Renamed the product column to service.description
  • Renamed the resource_type column to sku.description
  • Renamed the start_time and end_time columns to usage_start_time and usage_end_time
  • Added a service.id column
  • Added a sku.id column
  • Added an export_time column

You can use the sku.id column to map each of your line items to the list prices published on the Google Cloud Platform pricing pages and through the cloud billing catalog API. Use the export_time column to understand when the billing data was last updated.

Billing data in BigQuery

The following table describes the contents of the billing data that is exported to Google BigQuery.

Field Type Description
billing_account_id String The billing account ID that the usage is associated with.
service.id (v1 only) String The ID of the service that the usage is associated with.
service.description (v1)
product (original)
String The Google Cloud Platform service that reported the billing data.
sku.id (v1 only) String The ID of the resource used by the service. For the full list of SKUs, see GCP SKUs.
sku.description (v1)
resource_type (original)
String A description of the resource type used by the service. For example, a resource type for Cloud Storage is Standard Storage US.
usage_start_time (v1)
start_time (original)
Timestamp A UNIX timestamp of when the service usage began. For more information, see the BigQuery documentation on timestamp data types. See also, Differences between exported data and invoices below.
usage_end_time (v1)
end_time (original)
Timestamp A UNIX timestamp of when the service usage ended. For more information, see the BigQuery documentation on timestamp data types. See also, Differences between exported data and invoices below.
project.id String The ID of the project that generated the billing data.
project.name String The name of the project that generated the billing data.
project.labels.key String If labels are present, the key portion of the key:value pair that comprises the label on the project where the usage occurred. For more information about using labels, see Using Labels.
project.labels.value String If labels are present, the value portion of the key:value pair that comprises the label on the project where the usage occurred. For more information about using labels, see Using Labels.
labels.key String If labels are present, the key portion of the key:value pair that comprises the label on the resource where the usage occurred. For more information about using labels, see Using Labels.
labels.value String If labels are present, the value portion of the key:value pair that comprises the label on the resource where the usage occurred. For more information about using labels, see Using Labels.
cost Float The amount charged to your billing account.
currency String The currency that the cost is billed in. For more information, see Local currency for billing and payments.
currency_conversion_rate Float The exchange rate from US dollars to the local currency. That is, cost/currency_conversion_rate is the cost in US dollars.
usage.amount Float The quantity of usage.units used.
usage.unit String The measurement of resource used. For example, a usage unit for standard storage is byte-seconds.
credits.name String A description of the credit applied to the billing account.
credits.amount Float The amount of the credit applied to the usage. Credits are always negative amounts and the total charge is calculated as cost + credits.amount.
export_time (v1 only) Timestamp A processing time associated with new append data. This will always increase with each new export.
See also, Differences between exported data and invoices below.

About labels

You'll see columns for labels in your BigQuery dataset, but for the current release some label values will be empty. Label export data will be populated at different times for different services, depending on when each service provides it.

Differences between exported data and invoices

Queries on timestamp fields might include late-reported usage not included in your invoice and, as a result, the data returned might not map directly to your invoice.

Timestamp fields include:

  • usage_start_time (v1) or start_time (original)
  • usage_end_time (v1) or end_time (original)
  • export_time

Errors and adjustments

You should be aware that in the unlikely event that your billing data contains an error or requires an adjustment, your billing data will be appended with data that negates the error. All columns will remain the same, except for:

  • cost
  • credit
  • usage.amount
  • export_time

If you need to determine which data is new, you can do so by querying export_time (v1 only). If you're using the original version, you can use a query like the following example:

SELECT partition_id, MSEC_TO_TIMESTAMP(last_modified_time)
FROM [dataset_name.table_name$__PARTITIONS_SUMMARY__]

For the above example query, you must use legacy SQL because standard SQL does not support the partition decorator separator ($).

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.