How to export data from Snowflake cloud data warehouse to the local system?

Snowflake supports bulk export (i.e. unload) of data from a database table into flat, delimited text files. In this example, we want to create an extract of one of the snowflake tables to my local desktop. 

Steps:

1. Use the COPY INTO <location> command to copy the data from the Snowflake database table into one or more files in a Snowflake or external stage.

2. Download the file from the stage:

• From a Snowflake stage, use the GET command to download the data file(s).
  
• From S3, use the interfaces/tools provided by Amazon S3 to get the data file(s).
  
• From Azure, use the interfaces/tools provided by Microsoft Azure to get the data file(s)

Example

We can create a staging area pointing to an AWS S3 bucket,

create or replace stage my_ext_unload_stage url='s3://unload/files/'
    credentials=(aws_key_id='1a2b3c' aws_secret_key='4x5y6z')
    file_format = my_csv_unload_format;

Use the copy command to unload from a table in CSV format and store the file in the staging area,

copy into @my_ext_unload_stage/d1 from mytable;

Then use the S3 console or your favorite FTP client to download the file from S3 to local

Atori

posted on 23 Oct 18