SPEAKER: These days, just about every person in your business
asks questions about your data.
And the faster they can get answers to those questions,
the more efficiently they can do their jobs.
Not only can optimizing SQL queries
keep all your data consumers happy,
but it can also save you money.
In this video, we're going to talk through some strategies
for optimizing your BigQuery queries.
[MUSIC PLAYING]
In our last episode, we covered query execution,
including several tools and concepts
that provide the foundation for many of the optimization
techniques we're highlighting today.
If you haven't already, I highly recommend
taking a pause to watch it before diving into this one.
All right.
Onto the strategies.
One of the most straightforward ways to optimize a query
is to make sure that you limit the number of bytes
it needs to scan.
The first thing you want to do is
ensure you are only selecting the columns that you really
need.
That means no SELECT *.
Since BigQuery uses columnar storage,
the data scanned is proportional to the number
of columns used in the query.
If you want to take a peek at the first few rows of the data
to get a feel for what you're working with,
use the Preview Table feature instead.
The next thing you can do is leverage partitioning
and clustering to automatically prune out
data you don't need in your query,
like we talked about in our video on BigQuery storage.
This includes leveraging filters or WHERE
clauses on columns used to partition
or cluster the table itself.
All right.
So you went over some strategies for minimizing
the data scanned in the query.
The next thing we can look closer at is aggregations.
To understand how to optimize aggregation queries,
let's look at how BigQuery actually
executes a query that uses a GROUP BY clause.
First, the data is read from our storage system,
and GROUP BYs are done on individual slots.
Then, the results from each slot are
bucketed using a hash function, such that data
from the same key ends up on the same slot.
We often refer to this as shuffling.
So in this query, all partial aggregations for the same wiki
title end up in the same slot.
And we can see the shuffle step happening in the second stage
of the execution details.
Further aggregation occurs, and the final result
is then passed along to the last slot,
for which limits the results.
Since this requires a few different aggregation steps,
GROUP BY queries can be costly.
For this reason, you want to ensure
that you aggregate as late and as seldom as possible.
This can come into play for queries that have sub-SELECTs.
You'll want to move your aggregation functions up
to the top to avoid multiple GROUP BY executions.
But there is an exception.
If a table can be reduced drastically
by aggregating in preparation for being joined,
then aggregate it early.
Another thing you can do to avoid using GROUP BYs
is to nest repeated data.
For example, you might have a transactions table
that has information about the order,
including the items that were purchased.
If you use flat denormalization, you
would have one row for each ordered item.
Instead, you can create a repeated column that contains
a list of all the items.
Now if you want to calculate the number of items in each order,
you can use ARRAY_LENGTH and avoid doing any GROUP BYs.
Great.
So now you have some tactics for aggregations.
But what about JOINs?
Let's begin by looking at how BigQuery executes
different types of JOINs.
If you join two large tables together,
BigQuery favors what's called a hash JOIN, or a shuffle JOIN.
Similar to the aggregation we discussed
before, BigQuery uses a hash function
to shuffle both the left and the right tables
so that the matching keys end up on the same worker,
and then the data can be joined locally.
On the other hand, if one of your tables
is small enough to fit in memory,
it's not necessary to shuffle large amounts of data.
In a small JOIN, or broadcast JOIN,
BigQuery simply sends the small table
to every single node that's processing the larger table.
Now that you have a feel for how BigQuery executes joins,
let's consider how you can make queries
that join multiple tables as efficient as possible.
First off, you want to make sure you place the largest
table first in the JOIN query, followed by the smallest,
and then by decreasing size.
Next, WHERE clauses should be executed as soon as possible so
that the slots for forming the JOIN
are working with the least amount of data.
You also want to make sure we're using filters on both tables,
like in this example here.
This way, you're ensuring data you don't need
is being eliminated from both sides of the JOIN.
Finally, you can cluster or partition tables
based on common JOIN keys, because less data is scanned
and the query will execute faster.
And actually, clustering improves the efficiency
even more than partitioning.
So keep this in mind when deciding on how
to structure your tables.
One last way to optimize queries is by tweaking the way
you do filtering and ordering.
Something you may not realize is that when
you are using a WHERE clause, BigQuery
assumes that you have chosen the best order for your filters,
meaning the first part of your WHERE clause
should always contain a filter that will eliminate the most
data.
Next, if you're using an ORDER BY statement,
you may run into a Resources Exceeded error.
This is because the final sorting for your query
must be done on a single slot.
And if you're attempting to order a very large result set,
the final sorting can overwhelm the slot
that's processing the data.
One way you can solve this is by making sure that you use LIMIT,
so that the result set is easier to manage.
Phew!
OK, we were able to cover a lot of great techniques
to make your queries more performant.
More detail on what we discussed here,
plus even more optimization levers,
are covered in the links below.
So be sure to check them out.
Thanks for joining us today, and watch out
for our next episode of "BigQuery Spotlight."
Oh, and remember, stay curious.
[MUSIC PLAYING]