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
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

Amazon Redshift

SQL Server to Redshift Data Load Using SSIS

Contents1 Introduction – SQL Server to Redshift Load2 Video Tutorial – Redshift Data Load3 Right way but hard way3.1 Steps for Amazon Redshift Data Load from On-Premise files or RDBMS (e.g. MySQL, SQL Server)4 Doing it easy way4.1 Should I use SSIS to load Redshift4.2 Command line approach for SQL Server to Redshift data load5 […]

1 comment
Amazon Redshift

MySQL to Redshift Data Load Using SSIS

Contents1 Introduction – MySQL to Redshift Load2 Video Tutorial3 Right way but hard way3.1 Steps for Amazon Redshift Data Load from On-Premise files or RDBMS (e.g. MySQL, SQL Server)4 Doing it easy way4.1 Should I use SSIS to load Redshift Database?4.2 Command line approach for MySQL to Redshift data loading5 Setup your Amazon Redshift Cluster6 […]

1 comment

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)

Screenshots

Redshift Data Load – Amazon Redshift Import Command line tools (COPY, S3, AWS Redshift)

Redshift Data Load – Amazon Redshift Import Command line tools (COPY, S3, AWS Redshift)

Download

 

ZappyShell Command line tools for AWS Redshift (AWS CLI for Redshift)

Command line tools for Azure Blob Storage

 

Simple command line for bulk load into Amazon Redshift with Colored coded CLI

Upload enire folders or selected files to Azure Blob Storage from command line

 

Support for Script file – Use multi lines

Upload files to Azure blob storage on command line (using script file with exec command)

 

Easy to use help file system

List available Azure Storage Commands

 

List Azure blob storage files using command line

List Azure blob storage files using command line

Keywords

How to Load SQL server data into Redshift | Import MySQL to Redshift | Import Oracle to Redshift | Copy sql data into AWS Redshift | Redshift copy command | Amazon Redshift CLI | Pentaho Redshift Data Load | DataStage Redshift Data Load  | Informatica Redshift Data Load

References

See Also

  • .net framework 3.5 or higher must be installed
  • Supported Client Operating Systems: Windows 7, Windows 8, Windows 8.1, Windows 10
  • Supported Server Operating Systems: Windows Server 2003/R2, Windows Server 2008/R2, Windows Server 2012/R2