CODEWITHSUNDEEP
google-bigquery
David M Smith
David M Smith

Reputation: 2332

BigQuery Wildcard using TABLE_DATE_RANGE()

Great news about the new table wildcard functions this morning! Is there a way to use TABLE_DATE_RANGE() on tables that include date but no prefix?

I have a dataset that contains tables named YYYYMMDD (no prefix). Normally I would query like so:

SELECT foo 
FROM [mydata.20140319],[mydata.20140320],[mydata.20140321] 
LIMIT 100

I tried the following but I'm getting an error:

SELECT foo
FROM 
  (TABLE_DATE_RANGE(mydata., 
                TIMESTAMP('2014-03-19'), 
                TIMESTAMP('2015-03-21')))
LIMIT 100

as well as:

SELECT foo
FROM 
  (TABLE_DATE_RANGE(mydata, 
                TIMESTAMP('2014-03-19'), 
                TIMESTAMP('2015-03-21')))
LIMIT 100

Upvotes: 12

Views: 22028

Answers (3)

Luís Bianchin
Luís Bianchin

Reputation: 2476

Note that with standard SQL support in BigQuery, you can use _TABLE_SUFFIX, instead of TABLE_QUERY. For example:

SELECT foo
FROM `mydata_*`
WHERE _TABLE_SUFFIX BETWEEN '20140319' AND '20150321'

Also check this question for more about BigQuery standard SQL.

Upvotes: 6

Jordan Tigani
Jordan Tigani

Reputation: 26617

Note: The underlying bug has been fixed, please see my other answer. Original response left for posterity (since the workaround should still work, in case you need it for some reason)

Great question. That should work, but it doesn't currently. I've filed an internal bug. In the meantime, a workaround is to use the TABLE_QUERY function, as in:

SELECT foo
FROM (
  TABLE_QUERY(mydata,
              "TIMESTAMP(table_id) BETWEEN "
               + "TIMESTAMP('2014-03-19') "
               + "AND TIMESTAMP('2015-03-21')"))

Upvotes: 8

Jordan Tigani
Jordan Tigani

Reputation: 26617

The underlying bug here has been fixed as of 2015-05-14. You should be able to use TABLE_DATE_RANGE with a purely numeric table name. You'll need to end the dataset in a '.' and enclose the name in brackets, so that the parser doesn't complain. This should work:

SELECT foo
FROM 
  (TABLE_DATE_RANGE([mydata.], 
                TIMESTAMP('2014-03-19'), 
                TIMESTAMP('2015-03-21')))
LIMIT 100

Upvotes: 12

Related Questions