The flexibility and scalability of the cloud means that your usage can fluctuate dramatically from day to day with demand. And while you always pay only for what you use, customers often ask us to help them better understand their bill.
A prerequisite for understanding your bill is better access to detailed usage and billing data. So today, Google has announced the general availability of billing export to BigQuery, enabling a more granular and timely view into your GCP costs than ever before.
Billing export to BigQuery is a new and improved version of Google’s existing billing export to CSV/JSON files, and like the name implies, exports your cloud usage data directly into a BigQuery dataset. Once the data is there, you can write simple SQL queries in BigQuery, visualize your data in Data Studio, or programmatically export the data into other tools to analyze your spend.
New billing data is exported automatically into the dataset as it becomes available– usually multiple times per day. BigQuery billing export also contains a few new features to help you organize your data:
- User labels to categorize and track costs
- Additional product metadata to organize by GCP services:
- Service description
- Service category
- SKU ID to uniquely identify each resource type
- Export time to help organize cost by invoice
Getting started with billing export to BigQuery
It’s easy to export billing data into BigQuery and start to analyze it. The first step is to enable the export, which begins to build your billing dataset, following these setup instructions. Note that you need Billing Admin permissions in GCP to enable export so check you have the appropriate permissions or work with your Billing Admin.
Once you have billing export set up, the data will automatically start being populated within a few hours. Your BigQuery dataset will continue to automatically update as new data is available.
NOTE: Your BigQuery dataset only reflects costs incurred from the date you set up billing export; we will not backfill billing data at this time. While our existing CSV and JSON export features continue to remain available in their current format, we strongly encourage you to enable billing export to BigQuery as early as possible to build out your billing dataset, and to take advantage of the more granular cost analysis it allows.
Querying the billing export data
Now that you’ve populated your dataset, you can start the fun part–data analysis. You can export the full dataset, complete with new elements such as user labels, or write queries against the data to answer specific questions. Here are a couple of simple examples of how you might use BigQuery queries on exported billing data.
Query every row without grouping
The most granular view of your billing costs is to query every row without grouping. Assume all fields, except labels and resource types, are the same (project, product, and so on).
SELECT resource_type, TO_JSON_STRING(labels) as labels, cost as cost FROM `project.dataset.table`;
Group by label map as a JSON string
This is a quick and easy way to break down cost by each label combination.
SELECT TO_JSON_STRING(labels) as labels, sum(cost) as cost FROM `project.dataset.table` GROUP BY labels;
You can see more query examples or write your own.
Visualize Spend Over Time with Data Studio
Many business intelligence tools natively integrate with BigQuery as the backend datastore. With Data Studio, you can easily visualize your BigQuery billing data, and with a few clicks set up a dashboard and get up-to-date billing reports throughout the day, using labels to slice and dice your GCP bill.
You can find detailed instructions about how to copy and setup a Data Studio template here: Visualize spend over time with Data Studio
To learn more about billing export to BigQuery, check out the documentation.