This document provides all required steps to set up Cloud Billing dataexport to BigQuery.

Note: Using BigQuery to store and query Cloud Billingdata incurs minimal fees. For more information, seeCost of use.

To export Cloud Billing data to BigQuery, take thefollowing steps:

• Create a project where the Cloud Billing data will bestored, and enable billing on the project (if you haven't alreadydone 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 to store the data in.
• Enable Cloud Billing export of cost data and pricing data to be writteninto the dataset.

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

About the Google Cloud console

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

Permissions required for this task

To enable and configure the export of Google Cloud billingusage costdata to a BigQuery dataset, you need the followingpermissions:

• For Cloud Billing, you need either the Billing Account Costs Managerrole or the Billing Account Administrator role on the targetCloud Billing account.
• For BigQuery, you need theBigQuery User role for the Google Cloud project that contains the BigQuery dataset to be used tostore the Cloud Billing data.

To enable and configure the export of Cloud Billingpricing data andCUD metadata, you need the following permissions:

• For Cloud Billing, you need the Billing Account Administrator roleon the target Cloud Billing account.
• For BigQuery, you need theBigQuery Admin role for the Google Cloud project that contains the BigQuery dataset to be used tostore the Cloud Billing pricing data.
• For the Google Cloud project containing the target dataset, you need theresourcemanager.projects.update permission. This permission isincluded in theroles/editor role.

You might also need other project permissions. If you're a member of aGoogle Cloud Organization, to create a new project, you need theProject Creator role on the Organization or Folder. Specifically, tocreate a new project, you need the following permissions:

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

For more information about Google Cloud permissions, see:

• Overview of Cloud Billing access control
• Create custom roles for Cloud Billing
• Understanding Predefined Identity and Access Management Roles

Enable Cloud Billing data export to BigQuery

Interactive tutorial: Analyze billing data with BigQuery (20 minutes)

Get started with billing data exports using this interactive tutorial. In the tutorial, you'll enable Cloud Billing data to export to a BigQuery dataset. Next, you'll run some sample queries through the BigQuery web interface to examine billing data.

Launch the billing data export tutorial

Complete the five steps in this section to enable the export of yourCloud Billing data to BigQuery.

For a quick introduction to enabling billing data export and running samplequeries of Cloud Billing data in BigQuery, follow thisinteractive tutorial for billing data export.

If you're a reseller, you can set up areseller-specific export of yourChannel Services billing data using thePartner Sales Console. To do so,follow steps 1-4 on this page to create your project and BigQuerydataset. Then for step 5, seeExport Channel Services data to BigQueryto enable the export.

1. Select or Create a project

Cloud Billing data exported to BigQuery is contained ina BigQuery dataset. A dataset is contained within aGoogle Cloud project you specify.

We recommend that you create and use aseparate Google Cloud project to contain all of your FinOps and billingadministration needs for a Cloud Billing account, including yourexported Cloud Billing data.Learn more about the benefits of using a FinOps-focused project for your billing administration use cases.

Caution: The Google Cloud project you select to contain your datasetshould belinked to the same Cloud Billing account that contains the data that you plan to export to theBigQuery dataset.A Google Cloud project islinked to exactly one Cloud Billing account at a time.A Cloud Billing account islinked to one or more projects.

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

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Go to project selector

Learn more aboutcreating and managing projects.

2. Verify that billing is enabled

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

• Learn how toconfirm billing is enabled for your project
• Learn how toview projects linked to Cloud Billing accounts

3. Enable the BigQuery Data Transfer Service API

If you plan to export your Cloud Billingpricing data toBigQuery, you need to enable the BigQuery Data Transfer Service API. If youdon't plan to export your Cloud Billingpricing data, you canskip to Step 4. Create a BigQuery dataset.

The BigQuery Data Transfer Service API should be enabled in the same Google Cloud projectyou're using to contain your BigQuery datasets. If youfollowed the recommendation in theproject section,this is yourFinOps or 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 theBigQuery Data Transfer Service API in the same project you are using to contain yourBigQuery dataset. If you followed therecommendation in theproject section,this is yourFinOps or billing administration Google Cloud project.

3. On theBigQuery Data Transfer Service API page, clickEnable to enable the API in yourselected project.

For more information on enabling APIs, see theService Usage documentation.

4. Create a BigQuery dataset

Before you enable your Cloud Billing data to export toBigQuery, you must create at least oneBigQuery dataset to manage your exported data. You can use the same dataset to containyourstandard usage cost data,detailed usage cost data, and yourpricingdata.

A dataset is contained within a Google Cloud project you specify.Datasets are top-level containers that are used to organize and control accessto yourtables andviews.A table or view must belong to a dataset, so you need to create at least onedataset beforeloading data into BigQuery.

If you use an existing dataset, review thelimitations that might impact exporting your billing data to BigQuery,such as being unable to export data to datasets configured to usecustomer-managed key encryption, or being unable to use a datasetconfigured with an unsupportedregion location.

To create a BigQuery dataset, do the following:

1. Sign in to the Google Cloud console and go to theBigQuerypage.

   Go to BigQuery page

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

3. In theExplorer panel, in thepinned projects section, clickarrow_rightyour-project-ID to expand the project to view any existing datasets andsaved queries.

4. Next to theproject ID, click theView actions menu(more_vert) and thenclickCreate dataset. The Create dataset panel opens.

   1. Enter aDataset ID. We recommend an ID that spans projects, such asall_billing_data, rather than a project-specific ID.

   2. Select aData location. The data location specifies the multi-regionor region where your data is stored. All tables within this dataset share this location.

      We recommend selecting a multi-region location (EU or US). For yourstandard usage cost anddetailed usage cost data exports,the location you select can influence whether the previous month's datais added to the dataset retroactively. Review thelimitations for more information about dataset locations and data availability.

      Cloud Billing export supportsallmulti-region locations (EU or US). However, only asubset of datasetregion locations are supported for Cloud Billing export toBigQuery. Review thelimitations for more information about dataset locations.

      After you create the dataset, the locationcan't be changed.Learn more about locations.

   3. Ensure that theEnable table expiration option iscleared.

      If youenable table expiration and enter a number of days,any new table created in this dataset is automatically deletedfollowing the specified number of days after creation.

      Caution: If you delete any tables containing exported Cloud Billingdata records, those records are gone and we can't backfill the deletedrecords.

   4. In theAdvanced options section, choose anEncryption setting.The default option isGoogle-owned and Google-managed encryption key.

      If you want to use customer-managed encryption key (CMEK) encryption,selectCustomer-managed encryption key (CMEK), and choose anencryption key.

      Warning: To use CMEK encryption for your Cloud Billing data, youmust enable CMEK at the dataset level. If you enable CMEK encryption atthe project or organization level using organization policies, the policyprevents Cloud Billing from writing billing data to the tables inyour dataset.

   5. To save, clickCreate dataset.

See thelimitations that might impact exporting your billing data to BigQuery.

If you're a reseller, you can reuse the same project and dataset you justcreated when youenable the reseller-specific Channel Services billing data export.

5. Enable Cloud Billing export to the BigQuery dataset

Note: (Resellers) You can set up areseller-specific export ofyour Channel Services billing data to a specified BigQuerydataset using the Partner Sales Console. After completing steps 1-4 on thispage, seeExport Channel Services data to BigQueryto enable a Channel Services export.

You enable Cloud Billing data export in the Cloud Billingsection of the Google Cloud console. These are the types of data youcan 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 thestandard usage cost data plus resource-levelcost 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 mustalsoenable cost allocation for GKE.

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

• Committed use discounts (CUD) metadata(Preview) -Contains Cloud Billing CUD metadata information, such as billingaccount ID, CUD product ID, CUD product type, consumption model ID anddescription, subscription instance ID, subscription entitlement scope,commitment amount, and term. For a list of export fields, seeStructure of CUD metadata export.

  Note: If you use VPC Service Controls for BigQuery resources onyour project or organization, you must createingress and egress rules to properly enable CUD metadata exports.

To enable your Cloud Billingusage cost orpricing data to beexported to BigQuery, do the following:

1. In the Google Cloud console, go to theBilling export page.

   Go to the Billing export page

2. At the prompt,choose the Cloud Billing accountfor which you'd like to export billing data. TheBilling export page opens for the selected billing account.

3. On theBigQuery export tab, clickEdit settings foreach type of data you'd like to export. Each type of data is configuredseparately.

4. From theProjects list, select theproject that you set up to contain yourBigQuery dataset.

   The project you select is used to store the exported data in theBigQuery dataset.

   Forstandard anddetailed usage cost data exports, theCloud Billing data includes usage and cost data forallGoogle Cloud projects paid for by the same Cloud Billing account.

   Forpricing data export, the Cloud Billing data includes only thepricing data specific to theCloud Billing account that is linked to the selected dataset project.

   ForCUD metadata export, it contains CUD metadata for all CUDs purchasedin any of the projects linked to the billing account.

   Note: If you use VPC Service Controls for BigQuery resources onyour project or organization, you must createingress and egress rules to properly enable CUD metadata exports.

5. From theDataset ID field, select thedataset that you set up to contain your exported Cloud Billing data.

   Forall types of Cloud Billing data exported toBigQuery, the following applies:

   • The BigQuery API is required to export data toBigQuery. If the project you selected doesn't have theBigQuery API enabled, you're prompted to enable it.ClickEnable BigQuery API to enable the API.
   • If the project you selected doesn't contain anyBigQuery datasets, you're prompted to create one.If necessary,follow these steps to create a new dataset.
   • If you use an existing dataset, review thelimitations that might impact exporting your billing data toBigQuery, such as being unable to export data to datasetsconfigured to use aCMEK, or being unable to use a dataset configuredwith an unsupportedregion location.

   Forpricing data export, the BigQuery Data Transfer Service API is required to export thedata to BigQuery. If the project you selected doesn't havethe BigQuery Data Transfer Service API enabled, you're prompted to enable it. Ifnecessary,follow these steps to enable the API.

6. ClickSave.

Note: Cloud Billing data export supports allmulti-region datasetlocations (EU and US). However, only asubset ofregion dataset locations are supported foruse with Cloud Billing data. When you're configuring your Cloud Billingexport settings, if you create or select a dataset that's configured to use anunsupportedregion location, when you attempt to save your exportsettings, you'll see anInvalid dataset region error, and yoursettings aren't saved.
To correct this error, edit your export settings again, and select orcreate a BigQuery dataset that's configured to use eitheramulti-region location (recommended), or one of thesupportedregion locations.

Update Cloud Billing data to export

After youenable your Cloud Billing data to be exported to BigQuery,you can change various data export settings, for example:

• Change the location where your exported billing data is stored by editing theexport settings.
• Change which types of billing data are exported by disabling or enablingthe different types.

Before you update the project or dataset where your billing data is stored, ordisable any of your data exports, review thelimitations anddata availability guidelines for how these updates might impact your billing data exported toBigQuery.

Disable a type of billing data export

Todisable a type of Cloud Billing data being exported toBigQuery, do the following:

1. SelectDisable Standard Export,Disable Detailed Export, orDisable Pricing Export accordingly.
2. SelectConfirm to save your selection.

Enable a type of billing data export

Tore-enable a disabled export of Cloud Billing data toBigQuery, seeEnable your Cloud Billing data to be exported to BigQuery.

Change the project or dataset of a billing data export

To update the project or dataset where your exported billing data is stored, do the following:

1. For the export you want to update, selectEdit settings.
2. On theEdit settings page, choose a different project or dataset.
3. SelectSave to confirm your updates.

Caution: Previously exported billing data isn't backfilled automatically to thenew storage location. After switching your export settings to a new dataset, toinclude the billing information that was exported prior to the switch, you mustmanually join the new dataset with the previous dataset. For more information,seeJoin operation.

About dataset permissions

Cloud Billing export to BigQuery uses aservice account to manage dataset permissions.

Note: Service accounts don't apply to CUD metadata export.

After you enable Cloud Billing export to BigQuery, Googleautomatically adds a service account as an owner to the dataset that youspecify. 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 thepermissions that are necessary for our offline process to create a tableand write Cloud Billing records to it.

Warning: Don't remove the service accounts from your dataset. If you removea service account while Cloud Billing export to BigQuery isenabled, the table isn't updated, which puts you at risk of data loss. If youremoved a service account, you must either re-add it manually or disableand re-enable Cloud Billing export to BigQuery.

See thelimitations 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 BigQuerydataset. To learn more about these BigQuery tables and thetable schemas, seeUnderstanding the auto-created data tables in BigQuery.

Frequency of data loads

• When you first enable thestandard ordetailed usage cost export toBigQuery, it might take a few hours to start seeingGoogle Cloud cost data propagate to your BigQuerydataset. Data is exported in chronological order. If your export isconfigured to include data retroactively for the current and previous month,it might take up to five days for the exported data to fully catch up to yourcurrent usage data.
• When you first enable thepricing export to BigQuery, itmight take up to 48 hours to start seeing your Google Cloud pricingdata.

Learn more about the frequency of the data loads into yourBigQuery tables.

Limitations

Exporting Cloud Billing data to BigQuery is subject to thefollowing limitations.

• The table schema for any of the Cloud Billing data exports can change

  When the table schema changes, such as when new fields are added to aBigQuery table schema for a Cloud Billing dataexport, any queries thatdirectly reference the exported columns mightfail. To resolve this, we recommend creatingBigQuery views that query the exported tables and present the information in yourpreferred structure.

  You can then adjust the queries that feed your reports and dashboards topull from the views, instead of the exported tables. By using views, youcan standardize the structure of the data used in your queries anddashboards.

  The views you create should normalize the data so that all of the relevanttables present the same schema to your queries. This protects you fromfuture schema changes, allowing you to modify the view's underlying queryin those instances when the data schema changes.

• BigQuery dataset locations supported for use with Cloud Billing data

  BigQuery datasets are configured to use alocation – either amulti-region location (EU or US), or aregion location. Thedataset location is set at creation time. After a dataset is created, itslocation can't be changed.

  Cloud Billing data export supports allmulti-region locations(EU or US), but only a subset ofregion locations. When you'reconfiguring your Cloud Billing export settings, if you create or selecta dataset that's configured to use an unsupportedregion location,when you attempt to save your export settings, you'll see anInvalid dataset region error.

  The following table lists themulti-region locations and theregion locations that are supported for use with BigQuery datasets thatcontain Cloud Billing data.

  Americas	Asia Pacific	Europe
  Multi-region: US Regions: northamerica-northeast1 (Montréal) southamerica-east1 (São Paulo) us-central1 (Iowa) us-east1 (South Carolina) us-east4 (Northern Virginia) us-west1 (Oregon) us-west2 (Los Angeles) us-west3 (Salt Lake City) us-west4 (Las Vegas)	Regions: asia-east1 (Taiwan) asia-east2 (Hong Kong) asia-northeast1 (Tokyo) asia-northeast2 (Osaka) asia-northeast3 (Seoul) asia-south1 (Mumbai) asia-southeast1 (Singapore) asia-southeast2 (Jakarta) australia-southeast1 (Sydney)	Multi-region: EU Regions: europe-central2 (Warsaw) europe-north1 (Finland) europe-west1 (Belgium) europe-west2 (London) europe-west3 (Frankfurt) europe-west4 (Netherlands) europe-west6 (Zurich)

• If you edit your export settings to update the project or dataset whereyour exported billing data is stored, previously exported billing data isn'tbackfilled to your new dataset. To include the billing information that wasexported prior to the switch, you must manually join the new dataset withthe previous dataset. For more information, seeJoin operation.

• For yourBigQuery datasets containingstandard usage cost data ordetailed usage cost data, thetype of location you configure on the dataset impacts thetimingof when your Google Cloud billing data is exported to the dataset:

  • If you configure the dataset to use amulti-region location (EU or US), the dataset includes Google Cloud billing data incurredfrom the start of the previous month from when youfirst enabled theexport. That is,Google Cloud billing data is added retroactivelyfor the current and previous month. For theinitial backfill ofexported data, it might take up to five days for your retroactiveCloud Billing data to finish exporting before you start seeing yourcurrent usage data. This retroactive backfill of data only occurs thefirst time you enable export. If youedit the export settings to changethe project or dataset where your exported billing data is stored, or youarere-enabling the export, then billing data isnot addedretroactively.

  • If your dataset is configured to use a supportedregion location,yourstandard usage cost data and yourdetailed usage cost data onlyreflect Google Cloud billing data incurred starting from the dateyou enabled Cloud Billing export, and after. That is,Google Cloud billing data isnot added retroactively fornon-multi-region dataset locations, so you won't see Cloud Billingdata from before you enable export.

    Note: Cloud Billing data export supports allmulti-region locations (EU or US), and asubset ofregion locations.When you're configuring your Cloud Billing export settings, if youcreate or select a dataset that's configured to use an unsupportedregion location, when you attempt to save your export settings, you'llsee anInvalid dataset region error.

  • For more details, seeData availability.

• YourBigQuery datasets containingpricing data only collect Google Cloud billing data incurred fromthe date you set up Cloud Billing export, and after. That is,_Google Cloud pricing data isn't added retroactively, so you won'tsee Cloud Billing pricing data from before you enable export. For more details, seeData availability.

• When exportingdetailed usage cost data, the detailed exportautomatically includes resource-level information about Compute Engine.To view a breakdown of Google Kubernetes Engine (GKE) cluster costs in adetailed data export, you must alsoenable cost allocation for GKE.

• Dataset encryption:Customer-managed encryption keys (CMEK)aren't supported when exporting billing data toBigQuery. If you enable CMEK encryption for your billingdata dataset, this type of encryption prevents Cloud Billing fromwriting billing data to the appropriate tables within that dataset. Instead,you need to enable thedataset to use a Google-owned and Google-managed encryption key.

• If you want to useBigQuery row-level security on the table that contains your exported data, you must give theCloud Billing exportservice accountbilling-export-bigquery@system.gserviceaccount.comfull access to the tableusing the BigQueryTRUE filter.The following command grants access to the Cloud Billing service account:

  CREATE ROW ACCESS POLICY cloud_billing_export_policyON `__project_id__.__dataset_id__.__table_id__`GRANT TO ('serviceAccount:billing-export-bigquery@system.gserviceaccount.com')FILTER USING (TRUE);

• Resource-level Tags might take up to an hour to propagate toBigQuery exports. If a tag was added or removedwithin an hour, or if a resource has existed for less than an hour, it mightnot appear in the export.

  Resource-level tags are available for the following resources:

  • Compute Engine instances
  • Spanner instances
  • Cloud Run services
  • Artifact Registry repositories

Related topics

Topics related to exported Cloud Billing data

• Understanding the Cloud Billing data tables in BigQuery
• Example queries for Cloud Billing data export to BigQuery
• Visualize spend over time with Looker Studio

Cost and pricing reports available in the Google Cloud console

• View your Cloud Billing reports and cost trends
• View and download the cost details of your invoice or statement
• View and download prices for Google's cloud services
• Understand your savings with cost breakdown reports
• Analyze the effectiveness of your committed use discounts
• View your cost and payment history