11 Dec 20 · npack · #Bigquery ·   Bookmark  

BigQuery Time Travel and Undelete using Snapshot decorator

What is Time Travel in BigQuery

With Time travel, You can access the data in the table from any point (eg. any particular second) within the last seven days, even if the data has been deleted. You can use time travel to query data that was updated or deleted, restore a table that was deleted, or restore a table that expired. Better yet, there is no additional cost to enable this feature and there are no maintenance overheads as well

How to query a table using Time travel in BigQuery

If you have to take a look at the data in a table as of any particular time, all you have to do is add the AS OF Suffix (also known as snapshot decorator)

SELECT * FROM table
FOR SYSTEM_TIME AS OF '2020-01-01 12:00:00-07:00';

You can also use relative time as shown below:

SELECT * FROM `mydataset.mytable`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

with Time Travel you can query up to 7 days in the past

How to use time travel to recover deleted data

If you have accidentally deleted a table you can use time travel to retrieve the data as shown below:

Create table New_Table as
SELECT * FROM `mydataset.mytable`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

How to enable or disable Time Travel work in BigQuery ?

Time travel is automatically enabled in BigQuery by default

Time travel vs undrop in BigQuery

Time TravelUndrop
Time Travel history is available up to 7 days in the pastUndrop can be done only up to 2 days in the past
Time Travel can be used on active tablesUndrop can be used on dropped tables
Can be done use Command line as well as SQLCLI Only

UnDrop Syntax:

To undrop a table in Bigquery, use the below command from the command line

bq cp dataset.table@1577833204000 dataset.table

Where 1577833204000 is the unix timestamp (EPOCH)

NOTE: Undrop cannot be used if another table has been created with the same name