Unloading into a Snowflake Stage

This topic describes how to use the COPY INTO location 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 using the GET command.

Unloading data to a Snowflake stage

As illustrated in the diagram above, 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.

Specify the stage (user, table, or named stage) where the files are written.

This step requires an active virtual warehouse. The warehouse provides the compute resources to write rows from the table.

Step 2:

Download the files from the stage.

Use the GET command to download the data files.

Note

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

Tip

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

In this Topic:

Unloading the Data

This section provides instructions for unloading table data to a user stage, table stage, or internal (i.e. Snowflake) named stage.

Note

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

Unloading Data to Your User Stage

  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 Mac OS:

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

    Windows:

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

Unloading Data to a Table Stage

  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 Mac OS:

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

    Windows:

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

Unloading Data to an Internal Named 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 named stage using either the web interface or SQL:

Web Interface:Click on Databases > db_name > Stages
SQL Command: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
  FILE_FORMAT = my_csv_unload_format;

Unloading Data to the Named 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 Mac OS:

    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;
    

Managing Data Files

Staged files can be deleted from a Snowflake stage using the REMOVE command to remove the files in the stage after you are finished with them.

Removing files improves performance when loading data, because it reduces the number of files that the COPY INTO table command must scan to verify whether existing files in a stage were loaded already.