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.mytable where 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

DataFreak

posted on 05 May 19