Co-authored by Ryan McDowell, fellow Googler and data analysis ninja
Visualize GCP Billing using BigQuery and Data Studio
One of the most common questions we get from customers is “how can I see what I’m spending over time” and, up until recently, your only option was the Billing tab in your Google Cloud Console.
But now, by combining Google Cloud’s billing-export-to-BigQuery functionality with Google Data Studio, you can not only get up-to-date billing graphs throughout the day, you can use labels to slice and dice your GCP bill according to how you organize your business and/or applications.
If this looks like something you’re interested in, be sure to keep reading below as Ryan and I walk you through an overview of how to get this stuff set up…
SPOILER ALERT: If you’re not really interested in crafting this report by hand, skip on down to the bottom and we’ve got a pre-built report that you can just make a copy of, point at your billing data (you’ll still need to export to BQ), and it should just work out of the box for you. Pretty cool, right?
Enable Billing Export to BigQuery
The first thing we’ll need to do is get your billing data exported into a BigQuery dataset. This will allow us to use the dataset as the foundation for the graphs and queries used to build the billing report above.
Before you get started, though, please keep in mind that exporting your billing data requires Billing Admin permissions in GCP. So, check to make sure that you’ve got permission to make these changes, or work with someone in your organization who does. Otherwise, we won’t get very far…
Ok, so with that sorted out, let’s head on over to the Google Cloud Help Center, and get to work on the setup instructions here: Export billing data to BigQuery
Once you’ve gone through the steps there, it’ll take about a day (but sometimes less) to see the dataset begin to be populated with billing data. GCP services report billing separately, so don’t expect everything to be in there all at the same time. You might see some network egress first, then some VM charges, then PubSub usage, etc. Be patient, it’ll all get in there soon enough!
And once you’ve got data in your dataset, you can run fun queries against it, like so:
IMPORTANT NOTE: Your BigQuery dataset will only reflect billing incurred from the date you set up billing-export, forward. Billing data will NOT be added in retroactively, so you shouldn’t expect to see billing data from before export. We strongly encourage new customers to enable billing export to BigQuery as early as they can, so they have access to as much billing data as possible for analysis…
Get Started with At-a-Glance views
When I open up a report, the first thing I generally want to see is the high-level state of things. Give me a current snapshot of the system, and let me dig deeper from there. So, on the first page of our Billing Report, let’s start by answering some of the most frequently asked questions here:
- What did I spend so far today?
- What have I spent so far this month?
- What’s my Year-To-Date spending look like?
Also, as an added bonus, let’s break down daily & monthly spend by service. That way, we can see if any particular service is spending outside expected levels.
Slice and dice by labels
A very common question we hear from customers is “How can I look at my bill and know what Sevice X is spending?”, or similarly, “What am I spending on my development environments, versus production?”
By applying Labels to your Google Cloud resources, answering these questions becomes a whole lot easier. Labels are key/value pairs that help you organize and manage things in GCP, and they’re super useful when it comes to billing. Common uses of Labels include:
- Labels based on team or cost center. Add labels based on team or cost center to distinguish projects owned by different teams. This can be used in cost accounting or budgeting. For example, team:marketing and team:research.
- Labels based on component. For example, component:redis and component:frontend.
- Labels based on environment or stage. For example, environment:prod and environment:test.
As you’ll see in our example report and dataset, we have a ‘service’ label that we use to organize resources for different apps (very similar to the ‘component’ label described above). With that label in place, the filters on the Resource Billing page will allow us to split out costs for any single service, all services, or any combination of them.
Now, even if you’re only using labels in just the 3 ways described above, you’ll have the ability to answer all sorts of interesting questions. How much does a particular service cost each month? What is all of marketing spending on GCP? What are the research team’s development environment costs? All very useful insights into how your business is spending money on Google Cloud…
Analyze your BigQuery usage
Whether you’re on BigQuery’s flexible on-demand or flat-rate pricing, getting insights into costly queries is essential for optimizing cost and workload performance.
To start doing analysis over BigQuery data, we first need to export the data access logs from Stackdriver. The data access logs are a comprehensive audit of every query run in BigQuery along with the total bytes it scanned. These logs can be used to trace back expensive queries to the user who ran them. To create the export, navigate to the Stackdriver logs in the cloud console. Filter the logs using a filter of resource: BigQuery and logName: data_access.
Once the data_access logs are being exported to BigQuery, you can create a view which cleans up some of the verbose names and only include the columns relevant for the report. This saved query is some example SQL which calculates the total cost of each query and creates friendly names for the report. After creating the view in BigQuery, you’re ready to start hunting down those expensive users. Just create a data source on top of the view in DataStudio and off you go.
Analyze Usage of Other GCP Services
In addition to the BigQuery page that allows you to see BQ-specific usage and billing entries (along with which users are running expensive queries), we’ve added sample pages to the report for many other popular GCP services. Using those purpose-built pages, you can quickly answer questions like “What am I spending on service X?”.
Feel free to use these sample pages and queries as-is, or extend them to answer billing questions specific to your unique business…
Predict the future
So now you’ve got a view into what you’re currently spending, across products and labeled resources, and you can zero in on BigQuery spending. That’s super powerful all by itself, but wouldn’t it be an extra special bonus if you could see what your Google Cloud spending *might* be in the near future, if things keep going the way they are?
Well, once you’ve been exporting billing data into your BigQuery dataset for a few months, you’ll have enough history to look back on, apply some fancy statistical analysis, and use linear regression to help predict future billing!
Two of our colleagues (Vic and Ian) gave a fantastic talk at NEXT ’17 all about Billing, and they saved the best for last. Skip ahead to the 24:40 mark, and Ian will walk you through setting up a custom datasource that uses some fancy SQL to do the future looking math…
Our sample report has this future-predicting functionality built in, but you’ll need to make a few modifications once you copy it and point it at your BigQuery dataset. Mostly, you’ll need to edit the custom SQL, and replace our dataset name with yours…
Don’t Reinvent the Wheel — Use & Extend Our Sample
As promised, everything you’ve seen and read about above is freely available for you to make a copy of and work from. Detailed instructions in the last page of the world-readable report here:
https://datastudio.google.com/open/0B7GT7ZlyzUmCZHFhNDlKVENHYmc
NOTE: As of October 10th 2017, the export-to-BigQuery schema was changed slightly (beta products, amiright?!). While both the Data Studio report and data sources have been updated for the new schema, it might take a little while for past data to trickle into the new dataset, and some of the pages might not look back as far as they previously had (I’m looking at you, Spending Trends).
Got another cool graph/report that you’d like to contribute back here? Post a link and a description in the comments (remember to set permissions to world-viewable!), and we’ll get in touch if it’s a good fit for the sample…
