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>']
)
Copy

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

HEX

The MD5 checksum of the file.

ETAG

HEX

The ETag header for the file.

LAST_MODIFIED_ON

TIMESTAMP_LTZ

The timestamp of when the file was last updated.

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'
    )
  );
Copy

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      | 2024-12-09 11:00:41.000 -0800  | 30000      | 1                | 5cae923b13581f87cf6397ec491fb5d5  | 5cae923b13581f87cf6397ec491fb5d5 |
| data/08/snow_D9zlAoeipII_AODxT1uXDxg_0_1_006.parquet  | 1969-12-31 16:00:00.000 -0800  | 45568      | 2024-12-09 11:00:41.000 -0800  | 45000      | 1                | 3659cb341fec3a57309480d2e1bb7fc3  | 3659cb341fec3a57309480d2e1bb7fc3 |
| data/94/snow_D9zlAoeipII_AODxT1uXDxg_0_1_008.parquet  | 1969-12-31 16:00:00.000 -0800  | 45056      | 2024-12-09 11:00:41.000 -0800  | 45000      | 1                | 5bee899fa8ee60fa668329acae0ed215  | 5bee899fa8ee60fa668329acae0ed215 |
| data/24/snow_D9zlAoeipII_AODxT1uXDxg_0_1_004.parquet  | 1969-12-31 16:00:00.000 -0800  | 27136      | 2024-12-09 11:00:41.000 -0800  | 30000      | 1                | 43a489e450831c717d909a5c79ab9388  | 43a489e450831c717d909a5c79ab9388 |
+-------------------------------------------------------+--------------------------------+------------+--------------------------------+------------+------------------+-----------------------------------+-----------------------------------+

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)
    )
  );
Copy

Output:

+------------------------------------------------------+-----------+-----------+-----------------+----------------------------------+----------------------------------+
| FILE_NAME                                            | FILE_SIZE | ROW_COUNT | ROW_GROUP_COUNT | ETAG                             | MD5                              |
|------------------------------------------------------+-----------+-----------+-----------------+----------------------------------+----------------------------------|
| data/87/snow_D9zlAoeipII_AODxT1uXDxg_0_1_003.parquet |     27136 |     30000 |               1 | 5cae923b13581f87cf6397ec491fb5d5 | 5cae923b13581f87cf6397ec491fb5d5 |
| data/08/snow_D9zlAoeipII_AODxT1uXDxg_0_1_006.parquet |     45568 |     45000 |               1 | 3659cb341fec3a57309480d2e1bb7fc3 | 3659cb341fec3a57309480d2e1bb7fc3 |
| data/94/snow_D9zlAoeipII_AODxT1uXDxg_0_1_008.parquet |     45056 |     45000 |               1 | 5bee899fa8ee60fa668329acae0ed215 | 5bee899fa8ee60fa668329acae0ed215 |
| data/24/snow_D9zlAoeipII_AODxT1uXDxg_0_1_004.parquet |     27136 |     30000 |               1 | 43a489e450831c717d909a5c79ab9388 | 43a489e450831c717d909a5c79ab9388 |
+------------------------------------------------------+-----------+-----------+-----------------+----------------------------------+----------------------------------+
4 Row(s) produced. Time Elapsed: 1.502s
Language: English