How to save Amazon Redshift output to local CSV through SQL Workbench?

Question

I am writing psql through Amazon Redshift and now I am trying to save the output as CSV through PSQL query, on SQL Workbench The reason I am planning to do this through query instead of using select clause and then right click to save the output as csv, is because there are large amount of data, I found that if I generate the output into a temp table, it's much much faster than using select to display all the output. Therefore, I am thinking whether saving to local CSV can be faster too.

I have tried the top solution here, however, it doesn't work on Amazon Redshift, When I am using Copy (SELECT col1, col2 FROM my_table) TO '[my local csv path]' WITH CSV DELIMITER ',';, or tried \Copy, it kept showing me

Amazon Invalid operation: syntax error at or near "("

or

Amazon Invalid operation: syntax error at or near "\"

Then I have checked Amazon Redshift query tutorial, didn't find any clause that could save the output to local CSV. It seems that COPY is to copy data from an Amazon data source to Redshift, UNLOAD is to save data to s3, but I just want to save the data on my local machine.

So, is there any way to save the Redshift output to my local CSV but with SQL Workbench?

Colman

Answer 1

Try running any one of the following in the Workbench

WbExport -type=text
         -file='C:\Downloads\myData.txt'
         -delimiter='\t'
         -decimal=','
         -dateFormat='yyyy-MM-dd';
select a, b ,c from myTable;

WbExport -type=text
     -file='C:\Downloads\myQuery.txt'
     -delimiter='\t'
     -header=true
     -tableWhere="WHERE a is not null and date between 11/11/11 and 22/22/22"
     -sourcetable=mytable;

Shewchuk

I did a minor change in your solution and finally it works for me. Could you add ; after -dateFormat='yyyy-MM-dd'? Otherwise it won't work. Thank you very much! – Colman

I used the first solution you provided, but get the following error: "ERROR [42601] [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42601] ERROR: syntax error at or near "WbExport" LINE 1: WbExport -type=text ^". Any insights? Thanks! – Sulfide

@Sulfide Can you put it in one line and see if the error message is improved rr if you can post your short form query, – Shewchuk

Answer 2

Yes there is, try this out.

PGPASSWORD=<password> psql -h <host> -d <db> -U <user> -p 5439-a -c "select * from <table>" -F '<delimiter>' -o temp.csv

Skimpy

Thank you very much! My data is temp table, and I am working on SQL WorkBench. So using terminal may not work in this case. Just added that I am using SQL WorkBench in the question. Is there any method to write psql query through SQL WorkBench and save the output to local CSV? – Colman

I am no expert in tool usage, but I see once you got result set in workbench, you can save data using menu option Data -> Save Data As -> ?, Btw, redshift doesn't support all postgres way of loading and unloading – Skimpy

I could not use "save as" through work bench was because select clause can be running forever there, but saving to a file can be much faster.... that's why I am asking for any query available~ Anyway, thank you for the help! – Colman

Answer 3

I know your question is about Workbench but if you are willing to go to command line on linux possibly this is a solution, it's working nicely for us

#!/bin/zsh 
#we are assuming you are not appending to each file and you don't need header
out_put='/tmp/output.csv'
#be very careful here rm -rf is very dangerous 
rm -rf $out_put

PGPASSWORD='YOUR_PASSWORD' psql -h YOUR_STUFF-cluster-1.YOUR_STUFF.us-east-1.redshift.amazonaws.com -p YOUR_PORT_NUMBER -d YOUR_DATABASE -U YOUR_USER_NAME -A -t -c "select * from SOME_TABLE limit 10" -F ',' -o $out_put


echo "your file is ready" $out_put

Heartache