- Categories:
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.
Output¶
The function returns the following columns:
Column name | Data type | Description |
|---|---|---|
REGISTERED_ON | TIMESTAMP_LTZ | The timestamp of when the Parquet file was registered. |
FILE_NAME | TEXT | The full path to the registered file. |
FILE_SIZE | NUMBER | The size of the file (in bytes). |
ROW_COUNT | NUMBER | The number of rows in the file. |
ROW_COUNT_GROUP | NUMBER | The number of row groups in the file. |
MD5 | N/A | This field returns a placeholder value and should not be used. This field might be deprecated in a future release. |
ETAG | N/A | This field returns a placeholder value and should not be used. This field might be deprecated in a future release. |
LAST_MODIFIED_ON | N/A | This 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