BigQuery Wildcard tables with Regex and date range

Question

Is it possible to combine the table wildcard functions as documented here?

I've taken a look through the Table Query functions SO answer, but doesn't quite seem to cover my use case.

I have table names in the format: s_CUSTOMER_ID_YYYYMMDD

I can find all the tables for a customer ID using:

SELECT *
FROM TABLE_QUERY([project:dataset], 
                 'REGEXP_MATCH(table_id, r"^s_CUSTOMER_ID")')

And I can find all the tables for a date range via:

SELECT * 
FROM (TABLE_DATE_RANGE([project:dataset],
                       TIMESTAMP('2016-01-01'),
                       TIMESTAMP('2016-03-01')))

But how do I query for both at the same time?

I tried using sub queries like this:

SELECT * FROM    
    (SELECT *
    FROM TABLE_QUERY([project:dataset], 
                     'REGEXP_MATCH(table_id, r"^s_CUSTOMER_ID")'))
    ,(SELECT * 
    FROM (TABLE_DATE_RANGE([project:dataset],
                           TIMESTAMP('2016-01-01'),
                           TIMESTAMP('2016-03-01'))))

...but the parser complains of Error: Can't parse table: project:dataset.

Adding a dot so they are project:dataset. brings an error Error: Error preparing subsidiary query: Dataset project:dataset. not found

Are my table names poorly done? What would be a better way of organising them if so?

score 3 · Accepted Answer · answered Mar 17 '16 at 23:54

Below quick "solution" - should work and you can improve it based on real/extra requirements you probably have

SELECT *
FROM 
  TABLE_QUERY([project:dataset], 
    'REGEXP_MATCH(table_id, r"^s_CUSTOMER_ID") 
    AND RIGHT(table_id, 8) BETWEEN "20160101" AND "20160301"')

BigQuery Wildcard tables with Regex and date range

1 Answers1