Clever Geek Handbook

How do I create a view for yesterday's data?

I'm splitting my data into BigQuery in the afternoon and need a quick way to query "yesterday's data".

Is it possible? How can I write queries that automatically point to the latest data without having to rewrite the tables I want to query?

+3


source to share


1 answer


You can create a view with TABLE_QUERY to find data from yesterday (or an arbitrary relative date).

For example, GitHubArchive stores daily tables, and I created a view that points to yesterday's table:

SELECT *
FROM TABLE_QUERY(githubarchive:day, 'table_id CONTAINS "events_" 
  AND table_id CONTAINS STRFTIME_UTC_USEC(DATE_ADD(CURRENT_TIMESTAMP(), -1, "day"), "%Y%m%d")')


You can check and request this view:

SELECT COUNT(*)
FROM [fh-bigquery:public_dump.github_yesterday]
+4


source






More articles:


All Articles