LEIGHA JARETT: Over the past few "BigQuery Spotlight" episodes,
we've walked you through the fundamentals of BigQuery
to help you get started architecting and administering
your warehouse.
Today, we're going beyond that initial setup
and talking about how you can ensure that your BigQuery
environment stays cost effective, performant,
and secure through ongoing monitoring.
[MUSIC PLAYING]
Monitoring your data warehouse on a continuous basis
is important for every organization.
Let's walk through a few key use cases
so that you can understand the different data sources
available to monitor and the types of questions you
might be asking for each one.
First up, cost optimization.
One of the ways you can monitor BigQuery spend
and Google Cloud spend in general
is to leverage the billing reports.
You can analyze billing data directly in the Google Cloud
Console or export it back into BigQuery
to join it against other data sets
and create your own custom reports.
Let's jump into the Cloud Console to take a look.
In the Reports tab of the Billing page,
we can see a chart with our cost per a particular billing
account broken down by project, service, SKU, or even label
keys.
Focusing on BigQuery services, there
are a few different actions you might
want to take using this data.
First off, you can see how much money
is being spent on BigQuery across different teams
or projects and use this to set up budgets and notifications so
that you or others on your team know if spend is getting out
of hand.
Or you can even set up custom quotas for BigQuery
so that specific users or projects
are limited in the amount of data they can process.
Next, you might want to dig into the storage SKUs
to look at a breakdown of active versus long-term storage.
In case you don't already know, BigQuery charges half the price
for data that has not been modified in 90 days.
This includes entire tables or specific partitions
within a table.
So this information may be able to help
you modify the way you update data or narrow down
tables that could be partitioned to take advantage
of long-term storage.
Another SKU you may see in the billing data
is for streaming inserts.
While BigQuery charges for streaming inserts,
batch loading is free.
So if data being streamed into BigQuery
doesn't need to be consumed right away,
you might consider editing your loading process.
Finally, the billing reports can be
super helpful for pinpointing projects that
have spikes in analysis cost.
Here, analysis means on-demand querying,
where you're charged for the amount of bytes scanned.
There might be an opportunity to apply a slot reservation
to these projects to save costs and ensure predictable pricing.
If you're not familiar with BigQuery reservation
or BigQuery pricing model, I highly
recommend that you go and visit our "Jobs and Reservations"
video linked below.
Now, if you do decide to go with flat-rate pricing,
you'll need to figure out what reservation size makes
the most sense for your needs.
This brings us to our second use case--
workload management and performance monitoring.
You can see how many slots were consumed
across different workloads in your organization
using the Cloud Monitoring interface or the information
schema tables natively available in BigQuery.
For example, if your historical slot consumptions
looks like this, you may want to find the baseline
and purchase a monthly or annual slot
commitment for that amount, in this case, around 500 slots.
For queries that are causing those peaks,
you could choose to purchase a flex-slot commitment,
run the query, and then cancel the commitment.
And as a reminder, the number of available slots
is kind of like a measure of throughput.
This means that if less slots are available,
the query will likely just run slower.
You can always test out query performance
with different number of slots to find the size that
works best for your needs.
Besides just determining the size of your reservation,
you may also want to use Cloud Monitoring or the information
schema to monitor job performance.
And actually, if you're using slot reservations already,
you can also use the new BigQuery Admin Resource Charts.
Let's take a look.
This data can help you pinpoint queries
that should be optimized, like this scheduled query that
seems to be consuming a lot of resources.
With the job ID, you can even get the execution details
and use some of the optimization techniques
we covered in our previous video to figure out
if you can rewrite the SQL to be more efficient.
You might also want to dig into job concurrency
to rearrange scheduling for optimal slot utilization
or fail jobs to help troubleshoot
any ongoing issues.
OK, great.
So we've reviewed some ways to save money and optimize
performance.
Now let's talk about how you can audit data sharing and access
using Cloud Logging, which allows you to analyze system
and application log data.
And just like our other data sources,
logs can be analyzed in the console
or exported into BigQuery.
In one of our previous videos, we
talked about data governance.
You might remember that we use data catalog to develop policy
tags for controlling access.
In Cloud Logging, we can do things
like find the email address for the principle that
grants or removes access on a policy tag
or the email address for whom the access was granted
or removed.
You can also directly see what user identities have queried
which data sources, to ensure that all access policies are
correct and no one has access to data that they shouldn't.
As you can see, monitoring is essential to making
informed decisions on optimizing your BigQuery data warehouse.
As a quick review, you can use the billing data
to create budgets, custom quotas,
and dig into areas where you can reduce costs
To size reservations, improve performance, and troubleshoot
errors, you can use Cloud Monitoring or the information
schema, or if you're a reservation
customer, the new BigQuery Admin Resource Charts.
Finally, you can use Cloud Logging
to review granular log data, which
also might be useful for troubleshooting
and to monitor data access policies.
The great part of monitoring in BigQuery
is that you have the flexibility to export any of these data
sources back into your data warehouse for custom reporting.
In fact, we've even built a series of Data Studio
and Looker templates to accelerate
any additional analysis you want to do
on top of billing, information schema, or log data.
Make sure to check out the links below for details,
and follow me on social to get more BigQuery tips and tricks
Oh, and remember, stay curious.
[MUSIC PLAYING]