Google BigQuery is an extremely powerful analytics data warehouse. It allows you to run terabyte-scale queries, getting results in seconds, and gives you the benefits of being a completely fully managed solution. BigQuery and other Google Cloud Platform (GCP) services offers “pay as you go” pricing. Whilst this leads to greater flexibility and can keep costs low relative to other providers, it also means keeping track of all of your costs across your business can get a bit tricky. To help simplify this, we’ve taken logs and billing data from GCP, and surfaced analysis through Looker to give everyone a detailed view of your usage and associated costs.
Stackdriver Logging offers a managed service to track cost and usage of BigQuery across your entire GCP environment. With Stackdriver Logging, you can export these logs to BigQuery for more detailed analysis and reporting. We've included set-up instructions for this at the bottom of this post.
While these logs tend to have a slightly complicated structure - utilising nested and repeated fields in order to fully utilise the power of BigQuery - with the right tools, we can use these logs to get detailed information about BigQuery usage and costs across your enterprise.
To make analysing BigQuery audit data easy, we’ve built a Looker Block to model the logs allowing you to analyse the logs in a simple way, whilst utilising the underlying power of Google BigQuery.
With the Looker Block, you can now easily track BigQuery billing and monitor performance. When combined with cross-project log exports, it becomes a powerful way to manage costs across your business.
The block allows you to:
Using our easy-to-plug-in Looker Block, you can now go away and analyse your BigQuery logs, and export the data anywhere.
In addition to our BigQuery Monitoring Block, we’ve also just released a block for analysing GCP Billing exports. The Google Cloud console allows you to extract monthly reports on your billing account already, but using our Block gives you much more flexible analysis, more granularity, and the ability to combine this data with other sources.
You can use this Block to easily track GCP spend across your many projects, across services, and across labels applied to your GCP resources. As the majority of GCP products are pay as you go services, this provides a simple way to analyse spend, monitor resource usage, and even a projection of your total spend this month, based on current usage levels.
Access the Datatonic Looker Blocks by reaching out to your assigned Looker analyst, or request a Looker demo and trial.
This is just the starting point for analysing your entire GCP environment. Here at Datatonic, we’ve worked many companies to help them monitor and optimise their GCP environment. We have consultants who are experts across the GCP data stack, who can help employ best practices to enable you to save costs, and get the most out of the GCP services. Visit Datatonic to find out more about how we can optimise your analytics stack, or get you starter on GCP.
Let's run through the steps in both the Google Cloud Platform, and in Looker, to setup the logging exports and the Looker block.
Create a BigQuery dataset for the billing and BigQuery audit logs. Go to the Google Cloud Platform console, and select BigQuery, or go to https://bigquery.cloud.google.com/. Click the drop down next to the project name and select Create New Dataset, set a location and click OK.
Optional: We recommend setting up a new GCP Project, purely for this purpose.
To setup a billing export to BigQuery do the following:
Billing data will now be exported to your dataset at regular intervals. The Billing export table is date partitioned, and will incur a small data storage charge.
To set up the BigQuery log export do the following in a project that contains BigQuery:
bigquery.googleapis.com/projects/<project-name>/datasets/<dataset-name>, adding the project and dataset names you created earlier.
If you got a permission error then that is perfectly normal. It is because the project you have set up the export to is different to the project you have set up the logging export in. In this case the Service Account which writes the logs into the BigQuery dataset you have created will not have permission to do so. Follow the steps below to complete the setup:
The BigQuery audit log export should now be set up. The table will be updated throughout the day. The BigQuery audit log table is date sharded rather than date partitioned.
If you have more than one project using BigQuery, repeat the steps above. All logs from different projects will be added to the same table, allowing easy querying across projects.
Alternatively, if you have the Google Cloud SDK installed, you can set up the BigQuery logging using the following command (make sure you in the project you want to set up the logging for by running
gcloud config set project <project-name>)
gcloud beta logging sinks create <sink_name> bigquery.googleapis.com/projects/<project-name>/datasets/<dataset-name> --log-filter='resource.type="bigquery_resource"'
This block requires almost no configuration once added to your Looker instance. We will only need to change the billing export table name:
You should now be ready to start monitoring your BigQuery and GCP usage.