BigQuery lets you use time travel to access data stored in BigQuery that has been changed or deleted. You can access the data from any point within the last seven days. 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.
Query data at a point in time
You can query a snapshot of a table from any point in time
within the previous seven days by using a
FOR SYSTEM_TIME AS OF
clause. This clause takes a constant timestamp expression and references the
version of the table that was current at that timestamp. The table must be
stored in BigQuery; it cannot be an external table.
For example, the following query returns a historical version of the table from one hour ago:
SELECT *
FROM `mydataset.mytable`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
If the timestamp specifies a time from more than seven days ago or from before the table was created, then the query fails and returns an error like the following:
Invalid snapshot time 1601168925462 for table myproject:mydataset.table1@1601168925462. Cannot read before 1601573410026.
If you replace an existing table by using the CREATE OR REPLACE TABLE
statement, then you can still use FOR SYSTEM_TIME AS OF
to query the previous
version of the table.
If the table was deleted, then the query fails and returns an error like the following:
Not found: Table myproject:mydataset.table was not found in location US
However, you can restore the table by copying a snapshot to a new table, as described in this document.
Restore a table from a point in time
You can restore a table from a snapshot by copying the snapshot into a table. Copying a snapshot works even if the table was deleted or has expired, as long as you restore the table within seven days after deletion.
To copy a table snapshot, add a snapshot decorator to the table name. A snapshot decorator has the following syntax:
tableid@TIME
whereTIME
is the time in milliseconds since the Unix epoch.tableid@-TIME_OFFSET
whereTIME_OFFSET
is the relative offset from the current time, in milliseconds.tableid@0
: Oldest available snapshot.
The following example uses the bq
command-line tool to copy a table named table1
from
one hour ago into a table named table1_restored
, using a relative offset.
bq cp mydataset.table1@-3600000 mydataset.table1_restored
The next example specifies an absolute epoch value. This example only works within seven days of the date shown.
date "+%s" -d "02/20/2020 01:00:00"
1582160400bq cp mydataset.table1@1582160400 mydataset.table1_restored
For more information, see Restoring deleted tables.