COPY FILES¶
Copies files from one stage to another.
This command supports file copy operations from and to existing named stages, as the following table illustrates:
Source location |
Target location |
---|---|
Internal named stage |
Internal named stage |
External stage |
Internal named stage |
Internal named stage |
External stage |
External stage |
External stage |
A target or source external stage can reference files in any of the following cloud storage services or on-premises locations:
Amazon S3
Google Cloud Storage
Microsoft Azure Blob storage
Microsoft Data Lake Storage Gen2
Microsoft Azure General-purpose v2
- See also:
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 ofdatabase_name.schema_name
orschema_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 ...
orFROM ...
value in single quotes.The values for
INTO ...
andFROM ...
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 theFILES
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 namedfile
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:
Privilege |
Object |
Notes |
---|---|---|
USAGE |
External stage |
Required on a source or target external stage. |
READ |
Internal named stage |
Required on a source internal stage. |
WRITE |
Internal named stage |
Required on a target internal stage. |
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 forLIST @<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¶
Copy all of the files from an existing source stage (src_stage
) to an existing target stage (trg_stage
):
COPY FILES
INTO @trg_stage
FROM @src_stage;
Note
To copy files from or to an external stage with a protected storage location, make sure the stage definition includes credentials to access the cloud storage location.
Specify the names of files to copy from an existing source stage (src_stage
) to an existing target stage (trg_stage
):
COPY FILES
INTO @trg_stage
FROM @src_stage
FILES = ('file1.csv', 'file2.csv');
Copy files from a specific path on an existing stage (src_stage/src_path/
)
to a specific path on an existing target stage (trg_stage/trg_path/
):
COPY FILES
INTO @trg_stage/trg_path/
FROM @src_stage/src_path/;
Copy files using pattern matching¶
Use pattern matching to load only compressed CSV files in any path on an existing source stage (src_stage
) to an existing target stage (trg_stage
):
COPY FILES
INTO @trg_stage
FROM @src_stage
PATTERN='.*/.*/.*[.]csv[.]gz';
The .*
component represents zero or more occurrences of any character.
The square brackets escape the period character (.
) that precedes a file extension.
Copy only uncompressed CSV files whose names include the string sales
:
COPY FILES
INTO @trg_stage
FROM @src_stage
PATTERN='.*sales.*[.]csv';