Load data into Redshift using ZappyShell Command Line tools
Learn how to load data into redshift using simple command line. Read more.. http://zappysys.com/products/zappyshe...
Command line for Amazon Redshift
◾Import data to AWS Redshift database from files or relational source (e.g. MySQL, Oracle, SQL Server)
◾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
======================
Examples:
======================
##############################################
#below command will import data from any relation source (e.g. SQL Server) into redshift DB
##############################################
import Db
--source-driver ADONET_MSSQL
--source-query "SELECT top 10000 ROW_NUMBER()Over(order by a.CustomerID) Id , a.*,b.*,c.OrderID ,c.OrderDate,c.Freight FROM customers a,products b,orders c"
--maxrows-perfile 1000
--source-stagepath "c:\redshift\stage"
--target-table "customerdata"
--target-stagepath "bw-rstest/cmdstage"
--target-truncate
--logfile "c:\redshift\log.txt"
--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;"
##############################################
#below command will import local files in redshift DB
##############################################
import file
--source-path "c:\data\files\*.part.csv"
--source-stagepath "c:\redshift\stage"
--target-table "customerdata"
--target-stagepath "bw-rstest/cmdstage"
--target-truncate
--logfile "c:\redshift\log.txt"
--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;"
##############################################
#Import SQL Server records to Redshift Cluster - With Encryption turned on
##############################################
#===========================
#Step-1 : Create your encryption key (This step only required first time. Once encryption key is generated you can reuse it)
#===========================
mkkey "c:\private.key" --aes
#===========================
#Step-2 : Load your data to redshift
#===========================
import Db
--enc-key "c:\private.key"
--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-stagepath "c:\redshift\stage"
--target-table "customerdata"
--target-stagepath "bw-rstest/cmdstage"
--logfile "c:\redshift\log.txt"
--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;"