23 October 2018        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


Read great educational content like this and a lot more ! Create my free account now 🎁

Copied