23 Oct 18 · Atori ·       Add to Favorites   Report

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

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied