Pruning (limiting) partitions in bigquery partitioned tables

0
votes

I have a partitioned table created in BigQuery like this:

 CREATE TABLE IF NOT EXISTS `{{ my_project }}.{{ my_dataset }}.my_table`
(
dt TIMESTAMP
, other_fields
)
PARTITION BY DATE(dt)
OPTIONS(
partition_expiration_days=180
)
  • Partitioned by: DAY
  • Partitioning Field: dt (type: TIMESTAMP)

As you may see, the partitioning field is TIMESTAMP but I want to parition by DAY not by TIMESTAMP. So what is the correct way to prune partitions? Can I cast the partitioning field to DATE like approach 1 or I have to go with approach 2?

  • Approach 1:

SELECT COUNT(*) FROM `my_project.my_dataset.my_table` WHERE CAST(dt AS DATE) = "2019-01-01"

  • Approach 2:

SELECT COUNT(*) FROM `my_project.my_dataset.my_table` WHERE dt >= "2019-01-01 00:00:00" AND dt < "2019-01-02 00:00:00"

1
google-bigquery

1 Answers

0
votes

Approach 1 won't work because BigQuery SQL doesn't support a DATE data type in the CAST() function

Approach 2 should work however you could also use the FORMAT_DATE() function to remove the TIMSTAMP HH:MM:SS and then just pass in your "YYYY/MM/DD" sequence.

Another option if your table is structured with a table suffix is to use _TABLE_SUFFIX

WHERE _TABLE_SUFFIX >= '2019-01-01'
AND _TABLE_SUFFIX <= '2019-01-15'

Hope one of these options is helpful for you