Set up Cloud Billing data export to BigQuery

This guide provides all required setup steps to enable Cloud Billing data export to BigQuery.

To export Cloud Billing data to BigQuery, take the following steps:

  • Create a project where the Cloud Billing data will be stored, and enable billing on the project (if you have not already done so).
  • Configure permissions on the project and on the Cloud Billing account.
  • Enable the BigQuery Data Transfer Service API (required to export your pricing data).
  • Create a BigQuery dataset in which to store the data.
  • Enable Cloud Billing export of cost data and pricing data to be written into the dataset.

This document walks you through the details of each of these steps.

About the console

The Google Cloud console (visit documentation, open console) is a web UI used to provision, configure, manage, and monitor systems that use Google Cloud products. You use the console to set up and manage Google Cloud and Cloud Billing resources.

Permissions required for this task

To enable and configure the export of Google Cloud billing usage cost data to a BigQuery dataset, you need the following permissions:

  • For Cloud Billing, you need either the Billing Account Costs Manager role or the Billing Account Administrator role on the target Cloud Billing account.
  • For BigQuery, you need the BigQuery User role for the Cloud project that contains the BigQuery dataset to be used to store the Cloud Billing data.

To enable and configure the export of Cloud Billing pricing data, you need the following permissions:

  • For Cloud Billing, you need the Billing Account Administrator role on the target Cloud Billing account.
  • For BigQuery, you need the BigQuery Admin role for the Cloud project that contains the BigQuery dataset to be used to store the Cloud Billing pricing data.
  • For the Cloud project containing the target dataset, you need the resourcemanager.projects.update permission. This permission is included in the roles/editor role.

You might also need other project permissions. If you are a member of a Google Cloud Organization, to create a new project, you need the Project Creator role on the Organization or Folder. Specifically, to create a new project, you need the following permissions:

  • resourcemanager.organizations.get
  • resourcemanager.projects.create

For more information about Google Cloud permissions, see:

Enable Cloud Billing data export to BigQuery

Complete the steps detailed in this section to enable the export of your Cloud Billing data to BigQuery.

If you're a reseller, you can set up a reseller-specific export of your data using the Partner Sales Console. To do so, follow steps 1-4 on this page, then see Export Cloud Billing data to BigQuery to enable the export.

1. Select or Create a project

Cloud Billing data exported to BigQuery is contained in a BigQuery dataset. A dataset is contained within a Cloud project you specify.

Recommendation about projects: We recommend that you create a Cloud project to contain all of your billing administration needs, including your exported Cloud Billing data. You can also use this billing administration Cloud project for things like Cloud Billing APIs access, Pub/Sub channels for programmatic budget notifications, and other Cloud Billing administration tasks.

Important: The Cloud project you select to contain your dataset should be linked to the same Cloud Billing account that contains the data that you plan to export to the BigQuery dataset. A Cloud project is linked to exactly one Cloud Billing account at a time. A Cloud Billing account is linked to one or more projects.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Go to project selector

Learn more about creating and managing projects.

2. Verify that billing is enabled

You must make sure that billing is enabled on the Cloud project you select to contain your dataset, and that the Cloud project is linked to the same Cloud Billing account that contains the data that you plan to export to the BigQuery dataset.

3. Enable the BigQuery Data Transfer Service API

If you plan to export your Cloud Billing pricing data to BigQuery, you need to enable the BigQuery Data Transfer Service API. If you don't plan to export your Cloud Billing pricing data, you can skip to Step 4. Create a BigQuery dataset.

The BigQuery Data Transfer Service API should be enabled in the same Cloud project you are using to contain your BigQuery datasets. If you followed the recommendation in the project section, this is your billing administration Google Cloud project.

To enable the BigQuery Data Transfer Service API, do the following:

  1. Sign in to the Google Cloud console and go to the API page.

    Go to the BigQuery Data Transfer Service API page

  2. On the top left of the page, select a project. You must enable the BigQuery Data Transfer Service API in the same project you are using to contain your BigQuery dataset. If you followed the recommendation in the project section, this is your billing administration Google Cloud project.

  3. On the BigQuery Data Transfer Service API page, click Enable to enable the API in your selected project.

For more information on enabling APIs, see the Service Usage documentation.

4. Create a BigQuery dataset

Before you enable your Cloud Billing data to export to BigQuery, you must create at least one BigQuery dataset to manage your exported data. You can use the same dataset to contain your standard usage cost data, detailed usage cost data, and your pricing data.

A dataset is contained within a Cloud project you specify. Datasets are top-level containers that are used to organize and control access to your tables and views. A table or view must belong to a dataset, so you need to create at least one dataset before loading data into BigQuery.

If you create a new dataset, we recommend that you select a multi-region option for its location, such as United States (US) or European Union (EU). For example, the detailed usage cost data is only supported in the multi-region US and EU datasets.

If you use an existing dataset, review the limitations that might impact exporting your billing data to BigQuery, such as being unable to use datasets with customer-managed encryption keys enabled or that detailed usage cost data requires multi-region datasets.

To create a BigQuery dataset, do the following:

  1. Sign in to the Google Cloud console and go to the BigQuery page.

    Go to BigQuery page

  2. In the project drop-down list ( My Project ) at the top of the Google Cloud console page, select the project you set up to contain your dataset. Make note of the project ID, as you will use that in the next steps.

  3. In the Explorer panel, in the pinned projects section, click your-project-ID to expand the project to view any existing datasets and saved queries.

  4. Next to the project ID, click the View actions menu () and then click Create dataset. The Create dataset panel opens.

    1. Enter a Dataset ID. We recommend an ID that spans projects, such as all_billing_data, rather than a project-specific ID.
    2. Select a Data location. The data location specifies the region where your data is stored. All tables within this dataset share this location.

      When creating a dataset, we recommend that you select a multi-region option for its location, such as United States (US) or European Union (EU). This is required for exporting the detailed usage cost data.

      After you create the dataset, the location cannot be changed. Learn more about locations.

    3. Ensure that the Enable table expiration option is cleared.

      If you enable table expiration and enter a number of days, any new table created in this dataset is automatically deleted following the specified number of days after creation. Important: If you delete any tables containing exported Cloud Billing data records, those records are gone and we cannot backfill the deleted records.

    4. In the Advanced options section, ensure that the Use a customer-managed encryption key (CMEK) option is cleared.

      Customer-managed key encryption is not supported for exporting Cloud Billing data records to BigQuery.

    5. To save, click Create dataset.

See the limitations that might impact exporting your billing data to BigQuery.

If you're a reseller, you can also enable the reseller-specific Cloud Billing data export.

5. Enable Cloud Billing export to the BigQuery dataset

You enable Cloud Billing data export in the Cloud Billing section of the console. These are the types of data you can enable to export:

  • Standard usage cost data - Contains standard Cloud Billing account cost usage information, such as account ID, invoice date, services, SKUs, projects, labels, locations, cost, usage, credits, adjustments, and currency.

    Use the Standard usage export to analyze broad trends in your cost data.

  • Detailed usage cost data - Contains detailed Cloud Billing account cost usage information. Includes everything in the standard usage cost data plus resource-level cost data, like a virtual machine or SSD that generates service usage.

    Use the Detailed export to analyze costs at the resource level, and identify specific resources that might be driving up your costs.

    The detailed export automatically includes resource-level information about Compute Engine. To view a breakdown of Google Kubernetes Engine (GKE) cluster costs in a detailed data export, you must also enable cost allocation for GKE.

  • Pricing data - Contains Cloud Billing account pricing information, such as account ID, services, SKUs, products, geographic metadata, pricing units, currency, aggregation, and tiers.

To enable your Cloud Billing usage cost or pricing data to be exported to BigQuery, do the following:

  1. Sign in to the Google Cloud console.

    Sign in to console

  2. Open the console Navigation menu (), and then select Billing.

    If you have more than one Cloud Billing account, do one of the following:

    • To manage Cloud Billing for the current Cloud project, select Go to linked billing account.
    • To locate a different Cloud Billing account, select Manage billing accounts and choose the account for which you'd like to enable Cloud Billing data export to BigQuery.
  3. In the Billing navigation menu, select Billing export.

  4. Select the BigQuery export tab (this tab is selected by default).

  5. Click Edit settings for the type of data you'd like to export. Each type of data is configured separately.

  6. From the Projects list, select the project that you set up to contain your BigQuery dataset.

    The project you select is used to store the exported Cloud Billing data in the BigQuery dataset.

    For standard and detailed usage cost data exports, the Cloud Billing data includes usage/cost data for all Cloud projects paid for by the same Cloud Billing account.

    For pricing data export, the Cloud Billing data includes only the pricing data specific to the Cloud Billing account that is linked to the selected dataset project.

  7. From the Dataset ID field, select the dataset that you set up to contain your exported Cloud Billing data.

    For all types of Cloud Billing data exported to BigQuery, the following applies:

    • The BigQuery API is required to export data to BigQuery. If the project you selected doesn't have the BigQuery API enabled, you are prompted to enable it. Click Enable BigQuery API to enable the API.
    • If the project you selected doesn't contain any BigQuery datasets, you are prompted to create one. If necessary, follow these steps to create a new dataset.
    • If you use an existing dataset, review the limitations that might impact exporting your billing data to BigQuery, such as being unable to export data to datasets configured to use customer-managed key encryption.

    For pricing data export, the BigQuery Data Transfer Service API is required to export the data to BigQuery. If the project you selected doesn't have the BigQuery Data Transfer Service API enabled, you are prompted to enable it. If necessary, follow these steps to enable the API.

  8. Click Save.

Update Cloud Billing data to export

After you've enabled your Cloud Billing data to be exported to BigQuery, you can change which data is exported by disabling or enabling the different types.

Before disabling any of your data exports, review the limitations and data availability guidelines for how it might impact your billing data exported to BigQuery.

To disable a type of Cloud Billing being exported to BigQuery, do the following:

  1. Select Disable Standard Export, Disable Detailed Export, or Disable Pricing Export accordingly.
  2. Select Confirm to save your selection.

To enable a currently disabled type of Cloud Billing being exported to BigQuery, see Enable your Cloud Billing data to be exported to BigQuery.

About dataset permissions

Cloud Billing export to BigQuery uses a service account to manage dataset permissions.

After you enable Cloud Billing export to BigQuery, Google automatically adds a service account as an owner to the dataset that you specify. The service accounts look like this:

  • For the Standard and Detailed usage cost datasets: billing-export-bigquery@system.gserviceaccount.com
  • For the Pricing dataset: cloud-account-pricing@cloud-account-pricing.iam.gserviceaccount.com

The service accounts are owned and managed by Google and provide the permissions that are necessary for our offline process to create a table and write Cloud Billing records to it.

See the limitations that might impact exporting your billing data to BigQuery.

Billing data tables

Shortly after enabling Cloud Billing export to BigQuery, billing data tables are automatically created in the BigQuery dataset. To learn more about these BigQuery tables and the table schemas, see Understanding the auto-created data tables in BigQuery.

Frequency of data loads

  • When you first enable the standard or detailed usage cost export to BigQuery, it might take a few hours to start seeing your Google Cloud cost data.
  • When you first enable the pricing export to BigQuery, it might take up to 48 hours to start seeing your Google Cloud pricing data.

To learn more about the frequency of the data loads into your BigQuery tables, see Frequency of data loads.

Limitations

Exporting Cloud Billing data to BigQuery is subject to the following limitations.

  • Your BigQuery datasets containing standard usage cost and pricing data only reflect Google Cloud billing data incurred from the date you set up Cloud Billing export, and after. That is, Google Cloud billing data is not added retroactively, so you won't see Cloud Billing data from before you enable export. For more details, see Data availability.
  • Your BigQuery datasets containing detailed usage cost data reflect Google Cloud billing data incurred from the start of the previous month from when you first enabled the export, unless you are re-enabling it. That is, Google Cloud billing data is added retroactively only for the current and previous month. For more details, see Data availability.
  • When exporting detailed usage cost data, you must export to a dataset configured with a multi-region location, such as United States (US) or European Union (EU). If you select a dataset that is not a multi-region location, this prevents Cloud Billing from writing detailed billing data to the appropriate tables within that dataset.
  • When exporting detailed usage cost data, the detailed export automatically includes resource-level information about Compute Engine. To view a breakdown of Google Kubernetes Engine (GKE) cluster costs in a detailed data export, you must also enable cost allocation for GKE.
  • Customer Managed Encryption Keys (CMEK) are not supported when exporting billing data to BigQuery. If you enable CMEK for your billing data dataset, this prevents Cloud Billing from writing billing data to the appropriate tables within that dataset. Instead, you need to configure the dataset to use Google-managed key encryption.

Cost and pricing reports available in the Google Cloud console