Understand the Cloud Billing data tables in BigQuery (original) (raw)

This document provides reference information for the schema of the Cloud Billing data that's exported to each table in BigQuery.

Shortly afterenabling Cloud Billing export to BigQuery, billing data tables are automatically created in the BigQuery dataset.

• FOCUS usage cost table (Preview)– Google provides an immutable BigQuery dataset named: gcp_billing_immutable_<BILLING_ACCOUNT_ID>_<Location>and a FOCUS data table named:gcp_billing_export_focus_<BILLING_ACCOUNT_ID>.
• Standard usage cost table– In your BigQuery dataset, this table is namedgcp_billing_export_v1_<BILLING_ACCOUNT_ID>.
• Detailed usage cost table– This includes all of the data fields from the standard usage cost table, along with additional fields that provide resource-level cost data, such as a virtual machine or SSD that generates service usage. In your BigQuery dataset, this table is namedgcp_billing_export_resource_v1_<BILLING_ACCOUNT_ID>.
• Pricing data table– In your BigQuery dataset, this table name iscloud_pricing_export.
• Committed use discounts (CUD) metadata (Preview)– This includes CUD metadata such as billing account ID, product ID, consumption model ID, commitment amount units and values, and more. In your BigQuery dataset, this table name is cud_subscriptions_export.

Frequency of data loads

FOCUS, standard, and detailed usage cost export

• When you first enable the FOCUS, standard, or detailed usage cost export to BigQuery, it might take a few hours to start seeing Google Cloud cost data propagate to your BigQuery dataset. Data is exported in chronological order. If your export isconfigured to include data retroactivelyfor the current and previous month, it might take up to five days for the exported data to fully catch up to your current usage data.
• Google Cloud services report usage and cost data to Cloud Billing processes at varying intervals.
• Cloud Billing exports usage and cost data to your BigQuery dataset at regular intervals (there are no delivery or latency guarantees for the export to BigQuery).
• Because the usage reporting frequency varies by Google Cloud service, you might not see Cloud Billing usage and cost data right away for all recently used Google Cloud services.

Pricing and CUD metadata export

• When you first enable the pricing export to BigQuery, it might take up to 48 hours to start seeing your Google Cloud pricing data.
• After you enable pricing export, the pricing data applicable to your Cloud Billing account is exported to BigQuery once each day.

Data availability

• For customers who enable the FOCUS, standard, or detailed usage cost data export for the first time:
  • If you configure yourBigQuery datasetto use amulti-region location(EU or US), Cloud Billing data is exported retroactively from the start of the previous month. For example, if you enable FOCUS usage cost,standard usage cost or detailed usage cost data export on September 23rd, your export will include data beginning August 1st.
    We export data in chronological order, one day at a time, so the data from the first day of the last month is populated first. For the initial backfill of exported data, it might take up to five days for your retroactive Cloud Billing data to finish exporting before you start seeing your most recent usage data.
  • If you configure yourBigQuery datasetto use a supportedregion location, your FOCUS usage cost, standard usage cost, or _detailed usage cost_data is available from the date you enable Cloud Billing export, and after. That is, Google Cloud billing data isn't added retroactively for non-multi-region dataset locations, so you won't see Cloud Billing data from before you enable export.
  • For customers who have enabled, disabled, and subsequently re-enabled the_FOCUS usage cost_, standard usage cost, or detailed usage cost data export, the Cloud Billing data might not be available for the period when export was explicitly disabled.
• If you enable the pricing data export, your BigQuery dataset only reflects Cloud Billing data incurred from the date when you first enabled the pricing export, and after. Cloud Billing pricing data isn't added retroactively, so you won't see pricing data for dates that occurred before you enabled export.
• The data in the BigQuery datasets reflects the state of your resources at the time that the usage was recorded. If you make changes to your resources, such as adding new labels to a project, or changing a project's hierarchy (which would change project.ancestors), those changes are only shown in future exports of usage data. Previously exported records aren't changed.
• If you delete any exported data (such as BigQuery export records), we can't backfill the deleted records.
• If youedit the settings of an exportto update the project or dataset where your exported billing data is stored, data from the previous dataset isn't automatically backfilled into the new dataset; the new BigQuery dataset only reflects Cloud Billing data incurred from the date when you changed the export settings, and after. To include the complete record of exported billing data, you must manually join the new dataset with the previous dataset. For more information, seeJoin operation.
• BigQuery loads areACID compliant, so if you query the BigQuery Cloud Billing export dataset while data is being loaded into it, you won't encounter partially loaded data.
• See other limitationsthat might impact exporting your billing data to BigQuery, such as datasets with customer-managed encryption keys (CMEK) enabled.

Limitations

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

Table schema for the Cloud Billing data exports

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 a BigQuery table schema for a Cloud Billing data export, any queries that directly reference the exported columns might fail. To resolve this, we recommend creatingBigQuery viewsthat query the exported tables and present the information in your preferred structure.

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

The views you create should normalize the data so that all of the relevant tables present the same schema to your queries. This protects you from future schema changes, allowing you to modify the view's underlying query in those instances when the data schema changes.

FOCUS export and data retention

Billing data exported to the Google-provided FOCUS BigQuery export is subject to a 2-year Time To Live (TTL) policy. Data older than two years is automatically deleted from the provided FOCUS export table in the BigQuery dataset. If you need to retain your FOCUS billing data history beyond two years, copy the FOCUS data to a long-term storage table that you own.

BigQuery dataset locations and data availability

BigQuery dataset locations supported for use with Cloud Billing data:

BigQuery datasetsare configured to use alocation; either a multi-region location (EU or US), or a region location. The dataset location is set at creation time. After a dataset is created, its location can't be changed.

Cloud Billing data export supports all multi-region locations (EU or US), but only a subset of region locations. When you're configuring your Cloud Billing export settings, if you create or select a dataset that's configured to use an unsupported region location, when you attempt to save your export settings, you'll see an_Invalid dataset region_ error.

The following table lists themulti-region locationsand the region locationsthat are supported for use with BigQuery datasets that contain Cloud Billing data.

Locations and data availability:

• For your BigQuery datasetscontaining FOCUS usage cost data, standard usage cost data, or_detailed usage cost data_, the type of location you configure on the dataset impacts the timing of 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 incurred from the start of the previous month from when you first enabled the export, unless you are re-enabling the export. That is,Google Cloud billing data is added retroactively for the current and previous month. For the initial backfill of exported data, it might take up to five days for your retroactive Cloud Billing data to finish exporting before you start seeing your current usage data.
  • If your dataset is configured to use a supportedregion location, your FOCUS usage cost data, standard usage cost data, and_detailed usage cost data_ only reflect Google Cloud billing data incurred starting from the date you enabled Cloud Billing export, and after. That is, Google Cloud billing data is_not added retroactively_ for non-multi-region dataset locations, so you won't see Cloud Billing data from before you enable export.
  • For more details, seeData availability.
• YourBigQuery datasetscontaining pricing data only collect Google Cloud billing data incurred from the date you set up Cloud Billing export, and after. That is, Google Cloud pricing data isn't added retroactively, so you won't see Cloud Billing pricing data from before you enable export. For more details, seeData availability.

Disable, re-enable, or edit export settings

If youdisable,re-enable, or edit your export settingsto update the project or dataset where your exported billing data is stored, previously exported billing data isn't backfilled to your new dataset. To include the billing information that was exported prior to switching to a different project or dataset, you must manually join the new dataset with the previous dataset. For more information, seeJoin operation.

Dataset encryption

BigQuery datasetsare configured to useencryption at restby setting an encryption key option when you create the dataset – either a Google-owned and Google-managed encryption key, or a customer-managed encryption key (CMEK). The dataset encryption option can only be set at creation time. After a dataset is created, its encryption setting can't be changed.

If you already enabled the Cloud Billing data export to BigQuery, and your dataset is configured to use a Google-managed encryption key, you can use the bq command-line tool to enable CMEK on each table in the dataset. BigQuery will automatically use thedataset default keyto protect the export tables, unless you configure it otherwise.

Learn about enabling CMEK for BigQuery tables.

Row level security

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

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

FOCUS or detailed usage cost data and GKE

When exporting FOCUS usage cost data or detailed usage cost data, the export automatically includes resource-level information about Compute Engine and other services. However, for Google Kubernetes Engine (GKE) data, to view a breakdown of GKE cluster costs in a FOCUS or detailed data export, you must alsoenable cost allocation for GKE.

Resource-level tags

Resource-level Tagsmight take up to an hour to propagate to BigQuery exports. If a tag was added or removed within an hour, or if a resource has existed for less than an hour, it might not appear in the export.

Resource-level tags are available for several resources in thestandard usage cost exportand thedetailed usage cost export.

• Set up Cloud Billing data export to BigQuery
• Set up FOCUS export to BigQuery
• FOCUS usage cost table
• Standard usage cost table
• Detailed usage cost table
• Pricing data table
• 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