LEIGHA JARETT: Just like a Ferrari, every part of BigQuery
was designed to make your queries run fast.
And that includes BigQuery's managed storage.
In today's episode, we're going under the hood
to figure out exactly how BigQuery stores this data so
that you can make informed decisions on how to optimize
your BigQuery storage.
[MUSIC PLAYING]
When talking about BigQuery storage,
there are a few things I always like to point out.
First off, BigQuery offers fully managed storage,
meaning you don't have to worry about provisioning servers
or worry about configuring any hardware.
All sizing is done automatically and you only
pay for what you use.
Next, BigQuery uses what we call columnar datastore.
Taking a step back for a moment, traditional relational
databases, like Postgres and MySQL,
store data row by row in record-oriented storage.
This makes them really good at transactional updates,
for example, changing the status of an order
because they only need to open up a single row
to read or write data.
However, if you want to perform an aggregation like summing up
the Sale Price column to get your total revenue,
you would need to read the entire table into memory.
BigQuery, on the other hand, uses columnar storage,
where data is co-located by column rather than row.
This makes BigQuery an ideal solution for analytical use
cases.
Now, to calculate the total revenue,
BigQuery will only need to read in the Sale Price column.
Internally, BigQuery stores data in a proprietary column format
called Capacitor.
One really cool thing about Capacitor
is that it uses relevant factors like the type of data
and how the data is being used to reshuffle rows and encode
columns.
One example is run-length encoding.
Let's pretend that the value 1 appears three
times in a row in your data.
Rather than storing 1, 1, 1, Capacitor
can instead store 1:3.
But what if the rows are ordered such
that there are no long runs of the same value?
Well, to solve this, Capacitor simply
reorders the rows to obtain a compact encoding.
So now we know about BigQuery's file format.
But where do these files actually get saved?
The persistence layer for BigQuery
is provided by the distributed file system throughout Google--
Colossus.
Colossus has a ton of disks that live on a ton of servers.
But when you have so many, chances
are that dozens of disks are going to fail each day.
So the goal is to make sure that no data is lost.
Colossus ensures durability by using something
called erasure encoding, which breaks data into fragments
and saves redundant pieces across
a set of different disks.
However, to ensure the data is both durable and available,
the data is also replicated to another availability zone
within the same region that was designated
when you created your data set.
This means data is saved in a bunch of different buildings
that has a different power system and network.
The chances of multiple availability zones going
offline at once is very small.
But if you use multi-region locations,
like the US or the EU, BigQuery stores another copy
of the data in an off-region replica.
That way, the data is recoverable in the event
of a major disaster.
Aside from replication, Colossus also
protects your data by ensuring that it
is 100% encrypted at rest.
Plus, if you want to control encryption yourself,
you can use customer-managed encryption keys.
And the great thing about all of this
is that encoding, encryption and replication
are included in the price of BigQuery storage.
So no hidden costs.
So far, we've talked about the format
in which BigQuery stores data.
And we've talked about the physical file system
where the data is stored.
But how do we ensure that data is
stored in a way that makes our queries as performant
as possible?
BigQuery has a storage optimizer that helps arrange data
into the optimal shape for querying
by periodically rewriting files.
Files may be written first in a format that
is fast to write to.
But later, BigQuery will format them
in a way that's fast to query.
Aside from the optimization happening behind the scenes,
there are also a few things that you can
do to further enhance storage.
First off, BigQuery allows you to partition your table
when it's created.
Partitioning basically allows you to divide large tables
into smaller chunks or partitions, which
are stored in a physically separate location.
When you include a "where" clause in your query,
BigQuery will only need to read in the partitions
that you actually need.
BigQuery allows you to partition data based on the time
it was ingested--
a date column with granularity as low as hourly,
or an integer column.
When deciding on partitions, you should
consider how people at your organization
are using this data.
For example, if data consumers are most often
looking at orders placed in the past week,
it might make sense to partition your table by order date
so that you can efficiently read data just
from the past seven days.
Partitions are designed for places
where there is a large amount of data
and a low number of distinct values.
A good rule of thumb is making sure partitions
are greater than 1 gigabyte.
If you over partition your tables,
you'll create a lot of metadata, which
means that reading in lots of partitions
may actually slow down your query.
If you need higher cardinality or you have a smaller table,
you should use clustering.
Clustering is like sorting your tables
on a particular set of columns.
For example, clustering our table by customer
ID would sort the rows based on the customer ID.
In BigQuery, you're able to choose up
to four columns to cluster on.
This allows for efficient lookouts
because the query engine needs to open files
that have the cluster key.
For example, if we query our table
and include a filter to only look
at orders placed by customer number one,
then we only need to open up this file.
The tricky part of clustering is maintaining the clusters
when the data is changing.
But BigQuery solves this by periodically
reclustering each table.
This happens automatically without any user intervention
or additional cost.
Partitioning and clustering are often used together.
We can partition by the low cardinality field, for example,
the created_at date of the order,
and cluster by the high cardinality one,
like customer_id.
This lets you operate over a date range slice of the table,
but it also lets you find records
from a particular customer without having to scan
all the data in the partition.
As with partitioning, you should always
try and figure out the types of questions
users will be asking of the data to decide the keys that you'll
use to cluster.
Want to learn more about storage optimization?
Check out the links below.
Thanks for tuning into this episode
of "BigQuery Spotlight."
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]