CODEWITHSUNDEEP
google-bigquery
dorachan2010
dorachan2010

Reputation: 1091

How to query Partition Table in BigQuery

I was just reading up on partitioned table on BigQuery and couldn't figure out the best practice section. I tried running the following

#standardSQL
SELECT *
FROM `myproject.affiliate.log_20170101`
WHERE _PARTITIONTIME BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 * 24 HOUR),DAY)
AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(),DAY);

based on this documentation link

But I get this error,

Error: Unrecognized name: _PARTITION_TIME;

What am I doing wrong? And how is this different from wildcard query?

Upvotes: 1

Views: 8272

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

What am I doing wrong?

From below line - i am assuming that you are not actually using partitioned table, but rather sharded table 

FROM `myproject.affiliate.log_20170101`

To deal with sharded table in BigQuery Standard SQL you should use _TABLE_SUFFIX

And how is this different from wildcard query?

Sharded table is the set of daily tables which named as tablename_YYYYMMDD and can be queried with use of Table wildcard functions in Legacy SQL or with _TABLE_SUFFIX in Standard SQL
Partitioned table is tone table that internally partitioned by day

Upvotes: 6

Related Questions