TABLE_FILES table function

The TABLE_FILES table function allows to query a ‘virtual’ table containing information about all data and delete files of a given iceberg table.

Authorization

Any user can execute this function.

Syntax

The schema is SYSHADOOP.

Description

schema_name

An input argument of type VARCHAR(128) containing the name of the schema as stored in the Db2 catalog. This is a required parameter which identifies the schema name.

table_name

An input argument of type VARCHAR(128) containing the name of the table as stored in the Db2 catalog. This is a required parameter which identifies the table name.

Output

Returns a table with all of the table's data and delete files and each file's metadata. Each row contains the following columns:

Table 1.
Column Name	Data type	Description
CONTENT	INT	0 for a data file, 1 for a positional delete file and 2 for an equality delete files
FILE_PATH	VARCHAR (1024)	File path of the file in the object store.
FILE_FORMAT	VARCHAR (10)	File format of the file (e.g. PARQUET).
SPEC_ID	INT	Version number of the table’s schema spec. (The ID changes when the schema is changed, e.g. a column is added).
PARTITION	VARCHAR (1024)	String representing the partition id, e.g. {101,Berlin}
RECORD_COUNT	BIGINT	Number of records in file.
FILE_SIZE_IN_BYTES	BIGINT	Size of file in bytes

Examples

SELECT * FROM TABLE (TABLE_FILES('SAMPLE', 'EMPLOYEE'))

CONTENT     FILE_PATH                                                                       FILE_FORMAT SPEC_ID     PARTITION     RECORD_COUNT         FILE_SIZE_IN_BYTE   
----------- ------------------------------------------------------------------------------- ----------- ----------- ------------- -------------------- --------------------
          0 s3a://default/employee/data/1007593034-0-20240717070005749-00001.parquet        PARQUET               0 {}                               1                  720
          0 s3a://default/employee/data/1672519888-0-20240717070005088-00001.parquet        PARQUET               0 {}                               1                  720
          0 s3a://default/employee/data/722116838-0-20240717070004429-00001.parquet         PARQUET               0 {}                               1                  769
          0 s3a://default/employee/data/63793709-0-20240717070003742-00001.parquet          PARQUET               0 {}                               1                  727
          0 s3a://default/employee/data/873506256-0-20240717070003065-00001.parquet         PARQUET               0 {}                               1                  734
          0 s3a://default/employee/data/695295543-0-20240717070002403-00001.parquet         PARQUET               0 {}                               1                  741

  6 record(s) selected.