CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
user12632521
user12632521

Reputation: 97

GCP: Get old state of a table

I have a table in GCP (BigQuery) that is overwritten every day with data from an external source. Is there any way to view the state of the table at a point in the past? The following code (from https://cloud.google.com/bigquery/docs/time-travel):

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

produces results just for the case when the "interval" is younger than the time when the table was last modified.

EDIT: It looks like the maximum time travel window is 7 days.

Upvotes: 0

Views: 136

Answers (1)

Poala Astrid
Poala Astrid

Reputation: 832

I used the console since it was easier to manage and view.

In order to view the past state of your BigQuery table, you can hover to PERSONAL HISTORY to display the information of your recent jobs or to PROJECT HISTORY to display the recent jobs in your project.

enter image description here

After overwriting your query, you can notice that it has been executed, and per execution has a Job ID. If you specifically know the Job ID of the past table that you want to view, you can filter it. You can also view the Query job details when you click the 3 vertical dots, and from there you can see the state of the table with its specific timestamp and other essential details.

enter image description here

Upvotes: 0

Related Questions