Query a view which is created from partitioned table in bigquery

0
votes

I have a partitioned table called customers. I have created a view from customers table as below and its called customers_view (there are more constraints in where clause which I omitted here):

SELECT * FROM `customers`
WHERE DATE(_PARTITIONTIME) > "2021-05-10"

Now I want to query from customers_view but when I write:

SELECT * FROM `customers_view`
WHERE DATE(_PARTITIONTIME) > "2021-05-12"

OR

SELECT * FROM `customers_view`
WHERE DATE(_pt) > "2021-05-12"

It gives this error: ! Unrecognized name:_PARTITIONTIME (or _pt)

How I can query a view which is made from a partitioned table and have partition time in where clause? (Both table and view has a timestamp column as well but I think some rows have 1 day gap between timestamp and _pt)

1
sqlgoogle-bigquery

1 Answers

1
votes

If you want the partition time in the view, you need to include it explicitly:

SELECT c.*, _PARTITIONTIME as pt
FROM `customers`
WHERE DATE(_PARTITIONTIME) > '2021-05-10'