SPEAKER 1: Understanding workload management in BigQuery
is key to making efficient use of system resources
and optimizing performance.
In today's episode of "BigQuery Spotlight,"
we're talking about jobs, the reservation model,
and best practices for managing and controlling
work in BigQuery.
So let's get started.
[MUSIC PLAYING]
A job is an action that BigQuery runs on your behalf.
It can either be a query, extract, load, or copy job.
When you're performing actions in the console--
for example, writing a query against one of your data sets--
BigQuery will go ahead and create a job on your behalf.
Each job has an associated user identity
that created the job, a location or the region
where the job is actually executed,
which must be the same location as the data
that you're using in the job, and a status, either pending,
running, or done.
Because jobs have the potential to take a long time
to complete, BigQuery executes them asynchronously,
meaning each job is run independently
and you can execute multiple jobs at a time
without needing to finish the current job to move on
to the next one.
Since jobs are all doing some computational work,
each job will need to use some compute resources.
In BigQuery, a unit of compute is called a slot,
which is essentially a worker.
A slot is made up of CPU, RAM, and network.
BigQuery automatically calculates how many
slots are required by each job, depending
on the size of the data and the complexity of the logic.
When BigQuery executes a query, it
uses the query plan to execute different aspects
at different stages.
Each stage of the query may actually
use a different number of slots.
If there isn't a slot available, BigQuery
may take longer to finish the query
as it waits for one to free up.
Before we talk about how different jobs consume slots,
let's talk about how you can get access to slots.
As a reminder, BigQuery offers two different pricing models.
First, there's on demand, where you pay for the amount of bites
scanned in query jobs.
In this model, you get 2,000 slots in each BigQuery project.
Alternatively, there's flat rate pricing,
where you purchase a number of dedicated slots
for your workloads.
Flat rate customers purchase slots
in the form of commitments that are either annual,
monthly, or flex, which can be canceled after just 60 seconds.
With the flat rate model, you can
decide the amount of slots you need available
for your team's BigQuery usage, plus
you have predictable pricing.
Once you have purchased a commitment,
you can split that commitment into any number
of reservations.
A reservation is essentially a bucket of slots
that you can allocate to specific jobs.
For example, you might have a BI reservation that
is used for all Business Intelligence queries
and an ELT reservation that is used for all Data
Transformation queries.
Once you have a reservation in place,
you can create an assignment, which
assigns those slots to your whole organization,
a specific folder, or even a single project.
Each level in the resource hierarchy
inherits the assignment from the level
above, unless you override it.
Now what's really cool is that idle slots
are available for other reservations
in your organization to use.
So if no one is loading a dashboard,
the slots in your BI reservation can be used for ELT jobs.
All slots available to these resources
will be distributed fairly among projects and then jobs
within those projects, meaning if there is one query that
is being run in the project, it will have the ability
to use all the available slots.
If there are two queries that are being run,
then each will have access to half of the slots, and so on.
So it's unlikely that one resource-heavy query
will overpower the system and steal resources
from other running queries.
When you create an assignment, you specify the type of job
that the assignment will be used for.
For example, query assignments are used for query jobs.
Pipeline jobs, on the other hand,
are used for extract, copy, and load jobs.
And a quick side note on this, by default, load, copy,
and export jobs are free and use a shared pool of slots.
But BigQuery makes no guarantees on available slots,
which is why you may want to create a pipeline reservation.
When jobs are assigned to a pipeline assignment,
they lose access to that free pool,
so make sure that jobs have enough capacity.
Otherwise, performance could be worse than using the free pool.
Finally, there are also ML_EXTERNAL assignments
for BigQuery machine learning queries
that use external services.
Now let's consider how these resources
fit within the broader BigQuery resource hierarchy.
Commitments, reservations, and assignments
are created within a project, sometimes referred
to as the admin project.
Many organizations choose to purchase all commitments
from a single project to make it easier to monitor.
These resources also live within a specific location.
Commitments will be billed to the admin project,
but these slots are available to be
used by any jobs within the organization.
Reservations can be also applied to the whole organization,
or to any other folder or project, where they can then
be used by jobs that are executed in that same location.
This means that you might have queries
that were run in the us-northeast-1 region using
slots that you purchased, but other queries
in the same project that were run in the us-multiregion
might be running on demand.
Now that you have an understanding
of jobs and the reservation model, let's see it in action.
Inside the BigQuery console, I can purchase slots
from the Reservation page.
Here, I will go ahead and purchase 1,000 flex slots
in the United States multiregion.
Let's go ahead and confirm that.
Great.
Now, once I have my commitment, I'm
able to create a reservation.
Here, I'll create a reservation that
allocates 500 of my slots to handle queries from my Business
Intelligence tool.
With my reservation in place, I can create an assignment
that ties this reservation to my project
where BI queries are run.
Now, when a query is run in this project,
it will use slots in my reservation.
To drill into this further, I can use the job ID
to select the execution details from the information schema.
Here, I can see which reservation it used
and how many slots it used on average throughout the stages
of the query execution.
Look out for an upcoming video on BigQuery monitoring
where we'll go into detail on how you can use the information
schema to understand utilization and make decisions
around reservation sizes.
Amazing.
So now you know all about the BigQuery reservation model.
Today, we talked about the standard BigQuery reservation
model, but you may also hear the term reservation
when digging into BI Engine, BigQuery's in-memory execution
engine that allows for super fast queries.
A good way to differentiate between the two
is that BI Engine Reservation allocates
memory for intelligent caching, while standard reservations
allocate compute, or slots.
If you want to learn more about BigQuery,
check out the rest of our playlist
and don't forget to subscribe to our channel
on YouTube for other great content.
Look out for our next episode of "BigQuery Spotlight,"
and remember, stay curious.
[MUSIC PLAYING]