BigQuery Wildcard using TABLE_DATE_RANGE()

Question

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

Answer 1

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

Answer 2

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.