COPY INTO <location>¶

Additional cloud provider parameters¶

STORAGE_INTEGRATION = integration_name or . CREDENTIALS = ( cloud_specific_credentials )

Supported when the COPY statement specifies an external storage URI rather than an external stage name for the target cloud storage location. Specifies the security credentials for connecting to the cloud provider and accessing the private storage container where the unloaded files are staged.

Required only for unloading into an external private cloud storage location; not required for public buckets/containers

Amazon S3

STORAGE_INTEGRATION = integration_name

Specifies the name of the storage integration used to delegate authentication responsibility for external cloud storage to a Snowflake identity and access management (IAM) entity. For more details, see CREATE STORAGE INTEGRATION.

Note

Snowflake recommends the use of storage integrations. This option avoids the need to supply cloud storage credentials using the CREDENTIALS parameter when creating stages or loading data.

CREDENTIALS = ( AWS_KEY_ID = 'string' AWS_SECRET_KEY = 'string' [ AWS_TOKEN = 'string' ] ) or . CREDENTIALS = ( AWS_ROLE = 'string' )

Specifies the security credentials for connecting to AWS and accessing the private S3 bucket where the unloaded files are staged. For more information, see Configuring secure access to Amazon S3.

The credentials you specify depend on whether you associated the Snowflake access permissions for the bucket with an AWS IAM (Identity & Access Management) user or role:

• IAM user: Temporary IAM credentials are required. Temporary (aka “scoped”) credentials are generated by AWS Security Token Service (STS) and consist of three components:

  • AWS_KEY_ID

  • AWS_SECRET_KEY

  • AWS_TOKEN

  All three are required to access a private bucket. After a designated period of time, temporary credentials expire and can no longer be used. You must then generate a new set of valid temporary credentials.

  Important

  COPY commands contain complex syntax and sensitive information, such as credentials. In addition, they are executed frequently and are often stored in scripts or worksheets, which could lead to sensitive information being inadvertently exposed. The COPY command allows permanent (aka “long-term”) credentials to be used; however, for security reasons, do not use permanent credentials in COPY commands. Instead, use temporary credentials.

  If you must use permanent credentials, use external stages, for which credentials are entered once and securely stored, minimizing the potential for exposure.

• IAM role: Omit the security credentials and access keys and, instead, identify the role using AWS_ROLE and specify the AWS role ARN (Amazon Resource Name).

  Important

  The ability to use an AWS IAM role to access a private S3 bucket to load or unload data is now deprecated (i.e. support will be removed in a future release, TBD). Snowflake recommends modifying any existing S3 stages that use this feature to instead reference storage integration objects. For instructions, see Option 1: Configuring a Snowflake storage integration to access Amazon S3.

Google Cloud Storage

STORAGE_INTEGRATION = integration_name

Specifies the name of the storage integration used to delegate authentication responsibility for external cloud storage to a Snowflake identity and access management (IAM) entity. For more details, see CREATE STORAGE INTEGRATION.

Microsoft Azure

STORAGE_INTEGRATION = integration_name

Specifies the name of the storage integration used to delegate authentication responsibility for external cloud storage to a Snowflake identity and access management (IAM) entity. For more details, see CREATE STORAGE INTEGRATION.

Note

Snowflake recommends the use of storage integrations. This option avoids the need to supply cloud storage credentials using the CREDENTIALS parameter when creating stages or loading data.

CREDENTIALS = ( AZURE_SAS_TOKEN = 'string' )

Specifies the SAS (shared access signature) token for connecting to Azure and accessing the private container where the files containing data are staged. Credentials are generated by Azure.

ENCRYPTION = ( cloud_specific_encryption )

For use in ad hoc COPY statements (statements that do not reference a named external stage). Required only for unloading data to files in encrypted storage locations

Amazon S3

ENCRYPTION = ( [ TYPE = 'AWS_CSE' ] [ MASTER_KEY = '<string>' ] | [ TYPE = 'AWS_SSE_S3' ] | [ TYPE = 'AWS_SSE_KMS' [ KMS_KEY_ID = '<string>' ] ] | [ TYPE = 'NONE' ] )

TYPE = ...

Specifies the encryption type used. Possible values are:

• AWS_CSE: Client-side encryption (requires a MASTER_KEY value). Currently, the client-side master key you provide can only be a symmetric key. Note that, when a MASTER_KEY value is provided, Snowflake assumes TYPE = AWS_CSE (i.e. when a MASTER_KEY value is provided, TYPE is not required).

• AWS_SSE_S3: Server-side encryption that requires no additional encryption settings.

• AWS_SSE_KMS: Server-side encryption that accepts an optional KMS_KEY_ID value.

For more information about the encryption types, see the AWS documentation for client-side encryption or server-side encryption.

• NONE: No encryption.

MASTER_KEY = 'string' (applies to AWS_CSE encryption only)

Specifies the client-side master key used to encrypt the files in the bucket. The master key must be a 128-bit or 256-bit key in Base64-encoded form.

KMS_KEY_ID = 'string' (applies to AWS_SSE_KMS encryption only)

Optionally specifies the ID for the AWS KMS-managed key used to encrypt files unloaded into the bucket. If no value is provided, your default KMS key ID is used to encrypt files on unload.

Note that this value is ignored for data loading.

Google Cloud Storage

ENCRYPTION = ( [ TYPE = 'GCS_SSE_KMS' | 'NONE' ] [ KMS_KEY_ID = 'string' ] )

TYPE = ...

Specifies the encryption type used. Possible values are:

• GCS_SSE_KMS: Server-side encryption that accepts an optional KMS_KEY_ID value.

  For more information, see the Google Cloud Platform documentation:

  • https://cloud.google.com/storage/docs/encryption/customer-managed-keys

  • https://cloud.google.com/storage/docs/encryption/using-customer-managed-keys

• NONE: No encryption.

KMS_KEY_ID = 'string' (applies to GCS_SSE_KMS encryption only)

Optionally specifies the ID for the Cloud KMS-managed key that is used to encrypt files unloaded into the bucket. If no value is provided, your default KMS key ID set on the bucket is used to encrypt files on unload.

This value is ignored for data loading. The load operation should succeed if the service account has sufficient permissions to decrypt data in the bucket.

Microsoft Azure

ENCRYPTION = ( [ TYPE = 'AZURE_CSE' | 'NONE' ] [ MASTER_KEY = 'string' ] )

TYPE = ...

Specifies the encryption type used. Possible values are:

• AZURE_CSE: Client-side encryption (requires a MASTER_KEY value). For information, see the Client-side encryption information in the Microsoft Azure documentation.

• NONE: No encryption.

MASTER_KEY = 'string' (applies to AZURE_CSE encryption only)

Specifies the client-side master key used to encrypt files. The master key must be a 128-bit or 256-bit key in Base64-encoded form.

TYPE = CSV¶

COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE

String (constant) that specifies to compresses the unloaded data files using the specified compression algorithm.

Supported Values	Notes
AUTO	Unloaded files are automatically compressed using the default, which is gzip.
GZIP
BZ2
BROTLI	Must be specified when loading Brotli-compressed files.
ZSTD	Zstandard v0.8 (and higher) supported.
DEFLATE	Unloaded files are compressed using Deflate (with zlib header, RFC1950).
RAW_DEFLATE	Unloaded files are compressed using Raw Deflate (without header, RFC1951).
NONE	Unloaded files are not compressed.

Default: AUTO

RECORD_DELIMITER = 'string' | NONE

One or more singlebyte or multibyte characters that separate records in an unloaded file. Accepts common escape sequences or the following singlebyte or multibyte characters:

Singlebyte characters:

Octal values (prefixed by \\) or hex values (prefixed by 0x or \x). For example, for records delimited by the circumflex accent (^) character, specify the octal (\\136) or hex (0x5e) value.

Multibyte characters:

Hex values (prefixed by \x). For example, for records delimited by the cent (¢) character, specify the hex (\xC2\xA2) value.

The delimiter for RECORD_DELIMITER or FIELD_DELIMITER cannot be a substring of the delimiter for the other file format option (e.g. FIELD_DELIMITER = 'aa' RECORD_DELIMITER = 'aabb').

The specified delimiter must be a valid UTF-8 character and not a random sequence of bytes. Also note that the delimiter is limited to a maximum of 20 characters.

Also accepts a value of NONE.

Default: New line character. Note that “new line” is logical such that \r\n is understood as a new line for files on a Windows platform.

FIELD_DELIMITER = 'string' | NONE

One or more singlebyte or multibyte characters that separate fields in an unloaded file. Accepts common escape sequences or the following singlebyte or multibyte characters:

Singlebyte characters:

Octal values (prefixed by \\) or hex values (prefixed by 0x or \x). For example, for records delimited by the circumflex accent (^) character, specify the octal (\\136) or hex (0x5e) value.

Multibyte characters:

Hex values (prefixed by \x). For example, for records delimited by the cent (¢) character, specify the hex (\xC2\xA2) value.

The delimiter for RECORD_DELIMITER or FIELD_DELIMITER cannot be a substring of the delimiter for the other file format option (e.g. FIELD_DELIMITER = 'aa' RECORD_DELIMITER = 'aabb').

Note

For non-ASCII characters, you must use the hex byte sequence value to get a deterministic behavior.

The specified delimiter must be a valid UTF-8 character and not a random sequence of bytes. Also note that the delimiter is limited to a maximum of 20 characters.

Also accepts a value of NONE.

Default: comma (,)

FILE_EXTENSION = 'string' | NONE

String that specifies the extension for files unloaded to a stage. Accepts any extension. The user is responsible for specifying a valid file extension that can be read by the desired software or service.

Note

If the SINGLE copy option is TRUE, then the COPY command unloads a file without a file extension by default. To specify a file extension, provide a file name and extension in the internal_location or external_location path. For example:

copy into @stage/data.csv ...

Default: null, meaning the file extension is determined by the format type, e.g. .csv[compression], where compression is the extension added by the compression method, if COMPRESSION is set.

DATE_FORMAT = 'string' | AUTO

String that defines the format of date values in the unloaded data files. If a value is not specified or is set to AUTO, the value for the DATE_OUTPUT_FORMAT parameter is used.

Default: AUTO

TIME_FORMAT = 'string' | AUTO

String that defines the format of time values in the unloaded data files. If a value is not specified or is set to AUTO, the value for the TIME_OUTPUT_FORMAT parameter is used.

Default: AUTO

TIMESTAMP_FORMAT = 'string' | AUTO

String that defines the format of timestamp values in the unloaded data files. If a value is not specified or is set to AUTO, the value for the TIMESTAMP_OUTPUT_FORMAT parameter is used.

Default: AUTO

BINARY_FORMAT = HEX | BASE64 | UTF8

String (constant) that defines the encoding format for binary output. The option can be used when unloading data from binary columns in a table.

Default: HEX

ESCAPE = 'character' | NONE

Use:

Data loading and unloading

Definition:

A singlebyte character string used as the escape character for enclosed or unenclosed field values. An escape character invokes an alternative interpretation on subsequent characters in a character sequence. You can use the ESCAPE character to interpret instances of the FIELD_OPTIONALLY_ENCLOSED_BY character in the data as literals. The escape character can also be used to escape instances of itself in the data.

Accepts common escape sequences, octal values, or hex values.

Specify the character used to enclose fields by setting FIELD_OPTIONALLY_ENCLOSED_BY.

If this option is set, it overrides the escape character set for ESCAPE_UNENCLOSED_FIELD.

Default:

NONE

ESCAPE_UNENCLOSED_FIELD = 'character' | NONE

Use:

Data loading and unloading

Definition:

A singlebyte character string used as the escape character for unenclosed field values only. An escape character invokes an alternative interpretation on subsequent characters in a character sequence. You can use the ESCAPE character to interpret instances of the FIELD_DELIMITER or RECORD_DELIMITER characters in the data as literals. The escape character can also be used to escape instances of itself in the data.

Accepts common escape sequences, octal values, or hex values.

If ESCAPE is set, the escape character set for that file format option overrides this option.

Default:

backslash (\\)

FIELD_OPTIONALLY_ENCLOSED_BY = 'character' | NONE

Character used to enclose strings. Value can be NONE, single quote character ('), or double quote character ("). To use the single quote character, use the octal or hex representation (0x27) or the double single-quoted escape ('').

When a field in the source table contains this character, Snowflake escapes it using the same character for unloading. For example, if the value is the double quote character and a field contains the string A "B" C, Snowflake escapes the double quotes for unloading as follows:

A ""B"" C

Default: NONE

NULL_IF = ( 'string1' [ , 'string2' ... ] )

String used to convert from SQL NULL. Snowflake converts SQL NULL values to the first value in the list.

Default: \N (i.e. NULL, assuming ESCAPE_UNENCLOSED_FIELD=\)

EMPTY_FIELD_AS_NULL = TRUE | FALSE

Used in combination with FIELD_OPTIONALLY_ENCLOSED_BY. When FIELD_OPTIONALLY_ENCLOSED_BY = NONE, setting EMPTY_FIELD_AS_NULL = FALSE specifies to unload empty strings in tables to empty string values without quotes enclosing the field values.

If set to TRUE, FIELD_OPTIONALLY_ENCLOSED_BY must specify a character to enclose strings.

Default: TRUE

TYPE = JSON¶

COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE

String (constant). Compresses the data file using the specified compression algorithm.

Supported Values	Notes
AUTO	Unloaded files are automatically compressed using the default, which is gzip.
GZIP
BZ2
BROTLI
ZSTD
DEFLATE	Unloaded files are compressed using Deflate (with zlib header, RFC1950).
RAW_DEFLATE	Unloaded files are compressed using Raw Deflate (without header, RFC1951).
NONE	Unloaded files are not compressed.

Default: AUTO

FILE_EXTENSION = 'string' | NONE

String that specifies the extension for files unloaded to a stage. Accepts any extension. The user is responsible for specifying a valid file extension that can be read by the desired software or service.

Default: null, meaning the file extension is determined by the format type (e.g. .csv[compression]), where compression is the extension added by the compression method, if COMPRESSION is set.

TYPE = PARQUET¶

COMPRESSION = AUTO | LZO | SNAPPY | NONE

String (constant). Compresses the data file using the specified compression algorithm.

Supported Values	Notes
AUTO	Files are compressed using Snappy, the default compression algorithm.
LZO	Files are compressed using the Snappy algorithm by default. If applying Lempel-Ziv-Oberhumer (LZO) compression instead, specify this value.
SNAPPY	Files are compressed using the Snappy algorithm by default. You can optionally specify this value.
NONE	Specifies that the unloaded files are not compressed.

Default: AUTO

SNAPPY_COMPRESSION = TRUE | FALSE

Boolean that specifies whether the unloaded file(s) are compressed using the SNAPPY algorithm.

Note

Deprecated. Use COMPRESSION = SNAPPY instead.

Default: TRUE

Unloading data from a table to files in a table stage¶

Unload data from the orderstiny table into the table’s stage using a folder/filename prefix (result/data_), a named file format (myformat), and gzip compression:

COPY INTO @%orderstiny/result/data_
  FROM orderstiny FILE_FORMAT = (FORMAT_NAME ='myformat' COMPRESSION='GZIP');

Copy

Unloading data from a query to files in a named internal stage¶

Unload the result of a query into a named internal stage (my_stage) using a folder/filename prefix (result/data_), a named file format (myformat), and gzip compression:

COPY INTO @my_stage/result/data_ FROM (SELECT * FROM orderstiny)
   file_format=(format_name='myformat' compression='gzip');

Copy

Note that the above example is functionally equivalent to the first example, except the file containing the unloaded data is stored in the stage location for my_stage rather than the table location for orderstiny.

Unloading data from a table directly to files in an external location¶

Unload all data in a table into a storage location using a named my_csv_format file format:

Amazon S3

Access the referenced S3 bucket using a referenced storage integration named myint:

COPY INTO 's3://mybucket/unload/'
  FROM mytable
  STORAGE_INTEGRATION = myint
  FILE_FORMAT = (FORMAT_NAME = my_csv_format);

Copy

Access the referenced S3 bucket using supplied credentials:

COPY INTO 's3://mybucket/unload/'
  FROM mytable
  CREDENTIALS = (AWS_KEY_ID='xxxx' AWS_SECRET_KEY='xxxxx' AWS_TOKEN='xxxxxx')
  FILE_FORMAT = (FORMAT_NAME = my_csv_format);

Copy

Google Cloud Storage

Access the referenced GCS bucket using a referenced storage integration named myint:

COPY INTO 'gcs://mybucket/unload/'
  FROM mytable
  STORAGE_INTEGRATION = myint
  FILE_FORMAT = (FORMAT_NAME = my_csv_format);

Copy

Microsoft Azure

Access the referenced container using a referenced storage integration named myint:

COPY INTO 'azure://myaccount.blob.core.windows.net/unload/'
  FROM mytable
  STORAGE_INTEGRATION = myint
  FILE_FORMAT = (FORMAT_NAME = my_csv_format);

Copy

Access the referenced container using supplied credentials:

COPY INTO 'azure://myaccount.blob.core.windows.net/mycontainer/unload/'
  FROM mytable
  CREDENTIALS=(AZURE_SAS_TOKEN='xxxx')
  FILE_FORMAT = (FORMAT_NAME = my_csv_format);

Copy

Partitioning unloaded rows to Parquet files¶

The following example partitions unloaded rows into Parquet files by the values in two columns: a date column and a time column. The example specifies a maximum size for each unloaded file:

CREATE or replace TABLE t1 (
  dt date,
  ts time
  )
AS
  SELECT TO_DATE($1)
        ,TO_TIME($2)
    FROM VALUES
           ('2020-01-28', '18:05')
          ,('2020-01-28', '22:57')
          ,('2020-01-28', NULL)
          ,('2020-01-29', '02:15')
;

SELECT * FROM t1;

+------------+----------+
| DT         | TS       |
|------------+----------|
| 2020-01-28 | 18:05:00 |
| 2020-01-28 | 22:57:00 |
| 2020-01-28 | 22:32:00 |
| 2020-01-29 | 02:15:00 |
+------------+----------+

-- Partition the unloaded data by date and hour. Set ``32000000`` (32 MB) as the upper size limit of each file to be generated in parallel per thread.
COPY INTO @%t1
  FROM t1
  PARTITION BY ('date=' || to_varchar(dt, 'YYYY-MM-DD') || '/hour=' || to_varchar(date_part(hour, ts))) -- Concatenate labels and column values to output meaningful filenames
  FILE_FORMAT = (TYPE=parquet)
  MAX_FILE_SIZE = 32000000
  HEADER=true;

LIST @%t1;

+------------------------------------------------------------------------------------------+------+----------------------------------+------------------------------+
| name                                                                                     | size | md5                              | last_modified                |
|------------------------------------------------------------------------------------------+------+----------------------------------+------------------------------|
| __NULL__/data_019c059d-0502-d90c-0000-438300ad6596_006_4_0.snappy.parquet                |  512 | 1c9cb460d59903005ee0758d42511669 | Wed, 5 Aug 2020 16:58:16 GMT |
| date=2020-01-28/hour=18/data_019c059d-0502-d90c-0000-438300ad6596_006_4_0.snappy.parquet |  592 | d3c6985ebb36df1f693b52c4a3241cc4 | Wed, 5 Aug 2020 16:58:16 GMT |
| date=2020-01-28/hour=22/data_019c059d-0502-d90c-0000-438300ad6596_006_6_0.snappy.parquet |  592 | a7ea4dc1a8d189aabf1768ed006f7fb4 | Wed, 5 Aug 2020 16:58:16 GMT |
| date=2020-01-29/hour=2/data_019c059d-0502-d90c-0000-438300ad6596_006_0_0.snappy.parquet  |  592 | 2d40ccbb0d8224991a16195e2e7e5a95 | Wed, 5 Aug 2020 16:58:16 GMT |
+------------------------------------------------------------------------------------------+------+----------------------------------+------------------------------+

Retaining NULL/empty field data in unloaded files¶

Retain SQL NULL and empty fields in unloaded files:

-- View the table column values
SELECT * FROM HOME_SALES;

+------------+-------+-------+-------------+--------+------------+
| CITY       | STATE | ZIP   | TYPE        | PRICE  | SALE_DATE  |
|------------+-------+-------+-------------+--------+------------|
| Lexington  | MA    | 95815 | Residential | 268880 | 2017-03-28 |
| Belmont    | MA    | 95815 | Residential |        | 2017-02-21 |
| Winchester | MA    | NULL  | Residential |        | 2017-01-31 |
+------------+-------+-------+-------------+--------+------------+

-- Unload the table data into the current user's personal stage. The file format options retain both the NULL value and the empty values in the output file
COPY INTO @~ FROM HOME_SALES
  FILE_FORMAT = (TYPE = csv NULL_IF = ('NULL', 'null') EMPTY_FIELD_AS_NULL = false);

-- Contents of the output file
Lexington,MA,95815,Residential,268880,2017-03-28
Belmont,MA,95815,Residential,,2017-02-21
Winchester,MA,NULL,Residential,,2017-01-31

Copy

Unloading data to a single file¶

Unload all rows to a single data file using the SINGLE copy option:

copy into @~ from HOME_SALES
single = true;

Copy

Including the UUID in the unloaded filenames¶

Include the UUID in the names of unloaded files by setting the INCLUDE_QUERY_ID copy option to TRUE:

-- Unload rows from the T1 table into the T1 table stage:
COPY INTO @%t1
  FROM t1
  FILE_FORMAT=(TYPE=parquet)
  INCLUDE_QUERY_ID=true;

-- Retrieve the query ID for the COPY INTO location statement.
-- This optional step enables you to see that the query ID for the COPY INTO location statement
-- is identical to the UUID in the unloaded files.
SELECT last_query_id();
+--------------------------------------+
| LAST_QUERY_ID()                      |
|--------------------------------------|
| 019260c2-00c0-f2f2-0000-4383001cf046 |
+--------------------------------------+

LS @%t1;
+----------------------------------------------------------------+------+----------------------------------+-------------------------------+
| name                                                           | size | md5                              | last_modified                 |
|----------------------------------------------------------------+------+----------------------------------+-------------------------------|
| data_019260c2-00c0-f2f2-0000-4383001cf046_0_0_0.snappy.parquet |  544 | eb2215ec3ccce61ffa3f5121918d602e | Thu, 20 Feb 2020 16:02:17 GMT |
+----------------------------------------------------------------+------+----------------------------------+-------------------------------+

Validating data to be unloaded (from a query)¶

Execute COPY in validation mode to return the result of a query and view the data that will be unloaded from the orderstiny table if COPY is executed in normal mode:

COPY INTO @my_stage
FROM (SELECT * FROM orderstiny LIMIT 5)
VALIDATION_MODE='RETURN_ROWS';

----+--------+----+-----------+------------+----------+-----------------+----+---------------------------------------------------------------------------+
 C1 |   C2   | C3 |    C4     |     C5     |    C6    |       C7        | C8 |                                    C9                                     |
----+--------+----+-----------+------------+----------+-----------------+----+---------------------------------------------------------------------------+
 1  | 36901  | O  | 173665.47 | 1996-01-02 | 5-LOW    | Clerk#000000951 | 0  | nstructions sleep furiously among                                         |
 2  | 78002  | O  | 46929.18  | 1996-12-01 | 1-URGENT | Clerk#000000880 | 0  |  foxes. pending accounts at the pending\, silent asymptot                 |
 3  | 123314 | F  | 193846.25 | 1993-10-14 | 5-LOW    | Clerk#000000955 | 0  | sly final accounts boost. carefully regular ideas cajole carefully. depos |
 4  | 136777 | O  | 32151.78  | 1995-10-11 | 5-LOW    | Clerk#000000124 | 0  | sits. slyly regular warthogs cajole. regular\, regular theodolites acro   |
 5  | 44485  | F  | 144659.20 | 1994-07-30 | 5-LOW    | Clerk#000000925 | 0  | quickly. bold deposits sleep slyly. packages use slyly                    |
----+--------+----+-----------+------------+----------+-----------------+----+---------------------------------------------------------------------------+

Copy