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

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

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

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