COPY FILES¶

Syntax¶

COPY FILES INTO @[<namespace>.]<stage_name>[/<path>/]
  FROM @[<namespace>.]<stage_name>[/<path>/]
  [ FILES = ( '<file_name>' [ , '<file_name>' ] [ , ... ] ) ]
  [ PATTERN = '<regex_pattern>' ]
  [ DETAILED_OUTPUT = { TRUE | FALSE } ]

Required parameters¶

INTO @[namespace.]stage_name[/path/]

Specifies the target location for the copied files.

FROM @[namespace.]stage_name[/path/]

Specifies the source location where the files to copy are staged.

For the INTO and FROM parameters:

• namespace is the database or schema in which the internal or external stage resides, in the form of database_name.schema_name or schema_name. The namespace is optional if a database and schema are currently in use within the user session; otherwise, it is required.

• path is an optional, case-sensitive path in the cloud storage location that specifies a set of files to copy from the source stage or a specific location on the target stage. Your cloud storage service might call the path a prefix or a folder.

  Note

  • If a target or source path name includes special characters or spaces, you must enclose the INTO ... or FROM ... value in single quotes.

  • The values for INTO ... and FROM ... must be literal constants. The values cannot be SQL variables.

Optional parameters¶

FILES = ( 'file_name' [ , 'file_name' ... ] )

Specifies a list of one or more comma-separated file names to copy. The files must already be staged in the source location that you specify in the command. Snowflake skips any specified files that can’t be found.

You can specify a maximum of 1000 file names.

Note

To set the file path for external stages, Snowflake prepends the URL in the stage definition to each file name in the list.

However, Snowflake does not insert a separator between the path and file name. You must explicitly include a separator (/) at the end of the URL in the stage definition or at the beginning of each file name in the FILES list.

PATTERN = 'regex_pattern'

Specifies a regular expression pattern for filtering the list of files to copy. This command applies the regular expression to the entire storage location in the FROM clause.

Tip

For best performance, avoid patterns that filter on a large number of files.

DETAILED_OUTPUT = { TRUE | FALSE }

Specifies whether the command output should summarize the results of the copy operation or list each file copied.

Values:

• If TRUE, the output includes a row for each file copied to the target location. A single column named file contains the target path (if applicable) and file name for each copied file.

• If FALSE, the output is a single row with the number of files that were copied.

Default:

TRUE

Access control requirements¶

A role used to execute this SQL command must have the following privileges (depending on the source and target locations) at a minimum:

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

• This command does not support the following:

  • Copying files from or to user or table stages.

  • Copying data in archival cloud storage classes that requires restoration before it can be retrieved. Archival storage classes include Amazon S3 Glacier Flexible Retrieval, Glacier Deep Archive, or Microsoft Azure Archive Storage.

  • Copying files that are larger than 5GB.

• Considerations for running this command:

  • COPY FILES statements overwrite any existing files with matching names in the target location. The command does not remove any existing files that don’t match the names of the copied files.

  • If a file copy operation fails, Snowflake does not perform any automatic cleanup.

  • Copying files from Google Cloud Storage: A COPY FILES statement might fail if the object list for an external stage includes one or more directory blobs. A directory blob is a path that ends in a forward slash character (/). In the following example output for LIST @<stage>, my_gcs_stage/load/ is a directory blob.

    +---------------------------------------+------+----------------------------------+-------------------------------+
    | name                                  | size | md5                              | last_modified                 |
    |---------------------------------------+------+----------------------------------+-------------------------------|
    | my_gcs_stage/load/                    |  12  | 12348f18bcb35e7b6b628ca12345678c | Mon, 11 Aug 2022 16:57:43 GMT |
    | my_gcs_stage/load/data_0_0_0.csv.gz   |  147 | 9765daba007a643bdff4eae10d43218y | Mon, 11 Aug 2022 18:13:07 GMT |
    +---------------------------------------+------+----------------------------------+-------------------------------+
    

    Google creates directory blobs when you use the Google Cloud console to create a directory.

    To avoid this issue, use the PATTERN option to specify which files to copy. For an example, see Copy files using pattern matching.

• The COPY FILES command incurs data transfer and compute costs:

  • Data transfer: Cloud providers might charge for data transferred out of their own network. To recover these expenses, Snowflake charges a per-byte fee when you copy files from an internal Snowflake stage into an external stage in a different region or with a different cloud provider. Snowflake does not charge for data ingress (for example, when copying files from an external stage into an internal stage).

    For more information about data transfer billing, see Understanding data transfer cost.

  • Compute: COPY FILES is a serverless feature and doesn’t require a virtual warehouse. The line item for the COPY FILES command on your Snowflake bill does not include any cloud services charges.

    For more information about compute resource billing, see Understanding compute cost.

  Note

  Some Snowflake features, such as Native Apps and worksheets, incur COPY FILES charges. As a result, you might see COPY FILES charges even if you haven’t executed the COPY FILES command. For more information about these charges, contact Snowflake Support.

• Snowflake does not maintain a file copy history for this command.

Examples¶

COPY FILES
  INTO @trg_stage
  FROM @src_stage;

COPY FILES
  INTO @trg_stage
  FROM @src_stage
  FILES = ('file1.csv', 'file2.csv');

COPY FILES
  INTO @trg_stage/trg_path/
  FROM @src_stage/src_path/;

COPY FILES
  INTO @trg_stage
  FROM @src_stage
  PATTERN='.*/.*/.*[.]csv[.]gz';

COPY FILES
  INTO @trg_stage
  FROM @src_stage
  PATTERN='.*sales.*[.]csv';