USER MANUALS

ROLLBACK_ICEBERG_VIEW_TO_SNAPSHOT

Description

The stored procedure ROLLBACK_ICEBERG_VIEW_TO_SNAPSHOT rolls back the view data to the specified snapshot. It can be executed over base views from the Denodo MPP data source or a PrestoDB data source accessing tables in Apache Iceberg format.

Syntax

ROLLBACK_ICEBERG_VIEW_TO_SNAPSHOT (
    database_name : text,
    view_name : text,
    snapshot_id : bigint,
    rollback_to_instant : text
)

  • database_name: name of the database containing the view. If the database does not exist the procedure returns an error.

  • view_name: name of the view. If the view does not exist the procedure returns an error.

  • snapshot_id: identifier of the snapshot to which the view data will be regressed.

  • rollback_to_instant: timestamp with timezone value. When snapshot_id is null, the procedure will search for the latest snapshot prior to this specified value. The timestamp must be provided following the ISO-8601 syntax yyyy-MM-dd HH:mm:ss.SSSX.

Note

Invoking the procedure specifying both snapshot_id and rollback_to_instant values will fail. For one of the parameters to be valid, the other one must be null.

This stored procedure returns one unique row, with the following schema:

  • snapshot_id: containing the snapshot id to which the view data was regressed.

  • committed_at: the instant when the snapshot identified by snapshot_id was created.

  • error: true if the operation ended with an error, false otherwise.

  • status: the status of the operation, success if finished correctly or an error if the operation failed.

Privileges Required

Only users that have the Insert, Update and Delete privileges on the base view can execute this procedure. This means, the following users can execute this procedure:

  • Administrators or administrators of this database.

  • Users that have the Connect privilege on this database and Insert, Update and Delete privileges on the base view.

Examples

  • Execute rollback on view test.database.test_view by snapshot ID: call rollback_iceberg_view_to_snapshot('test_database', 'test_view', 7844484970052735616, null)

  • Execute rollback on view test.database.test_view by snapshot ID (alternative syntax): SELECT * FROM rollback_iceberg_view_to_snapshot() WHERE database_name = 'test_database' AND view_name = 'test_view' AND snapshot_id = 7844484970052735616

  • Execute rollback on view test_database.test_view by timestamp: call rollback_iceberg_view_to_snapshot('test_database', 'test_view', null, '2024-04-17 08:10:30.128Z')

  • Execute rollback on view test_database.test_view by timestamp (alternative syntax):SELECT * FROM rollback_iceberg_view_to_snapshot() WHERE database_name = 'test_database' AND view_name = 'test_view' AND timestamp = '2024-04-17 08:10:30.128Z'

Add feedback