Monitoring BigQuery usage and reports costs with Data Studio

Monitoring BigQuery usage and reports with Data Studio

We all use BigQuery to generate reports that makes sense and useful to take better decisions in our business. But have you wondered about how much we are spending and how we are spending for each report we are generating. In this blog we’ll help you to visualize the costs incurred by using BigQuery for generating reports in two ways

1. Costs Incurred by User
2. Cost incurred per report

we used standard Google Ads template from Google Data Studio

Before going into the reciepe we need all necessary ingredients. Here we need to have. GCP aacount, BigQuery empty data and Data Studio.

If you’ve all these at your fingertips now let’s start with our first step

1. Go to Logging and head towards Log router
2. Now we need to create a sink
3. While creating Sink you need to specify sink description and destination
4. You need to select a empty BigQuery dataset in destination

Here i’ve created and a dataset named monitoring_db

5. In next step you need to choose what logs to include in sink

6. If you want to filter out specific logs you can opt it in the next step

Creating a log sink

After doing all of this you will get a output of all BigQuery `query_job_completed` and log events from Cloud Audit Log service will start flowing into your BigQuery table. As shown in the below picture

Data flow into the BigQuery dataset

Now we are done with our first step which is making data flow from audit logs to our BigQuery dataset. So it’s time to jump into step 2 which is getting that data ready for visualization

As we have mentioned already in the beginning that we will be visualizing the costs per user and per query. At first we will look at how to monitor the costs per query.

To monitor the cost of a query we need to know how many bits it has processed. Don’t panic about how to get these, all details about bytes and every thing about the query will be availiable in the Log itself.

No.of processed bytes per query

Now we have no.of bytes it’s time to get the cost of the query. For that we just need to simply multiply cost per TB processed * numbers of TB processed.

We have used a custom query to modify and create new columns bq_cost_analysis.sql:

Our custom query looks like the one below:

custom query to process and save data

Now we have proceesed bytes with cost per user and query separately. So we have grouped them both using SQL. And after this grouping takes us to the next part of visualizing the data

Grouping usage by user and query

3. Visualizing the Costs

To visualize the data we have use Google Ads template in DataStudio.

We need to add data to the data studio by connecting into BigQuery and with some custom SQL as shown below

Connecting bigquery with datastudio with SQL

After connnecting the BigQuery with DataStudio using SQL that’s it you’ll get your costs displsyed. Here in below we have added two dashboards one is costs incurred per user and the onther one is costs incurred per query

cost per user

If you felt this blog helped you in monitoring costs for your BigQuery usage at your organization consider following us on medium. If you feel this is useful for someone else please share it with them..

Stay tuned for our next story….