Managing Snowflake stages

The snow stage commands let you perform additional stage-specific tasks:

Create a named stage

The snow stage create command creates a named stage if it does not already exist.

snow stage create <stage_name>
Copy

For example, to create a stage called new_stage, enter the following command:

snow stage create new_stage
Copy
+-----------------------------------------------------+
| key    | value                                      |
|--------+--------------------------------------------|
| status | Stage area NEW_STAGE successfully created. |
+-----------------------------------------------------+

The following example shows what happens if you try to create a stage, packages, that already exists.

# stage that already exists
snow stage create packages
Copy
+--------------------------------------------------------+
| key    | value                                         |
|--------+-----------------------------------------------|
| status | PACKAGES already exists, statement succeeded. |
+--------------------------------------------------------+

Copy files to and from a stage

The snow stage copy command copies a file from the local machine to a stage or from a stage to a local machine.

snow stage copy <source_path> <destination_path>
Copy

One of <source_path> or <destination_path> must be a local path, while the other must a path to the Snowflake stage. Note the following:

  • The stage path must start with @, as shown in the following examples.

  • When copying a single file, the <destination_path> must identify a directory, not a file. If the specified directory does not exist, the command creates it.

  • By default, when copying a local directory to a stage, the local directory must contain only files. You can use the --recursive option to upload sub-directories in the local directory. You can use glob patterns with the --recursive option.

  • When copying a directory from a stage to a local filesystem, the command currently flattens its internal tree structure. To illustrate, assume your local directory contains the following:

    test_case.py
tests/abc.py
tests/test1/x1.txt
tests/test1/x2.txt

    After copying the directory from the stage, the local filesystem directory contains the following:

    test_case.py
abc.py
x1.txt
x2.txt

    Note

    If you want to maintain the file structure from the source directory, you can include the --recursive option.

Copy files to a stage

To copy files from the local machine to a stage, enter a commands similar to the following:

snow stage copy local_example_app @example_app_stage/app
Copy
put file:///.../local_example_app/* @example_app_stage/app4 auto_compress=false parallel=4 overwrite=False
+--------------------------------------------------------------------------------------
| source           | target           | source_size | target_size | source_compression...
|------------------+------------------+-------------+-------------+--------------------
| environment.yml  | environment.yml  | 62          | 0           | NONE             ...
| snowflake.yml    | snowflake.yml    | 252         | 0           | NONE             ...
| streamlit_app.py | streamlit_app.py | 109         | 0           | NONE             ...
+--------------------------------------------------------------------------------------

You can use the snow stage list-files command to verify the command copied the files successfully:

snow stage list-files example_app_stage
Copy
ls @example_app_stage
​+------------------------------------------------------------------------------------
| name                                   | size | md5                              | ...
|----------------------------------------+------+----------------------------------+-
| example_app_stage/app/environment.yml  | 64   | 45409c8da098125440bfb7ffbcd900f5 | ...
| example_app_stage/app/snowflake.yml    | 256  | a510b1d59fa04f451b679d43c703b6d4 | ...
| example_app_stage/app/streamlit_app.py | 112  | e6c2a89c5a164e34a0faf60b086bbdfc | ...
+------------------------------------------------------------------------------------

Copy files from a stage

The following example copies files from a stage to a directory on the local machine:

mkdir local_app_backup
snow stage copy @example_app_stage/app local_app_backup
Copy
get @example_app_stage/app file:///.../local_app_backup/ parallel=4
+------------------------------------------------+
| file             | size | status     | message |
|------------------+------+------------+---------|
| environment.yml  | 62   | DOWNLOADED |         |
| snowflake.yml    | 252  | DOWNLOADED |         |
| streamlit_app.py | 109  | DOWNLOADED |         |
+------------------------------------------------+

You can list the directory contents to verify the command copied the files correctly:

ls local_app_backup
Copy
environment.yml  snowflake.yml    streamlit_app.py

Note that the local directory must exist.

You can copy from a user stage (@~):

snow stage copy "@~" . --recursive
Copy
+------------------------------------------------+
| file             | size | status     | message |
|------------------+------+------------+---------|
| environment.yml  | 62   | DOWNLOADED |         |
| snowflake.yml    | 252  | DOWNLOADED |         |
| streamlit_app.py | 109  | DOWNLOADED |         |
+------------------------------------------------+

Use glob patterns to specify files

You can specify multiple files matching a regular expression by using a glob pattern for the source_path argument. You must enclose the glob pattern in single or double quotes.

The following example copies all .txt files in a directory to a stage.

snow stage copy "testdir/*.txt" @TEST_STAGE_3
Copy
put file:///.../testdir/*.txt @TEST_STAGE_3 auto_compress=false parallel=4 overwrite=False
+------------------------------------------------------------------------------------------------------------+
| source | target | source_size | target_size | source_compression | target_compression | status   | message |
|--------+--------+-------------+-------------+--------------------+--------------------+----------+---------|
| b1.txt | b1.txt | 3           | 16          | NONE               | NONE               | UPLOADED |         |
| b2.txt | b2.txt | 3           | 16          | NONE               | NONE               | UPLOADED |         |
+------------------------------------------------------------------------------------------------------------+

List the contents of a stage

The snow stage list-files command lists the stage contents.

snow stage list-files <stage_path>
Copy

For example, to list the packages in a stage, enter the following command:

snow stage list-files packages
Copy
ls @packages
+-------------------------------------------------------------------------------------
| name                 | size     | md5                              | last_modified
|----------------------+----------+----------------------------------+----------------
| packages/plp.Ada.zip | 824736   | 90639175a0ac7735e67525118b81047c | Tue, 16 Jan ...
| packages/samrand.zip | 13721024 | 648f0bae2f65fd4c9f178b17c23de7e5 | Tue, 16 Jan ...
+-------------------------------------------------------------------------------------

Execute files from a stage

The snow stage execute command executes SQL or Python files from a stage.

snow stage execute <stage_path>
Copy

  • For .sql files, the it performs an EXECUTE IMMEDIATE FROM command on .sql files from a stage.

  • For .py files, it executes a session-scoped Snowpark Python procedure.

    Snowflake CLI executes the procedure in Snowflake to guarantee a consistent execution environment. If your Python scripts require additional requirements, you should specify them in a requirements.txt file that resides in the same directory as the files on the stage. The snow stage execute command only supports packages from the Snowflake Anaconda channel.

    By default, the command looks for the requirements.txt file in the following precedence:

    • Stage path specified in the command’s stage_path parameter.

    • Parent directories of the specified stage path hierarchy, until it reaches the stage.

    • If you don’t specify a requirements.txt file, the command assumes no additional packages are necessary.

    For example, if you run snow stage execute @my_stage/ml/app1/scripts, the command looks for the file as follows:

    • my_stage/ml/app1/scripts/requirements.txt

    • my_stage/ml/app1/requirements.txt

    • my_stage/ml/requirements.txt

    • my_stage/ml/requirements.txt

The following examples illustrate ways to execute different sets of .sql files from a stage:

  • Specify only a stage name to execute all .sql files in the stage:

    snow stage execute "@scripts"
    Copy
    SUCCESS - scripts/script1.sql
SUCCESS - scripts/script2.sql
SUCCESS - scripts/dir/script.sql
+------------------------------------------+
| File                   | Status  | Error |
|------------------------+---------+-------|
| scripts/script1.sql    | SUCCESS | None  |
| scripts/script2.sql    | SUCCESS | None  |
| scripts/dir/script.sql | SUCCESS | None  |
+------------------------------------------+

  • Specify a user stage (@~) to execute the script.sql files in the user stage:

    snow stage execute "@~/script1.sql"
    Copy
    SUCCESS - scripts/script1.sql
+------------------------------------------+
| File                   | Status  | Error |
|------------------------+---------+-------|
| @~/script.sql          | SUCCESS | None  |
+------------------------------------------+

Use glob patterns to select subsets of files

  • Specify a glob-like pattern to execute all .sql files in the dir directory:

    snow stage execute "@scripts/dir/*"
    Copy
    SUCCESS - scripts/dir/script.sql
+------------------------------------------+
| File                   | Status  | Error |
|------------------------+---------+-------|
| scripts/dir/script.sql | SUCCESS | None  |
+------------------------------------------+

  • Specify a glob-like pattern to execute only .sql files in the dir directory that begin with “script”, followed by one character:

    snow stage execute "@scripts/script?.sql"
    Copy
    SUCCESS - scripts/script1.sql
SUCCESS - scripts/script2.sql
+---------------------------------------+
| File                | Status  | Error |
|---------------------+---------+-------|
| scripts/script1.sql | SUCCESS | None  |
| scripts/script2.sql | SUCCESS | None  |
+---------------------------------------+

  • Specify a direct file path with the --silent option:

    snow stage execute "@scripts/script1.sql" --silent
    Copy
    +---------------------------------------+
| File                | Status  | Error |
|---------------------+---------+-------|
| scripts/script1.sql | SUCCESS | None  |
+---------------------------------------+

Remove a file from a stage

The snow stage remove command removes a file from a stage.

snow stage remove <stage_name> <file_name>
Copy

For example, to remove a file from a stage, enter a command similar to the following:

snow stage remove example_app_stage app/pages/my_page.py
Copy
+-------------------------------------------------+
| key    | value                                  |
|--------+----------------------------------------|
| name   | example_app_stage/app/pages/my_page.py |
| result | removed                                |
+-------------------------------------------------+
Language: English