Time Travel
Time travel allows you change the view of the data to a previous time. This is not the same as an AS OF
query commonly used in SQL. The data is rolled back to a prior time.
Before starting, make sure you are in the Query Workspace by clicking this icon on the left side
Let us look at the snapshots available for the customer table in the workshop schema.
Check current snapshots – STARTING STATE
SELECT
*
FROM
iceberg_data.workshop."customer$snapshots"
ORDER BY
committed_at;
You need to capture the first snapshot ID returned by the SQL statement. You will need this value when you run the rollback command.
Capture the first snapshot ID
SELECT
snapshot_id
FROM
iceberg_data.workshop."customer$snapshots"
ORDER BY
committed_at;
Remember the number that was returned with the query above.
Your snapshot ID will be different than the examples in this lab
Insert the following record to change the customer table in the workshop schema.
Insert a new customer record into the table
insert into iceberg_data.workshop.customer
values(1501,'Deepak','IBM SVL',16,'123-212-3455',
123,'AUTOMOBILE','Testing snapshots');
Let's look at the snapshots available for the customer table in the workshop schema. You should have 2 snapshots.
View available snapshots
SELECT
*
FROM
iceberg_data.workshop."customer$snapshots"
ORDER BY
committed_at;
Querying the customer table in the workshop schema, we can see the record inserted with name=’Deepak’.
Select customer Deepak
select * from iceberg_data.workshop.customer where name='Deepak';
We realize that we don't want the recent updates or just want to see what the data was at any point in time to respond to regulatory requirements. We will leverage the out-of-box system function rollback_to_snapshot
to rollback to an older snapshot. The syntax for this function is:
CALL iceberg_data.system.rollback_to_snapshot('workshop','customer',x);
The "x" would get replaced with the snapshot_id
number that was found in the earlier query. It will be different on your system than the examples above.
View the available snapshots
SELECT
snapshot_id
FROM
iceberg_data.workshop."customer$snapshots"
ORDER BY
committed_at;
Copy the first snapshot ID back into the SQL window by itself. Then when you copy the next command, make sure to paste the SQL before the number, and then you can edit the SQL to add the snapshot ID.
Rollback to previous snapshot - Replace the ID parameter with the snapshot ID previously found. Remember the closing parenthesis!
CALL iceberg_data.system.rollback_to_snapshot('workshop','customer',ID)
The only output that is produced is a true or false value.
Querying the customer table in the workshop schema, we cannot see the record inserted with name=’Deepak’.
Select customer Deepak
select * from iceberg_data.workshop.customer where name='Deepak';
Summary
In this lab you learned how to rollback transactions to a previous checkpoint in the table.