Google BigQuery Daily Imports Help

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”)

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?

Find more posts tagged with

Accepted answers

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()

All comments

laura_igl

Is anyone able to help out with this request?

Thanks,

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()