BigQuery Table Partitioning — A Comprehensive Guide
What is it? Why should you use it? When should you use it? How do you use it?
What is Table Partitioning
In BigQuery data is stored in tables (fine there are some edge cases where this might not be 100% true but for the purposes of this article let’s make this generalization). A “partition” of a table can be visualized as a smaller version of that table (a table segment) filtered down by a specific column. The set of all table partitions is the table, itself.
You may be asking — “Why not just create smaller tables, instead of partitioning one large table?” The answer is simple. If you were to create a new table per partition value, you would end up with 10s, 100s or even 1000s of separate objects, each of which would need to be queried separately. What is you needed data from 20% of those tables? You would need to UNION 10s or 100s of queries together to return the necessary data. AND if you don’t know ahead of time which data you will need (say you are layering a BI tool on top of the data for instance), you will have to UNION all tables together — I hope this is sounding horrible to you.
The good news? Effectively speaking, partitioning handles all of this for you, and comes with a host of performance and cost benefits when used correctly.
Benefits to Using Table Partitioning
Before jumping into How, let;’s first look at Why. There are a ton of benefits correlated to the use of table partitioning in BQ. However, at the end of the day, these benefits really boil down to two top level objectives: Reducing Cost and Increasing Performance when handling large datasets. This goal is felt across the entire set of DML statements. Let’s dive into them here.
Partition Pruning — This benefit will be noticed almost immediately when your SELECT statements take advantage of the partition column in the WHERE clause. Let’s explain this through example. Suppose you have a questions table that is partitioned by a creation_date using time-unit partitioning (more on this later). When you issue the following statement against a non-partitioned table, the execution engine will need to scan the entire table. If that table is 1TB in size, you will be billed for 1TB of data scanned.
-- Data Scanned (Unpartitioned Table) - 1TB
SELECT *
FROM questions
WHERE creation_date = CAST('2018-03-01' AS DATE)When this table is partitioned on the creation_date , the execution engine is able to “prune” the table to the partition(s) needed in order to accurately respond to the query by introspecting the table’s partition map (metadata), eliminating the need to scan the unneeded data partitions. This can dramatically reduce the amount of data scanned — which in turn lowers the cost of the query while simultaneously increasing the performance.
-- Data Scanned (Partitioned Table) - 1GB
SELECT *
FROM questions
WHERE creation_date = CAST('2018-03-01' AS DATE)Partition-Scoped INSERT/ “UPDATE”/ DELETE
Because the partitions created through table partitioning are basically small tables, they can actually be acted on as such. For instance, you can insert directly into a single partition, which makes the insert much more performant.
bq query \
--use_legacy_sql=false \
--destination_table='questions$20180302' \
--append_table=true \
'SELECT * FROM staging.questions'By default this will append to the partition. It is also a common pattern to overwrite the partition with a fresh version of the data. This is a more performant way to achieve the results of an UPDATE, without having to scan any data. Its simple too — just flip the append_table flag to false and the execution engine will overwrite the entire partition with the newly inserted data.
bq query \
--use_legacy_sql=false \
--destination_table='questions$20180302' \
--append_table=false \
'SELECT * FROM staging.questions'A similar technique in BQ SQL takes advantage of procedural language and leverages transactions to assure the operation is atomic in nature.
DECLARE REPLACE_DAY DATE;
BEGIN TRANSACTION;
-- find the partition which we want to replace
SET REPLACE_DAY = CAST('2018-03-02' AS DATE);
-- delete the entire partition from mytable
DELETE FROM prod.questions WHERE creation_date = REPLACE_DAY;
-- insert the new data into the same partition in mytable
INSERT INTO prod.questions
SELECT * FROM staging.questions WHERE creation_date = REPLACE_DAY;
COMMIT TRANSACTION;*** Note: Here we do not specify a partition of the table, but the entire table to insert into. The execution engine is smart enough to realize that this is a partitioned table and inserts intelligently into the single partition. This saves us from having to write dynamic SQL to accomplish this.
Delete statements that “cover” all rows in a partition will simply cause the storage engine to drop the partition from the partition map, leading to the the data being dropped from the table (as expected) without having to scan any data in the table (culminating in a FREE operation). This concept is used above in the transaction example, but I wanted to call this out specifically here as well.
DELETE FROM prod.questions
WHERE creation_date = CAST('2018-03-02' AS DATE);There are some gotcha’s with partition tables, especially when the execution engine is left alone to infer your operation. For more on these specifics see the docs here.
This seems great, faster queries that cost a ton less than they did before — let’s partition every table we have, right? Not quite.
A Word of Caution
Before you go rogue and start partitioning every table you own in pursuit of speed and dollars saved, remember — with partitioned tables — size matters — like a lot.
The partitions of your table should be at least ~10GB in size. This is straight from the docs. While the actual number will vary from table to table based on usage patterns, the general recommendation is that creating a ton of small partitions is a bad idea. Queries across multiple partitions will be slower then if the table was one big heap (unpartitioned). This is because the overhead needed to “stitch” the partitions back together negates any speed improvement that the partitioning was attempting to afford us. So, when you start to think through your partitioning strategy (see below for my process in determining a partitioning strategy), always keep this in mind and, if you can, try to measure estimated partition sizes during your planning stage (again, more on this later).
One other word of caution. Even if you have done the diligence to create the most optimal partitioning strategy for your table, it will mean absolutely nothing if the queries issued against the table are not using it. BigQuery actually offers a way to enforce the use of partition columns in queries against a partitioned table, but this can become very obtrusive to end users if they are not used to working with partitioned tables, so use this power cautiously. I’ll talk more about how to configure your table to enforce the partition usage later on, but first let’s talk strategy.
Choosing a Partitioning Strategy
In order to choose a partitioning strategy, I have created a decision flowchart to follow to help systematize the process. I will break this down into four stages: Table Candidacy, Column Candidacy, Column Elimination, Table Monitoring.
I recognize this is hard to read, so let’s zoom into each section one by one. Feel free to download the full decision map, if you would like to follow along!
Table Candidacy
Although, this first step may seem easy at first glance — there is ambiguity around defining whether a table is big enough to benefit from partitioning. As we will find out later, Google recommends partitions be at least 10GB in size. They do not, however, provide a recommendation for the minimum table size that could benefit from partitioning. Honestly, it really will depend on the partitioning column and the table structure. From my experience, I don’t think you should consider partitioning tables until you hit the 50GB mark (if using integer based partitioning) and 100GB mark (if you are using date/ time partitioning). Even these numbers feel a bit low to me, but I felt it necessary to put something there. If the table is small — just go ahead and create an unpartitioned table and short circuit the rest of this decision process. If the table is large enough that you think it will benefit from parititioning continue on.
Column Candidacy
Now the fun part. At this point we have confirmed the table is large enough that it could benefit from partitioning. Now to choose the column.
Unlike other engines — BigQuery currently only allows for a single column to be used for partitioning. With this in mind, how should we choose the “best” partitioning column for our table? Well first we need to come up with a list of candidate columns. How do you get that list?
Well, if this object currently exists and the usage patterns are being migrated to BQ (with the table) chances are you will have usage logs of the queries running against that table. If you don’t have logs or the object is new, you will need to work backwards from the business need of the table, in conjunction with the rest of the data model to come up with a representative set of queries of which you would expect to be run against this table.
After you have this set of queries you want to see what users are filtering the table in question, on. From the queries, create a list of columns used to filter the table, along with the relative usage of that column compared to the number of queries executed against the table. You want to figure out the most filtered-by columns. The more information you can capture and distill here the better — it will only make our next step easier.
Of this list of columns, it is likely safe to trim it down to 3–5 columns prior to moving to the next step (you’ll probably see 3–5 columns used as filter criteria for the majority of queries unless it is an extremely wide table).
Column Elimination
Now that you have a list of candidate columns and an idea of the most prevalent ones, we can start to work through them.
First — we need to understand if the most used column is a date/ time field representing the time the record arrived at the table (a lot of times these are event tables, IOT/ sensor tables/ etc.). If this is true, we will work through the Partition Column Candidate Questions to attempt to eliminate this column first, but 9/10 times if there is a column that fits this criteria it will be used to partition the table.
If we do not have a column that fits this criteria or this/ these column(s) are eliminated by the questions we will move on to the second most popular column type for partitioning — a time-unit field representing a natural property of the record (event date, purchase date, return date, etc.). If a column matching this criteria is in your list, it is time to take that column through the Partition Column Candidate Questions (see below).
If we do not have a column that fits this criteria or this/ these column(s) are eliminated by the questions we can move onto the final column type numeric columns (or categorical columns having low cardinality that can be mapped to a numeric column via a static lookup). Same thing, pick out these columns and run them through the Partition Column Candidate Questions.
If you have made it through all columns on your list and have eliminated each of them using the Partition Column Candidate Questions then it is time to build the table as a heap and look into Clustering (more on that in the next article).
Let’s look deeper into these Partition Column Candidate Questions.
Partition Column Candidate Questions
(If the answer to any of the questions is No/ Incorrect then the column should be eliminated as a potential partitioning column)
- Given the column type, the associated granularity is low enough to accommodate your needs? (Do you need minute granularity for instance? I hope not, but it’s a question that needs answered.)
- Given the column type, and the granularity you are envisioning using, does the number of partitions created stay below the limits on partitioned tables?
- There are no frequent (every few minutes) upstream ingestion processes that feed this table, which would update/ rewrite most partitions in the table. Correct? (If you are re-writing the whole table so frequently — the cost of keeping the partition map up to date may eliminate the efficiency gains afforded by partitioning on that column.)
- The partitions created when the candidate column (at the candidate granularity) is used, are not less than 10GB in size. (measure this) Correct?
Assuming you have gone through this process and have a column in mind for partitioning it is now time to actually create the partitioned table. For steps to do that see below section Implementing — New Table. For now, I want to talk about what to do AFTER you have created the table and it is being used by your users.
Table Monitoring
Once you have created and hydrated your partitioned table it is essential to monitor the table (like your other tables) for operational issues or improvement opportunities. In order to do this, for partitioned tables especially I have found it helpful to monitor two aspects closely — Query Patterns and Partition Size.
Monitoring Query Patterns
I really like this part because there are a lot of interesting techniques you can use to do this. Techniques aside — you need a solid backbone for capturing query logs. For that — I continually turn to this method described (and implemented) by the Reddit Data Platform Engineering Team. I suggest you give it a read, but in summary they leverage a Log Router to persist query logs to BigQuery and a custom view(s) to normalize the logs into a format that is more digest-able. If you are not doing something similar today, I recommend you look into this. From there, you can be as creative as you would like when it comes to “classifying” usage patterns. At the very least a repeatable way to confirm where clauses against the table are leveraging the partition column is mandatory.
Monitor Partition Size
Now that partitioning is enabled on this table, the dry-run of a query that covers a single partition will return the size of the partition. Script that out against all your partitioned tables, write results to a table, and wrap monitoring/ alerting and analytics around it. With this in place you’ll ensure partitions are remaining within appropriate size bounds and you will gain the added benefit of a table quality check you can use as part of your broader platform QA process(es).
Implementing — New Table
Okay — so how do we actually do this?
Creating an Empty Table (to be later loaded)
The syntax for creating a partition table is exactly the same as creating a heap table, with the addition of a PARTITION BY clause after the column definitions — it’s that simple. The below will create our questions table with a time based partition of the creation_date field at the granularity of a month.
CREATE TABLE
prod.questions (creation_date TIMESTAMP, title STRING, tags STRING)
PARTITION BY
DATE_TRUNC(creation_date, MONTH)Creating a Table Based on a Query Result
You are also able to leverage the PARTITION BY clause when you create a table using theCREATE TABLE AS (CTAS) command.
CREATE TABLE
prod.questions (creation_date TIMESTAMP, title STRING, tags STRING)
PARTITION BY
DATE_TRUNC(creation_date, MONTH)
AS (
SELECT
creation_date, title, tags
FROM
prod.questions_heap
);Google provides a great set of examples, spanning all partition types and execution environments in their docs here, so I reccomend you check that out!
Implementing — Existing Table
Now, say you have an existing table prod.questions which is already fielding the query workload for your environment. You decide you would like to partition this table (after following the design flow above). How do you go about doing this ensuring:
- The table name stays the same.
- The downtime/ query failure rate is as minimal as possible during the process.
This isn’t as straightforward as you would think — but there are various ways to accomplish this. A simple way to do this — that doesn't required GCS or other tools is to execute the below statements serially.
-- CREATE THE "NEW" TABLE FROM THE EXISTING TABLE RECORDS
-- THIS MAY TAKE SOME TIME
CREATE TABLE
prod.questions_TEMP (creation_date TIMESTAMP, title STRING, tags STRING)
PARTITION BY
DATE_TRUNC(creation_date, MONTH)
AS (
SELECT
creation_date, title, tags
FROM
prod.questions
);
-- ENSURE YOU TRANSFER ALL PERMISSIONS OVER TO THE NEW TABLE
-- RENAME THE EXISTING TABLE
ALTER TABLE prod.questions RENAME TO questions_old;
-- RENAME THE "NEW" TABLE
ALTER TABLE prod.questions_TEMP RENAME TO questions;
-- CONFIRM / QA DATA
-- DELETE THE OLD TABLE
DROP TABLE prod.questions_old;A few things to think about:
- You will need to watch permissions as a “new” object will be swapped in for the current object.
- There will also be some time, during the two renames, where the table will be in various states of availability.
- There will be some time — up to you how long — they you will be paying for multiple copies of the data.
BONUS: Implement Forced Partition Utilization
Now, sometimes the carrot doesn’t work as well as you would of hoped and you are forced to turn to the stick. Well, the stick, in terms of BigQuery partitioned tables is an option called require_partition_filter . When that option is set to TRUE for a partitioned table, if a query is executed that does not specify the partition column in the WHERE clause the query fails with the below error.
Again, not the best customer experience — but this is a sure fire way to keep large table scans in check!
Taking this one step further — Clustering
In addition to partitioning, BigQuery also offers another mechanism for tuning storage structure of your tables — Clustering. Look out for the next article where ill dive deep on it.
Until, next time… ☟
