I have daily tables on BigQuery. How to request the "latest"?

Question

I create daily tables. How to request the "newest"?

For example, GitHubArchive now publishes daily tables - instead of monolithic (see /r/bigquery/.../github_archive_changes_monthly_and_daily_tables/ ).

Answer 1

To find the last table, you can use the BigQuery table query functions:

For example, the last daily GitHubArchive table:

SELECT COUNT(*) num_records FROM TABLE_QUERY(githubarchive:day, "table_id IN ( SELECT table_id FROM githubarchive:day.__TABLES__ ORDER BY creation_time DESC LIMIT 1)") # 201859 

For maximum convenience, you can save this query as a presentation for sharing with others. Then, to query the last table, just query the view:

 SELECT num_records FROM [fh-bigquery:public_dump.githubarchive_latest_day] # 201859 

For more information on table query functions, see Jordan's answer to How to use the TABLE_QUERY () function in BigQuery? .

Answer 2

Answer Felipe uses Legacy SQL. Here is the solution in standard SQL.

If your table names have a predictable suffix, for example the GitHub example in your link (which uses the YYYYMMDD suffix for each day), you can use wildcard tables and TABLE_SUFFIX :

 SELECT COUNT(*) FROM `githubarchive.day.events_*` WHERE _TABLE_SUFFIX = ( SELECT MAX(SUBSTR(table_id, -8)) FROM `githubarchive.day.__TABLES_SUMMARY__` WHERE REGEXP_CONTAINS(table_id, "events_\\d{8}") ) 

If you want to rely on BigQuery metadata about creation time instead of a prefix, you can do:

 SELECT COUNT(*) FROM `githubarchive.day.*` WHERE _TABLE_SUFFIX = ( SELECT table_id FROM `githubarchive.day.__TABLES_SUMMARY__` ORDER BY creation_time DESC LIMIT 1 ) 

Please note that if your project has tables with different schemas, the last syntax is dangerous, since the query will use the schema of the first table that it sees in the template.

If you are trying to create tables separated by date, there is a BigQuery function called date tables that does just that and simplifies the query after that.