USER MANUALS

SET_ICEBERG_VIEW_CURRENT_SNAPSHOT

Description

The stored procedure SET_ICEBERG_VIEW_CURRENT_SNAPSHOT updates a view to point 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

SET_ICEBERG_VIEW_CURRENT_SNAPSHOT (
    database_name : text,
    view_name : text,
    snapshot_id : bigint,
    snapshot_timestamp : 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 that the view should point to.

  • snapshot_timestamp: 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 snapshot_timestamp 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 that the view is now pointing to.

  • 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

  • Setting the current snapshot for the view test.database.test_view using snapshot ID: call set_iceberg_view_current_snapshot('test_database', 'test_view', 7844484970052735616, null)

  • Setting the current snapshot for the view test.database.test_view using snapshot ID (alternative syntax): SELECT * FROM set_iceberg_view_current_snapshot() WHERE database_name = 'test_database' AND view_name = 'test_view' AND snapshot_id = 7844484970052735616

  • Setting the current snapshot for the view test_database.test_view using timestamp: call set_iceberg_view_current_snapshot('test_database', 'test_view', null, '2024-04-17 08:10:30.128Z')

  • Setting the current snapshot for the view test_database.test_view using timestamp (alternative syntax): SELECT * FROM set_iceberg_view_current_snapshot() WHERE database_name = 'test_database' AND view_name = 'test_view' AND snapshot_timestamp = '2024-04-17 08:10:30.128Z'

Add feedback