BigQuery export using FOR SYSTEM_TIME AS OF

[smbcbe]

Is there a way to export a BigQuery table with respect to a past time using time travel.  Snapshot and clone allows this using "FOR SYSTEM_TIME AS OF". Is there a way to achieve this for export operation.

[ms4446]

In BigQuery, you can use the "time travel" feature to query a table as it existed at a past time using the FOR SYSTEM_TIME AS OF clause. However, when it comes to exporting data from BigQuery, this feature is not directly available in the export operation itself.

Instead, you can work around this by first querying the table with the FOR SYSTEM_TIME AS OF clause to retrieve the data as it existed at the past time, and then exporting the results of that query.

Important: Keep in mind that time travel in BigQuery is limited by a fixed retention period. By default, BigQuery retains table data for time travel for 7 days, and this period cannot be extended. Additionally, remember to clean up any temporary tables you create to avoid unnecessary storage costs.

Here’s how you can do it:

1. Run a Query with Time Travel

CREATE OR REPLACE TABLE `project.dataset.temporary_table` AS
SELECT * FROM `project.dataset.your_table`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);

This query creates a temporary table with the data as it existed at the specified past time (in this example, 1 day ago).

2. Export the Temporary Table

Once the data is stored in a temporary table, you can use the export feature to export this table to Google Cloud Storage:

EXPORT DATA
OPTIONS(
  uri='gs://your-bucket/your-path/*.csv',
  format='CSV'
) AS
SELECT * FROM `project.dataset.temporary_table`;

This command exports the data in the temporary table to a CSV file in the specified GCS bucket.

3. Clean Up

After exporting, make sure to drop the temporary table to avoid unnecessary storage costs:

DROP TABLE `project.dataset.temporary_table`;

• Time Travel Limitation: You can only query past data within the retention period set for your BigQuery table. This period is 7 days by default but can be extended.
• Workaround for Exporting Past Data: Since direct export with time travel isn’t supported, you need to create a temporary table first and then export the data.
• Cleaning Up: Always remember to delete any temporary tables you create to manage storage costs effectively.