We have previously seen how to load data from foreign sources to your Snowflake account through staging.
Particularly, we have focused on importing files from local client machines into internal stages and then on transfering their data into specific tables.

Data unload

We will now show how to act if the desire is instead to do the reverse. Thus, in the next lines we are going to try to explain how to export data from a table with a file in an internal stage and then download it. The commands needed fot this operation are: COPY INTO stage and GET file.

COPY INTO stage

It’s intuitive to say that, being the stage the joining link between Snowflake and the outer world, this time the first operation must be to create a file containing our interesting data in one of our stages. With very little fantasy, we will use again the command COPY INTO.

What is its syntax in this reverse context? Let’s analyze an example:

#Load data from a the table in the file my_final_file.csv in the table stage:
COPY INTO @%my_table/my_final_file.csv
FROM my_table
 FILE_FORMAT = (TYPE = CSV RECORD_DELIMITER = '\n' FIELD_DELIMITER = ',' COMPRESSION = NONE)
 HEADER = TRUE
 OVERWRITE = TRUE;

Firstly, we have loaded the data from the table the a file of pertinence contained in the table stage, and we decided to name it my_final_file.csv.
Simultaneously, we have declared the file format for the export and its specifics. As for the loading, Snowflake, by default, allows the following file formats also for the unloading: CSV | JSON | AVRO | ORC | PARQUET | XML. All the other options or modifications on the interpretation of these formats can be achieved generating your own FILE FORMAT.

Something not mandatory is to define whether to include the header or not in the export file.
It is possible to save the file in the plain stage or in a nested folder within it.
Keep in mind that by default Snowflake will not allow to save in the same stage two files with the same name. To overcome this issue Snowflake allows to add the clause OVERWRITE = TRUE in the COPY INTO stage statement.

Many more clauses can be listed in this statement. To read more about the COPY INTO stage function, check the documentation.

GET

Next on our list, is to provide the SnowSQL connection between the client terminal and the Snowflake account.
Once established a connection with the account from the command prompt, we can download the files from the internal stage using the command GET.

This command takes as input the path of the file on the stage and path to the directory on the local machine where the files should be loaded. Let’s see an example:

USE SCHEMA my_DB.my_schema;

#Load from a table stage (called with %) into Windows path:
GET @%Test_Table/my_file.csv file://C:\Users\\Downloads
;

We have first specified the level at which we are operating, our schema.
Then, we showed how to define a Windows path to the Download folder of your machine, that we decided – for simplicity – could be the place where Snowflake should save the file. We remember to the user that it is also possible to provide Windows paths in an encoded format and that SnowSQL runs also on Linux and MacOs environments.

Conclusion

We hope we have managed to convey all the fundamentals to unload data from Snowflake account.
To wrap it up: you can reverse the COPY INTO function to copy into stages the data from an interesting table with specific directives on file compression and formatting.
Finally, the download the newly-generated files to your local machine use the GET function.

Read the next chapters on FILE FORMAT creation and pipes.

Have questions about data? Be sure to check our blog.