CODEWITHSUNDEEP
google-bigqueryextract
santosh sharma
santosh sharma

Reputation: 11

BigQuery extract data for yesterday where date is in the table name as filename20181203

I want to extract the data in big query as below BigQuery extract data for yesterday where date is in the table name as filename20181203

File are filename20181201 filename20181202 filename20181203

What is the best way to do :)

Upvotes: 1

Views: 1282

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

You probably want to use a wildcard table. For example:

SELECT *
FROM `dataset.filename*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

The * will match the end of the table names, and the special _TABLE_SUFFIX pseudo-column will have values such as '20181201' and '20181202'. Using FORMAT_DATE with the pattern '%Y%m%d' produces a string in the format 'YYYYMMDD', which matches the dates at the end of the table names.

Upvotes: 2

Related Questions