This set of topics describes how to use the COPY command to unload data from a table into an internal (i.e. Snowflake) stage. You can then download the unloaded data files to your local file system.

As illustrated in the diagram below, unloading data to a local file system is performed in two, separate steps:

Step 1

Use the COPY INTO <location> command to copy the data from the Snowflake database table into one or more files in a Snowflake stage. In the SQL statement, you specify the stage (named stage or table/user stage) where the files are written.

Regardless of the stage you use, this step requires a running, current virtual warehouse for the session. The warehouse provides the compute resources to write rows from the table.

Step 2

Tip

The instructions in this set of topics assume you have read Preparing to Unload Data and have created a named file format, if desired.

Before you begin, you may also want to read Data Unloading Considerations for best practices, tips, and other guidance.

In this Topic:

This section provides instructions for unloading table data to a named internal stage, table stage, or user stage.

Internal stages are named database objects that provide the greatest degree of flexibility for data unloading. Because they are database objects, privileges for named stages can be granted to any role.

You can create an internal stage using either the web interface or SQL:

Web Interface

Click on Databases » <db_name> » Stages

SQL

CREATE STAGE

#### Creating a Named Stage¶

The following example creates an internal stage that references the named file format object called my_csv_unload_format that was created in Preparing to Unload Data:

CREATE OR REPLACE STAGE my_unload_stage


1. Use the COPY INTO <location> command to unload all the rows from a table into one or more files into the my_csv_unload_format stage. The statement prefixes the unloaded file(s) with unload/ to organize the files in the stage:

For example:

COPY INTO @mystage/unload/ from mytable;


Note that the @ character by itself identifies a named stage.

Note

Because the file format options were defined for the stage, it isn’t necessary to specify the same file format options in the COPY command.

2. Use the LIST command to view a list of files that have been unloaded to the stage:

LIST @mystage;

+----------------------------------+------+----------------------------------+-------------------------------+
| name                             | size | md5                              | last_modified                 |
|----------------------------------+------+----------------------------------+-------------------------------|
| mystage/unload/data_0_0_0.csv.gz |  112 | 6f77daba007a643bdff4eae10de5bed3 | Mon, 11 Sep 2017 18:13:07 GMT |
+----------------------------------+------+----------------------------------+-------------------------------+

3. Use the GET command to download the generated file(s) from the table stage to your local machine. The following example downloads the files to the data/unload directory:

For example:

Linux or macOS:

GET @mystage/unload/data_0_0_0.csv.gz file:///data/unload;


Windows:

GET @mystage/unload/data_0_0_0.csv.gz file://C:\data\unload;


1. Use the COPY INTO <location> command to unload all the rows from a table into one or more files in the stage for the table. The following example unloads data files to the stage using the named my_csv_unload_format file format created in Preparing to Unload Data. The statement prefixes the unloaded file(s) with unload/ to organize the files in the stage:

For example:

COPY INTO @%mytable/unload/ from mytable FILE_FORMAT = (FORMAT_NAME = 'my_csv_unload_format' COMPRESSION = NONE);


Note that the @% character combination identifies a table stage.

2. Use the LIST command to view a list of files that have been unloaded to the stage:

LIST @%mytable;

+-----------------------+------+----------------------------------+-------------------------------+
| name                  | size | md5                              | last_modified                 |
|-----------------------+------+----------------------------------+-------------------------------|
| unload/data_0_0_0.csv |   96 | 29918f18bcb35e7b6b628ca41024236c | Mon, 11 Sep 2017 17:45:20 GMT |
+-----------------------+------+----------------------------------+-------------------------------+

3. Use the GET command to download the generated file(s) from the table stage to your local machine. The following example downloads the files to the data/unload directory:

For example:

Linux or macOS:

GET @%mytable/unload/data_0_0_0.csv file:///data/unload;


Windows:

GET @%mytable/unload/data_0_0_0.csv file://C:\data\unload;


1. Use the COPY INTO <location> command to unload all the rows from a table into one or more files in your stage. The following example unloads data files to your user stage using the named my_csv_unload_format file format created in Preparing to Unload Data. The statement prefixes the unloaded file(s) with unload/ to organize the files in the stage:

For example:

COPY INTO @~/unload/ from mytable FILE_FORMAT = (FORMAT_NAME = 'my_csv_unload_format' COMPRESSION = NONE);


Note that the @~ character combination identifies a user stage.

2. Use the LIST command to view a list of files that have been unloaded to the stage:

LIST @~;

+-----------------------+------+----------------------------------+-------------------------------+
| name                  | size | md5                              | last_modified                 |
|-----------------------+------+----------------------------------+-------------------------------|
| unload/data_0_0_0.csv |   96 | 94a306c55733b95a0887511ff355936b | Mon, 11 Sep 2017 17:25:07 GMT |
+-----------------------+------+----------------------------------+-------------------------------+

3. Use the GET command to download the generated file(s) from your stage to your local machine. The following example downloads the files to the data/unload directory:

For example:

Linux or macOS:

GET @~/unload/data_0_0_0.csv file:///data/unload;


Windows:

GET @~/unload/data_0_0_0.csv file://C:\data\unload;