How to use clustering without a partition key in a Bigquery table?
Currently, you can partition only on a date or a timestamp column in bigquery. But, what if your table doesnt have a date or timestamp column ? Well, its going to do a full table scan. Consider the below example,
SELECT ID,Key FROM
mydataset.mytablewhere ID = 100077113;
(3.3s elapsed, 72.1 GB processed)
How can we use the clustering capabilities in BigQuery without a partition key on the table?
Our example table has 12m rows and 76 GB of data. This table has no timestamp column.
This is how to cluster said table - while creating a fake date column for fake partitioning:
CREATE TABLE `fh-bigquery.public_dump.github_java_clustered` (id STRING, size INT64, content STRING, binary BOOL , copies INT64, sample_repo_name STRING, sample_path STRING , fake_date DATE) PARTITION BY fake_date CLUSTER BY id AS ( SELECT *, DATE('1980-01-01') fake_date FROM `fh-bigquery.github_extracts.contents_java` )
Did it work?
# original table SELECT * FROM `fh-bigquery.github_extracts.contents_java` WHERE id='be26cfc2bd3e21821e4a27ec7796316e8d7fb0f3' (3.3s elapsed, 72.1 GB processed) # clustered table SELECT * FROM `fh-bigquery.public_dump.github_java_clustered2` WHERE id='be26cfc2bd3e21821e4a27ec7796316e8d7fb0f3' (2.4s elapsed, 232 MB processed)
What we learned here:
- Clustering can work with unique ids, even for tables without a date to partition by.
- Prefer using a fake date instead of a null date
- Clustering made my query 99.6% cheaper when looking for rows by id
Read great educational content like this and a lot more !
Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too