CODEWITHSUNDEEP
snowflake-cloud-data-platform
npool
npool

Reputation: 121

How download more than 100MB data into csv from snowflake's database table

Is there a way to download more than 100MB of data from Snowflake into excel or csv?

I'm able to download up to 100MB through the UI, clicking the 'download or view results button'

Upvotes: 4

Views: 19296

Answers (1)

Rich Murnane
Rich Murnane

Reputation: 2920

You'll need to consider using what we call "unload", a.k.a. COPY INTO LOCATION which is documented here: https://docs.snowflake.net/manuals/sql-reference/sql/copy-into-location.html

Other options might be to use a different type of client (python script or similar).

I hope this helps...Rich

.....EDITS AS FOLLOWS....

Using the unload (COPY INTO LOCATION) isn't quite as overwhelming as it may appear to be, and if you can use the snowSQL client (instead of the webUI) you can "grab" the files from what we call an "INTERNAL STAGE" fairly easily, example as follows.

CREATE TEMPORARY STAGE my_temp_stage;

COPY INTO @my_temp_stage/output_filex
FROM (select * FROM databaseNameHere.SchemaNameHere.tableNameHere)
FILE_FORMAT = ( 
 TYPE='CSV' 
 COMPRESSION=GZIP 
 FIELD_DELIMITER=',' 
 ESCAPE=NONE 
 ESCAPE_UNENCLOSED_FIELD=NONE 
 date_format='AUTO' 
 time_format='AUTO' 
 timestamp_format='AUTO'
 binary_format='UTF-8' 
 field_optionally_enclosed_by='"' 
 null_if='' 
 EMPTY_FIELD_AS_NULL = FALSE 
)  
overwrite=TRUE 
single=FALSE 
max_file_size=5368709120 
header=TRUE;

ls @my_temp_stage;

GET @my_temp_stage file:///tmp/ ;

This example:

  1. Creates a temporary stage object in Snowflake, which will be discarded when you close your session.
  2. Takes the results of your query and loads them into one (or more) csv files in that internal temporary stage, depending on size of your output. Notice how I didn't create another database object called a "FILE FORMAT", it's considered a best practice to do so, but you can do these one off extracts without creating that separate object if you don't mind having the command be so long.
  3. Lists the files in the stage, so you can see what was created.
  4. Pulls the files down using the GET, in this case this was run on my mac and the file(s) were placed in /tmp, if you are using Windoz you will need to modify a little bit.

Upvotes: 9

Related Questions