Access historical data using time travel
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 time travel window, which covers the past seven days by default. Time travel lets you query data that was updated or deleted, restore a table that was deleted, or restore a table that expired.
When you set your storage billing model (in preview) to use physical bytes, the total storage costs you are billed for include the bytes used for time travel storage. If you set your storage billing model to use logical bytes, the total storage costs you are billed for do not include the bytes used for time travel storage. You can configure the time travel window to balance storage costs with your data retention needs.
Limitations
Time travel only provides access to historical data for the duration of the time travel window. To preserve table data for longer than the time travel window, use table snapshots.
If a table has, or has previously had, row-level access policies, then time travel can only be used by table administrators. For more information, see Time travel and row-level access.
Query data at a point in time
You can query a table's historical data from any point in time within the
time travel window 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. There is no
limit on table size when using SYSTEM_TIME AS OF
.
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 prior to the time travel window 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.
After you replace an existing table by using the CREATE OR REPLACE TABLE
statement, you can 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 from a point in time to a new table, as described in this document.
Restore a table from a point in time
You can restore a table from historical data by copying the historical data into a table. Copying a data works even if the table was deleted or has expired, as long as you restore the table within the duration of the time travel window.
To copy historical data from a table, add a decorator to the table name using the following syntax:
tableid@TIME
whereTIME
is the number of milliseconds since the Unix epoch.tableid@-TIME_OFFSET
whereTIME_OFFSET
is the relative offset from the current time, in milliseconds.tableid@0
: Specifies the oldest available historical data.
For example, the following bq
command-line tool command copies a table named table1
from
one hour ago into a table named table1_restored
. The time, -3600000
, is
specified in milliseconds using a relative offset.
bq cp mydataset.table1@-3600000 mydataset.table1_restored
The following bq
command-line tool command copies a table named table1
into a table
named table1_restored
. The time, 1624046611000
, is
specified as milliseconds since the Unix epoch.
bq cp mydataset.table1@1624046611000 mydataset.table1_restored
For more information, see Restoring deleted tables.
Configure the time travel window
You can set the duration of the time travel window, from a minimum of two days to a maximum of seven days. Seven days is the default. You set the time travel window at the dataset level, which then applies to all of the tables within the dataset.
You can configure the time travel window to be longer in cases where it is more important to have the option of recovering the data, and to be shorter where that isn't a concern. Using a shorter time travel window allows you to save on storage costs. It can also allow you to conform to data retention policies that require hard deletion of data in a specific time frame.
How the time travel window affects table recovery
A deleted table is permanently associated with the time travel window duration that was in effect at the time of table deletion.
For example, if you have a time travel window duration of two days and then increase the duration to seven days, tables deleted before that change are still only recoverable for two days.
Similarly, if you have a time travel window duration of five days and you reduce that duration to three days, any tables that were deleted before the change are still recoverable for five days.
If you reduce the time travel window duration, delete a table, and then realize that you need a longer period of recoverability for that data, you can create a snapshot of the table from a point in time prior to the table deletion. To be successful, you must do this while the deleted table is still recoverable. For more information, see Create a table snapshot using time travel.
Specify a time travel window
You can use the Google Cloud console, the bq
command-line tool, or the
BigQuery API to specify the time travel window for a dataset.
For instructions on how to specify the time travel window for a new dataset, see Create datasets.
For instructions on how to update the time travel window for an existing dataset, see Update time travel windows.
If the timestamp specifies a time outside time travel window, or from before the table was created, then the query fails and returns an error like the following:
TableID
was created at time which is before its allowed time travel intervaltimestamp
. Creation time:timestamp
Time travel and row-level access
If a table has, or has had, row-level access policies, then only a table administrator can access historical data for the table.
The following Identity and Access Management (IAM) permission is required:
Permission | Resource |
---|---|
bigquery.rowAccessPolicies.overrideTimeTravelRestrictions
|
The table whose historical data is being accessed |
The following BigQuery role provides the required permission:
Role | Resource |
---|---|
roles/bigquery.admin
|
The table whose historical data is being accessed |
The bigquery.rowAccessPolicies.overrideTimeTravelRestrictions
permission
can't be added to a custom role.
What's next
- See Table snapshots.