Categories:: Information Schema , Table functions

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY¶

This table function can be used to query information about the metadata history for an external table, including:

Files added or removed automatically as part of a metadata refresh.
Any errors found when refreshing the metadata.

Syntax¶

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

Arguments¶

Required:

TABLE_NAME => 'string': A string specifying an external table name.

Optional:

START_TIME => constant_expr

Timestamp (in TIMESTAMP_LTZ format), within the last 30 days, marking the start of the time range for retrieving metadata update events.

Note

If no start time is specified, the function returns all update events within the last 30 days.
If the start time falls outside the last 30 days, the function returns results within the last 30 days.
If the start time is not a timestamp, it is ignored.

Usage notes¶

Returns results for the external table owner (i.e. the role with the OWNERSHIP privilege on the external table), or a higher role, or a role that has the USAGE privilege on the database and schema that contain an external table and any privilege on the external table.
The table function cannot retrieve metadata about staged data files until the external table is refreshed (i.e. synched) to include the data files in its metadata.
When calling an Information Schema table function, the session must have an INFORMATION_SCHEMA schema in use or the function name must be fully-qualified. For more details, see Snowflake Information Schema.

Output¶

The function returns the following columns:

Column Name	Data Type	Description
JOB_CREATED_TIME	TIMESTAMP_LTZ	Timestamp when the operation occurred
FILE_NAME	TEXT	Name of the staged source file and relative path to the file
OPERATION_STATUS	TEXT	Status: REGISTERED_NEW, REGISTERED_UPDATE, REGISTER_SKIPPED, REGISTER_FAILED, UNREGISTERED, or UNREGISTER_FAILED
MESSAGE	TEXT	Message accompanying the operation status
FILE_SIZE	NUMBER	Size of the file (in bytes) added to the external table
LAST_MODIFIED	TIMESTAMP_LTZ	Timestamp when the file was last updated in the stage

Examples¶

Retrieve the metadata stored for all data files referenced by the mytable external table:

select *
from table(information_schema.external_table_file_registration_history(TABLE_NAME=>'MYTABLE'));

Retrieve the registration events for external table mydb.public.external_table_name that started within the last hour:

select *
  from table(information_schema.external_table_file_registration_history(
    start_time=>dateadd('hour',-1,current_timestamp()),
    table_name=>'mydb.public.external_table_name'));

Retrieve the registration events for external table mydb.public.external_table_name starting at midnight on April 25, 2022:

select *
  from table(information_schema.external_table_file_registration_history(
    start_time=>cast('2022-04-25' as timestamp),
    table_name=>'mydb.public.external_table_name'));