Managing Directory Tables¶

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>' ]

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:

Amazon S3

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

Google Cloud Storage

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

Microsoft Azure

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

Manually Refreshing Directory Tables¶

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

For example:

ALTER STAGE mystage REFRESH;

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

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;

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