Categories:

DML Commands - File Staging

REMOVE

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

  • Named internal stage.

  • Stage for a specified table.

  • Stage for the current user.

REMOVE can be abbreviated to RM.

See also:

LIST

Syntax

REMOVE internalStage [ PATTERN = '<regex_pattern>' ]

Where:

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

Required Parameters

internalStage

Specifies the location in Snowflake where files are staged:

@[namespace.]int_stage_name[/path]

Files are removed from the specified named internal stage.

@[namespace.]%table_name[/path]

Files are removed from the stage for the specified table.

@~[/path]

Files are removed from the stage for the current user.

Where:

  • namespace is the database and/or schema in which the named internal 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 uploaded 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 uploaded.

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

Optional Parameters

PATTERN = 'regex_pattern'

Specifies a regular expression pattern for filtering files to remove.

Usage Notes

  • The command removes all directories and files that match a specified path. For example, the following statement would match any of the following objects in the mytable table stage:

    • myobject.csv.gz (file)

    • myobject (directory)

    • myobject_new (directory)

    rm @%mytable/myobject;
    
  • To remove all files for a specific directory, include a forward-slash (/) at the end of the path. For example:

    rm @%mytable/myobject/;
    
  • If a REMOVE statement is interrupted before it has completed running, any files already removed by the statement are not restored.

Examples

Remove all files from the stage for the orderstiny_ext table:

LIST @%orderstiny_ext;

-------------------+---------+----------------------------------+
       name        |  size   |               md5                |
-------------------+---------+----------------------------------+
 orders_001.csv    | 1126625 | f9fd7b364318ccf69555f172b5f93636 |
 orders_100.csv    | 5865    | eac8b23263058294ed86e652b3fb868d |
 orders_101.csv    | 101225  | 55aa7fcef159714395d537e39c0606cb |
-------------------+---------+----------------------------------+

REMOVE @%orderstiny_ext;

----------------+---------+
      name      | result  |
----------------+---------+
 orders_001.csv | removed |
 orders_100.csv | removed |
 orders_101.csv | removed |
----------------+---------+

LIST @%orderstiny_ext;

-------------------+---------+----------------------------------+
       name        |  size   |               md5                |
-------------------+---------+----------------------------------+

Use the abbreviated form of the command to remove files whose names match the pattern *jun* from the stage for the current user:

RM @~ pattern='.*jun.*';

-----------------+---------+
       name      | result  |
-----------------+---------+
 01-jun-2015.csv | removed |
 02-jun-2015.csv | removed |
-----------------+---------+