CODEWITHSUNDEEP
google-bigquery
ck reddy
ck reddy

Reputation: 504

format the timestamp output for TABLE_DATE_RANGE() BigQuery

I have a requirement to query different tables a once to save my time. Tables names like

abc_yyyymmdd

can be easily query using the 

table_date_range(abc_,timestamp('2016-01-01'),timestamp('2016-03-12'))

but I have different format table name

abc_mm_dd_yyyy

is there a way to query in these tables using table_date_range.

Upvotes: 0

Views: 173

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

In Legacy SQL you can use TABLE_QUERY for this

So it can be something like below 

SELECT *  
FROM (
  TABLE_QUERY(YourDataset, 'LEFT(table_id, 4) = "abc_" AND LENGTH(table_id) = 14
  AND CONCAT(SUBSTR(table_id,11,4),'-',SUBSTR(table_id,5,2), -",SUBSTR(table_id,8,2)) 
    BETWEEN "2016-01-01" AND "2016-03-12"')
)

Upvotes: 1

Pavan Edara
Pavan Edara

Reputation: 2315

If you can use Standard SQL, you can use the _TABLE_SUFFIX pseudo column to work with any table name format.

Is there an equivalent of table wildcard functions in BigQuery with standard SQL?

In this case, it would be something like:

SELECT ... FROM `mydataset.abc_2016_*` WHERE _TABLE_SUFFIX = '01-01' or _TABLE_SUFFIX = '03-12'

Upvotes: 1

Related Questions