By Emma Zhang and Matt Leonard, Product Managers
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, we are excited to announce the general availability of billing export to BigQuery, our data warehouse service, 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 our 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:
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.
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.
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`;
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.
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
Here at Google Cloud, we’re all about making your cloud costs as transparent and predictable as possible. To learn more about billing export to BigQuery, check out the documentation, and let us know how else we can help you understand your bill, by sending us feedback.