LEIGHA JARETT: If you watched our previous video,
you're already familiar with how BigQuery stores your data.
But how does BigQuery actually process your data
and execute queries?
Understanding what goes on behind the scenes
is key to ensuring your queries will run super fast.
In this video, we'll walk through the life of a query
and show you how to leverage the query plan to troubleshoot
and optimize your SQL.
[MUSIC PLAYING]
Let's begin with an overview of BigQuery's architecture.
In our last video, we talked about Google's distributed file
system that stores BigQuery data, Colossus.
But to actually compute data, BigQuery
uses Dremel, the query engine.
Here, workers or slots, like we discussed in our video
on jobs and the reservation model,
are used to extract data from storage
and perform aggregations.
To increase the performance, each of these slots
does work independently from one another.
Instead of communicating directly with each other,
they use what we call the remote memory shuffle
to store intermediary results.
In BigQuery, each shuffled row can be consumed by slots
as soon as it's created, which makes
it possible to execute distributed
operations in a pipeline.
All of this data movement is facilitated
by Jupiter our super-fast, Google-scale network.
Now that you understand the architecture,
let's see what happens inside of BigQuery when you actually
submit a query to be run.
First off, an API request will be
sent with all the details of the query.
To proceed, there's some level of API processing
that must occur--
for example, authenticating and authorizing the request,
and building and tracking metadata
such as the SQL statement, cloud project, and query parameters.
Next, BigQuery will perform lexing and parsing.
Lexing refers to the process of scanning an array of bytes--
in this case, the raw SQL statement--
and converting that into a series of tokens.
Parsing is the process of consuming those tokens to build
up a syntactical tree representation of the query,
which can be validated and understood
by BigQuery's software.
While lexing and parsing are underway,
BigQuery will also perform catalog resolution.
This basically means that BigQuery
will resolve references to entities like tables, views,
procedures, and functions.
As a more fully-formed picture of the request emerges,
BigQuery begins to build a query plan.
Here, BigQuery will use different techniques
to refactor and improve the query,
including adapting it to run as a set of distributed execution
tasks.
Once BigQuery has the plan in place,
it will work through the query stages in the execution graph.
Besides the slots that perform the work of the query plan
itself, additional slots monitor and direct
the overall progress.
Not only will these slots determine
how the work is queued and executed,
but they can also leverage BigQuery's dynamic planning
capabilities to make on-the-fly adjustments
to the query plan that will further optimize performance.
Finally, when the query has finished executing,
the results will be sent back to you, the user.
Awesome.
So now that you have a picture of what
happens behind the scenes when a query is submitted,
let's go a little bit deeper and see what one of those query
plans actually looks like.
Here, I'm going to run a simple query
to count the number of trips that
began at a station with Broadway in the name against the Citi
Bike data available in BigQuery's public data sets.
In this first stage of the query plan, a set of slots
will access the distributed storage to read in the table.
The input table is actually made up
of a set of input file blocks.
For each input block, a slot is needed to process the file,
read, and filter down the rows.
Each worker then writes one record
into shuffle that contains the partial count for that input
file.
This second stage here reads from those shuffle records
as its input and then sums them together.
It then writes the output file into a single file,
which becomes accessible as the results of the query.
In the console, after you run a query,
you can view the query plan to see this in action.
In the execution details, we can see the average and maximum
times each worker spent in the wait phase, where the engine is
waiting for either slots to become available
or for a previous stage to start writing results that it
can begin consuming.
In the read phase, where the worker is reading data,
either from Colossus or from shuffle.
In the compute phase, where the actual processing
takes place, such as evaluating SQL functions or expressions.
And in the write phase, where the shuffle
output is written in the next stage
or in the final output table.
Using the query execution details,
we can clearly see the different stages of the query plan,
like we showed before, where the first stage includes workers
that read, filter, and aggregate the data,
and then write the results to the shuffle.
And in the second stage, we read from the shuffle,
aggregate the partial results, and then
write out the final results.
In addition to being available in the console,
the query plan information can be retrieved through the API
from the job response or by querying the information
schema.
So what can you actually do with this information?
First off, the query plan and timeline statistics
can help you understand whether certain stages dominate
resource utilization.
For example, a join stage that generates far more output
rows than input rows can indicate an opportunity
to filter early in that query.
Additionally, if you notice a significant difference
between average and maximum time spent by workers,
that could indicate data skew, which results in one worker
having much more data to process than the others.
That can, again, represent an opportunity to filter early.
Or if there is a lot of time spent on CPU tasks,
you may want to consider approximate functions, which
are often within 1% of the exact function.
Armed with your new understanding of how BigQuery
executes queries, you're now ready to dig in and start
troubleshooting your own SQL statements.
For more details on specific strategies
on how to optimize your SQL queries,
stay tuned for our next video.
Thanks for tuning in to this episode
of "BigQuery Spotlight," and remember, stay curious.
[MUSIC PLAYING]