如何通过SQL Workbench将Amazon Redshift输出保存到本地CSV? [英] How to save Amazon Redshift output to local CSV through SQL Workbench?

问题描述

我正在通过Amazon Redshift编写psql，现在我正在尝试在SQL Workbench上通过PSQL查询将输出保存为CSV 我计划通过查询而不是使用select子句然后右键单击将输出另存为csv的原因是因为有大量数据，我发现如果将输出生成到临时表中，它比使用select显示所有输出要快得多.因此，我在考虑是否也可以更快地保存到本地CSV.

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.

我已经尝试了最佳解决方案此处，但是，它在Amazon Redshift上不起作用，当我使用Copy (SELECT col1, col2 FROM my_table) TO '[my local csv path]' WITH CSV DELIMITER ',';或尝试使用\Copy时，它一直向我显示

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无效操作:("或附近的语法错误

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

或

Amazon无效操作:"\"或附近的语法错误

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

然后，我检查了 Amazon Redshift查询教程，找不到任何可以将输出保存到本地CSV的子句.似乎COPY是将数据从Amazon数据源复制到Redshift，UNLOAD是将数据保存到s3，但我只想将数据保存在本地计算机上.

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.

因此，是否可以通过SQL Workbench将 Redshift输出保存到我的本地CSV 中?

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

推荐答案

尝试在工作台中运行以下任意一项

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;

这篇关于如何通过SQL Workbench将Amazon Redshift输出保存到本地CSV?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！