BigQuery - creating timestamped 'versions' of tables

user_5757155 0
Viewed 1362

How can I create tables with multiple 'generations' (i.e. like on old mainframe environments with JCL), I've seen this done with Firebase analytics sample data.

e.g. I have the following table: mydataset.mytable (7), as listed on the UI.

If I expand the table details, I can see that I can select from the timestamped tables and the preview details for that data

In BigQuery, how can I go around emulating this? This looks REALLY useful.

EDIT: This is better explained with a picture!

Here's the table with the 7 snapshots:

enter image description here

Here, looking at the schema, I can select the snapshot I want to query:

enter image description here

I can't quite work out how to do this.

best wishes

Dave

google-bigquery
1 Answers

You can use snapshot decorators for this
For example, below gives you version of table as of hour ago 

#legacySQL
SELECT .... FROM [project:dataset.table@-3600000]

in BigQuery StandardSQL - you can use below syntax 

#standardSQL
SELECT ... FROM `project.dataset.table` FOR SYSTEM TIME AS OF <timestamp_expression>

Update for

Here, looking at the schema, I can select the snapshot I want to query

That drop down represents actual sharded tables rather than snapshots.
Those are just separate tables with suffix that is presented as YYYYMMDD
Whenever you have any tables having common prefix with YYYYMMDD as a suffix in your dataset - Web UI just "collapse" them (in UI only - they are still separate tables) into one entry with count of actual tables in pair of round brackets ( )
Then, you can select which exactly table you want to deal with by selecting it from that drop down (in image from your question)
Hope, this helps you

user_52219440
Related