CODEWITHSUNDEEP
google-bigquery
WJA
WJA

Reputation: 7004

Do I need to use the pseudo column _PARTITIONTIME when querying from a column partitioned table?

I created a time-partitioned table on BigQuery by using a date column from the table itself:

new_table.time_partitioning = bigquery.TimePartitioning(field='date')

I query the data by a simple request as follows:

SELECT * FROM t where date="2020-04-08"

My question is whether this is sufficient to query the partitioning, and thus reduce costs, or do I need to add also the pseudo columns _PARTITIONTIME as outlined in the section on Querying Partitioned Tables?

SELECT * FROM t where _PARTITIONTIME = TIMESTAMP("2020-04-08")

Upvotes: 0

Views: 720

Answers (1)

Yun Zhang
Yun Zhang

Reputation: 5503

Quick answer is SELECT * FROM t where date="2020-04-08" is good enough for you to engage "partition pruning" and reduce cost.

Longer answer is always consult UI to see if partition filter is properly engaged for certain query:

SELECT * FROM `bigquery-public-data.crypto_bitcoin.transactions` 
WHERE block_timestamp_month >= "2020-01-01"

This month -> enter image description here

Year to date -> enter image description here

Upvotes: 1

Related Questions