Google BigQuery - Using wildcard table query with date partitioned table?

Question

I am trying to use wildcard table functions to query bunch of date-partitioned tables.

This query works:

select * from `Mydataset.fact_table_1` where _partitiontime='2016-09-30' limit 10

This query does not work:

select * from `Mydataset.fact_table_*` where _partitiontime='2016-09-30' limit 10

Is this operation not supported?

If it is not supported what's the best way to read same day's data from multiple date-partitioned tables?

Can you clarify what you mean by "does not work"? Do you get an error, or is the issue that the result is unexpected? — Elliott Brossard
I get the error -" Unrecognized name: _partitiontime " .. same works if I do not use wildcard character or do not use _partitiontime. I can not use both of them in same query. — Tim S
I think if you use a wildcard table, you have to filter on _TABLE_SUFFIX instead, or at least that's my interpretation of cloud.google.com/bigquery/docs/querying-wildcard-tables. I'm not sure that you can use both _TABLE_SUFFIX and _PARTITIONTIME but I asked a coworker to confirm. — Elliott Brossard
I am able to use TABLE_QUERY in legacy sql along with PARTITIONTIME. so if I want to list partitions for all tables - select _partitiontime as pt from (TABLE_QUERY(Mydataset, 'table_id CONTAINS "fact_table"')) group by 1 — Tim S
And here's the link to the feature request FYI: code.google.com/p/google-bigquery/issues/detail?id=728. — Hua Zhang

Royzipuff Royzipuff · Accepted Answer · 2016-10-06T21:13:33

Following statement

select * from TABLE_QUERY(YOUR_DATASET,'table_id contains "fact_table_"') where _PARTITIONTIME = TIMESTAMP('2016-09-30')

Should do the trick