Categories:

Table functions

ICEBERG_TABLE_FILE_REGISTRATION_HISTORY

Returns information about the Apache Parquet™ files that Snowflake registered or unregistered for a Parquet Direct table, including the status of each operation and any error message. Use this function to verify that files were registered, and to diagnose files that failed to register.

See also:

ICEBERG_TABLE_FILES , SYSTEM$PARQUET_DIRECT_AUTO_REFRESH_STATUS , Parquet Direct

Syntax

ICEBERG_TABLE_FILE_REGISTRATION_HISTORY(
  TABLE_NAME => '<string>'
  [ , START_TIME => <constant_expr> ]
)

Arguments

Required:

TABLE_NAME => 'string'

The name of the Parquet Direct table.

If the name is case-sensitive or includes special characters or spaces, enclose it in double quotes within the single quotes; for example, '"My_Table"'.

Optional:

START_TIME => constant_expr

Timestamp (in TIMESTAMP_LTZ format) that marks the beginning of the time range for retrieving registration events. Snowflake retains registration history for 65 days, so the function doesn’t return events older than 65 days, even if you specify an earlier start time.

Default: All available history (the last 65 days).

Output

The function returns the following columns:

Column nameData typeDescription
OPERATION_TIMETIMESTAMP_LTZTime when the registration operation occurred.
FILE_NAMETEXTPath of the Parquet file.
OPERATION_STATUSTEXTStatus of the operation. Valid values:
- REGISTERED_NEW: The file was registered.
- REGISTERED_UPDATE: The file was registered again because it changed.
- REGISTER_SKIPPED: The file was already registered.
- REGISTER_SKIPPED_PATTERN_MISMATCH: The file path didn’t match the table’s PATTERN.
- REGISTER_FAILED: The file couldn’t be registered. MESSAGE contains the reason.
- UNREGISTERED: The file was removed from the table.
- UNREGISTER_FAILED: The file couldn’t be removed from the table. MESSAGE contains the reason.
MESSAGETEXTMessage that accompanies the operation status; for example, the reason a file failed to register.
FILE_SIZENUMBERSize of the file, in bytes.
ROW_COUNTNUMBERNumber of rows in the file.
LAST_MODIFIEDTIMESTAMP_LTZTime when the file was last modified in cloud storage.

Usage notes

  • This function only works with Parquet Direct tables (tables that use a catalog integration with TABLE_FORMAT = NONE or TABLE_FORMAT = HIVE).

  • Snowflake retains file registration history for Parquet Direct tables for 65 days.

  • When you call an Information Schema table function, the session must have an Information Schema in use, or the function name must be fully qualified. For more information, see Snowflake Information Schema.

  • To call this function, use a role that has any privilege on the table.

Examples

Retrieve the file registration history for the table my_parquet_table for the last day:

SELECT *
  FROM TABLE(INFORMATION_SCHEMA.ICEBERG_TABLE_FILE_REGISTRATION_HISTORY(
    TABLE_NAME => 'my_db.my_schema.my_parquet_table',
    START_TIME => DATEADD(day, -1, CURRENT_TIMESTAMP())))
  ORDER BY operation_time DESC;

Find files that have an error message, which can indicate a failed registration:

SELECT operation_time, file_name, operation_status, message
  FROM TABLE(INFORMATION_SCHEMA.ICEBERG_TABLE_FILE_REGISTRATION_HISTORY(
    TABLE_NAME => 'my_parquet_table'))
  WHERE message IS NOT NULL AND message <> ''
  ORDER BY operation_time DESC;