Command line tools for Amazon Redshift Copy from MySQL, SQL Server, Oracle

Amazon Redshift Import/Export Command line

Import data to AWS Redshift database from files or relational source (e.g. MySQL, Oracle, SQL Server)
Export Redshift data to local CSV, JSON, Excel or PDF files
Execute Redshift SQL commands from command line (direct mode or script file mode)
Import huge amount of data (millions of rows) in few minutes with parallel load techniques
Load local flat files to Redshift in just single line command (option to compress data files to *.gz to speed up transfer)
Support for Client side encryption using AES 256 Key
Load data from any data source (ODBC,ADO.net or OLEDB) using sql query
Import compressed data files (*.gz) to redshift
Archive files, error reporting, file splitting, many other features
Simple to use command line can be called from Ruby, Python, or C#. Or use any ETL tools such as Pentaho, Informatica PowerCenter, IBM DataStage

Download

Help File

Buy

Watch this video on YouTube

OverviewScreenshotsUseful LinksSystem Requirements

Sample Command Lines

Load data from SQL Server to Redshift

c:\tools>aws.exe redshift import Db 
	--source-driver ADONET_MSSQL 
	--source-query "select ROW_NUMBER()Over(order by a.CustomerID) Id, a.*,b.*,c.OrderID,c.OrderDate,c.Freight  from customers a,products b,orders c" 
	--source-archivemethod Delete 
	--source-stage-archivemethod Delete 
	--target-stage-archivemethod Delete 
	--source-stagepath "c:\redshift\stage" 
	--target-table "customerdata" 
	--target-stagepath "bw-rstest/cmdstage" 
	--target-truncate 
	--logfile "c:\redshift\log.txt" 
	--maxrows-perfile 100000 
	--region us-east-1 
	--accesskey "AKIA*****************" 
	--secretkey "lPi+XQ************************"  
	--source-connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"  
	--target-connstr "Host=mytestcluster-1.csu********.us-east-1.redshift.amazonaws.com;Port=5439;Database=dev;UserName=masteruser;Password=*********;EnableSsl=true;Timeout=30;CommandTimeout=3600;"

Load local files to Redshift

c:\tools>aws.exe redshift import File 
	--source-path "c:\data\files\*.csv" 
	--source-archivemethod Delete 
	--source-stage-archivemethod Delete 
	--target-stage-archivemethod Delete 
	--source-stagepath "c:\redshift\stage" 
	--target-table "customerdata" 
	--target-stagepath "bw-rstest/cmdstage" 
	--target-truncate 
	--logfile "c:\redshift\log.txt" 
	--maxrows-perfile 100000 
	--region us-east-1 
	--accesskey "AKIA*****************" 
	--secretkey "lPi+XQ************************"  
	--target-connstr "Host=mytestcluster-1.csu********.us-east-1.redshift.amazonaws.com;Port=5439;Database=dev;UserName=masteruser;Password=*********;EnableSsl=true;Timeout=30;CommandTimeout=3600;"

Export Redshift data to Local CSV file

c:\tools>aws.exe redshift export "select * from customerdata limit 50000" --csv -o "C:\amazon\customers.csv" -y  --connstr "Pooling=false;Host=myxxxxxxxxxx-1.cxxxxxxxxxx.us-east-1.redshift.amazonaws.com;Port=5439;Database=dev;UserName=masteruser;Password=xxxxxxxxxxxxxx;EnableSsl=true;Timeout=30;CommandTimeout=3600;"

Execute Redshift SQL Command (e.g. CREATE/DROP)

c:\tools>aws.exe redshift sqlexec --sql "DROP TABLE customers; DROP TABLE products;" --connstr "Pooling=false;Host=myxxxxxxxxxx-1.cxxxxxxxxxx.us-east-1.redshift.amazonaws.com;Port=5439;Database=dev;UserName=masteruser;Password=xxxxxxxxxxxxxx;EnableSsl=true;Timeout=30;CommandTimeout=3600;"

Execute Redshift SQL Command from Script File (e.g. CREATE/DROP)

c:\tools>aws.exe redshift sqlexec --script-file "c:\amazon\scripts\drop-create-table.sql" --connstr "Pooling=false;Host=myxxxxxxxxxx-1.cxxxxxxxxxx.us-east-1.redshift.amazonaws.com;Port=5439;Database=dev;UserName=masteruser;Password=xxxxxxxxxxxxxx;EnableSsl=true;Timeout=30;CommandTimeout=3600;" 

drop-create-table.sql
/*=========================*/
DROP TABLE public.customers;
DROP TABLE public.products;

Featured Articles

SQL Server to Redshift Data Load Using SSIS

Posted: June 4, 2015

Introduction – SQL Server to Redshift Load Before we talk data load from SQL Server to Redshift using SSIS lets talk what is Amazon Redshift (or sometimes referred as AWS Redshift). Amazon Redshift is a Cloud based Data warehouse service. This type of system also referred as MPP (Massively Parallel Processing). Amazon Redshift uses highly […]

MySQL to Redshift Data Load Using SSIS

Posted: June 29, 2015

Introduction – MySQL to Redshift Load Before we talk about data load from MySQL to Redshift using SSIS, first lets talk what is Amazon Redshift (or AWS Redshift). Amazon Redshift is a Cloud based Data warehouse service. This type of system also referred as MPP (Massively Parallel Processing). Amazon Redshift uses highly modified version of […]

Loading Data into Redshift using Informatica PowerCenter

Click Here to read more: Amazon Redshift data load in Informatica PowerCenter

List of Amazon Redshift Commands

Command	Alias	Description
import		Bulk import data from existing files or from any relational database source to redshift in single command. Command supports ODBC, OLEDB and ADO.net drivers to consume source data
export		Bulk export data from Redshift to CSV, JSON, Excel or PDF file format
import		Execute Redshift SQL Commands (DDL or DML)