This page addresses how to handle the schema changes made on October 28, 2020 to the Cloud Billing data
that is exported to tables in BigQuery.
The table schema for the Cloud Billing standard usage cost data exported to BigQuery has been updated to
provide more clarity with additional data elds. This table is named
in the BigQuery dataset.
The following data elds have been added to the Cloud Billing BigQuery usage export schema:
This data is new as of October 29, 2020, and is not available for data usage recorded before then. Please
update your integrations or automations based on the new schema by performing migrations, if and when
necessary. For information on the data these new elds provide, refer to Understanding Cloud Billing data
tables in BigQuery.(/billing/docs/how-to/export-data-bigquery-tables#standard-usage-cost-data-schema).
Because the table structure for the standard usage cost data export changed, any queries that directly
reference the exported tables no longer provide you with all of the available data. To resolve this, we
recommend creating BigQuery views(/bigquery/docs/views) that 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.
If you need to retain tables that use the previous schema, we recommend creating BigQuery views for those
tables to normalize the data schema. When creating a view to migrate from the previous schema to the new
one, you can use a statement(/bigquery/docs/reference/standard-sql/query-syntax#union) to combine tables
with mismatching schemas. The view you create will depend on the data elds you use in your queries and
dashboards.
One or more of the following examples might apply to your situation, where your queries might or might not use
the new elds and .
1. You use tables that include pre-existing schema properties and new ones, such as ,
, , , and . For an example of how to create
this view, see Creating a view for tables with all elds in the updated schema(#create_view_all_elds).
2. You use tables that do not include the pre-existing schema properties , , and
. For an example of how to create this view, see Creating a view for tables without
, , and (#create_view_without_credits).
3. You use tables that include pre-existing schema properties , , and
but do not include the new schema properties and .
For an example of how to create this view, see Creating a view for tables without and
(#create_view_without_project_and_adjustment).
You can create a view by composing a SQL query that is used to dene the data accessible to the view. For
further details, see Creating a view(/bigquery/docs/views#creating_a_view).
The following are a summary of the steps to create a BigQuery view.
1. Select the query to create the view
2. Run the query and observe the results
3. Save the view
4. Enter the name for the new view
5. Observe the schema of the new view
The following is a query that will create a new view using both the pre-existing and updated schemas. This type
of view limits your exposure to future schema changes.
By using this view for your queries, they will all have the same schema and allow statements to work
successfully. This query preserves the , , , , and
elds and values from the underlying tables.
Standard SQL
The following is a query that will create a new view using tables that do not include the pre-existing schema
properties , , and .
Standard SQL
The following is a query that will create a new view using tables that include the pre-existing schema properties
, , and but do not include the new schema properties
and .
Standard SQL
The following queries allow you to verify that the views you created provide data that is consistent with the
original tables you were querying. The queries use the view created in the example without ,
, and . For details on how to create this view, see Creating a view for tables without
, , and (#create_view_without_credits).
This query provides a row-by-row comparison of cost between the original table and the view created without
, , and .
Standard SQL
This query provides a row by row comparison of credits between the original table and the view created without
, , and .
Standard SQL
Understanding Cloud Billing data tables in BigQuery.(/billing/docs/how-to/export-data-bigquery-tables)
Set up Cloud Billing data export to BigQuery(/billing/docs/how-to/export-data-bigquery-setup)
Example queries for Cloud Billing data export to BigQuery(/billing/docs/how-to/bq-examples)
Visualize spend over time with Data Studio(/billing/docs/how-to/visualize-data)
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License
(https://creativecommons.org/licenses/by/4.0/), and code samples are licensed under the Apache 2.0 License
(https://www.apache.org/licenses/LICENSE-2.0). For details, see the Google Developers Site Policies
(https://developers.google.com/site-policies). Java is a registered trademark of Oracle and/or its aliates.
Last updated 2022-07-25 UTC.