Managing Directory Tables

This topic provides instructions for creating and managing external or internal stages with directory tables.

Automatically Refreshing Directory Table Metadata

The metadata for a directory table can be refreshed automatically using the event messaging service for your cloud storage service.

The refresh operation synchronizes the metadata with the latest set of associated files in the external stage and path, i.e.:

  • New files in the path are added to the table metadata.

  • Changes to files in the path are updated in the table metadata.

  • Files no longer in the path are removed from the table metadata.

For instructions to create stages with automatically refreshed directory tables, see Automated Directory Table Metadata Refreshes.

Note

Automatically refreshing the metadata is not available for directory tables on internal stages that reference external cloud storage. You must manually refresh the directory table metadata for these types of stages. For instructions, see Manually Refreshing Directory Table Metadata (in this topic).

To register any missed files, we suggest that you follow our best practices for staging your data files and periodically execute an ALTER STAGE … REFRESH statement. For satisfactory performance, we also recommend that you use a selective path prefix with ALTER STAGE. Doing so reduces the number of files that need to be listed and checked if they have been registered already (e.g. bucket_name/YYYY/MM/DD/ or even bucket_name/YYYY/MM/DD/HH/ depending on your volume).

Manually Refreshing Directory Table Metadata

This section provides instructions for creating stages (using CREATE STAGE) that layer a directory table to store metadata about the staged files. The directory tables created using the instructions require manual metadata refreshes.

Currently, directory tables on internal stages require manual metadata refreshes. You could also choose to include a directory table on external stages and refresh the metadata manually. For information about automated metadata refreshes, see Automatically Refreshing Directory Table Metadata (in this topic).

Note

Manually refreshing metadata on an external stage blocks any automated refresh operations from occurring at the same time. Automated refreshes will resume after the manual refresh completes.

The syntax for creating a stage with a directory table is nearly identical to creating a standard external or internal stage. Set the optional DIRECTORY parameter to TRUE.

For the complete syntax and parameter descriptions, see CREATE STAGE.

Syntax

-- Internal stage
CREATE [ OR REPLACE ] [ TEMPORARY ] STAGE [ IF NOT EXISTS ] <internal_stage_name>
  [ DIRECTORY = ( { ENABLE = TRUE | FALSE } ) ]
  [ ENCRYPTION = (TYPE = 'SNOWFLAKE_FULL' | TYPE = 'SNOWFLAKE_SSE') ]
  [ FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
  [ COPY_OPTIONS = ( copyOptions ) ]
  [ COMMENT = '<string_literal>' ]

-- External stage
CREATE [ OR REPLACE ] [ TEMPORARY ] STAGE [ IF NOT EXISTS ] <external_stage_name>
      <cloud_storage_access_settings>
    [ DIRECTORY = ( { ENABLE = TRUE | FALSE } ) ]
    [ FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
    [ COPY_OPTIONS = ( copyOptions ) ]
    [ COMMENT = '<string_literal>' ]
Copy

Where:

ENABLE = TRUE | FALSE

Specifies whether to add a directory table to the stage. When the value is TRUE, a directory table is created with the stage.

Default: FALSE

[ ENCRYPTION = (TYPE = 'SNOWFLAKE_FULL' | TYPE = 'SNOWFLAKE_SSE') ]

Specifies the type of encryption supported for all files stored in the stage.

TYPE = ...

Specifies the encryption type used. Possible values are:

  • SNOWFLAKE_FULL: Client-side encryption. The files are encrypted by a client when it uploads them to the internal stage using PUT.

  • SNOWFLAKE_SSE: Server-side encryption. The files are encrypted when they arrive in the stage.

    Specify server-side encryption if you plan to query pre-signed URLs for your staged files.

    When files in a stage are client-side encrypted, users cannot read the staged files without having access to the encryption key.

    For more information about internal named stages with server-side encryption only, see CREATE STAGE.

Default: SNOWFLAKE_FULL

Usage Notes

  • After you create a stage with a directory table, you must execute ALTER STAGE … REFRESH to manually refresh the directory table metadata.

Examples

Create an internal stage named mystage that includes a directory table. The stage references a file format named myformat:

CREATE STAGE mystage
  DIRECTORY = (ENABLE = TRUE)
  FILE_FORMAT = myformat;
Copy

Create an external stage named mystage that includes a directory table. The stage references a bucket or container named load with a path of files. Secure access to the cloud storage location is provided via the my_storage_int storage integration:

Note

The storage location in the URL value must end in a forward slash (/).

Amazon S3

CREATE STAGE mystage
  URL='s3://load/files/'
  STORAGE_INTEGRATION = my_storage_int
  DIRECTORY = (ENABLE = TRUE);
Copy

Google Cloud Storage

CREATE STAGE mystage
  URL='gcs://load/files/'
  STORAGE_INTEGRATION = my_storage_int
  DIRECTORY = (ENABLE = TRUE);
Copy

Microsoft Azure

CREATE STAGE mystage
  URL='azure://myaccount.blob.core.windows.net/load/files/'
  STORAGE_INTEGRATION = my_storage_int
  DIRECTORY = (ENABLE = TRUE);
Copy

Manually Refreshing Directory Tables

Refresh the metadata in a directory table manually using the ALTER STAGE command.

For example:

ALTER STAGE mystage REFRESH;
Copy

Retrieving File URLs from Directory Tables

Query a directory table:

SELECT * FROM DIRECTORY( @<stage_name> )
Copy

Where:

stage_name

Name of a stage that includes a directory table.

For information about SELECT as a statement, and the other clauses within the statement, see Query Syntax.

Output

The output from a directory table query can include the following columns:

Column

Data Type

Description

RELATIVE_PATH

TEXT

Path to the files to access using the file URL.

SIZE

NUMBER

Size of the file (in bytes).

LAST_MODIFIED

TIMESTAMP_LTZ

Timestamp when the file was last updated in the stage.

MD5

HEX

MD5 checksum for the file.

ETAG

HEX

ETag header for the file.

FILE_URL

TEXT

Snowflake-hosted file URL to the file.

The file URL has the following format:

https://<account_identifier>/api/files/<db_name>.<schema_name>.<stage_name>/<relative_path>
Copy

Where:

account_identifier

Hostname of the Snowflake account for your stage. The hostname starts with an account locator (provided by Snowflake) and ends with the Snowflake domain (snowflakecomputing.com):

account_locator.snowflakecomputing.com

For more details, see Account Identifiers.

Note

For Business Critical accounts, a privatelink segment is prepended to the URL just before snowflakecomputing.com (privatelink.snowflakecomputing.com), even if private connectivity to the Snowflake service is not enabled for your account.

db_name

Name of the database that contains the stage where your files are located.

schema_name

Name of the schema that contains the stage where your files are located.

stage_name

Name of the stage where your files are located.

relative_path

Path to the files to access using the file URL.

Usage Notes

  • If files downloaded from an internal stage are corrupted, verify with the stage creator that ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE') is set for the stage.

Examples

Retrieve all metadata columns in a directory table for a stage named mystage:

SELECT * FROM DIRECTORY(@mystage);
Copy

Return the FILE_URL column values from the same directory table. Only return the file URLs for files greater than 100 K bytes in size:

SELECT FILE_URL FROM DIRECTORY(@mystage) WHERE SIZE > 100000;
Copy

Return the FILE_URL column values from the same directory table. Only return the file URLs for comma-separated value files:

SELECT FILE_URL FROM DIRECTORY(@mystage) WHERE RELATIVE_PATH ILIKE '%.csv';
Copy

Streams on Directory Tables

Standard (i.e. delta) streams on a directory table track files that are added or dropped in the referenced cloud storage location. For the SQL command syntax and examples, see CREATE STREAM.