REMOVE | Snowflake Documentation (original) (raw)

Removes files from either an external (external cloud storage) or internal (i.e. Snowflake) stage.

For internal stages, the following stage types are supported:

• 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 | externalStage } [ PATTERN = '' ]

Where:

internalStage ::= @[.][/] | @[.]%[/] | @~[/]

externalStage ::= @[.][/]

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 internal stage or table resides. It is optional if a database and schema are currently in use within the session; otherwise, it is required.
• _path_ is an optional case-sensitive path for files in the cloud storage location (i.e. files have names that begin with a common string) that limits access to a set of files. Paths are alternatively called prefixes or folders by different cloud storage services.

Note

If the stage name or path includes spaces or special characters, it must be enclosed in single quotes (e.g. '@"my stage"'for a stage named "my stage").

Optional parameters¶

PATTERN = '_regexpattern_'

Specifies a regular expression pattern for filtering files to remove. The command lists all files in the specified _path_and applies the regular expression pattern on each of the files found.

Usage notes¶

• If you are loading data from a file on a stage, do not remove the staged files until the data has been loaded successfully. To check if the data has been loaded successfully, use the COPY_HISTORY command. Check the STATUS column to determine if the data from the file has been loaded. Note that if the status is Load in progress, removing the staged file can result in partial loads and data loss.
• To run this command with an external stage that uses a storage integration, you must use a role that has or inherits the USAGE privilege on the storage integration.
  For more information, see Stage privileges.
• Removing files from an external stage requires granting the following role or permission to Snowflake in your cloud storage account:

  Cloud Storage Service	Role or Permission	Instructions
  Amazon S3	s3:DeleteObject	Configuring secure access to Amazon S3
  Google Cloud Storage	storage.objects.delete	Configuring an integration for Google Cloud Storage
  Microsoft Azure (Blob storage)	Storage Blob Data Contributor	Configuring an Azure container for loading data

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

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

• Do not remove the worksheet_data directory in the Snowflake user stage. The Classic Console stores metadata for worksheets in the Worksheets tab in this directory. Removing the directory removes access to the worksheets, which cannot be restored.
  This precaution does not apply to worksheets in Snowsight, which are stored elsewhere and are not prone to accidental deletion.
• 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 path1/subpath2 path in a named internal or external stage named mystage:

REMOVE @mystage/path1/subpath2;

Remove all files from the stage for the orders table:

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