Google BigQuery Daily Imports Help

Adele

Hey,

So in BigQuery I've got a table full of data for each day:

 

app_events_20180201

app_events_20180202

 

etc. etc. what I'm trying to do is create a query in the connector that will simply run and append yesterdays data. I've been playing around with things like

 

select user_id

from app_events_DATE_FORMAT(current_date - 1, “%Y%m%d”)

or

from "app_events_"+"DATE_FORMAT(current_date - 1, “%Y%m%d”)"

 

but I can't seem to get anything to work. Has anyone else figured out a simple way to append each days data aside from going in everyday and changing the table name?

Adele

We just used regex in the query statement to find yesterdays table via the suffix and pull in all the data from it.

 

REGEXP_CONTAINS(_TABLE_SUFFIX, r'^\d{8}$') AND PARSE_DATE("%E4Y%m%d", _TABLE_SUFFIX) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND CURRENT_DATE()