ICEBERG_SNAPSHOTS table function

The SYSHADOOP.ICEBERG_SNAPSHOTS (SYSHADOOP schema) table function is used to retrieve information about the snapshots of a Datalake Iceberg table. The snapshot information can be queried and used for snapshot management, such as rolling back to a particular snapshot. The ICEBERG_SNAPSHOTS table function can be called by any user.

Syntax

Description

schema: The Datalake Iceberg schema name.

table name: The name of the Iceberg table to retrieve snapshot info from.

Output

The function returns a table with one row per snapshot containing information about the snapshot. Each row contains the following columns:

Table 1. Function Output
Column name	Data type	Description
SCHEMANAME	VARCHAR(128)	The schema name of the Datalake Iceberg table.
TABLENAME	VARCHAR(128)	The table name of the Datalake Iceberg table.
SEQNUM	BIGINT	The sequence number of the snapshot indicating the order that the snapshots were created.
CREATE_TS	TIMESTAMP	The timestamp of when the snapshot was created.
ROLLBACK_TS	TIMESTAMP	This is the timestamp to use if intending to rollback to the snapshot by timestamp. This column’s value is the creation timestamp incremented by one millisecond, since rolling back to a timestamp rolls back to the latest snapshot after the given timestamp, this column is provided for convenience.
SNAPSHOT_ID	BIGINT	The ID of the snapshot.
PARENT_ID	BIGINT	The snapshot ID of the parent snapshot. If there is no parent then this is NULL.
SCHEMA_ID	INT	The ID of the schema that is used for the snapshot.
OPERATION	VARCHAR(16)	The operation done by the snapshot. Possible values are “append”, “replace”, “overwrite”, or “delete”.
IS_CURRENT	CHAR(1)	Specifies if the snapshot is the current snapshot for the table. The value is either ‘Y’ or ‘N’.

Example

SELECT * FROM TABLE(SYSHADOOP.ICEBERG_SNAPSHOTS(‘EXAMPLE’, ‘TEST_TABLE’));


SCHEMANAME                                                                                                                       TABLENAME                                                                                                                        SEQNUM               CREATE_TS                  ROLLBACK_TS                SNAPSHOT_ID          PARENT_ID            SCHEMA_ID   OPERATION        IS_CURRENT
------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------------- -------------------------- -------------------- -------------------- ----------- ---------------- ----------
EXAMPLE                                                                                                                          TEST_TABLE                                                                                                                                          1 2023-09-12-11.23.11.336000 2023-09-12-11.23.11.337000  6082606189141553394                    -           0 append           N
EXAMPLE                                                                                                                          TEST_TABLE                                                                                                                                          2 2023-09-12-11.24.47.019000 2023-09-12-11.24.47.020000  2837479794443339597  6082606189141553394           1 append           N
EXAMPLE                                                                                                                          TEST_TABLE                                                                                                                                          3 2023-09-12-11.26.09.415000 2023-09-12-11.26.09.416000  1844248813417128083  6082606189141553394           0 append           Y
EXAMPLE