CODEWITHSUNDEEP
sqlgoogle-bigquery
user11035754
user11035754

Reputation: 227

Partition date ranges in partitioned tables

Is it possible to select the partition date range based on some other date in the same GBQ table?

_partitiondate is of datatype timestamp

purchase_date is of datatype string

Below is what I am trying to do-

_PARTITIONDATE < DATE(CAST(purchase_date as TIMESTAMP))
and _PARTITIONDATE >= date_add(date(CAST(purchase_date as TIMESTAMP)), interval -30 day)

I get the following error-

Cannot query over table 'project.dataset.table' without a filter over column(s) '_PARTITION_LOAD_TIME', '_PARTITIONDATE', '_PARTITIONTIME' that can be used for partition elimination

Upvotes: 0

Views: 2179

Answers (1)

rsantiago
rsantiago

Reputation: 2099

It is documented that it is possible (see Querying partitions using pseudo colums for more details), below it is the structure recommmended in documentation:

_PARTITIONDATE >= "2018-01-29" AND _PARTITIONDATE < "2018-01-30"

or

_PARTITIONDATE BETWEEN '2016-01-01' AND '2016-01-02'

The thing is that, as you correctly suggested, the comparision value should be a TIMESTAMP data type, so a different approach can be:

_PARTITIONDATE < TIMESTAMP(purchase_date)
and _PARTITIONDATE >= TIMESTAMP_ADD(TIMESTAMP(purchase_date), interval -30 day)

I think something like the following should work:

SELECT
  columns
FROM
  dataset.table
WHERE
  _PARTITIONDATE BETWEEN TIMESTAMP_SUB(TIMESTAMP(purchase_date), interval 30 day)
  AND TIMESTAMP(purchase_date)

Upvotes: 2

Related Questions