Export Cloud Billing Data to BigQuery

Managing and reporting costs effectively is a critical part of financial stewardship, whether you're running a multi-billion-dollar enterprise business or small household budget. Making data-driven decisions about your Google Cloud costs and usage starts with collecting the data you'll need to inform those decisions.

Cloud Billing export to BigQuery enables you to export detailed Google Cloud billing data (such as usage and cost estimate data) automatically throughout the day to a BigQuery dataset that you specify. Then you can access your Cloud Billing data from BigQuery for detailed analysis, or use a tool like Google Data Studio to visualize your data. 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 Cloud Billing export to BigQuery

Required Permissions

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

For more information about Google Cloud permissions, see:

1. Create a BigQuery dataset

Before you enable Cloud Billing export to BigQuery, we recommend you create a BigQuery dataset first.

A dataset is contained within a specific Google Cloud project. 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.

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 ( My Project ) at the top of the Google Cloud Console page, select or create a project to contain your dataset.

    Recommendation: We recommend that you configure a separate, single Google Cloud project to contain all of your billing administration needs, including your exported Cloud Billing data. Your billing administration Google Cloud project can also be used for things like Cloud Billing APIs access, Pub/Sub channels for programmatic budget notifications, and so on.

  3. In the BigQuery navigation panel, click your-project-name.

  4. Click CREATE DATASET. The Create dataset panel opens.

    1. Enter a Dataset ID.
    2. Select a Data location.

    3. Select the Default table expiration.

    4. Select the Encryption option. For Cloud Billing export, select Google-managed key.

    5. To save, click Create dataset.

2. Enable Cloud Billing export to the BigQuery dataset

To enable Cloud Billing export to BigQuery, do the following:

  1. Sign in to the Google Cloud Console.

    Sign in to Cloud 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 Google 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.

  5. Click Edit settings to enable export and update the export settings.

  6. From the Project list, select the project where your BigQuery dataset is stored.

  7. From the Billing export dataset list, specify a dataset to export data to.

    Tip: If you don't have a BigQuery dataset created yet, you'll be prompted to create one. If necessary, follow these steps to create a new dataset.

  8. Click Save.

Frequency of data loads

  • When you first enable export to BigQuery, it might take a few hours to start seeing your Google Cloud billing data.
  • Google Cloud services report data to Cloud Billing processes at varying intervals.
  • Cloud Billing data exports 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 data right away for all recently used Google Cloud services.
  • Your BigQuery dataset only reflects Cloud Billing data incurred from the date when you first enabled the export, and after. Cloud Billing data is not added retroactively, so you won't see Cloud Billing data for usage that occurred before you enabled export.
  • If you delete any exported data (such as BigQuery export records), we cannot backfill the deleted records.
  • BigQuery loads are ACID compliant, so if you query the BigQuery Cloud Billing export dataset while data is being loaded into it, you will not encounter partially loaded data. Learn more about BigQuery characteristics.

Usage table

Shortly after enabling Cloud Billing export to BigQuery, the usage table is automatically created.

If you want to consolidate billing data from past configurations or versions of Cloud Billing export to BigQuery, we recommend keeping the data in separate tables and using a UNION query instead .

Dataset permissions

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

When you enable Cloud Billing export to BigQuery, a service account that is owned and managed by Google is added as an owner to the dataset that you specify. The service account looks like this:

  • billing-export-bigquery@system.gserviceaccount.com

The service account provides the permissions that are necessary for our offline process to create a table and write Cloud Billing records to it.

Cost of use

Using BigQuery to store usage data will incur minimal fees. When exporting and analyzing Cloud Billing data with BigQuery, the associated cost will depend on the amount of data you stream, store, and query. To get an idea of what charges to expect, see Estimating storage and query costs. Review BigQuery Pricing for more details.

Google Cloud usage cost data in BigQuery

The following table describes the contents of the Google Cloud usage cost data that is exported to BigQuery.

Field Type Description
billing_account_id String

The Cloud Billing Account ID that the usage is associated with.

For resellers: For usage costs generated by a Cloud Billing subaccount, this is the ID of the subaccount, not the ID of the parent reseller Cloud Billing Account.

invoice.month String

The year and month (YYYYMM) of the invoice that includes the cost line items. For example: "201901" is equivalent to January, 2019.

You can use this field to get the total charges on the invoice. See Cloud Billing Export to BigQuery Query Examples.

cost_type String

The type of cost this line item represents: regular, tax, adjustment, or rounding error.

service.id String The ID of the service that the usage is associated with.
service.description String The Google Cloud service that reported the Cloud Billing data.
sku.id String The ID of the resource used by the service. For the full list of SKUs, see Google Cloud SKUs.
sku.description 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 Timestamp The start time of the hourly usage window within which the given cost was calculated. The usage/costs for all services is displayed with hourly granularity, which means long running service usage is spread across multiple hourly windows.

For more information, see the BigQuery documentation on timestamp data types. See also, Differences between exported data and invoices below.

usage_end_time Timestamp The end time of the hourly usage window within which the given cost was calculated. The usage/costs for all services is displayed with hourly granularity, which means long running service usage is spread across multiple hourly windows.

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 Google Cloud project that generated the Cloud Billing data.
project.name String The name of the Google Cloud project that generated the Cloud Billing data.
project.ancestry_numbers String The ancestors in the resource hierarchy for the project identified by the specified project.id (for example, my-project-123).

For example: /ParentOrgNumber/ParentFolderNumber/. Learn more about the Resource Hierarchy.

project.labels.key String If labels are present, the key portion of the key:value pair that comprises the label on the Google Cloud 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 Google Cloud 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 Google Cloud 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 Google Cloud resource where the usage occurred. For more information about using labels, see Using Labels.
system_labels.key String If system labels are present, the key portion of the key:value pair that comprises the system-generated label on the resource where the usage occurred. See also, Available system labels.
system_labels.value String If system labels are present, the value portion of the key:value pair that comprises the system-generated label on the resource where the usage occurred. See also, Available system labels.
location.location String Location of usage at the level of a country, region, or zone; or global for resources that have no specific location. See also, Regions and Zones for more information.
location.country String When location.location is a country, region, or zone, this field is the country of usage, e.g. US. See also, Regions and Zones for more information.
location.region String When location.location is a country or region, this field is the region of usage, e.g. us-central1. See also, Regions and Zones for more information.
location.zone String When location.location is a zone, this field is the zone of usage, e.g. us-central1-a. See also, Regions and Zones for more information.
cost Float The cost of the usage before any credits. To get the total cost including credits, any credits.amount should be added to cost. See this example query for more information.
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.unit used.
usage.unit String The base unit in which resource usage is measured. For example, the base unit for standard storage is byte-seconds.
usage.amount_in_pricing_units Float The quantity of usage.pricing_unit used.
usage.pricing_unit String The unit in which resource usage is measured, according to the Cloud Billing Catalog API.
credits.name String A description of the credit applied to the Cloud Billing Account.
credits.amount Float The amount of the credit applied to the usage. Credits are always negative amounts.
export_time Timestamp A processing time associated with an append of Cloud Billing 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.

Available system labels

System labels are key-value pairs for important metadata about the resource that generated the usage. The following system labels are automatically included on applicable usage.

system_labels.key Example system_labels.value Description
compute.googleapis.com/machine_spec n1-standard-1, custom-2-2048 Configuration of the virtual machine. See Machine Types for more information.
compute.googleapis.com/cores for n1-standard-4 this is 4; for custom-2-2048 this is 2 The number of vCPUs available to the virtual machine.
compute.googleapis.com/memory for n1-standard-4 this is 15360 (i.e. 15 GB * 1024 MB/GB); for custom-2-2048 this is 2048 The amount of memory (in MB) available to the virtual machine.

Differences between exported data and invoices

Because Google Cloud services report usage and cost data to Cloud Billing processes at varying intervals, sometimes there is a slight delay in usage reporting. At the end of a calendar month, late-reported usage might not be included on that month's invoice and instead might roll over to the next month's invoice. However, that same usage includes a timestamp by actual usage date and time when the usage is reported to Cloud Billing processes.

When you query your costs using timestamp fields, your returned data might pick up late-reported usage that was not originally included on the invoice that was generated for the same usage month. As a result, the Cloud Billing data returned might not map directly to that invoice.

Timestamp fields include:

  • usage_start_time
  • usage_end_time
  • export_time

To return Cloud Billing data that maps directly to an invoice, query on invoice.month instead of timestamp fields.

Errors and adjustments

You should be aware that in the unlikely event that your Cloud Billing data contains an error or requires an adjustment, your Cloud 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.

Var denne side nyttig? Giv os en anmeldelse af den:

Send feedback om...

Har du brug for hjælp? Besøg vores supportside.