Categories:

Table functions

ICEBERG_TABLE_FILES

Returns information about the data files registered to an externally managed Apache Iceberg™ table at a specified point in time.

See also:

Apache Iceberg™ tables , Metadata and retention for Apache Iceberg™ tables , ALTER ICEBERG TABLE … REFRESH

Syntax

ICEBERG_TABLE_FILES(
  TABLE_NAME => '<table_name>'
  [, AT => '<timestamp_ltz>']
)

Arguments

Required

TABLE_NAME => 'table_name'

The name of the externally managed Iceberg table for which you want to retrieve the data file information.

Optional

AT => 'timestamp_ltz'

Specifies an exact date and time to use for retrieving the file information. The value must be explicitly cast to a TIMESTAMP_LTZ data type. For information, see Date & time data types.

If not specified, the function returns information about the table files for the current snapshot.

Note

The AT argument isn’t supported for Parquet Direct tables. For Parquet Direct tables, the function returns the Parquet files that are currently registered to the table. To see the history of file registration, use ICEBERG_TABLE_FILE_REGISTRATION_HISTORY.

Output

The function returns the following columns:

Column nameData typeDescription
REGISTERED_ONTIMESTAMP_LTZThe timestamp of when the Parquet file was registered.
FILE_NAMETEXTThe full path to the registered file.
FILE_SIZENUMBERThe size of the file (in bytes).
ROW_COUNTNUMBERThe number of rows in the file.
ROW_COUNT_GROUPNUMBERThe number of row groups in the file.
MD5N/AThis field returns a placeholder value and should not be used. This field might be deprecated in a future release.
ETAGN/AThis field returns a placeholder value and should not be used. This field might be deprecated in a future release.
LAST_MODIFIED_ONN/AThis field returns a placeholder value and should not be used. This field might be deprecated in a future release.

Note

The ETAG, MD5, and LAST_MODIFIED_ON fields return a placeholder value and should not be used. These fields might be deprecated in a future release.

Examples

Retrieve information about the Parquet data files for the current snapshot registered to an externally managed Iceberg table named my_iceberg_table:

SELECT *
  FROM TABLE(
    INFORMATION_SCHEMA.ICEBERG_TABLE_FILES(
      TABLE_NAME => 'my_iceberg_table'
    )
  );

Output:

+-------------------------------------------------------+--------------------------------+------------+--------------------------------+------------+------------------+-----------------------------------+-----------------------------------+
| FILE_NAME                                             | REGISTERED_ON                  | FILE_SIZE  | LAST_MODIFIED_ON               | ROW_COUNT  | ROW_GROUP_COUNT  | ETAG                              | MD5                               |
| data/87/snow_D9zlAoeipII_AODxT1uXDxg_0_1_003.parquet  | 1969-12-31 16:00:00.000 -0800  | 27136      | 1969-12-31 16:00:00.000 -0800  | 30000      | 1                | NULL                              | NULL                              |
| data/08/snow_D9zlAoeipII_AODxT1uXDxg_0_1_006.parquet  | 1969-12-31 16:00:00.000 -0800  | 45568      | 1969-12-31 16:00:00.000 -0800  | 45000      | 1                | NULL                              | NULL                              |
| data/94/snow_D9zlAoeipII_AODxT1uXDxg_0_1_008.parquet  | 1969-12-31 16:00:00.000 -0800  | 45056      | 1969-12-31 16:00:00.000 -0800  | 45000      | 1                | NULL                              | NULL                              |
| data/24/snow_D9zlAoeipII_AODxT1uXDxg_0_1_004.parquet  | 1969-12-31 16:00:00.000 -0800  | 27136      | 1969-12-31 16:00:00.000 -0800  | 30000      | 1                | NULL                              | NULL                              |
+-------------------------------------------------------+--------------------------------+------------+--------------------------------+------------+------------------+-----------------------------------+-----------------------------------+

Retrieve information about the Parquet data files for a table named my_iceberg_table at a specified time and day:

SELECT file_name, file_size, row_count, row_group_count, etag, md5
  FROM TABLE(
    INFORMATION_SCHEMA.ICEBERG_TABLE_FILES(
      TABLE_NAME => 'my_iceberg_table',
      AT => CAST('2024-12-09 11:02:00' AS TIMESTAMP_LTZ)
    )
  );

Output:

+------------------------------------------------------+-----------+-----------+-----------------+----------------------------------+----------------------------------+
| FILE_NAME                                            | FILE_SIZE | ROW_COUNT | ROW_GROUP_COUNT | ETAG                             | MD5                              |
|------------------------------------------------------+-----------+-----------+-----------------+----------------------------------+----------------------------------|
| data/87/snow_D9zlAoeipII_AODxT1uXDxg_0_1_003.parquet | 27136     | 30000     | 1               | NULL                             | NULL                             |
| data/08/snow_D9zlAoeipII_AODxT1uXDxg_0_1_006.parquet | 45568     | 45000     | 1               | NULL                             | NULL                             |
| data/94/snow_D9zlAoeipII_AODxT1uXDxg_0_1_008.parquet | 45056     | 45000     | 1               | NULL                             | NULL                             |
| data/24/snow_D9zlAoeipII_AODxT1uXDxg_0_1_004.parquet | 27136     | 30000     | 1               | NULL                             | NULL                             |
+------------------------------------------------------+-----------+-----------+-----------------+----------------------------------+----------------------------------+
4 Row(s) produced. Time Elapsed: 1.502s