BigQuery wildcard using TABLE_DATE_RANGE ()

Question

Great news about the new wildcard features this morning! Is there a way to use TABLE_DATE_RANGE () for tables containing a date but without a prefix?

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

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

I tried the following, but I get an error:

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

and:

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

Answer 1

The main error here has been fixed since 2015-05-14. You should be able to use TABLE_DATE_RANGE with a purely numeric table name. You will need to end the data set in '.' and enclose the name in brackets so that the parser does not complain. This should work:

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

Answer 2

Note. Fixed a basic bug, see my other answer. The original answer remained for posterity (since the workaround should still work if you need it for some reason)

Great question. This should work, but currently it is not. I filed an internal error. At the same time, 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')")) 

Answer 3

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 information about the standard BigQuery SQL standard.