Categories:

DML Commands - File Staging

LIST

Returns a list of files that have been staged (i.e. uploaded from a local file system or unloaded from a table) in one of the following Snowflake stages:

  • Named internal stage.

  • Named external stage.

  • Stage for a specified table.

  • Stage for the current user.

LIST can be abbreviated to LS.

See also:

REMOVE

PUT , COPY INTO <table>

COPY INTO <location> , GET

Syntax

LIST { internalStage | externalStage } [ PATTERN = '<regex_pattern>' ]

Where:

internalStage ::=
    @[<namespace>.]<int_stage_name>[/<path>]
  | @[<namespace>.]%<table_name>[/<path>]
  | @~[/<path>]
externalStage ::=
    @[<namespace>.]<ext_stage_name>[/<path>]

Required Parameters

internalStage | externalStage

Specifies the location where the data files are staged:

@[namespace.]int_stage_name[/path]

Files are in the specified named internal stage.

@[namespace.]ext_stage_name[/path]

Files are in the specified named external stage.

@[namespace.]%table_name[/path]

Files are in the stage for the specified table.

@~[/path]

Files are in the stage for the current user.

Where:

  • namespace is the database and/or schema in which the named stage or table resides. It is optional if a database and schema are currently in use within the session; otherwise, it is required.

  • The optional path restricts the set of files being listed to the files under the folder prefix. If path is specified, but no file is explicitly named in the path, all data files in the path are listed.

The string can be enclosed in single quotes, which allows special characters, including spaces, in location names (e.g. '@"my stage"').

Tip

Specifying a path/prefix provides a scope for the LIST command, potentially reducing the amount of time required to run the command.

Optional Parameters

PATTERN = 'regex_pattern'

Specifies a regular expression pattern for filtering files from the output.

Usage Notes

  • In contrast to named stages, table and user stages are not first-class database objects; rather, they are implicit stages associated with the table/user. As such, they have no grantable privileges of their own:

    • You can always list files in your user stage (i.e. no privileges are required).

    • To list files in a table stage, you must use a role that has the OWNERSHIP privilege on the table.

Output

The command returns the following columns:

Column

Description

name

Name of the staged file

size

Size of the file compressed

md5

The MD5 column stores an MD5 hash of the contents of the staged data file, which can be used to verify the file was staged (uploaded) correctly. Note that AWS S3 stages report the value via the S3 eTag field, which may not be an MD5 hash of the file contents.

last_modified

Timestamp when the file was last updated in the stage

Examples

List all the files in the stage for the mytable table:

LIST @%mytable;

List the files that match a regular expression (i.e. all file names containing the string data_0) in the stage for the mytable table:

LIST @%mytable PATTERN='.*data_0.*';

List the files that match a regular expression (i.e. all file names containing the string data_0) in a named stage (my_csv_stage) with a prefix (/analysis/):

LIST @my_csv_stage/analysis/ PATTERN='.*data_0.*';

Use the abbreviated form of the command to list all the files in the stage for the current user:

LS @~;