31 Jan 19 ·  nVector in Snowflake ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

Unloading data from Snowflake tables


Similar to data loading, Snowflake supports bulk export (i.e. unload) of data from a database table into flat, delimited text files. 

First, Set the Context:

USE WAREHOUSE TRAINING_WH;
USE DATABASE SALES_NAVEEN_DB;
USE SCHEMA SALES_DATA;

For the purpose of this tutorial let us create a temporary sales table, from where we can unload the data

CREATE TABLE SALES_NAVEEN_DB.SALES_DATA.SALES AS select * from snowflake_sample_data.TPCDS_SF100TCL.STORE_SALES LIMIT 1000;

Create a named stage:

create stage my_unload_stage;

Unload the table into a file in the named stage:

copy into @my_unload_stage
from (select * from SALES_NAVEEN_DB.SALES_DATA.SALES)
file_format = (type = csv field_optionally_enclosed_by='"');

List the files to make sure the export was successful

list @my_unload_stage;

Finally, download the files to our local system:

get @my_unload_stage file://C:\Users\Naveen\Desktop\unload;

📙 Course Index
◀ Prev: Load Data using SnowSQL
▶ Next: (Coming soon)

nVector

posted on 31 Jan 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds




Hi, If i want to load the large data. Do i need to split the files?.If so, could you please guide me.

nVector30-Jun-19

If you want to load large volumes of data, it is a good practice to split them in to multiple files as it uploads the files in parallel and speeds up the time to transport to the cloud over the wire.

Dgong2903-Jul-21

 data_0_0_0.csv.gz | -1 | ERROR | [Errno 13] Permission denied: 'C:\Users\DEMO\unload\data_0_0_0.csv.gz.D089ddD8', file=data_0_0_0.csv.gz |

Hi, thanks for the DEMO.  during the get command execution, received an error on permission denied.  currently using accountadmin role, how do I fix this?  thanks