Querying BigQuery tables in a given date range

Question

In Google BigQuery i have tables in the following format: daily_records_yyyy_mm_dd . I need to run a query over multiple tables in a given date range. The TABLE_DATE_RANGE function expects table names to be in the format [prefix][day], where [day] is in the format YYYYMMDD. Can i still use TABLE_DATE_RANGE in my case or is there some other way ?

Thanks

Answer 1

No, you cannot use TABLE_DATE_RANGE here as it requires specific format

In your case (for BigQuery Legacy SQL) - you should use TABLE_QUERY where you can use any expressions to choose tables

Also - for BigQuery Standard SQL - you can use _TABLE_SUFFIX pseudo column to also work with any table name format

Examples:

Legacy SQL

SELECT *  
FROM (
  TABLE_QUERY(YourDataset, 'LEFT(table_id, 14) = "daily_records_" 
                            AND LENGTH(table_id) = length("daily_records_") + 10
                            AND DATE(REPLACE(RIGHT(table_id, 10), "_", "-")) 
                            BETWEEN DATE("2015-11-01") AND DATE("2016-01-30")')
)  

Standard SQL

SELECT * 
FROM `YourDataset.daily_records_*` 
WHERE REPLACE(_TABLE_SUFFIX, "_", "-")
      BETWEEN "2015-11-03" AND "2016-01-05"