Categories:

Information Schema , Table Functions

STAGE_DIRECTORY_FILE_REGISTRATION_HISTORY¶

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

  • Files added or removed automatically as part of a metadata refresh.

  • Any errors found when refreshing the metadata.

Syntax¶

STAGE_DIRECTORY_FILE_REGISTRATION_HISTORY (
      STAGE_NAME => '<string>'
      [, START_TIME => <constant_expr> ] )
Copy

Arguments¶

Required:

STAGE_NAME => 'string'

A string specifying the name of a stage that has a directory table.

Optional:

START_TIME => constant_expr

Timestamp (in TIMESTAMP_LTZ format), within the last 14 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 14 days.

  • If the start time falls outside the last 14 days, the function returns empty results.

Usage Notes¶

  • Returns results for the stage owner (i.e. the role with the OWNERSHIP privilege on the stage), or a higher role, or a role that has the USAGE privilege on the database and schema that contain a stage with a directory table and any privilege on the stage.

  • The table function cannot retrieve metadata about staged data files until the directory 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 directory 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 mystage stage:

SELECT *
  FROM TABLE(information_schema.stage_directory_file_registration_history(
  STAGE_NAME=>'MYSTAGE'));
Copy

Retrieve the registration events for the directory table on the mydb.public.mystage stage that started within the last hour:

SELECT *
  FROM TABLE(information_schema.stage_directory_file_registration_history(
    START_TIME=>DATEADD('hour',-1,current_timestamp()),
    STAGE_NAME=>'mydb.public.mystage'));
Copy
Language: English