Extremely Slow Performance with SnowSQL

user_16179890 0
Viewed 499

We are running a very simple select from a single table that results in a set of data that is about 1.7-ish million rows.

We've hoped that we can export that to a shared network drive, but the performance on the file write ( the query itself runs in about 3.5 seconds ) has been abysmal, returning 150,000-ish of those rows in about 6 hours. At that point, our Okta authentication times out and the query must be altered and re-run to catch the rows not brought through in the original run.

I can run it against my local and the entirety of the returned data set plops happily into the output csv in about 15 minutes.

Is there a way that I could dig further into the delays here? Has anyone else encountered this issue and been able to track down the root cause?

Thanks!

I should have added the command I was using for reference:

snowsql -c {connection} -f .\source_q.sql -o output_file=c:\users\{username}\desktop\snowsql\output.csv -o quiet=true -o friendly=false -o header=true -o output_format=csv --authenticator externalbrowser

The query I was running looks like this:

select 
   cnt.id, 
   cnt.account_id, 
   cnt.did_c, 
   cnt.okta_id_c     
from 
   MEMBER.CONTACT cnt

with some small where clauses that I've removed for simplicity.

snowflake-cloud-data-platform csv
2 Answers

Seems like the issue is that writing to your network drive is too slow. c:\users\{username} is a network path right? If you can write to your local machine without issue but the network path is slow then it's most likely something that you can't fix without speeding up the network or making the file smaller.

One thing that will help is compressing the file before uploading to the network drive. A simple way to do that would be something like:

snowsql -c {connection} -f .\source_q.sql -o friendly=false -o header=true -o output_format=csv --authenticator externalbrowser | gzip > c:\users\{username}\desktop\snowsql\output.csv

I removed the -o output_file=... and -o quiet=true flags which sends the CSV to stdout and then I pipe it through GZIP first before loading into the network location.

This method obviously compresses the file after it's downloaded from Snowflake but ideally you'd want to compress it before downloading. You could do that by:

  1. Using the COPY INTO command with compression = gzip flag, copy the data into a file in a named internal stage
  2. Use SNOWSQL to run a GET command to download the file into the network location

That last option is probably the fastest way, however it will produce multiple files in the internal stage so you'd have to download each of them. Either way you do it, you should see a pretty significant improvement by compressing the data. When I tested it for a table with 150,000 records, my file size went from 24MB to 9MB.

user_32872500

Can confirm that writing file to shared network is orders of magnitude slower compared to local C: drive. Building off of Simon's answer, this is what I use whenever I need to download large tables (< 5GB) from Snowflake as single .csv file. Obviously add, remove, change certain options as desired.

create or replace temporary stage unload_stage
file_format=(type=csv, compression=gzip, field_delimiter=',');

copy into @unload_stage/data.gz 
from my_table
single=true
max_file_size=4900000000;

get @unload_stage file://\\shared_network_folder\subfolder
parallel=1;

If you can access shared network using a remote server, extracting the gzip-ed file can take just a couple seconds up there, compared to minutes or even hours on your local machine.

user_99471590
Related