Load data into Redshift using ZappyShell Command Line tools
- 8 years ago
Learn how to load data into redshift using simple command line. Read more.. http://zappysys.com/products/zappyshell/amazon-redshift-command-line-tools/
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;"
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;"