Export Specified Columns From Redshift
1. Tool
- sqlworkbench.jar
- RedshiftJDBC42-1.2.10.1009.jar
2. Configuration
profile.redshift.driverclass=com.amazon.redshift.jdbc42.Driver
profile.redshift.url=jdbc:redshift://...
profile.redshift.username=aaa
profile.redshift.password=aaa
profile.redshift.driverjar=RedshiftJDBC42-1.2.10.1009.jar
profile.redshift.autocommit=true
Put them in a file “redshift.properties”.
3. Command
java -jar sqlworkbench.jar -profile='AWS Redsfhit' -profileStorage=./redshift.properties -command="WbExport -type=text -encoding=utf-8 -file=test.txt; select id,name,replace(keyword,'\t',' ') as keyword,settime,updatetime,'' as operId from test where company_name='abc';"
Note:
- Default file column separator is ‘\t’, we need to make sure this character in table fields are removed.
- Default file encode is ANSI for Windows, which should be read in charset “Cp1252” in java.
- NULL value in tables are exported as empty string. This make it difficult to be imported into MYSQL for non-string columns. MYSQL can read ‘\N’ as NULL.
Reference:
http://www.sql-workbench.net/manual/wb-commands.html#command-wbinclude