Amazon Redshift Import/Export Command line
|
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
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 […]
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 […]
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
Download
ZappyShell Command line tools for AWS Redshift (AWS CLI for Redshift)
Simple command line for bulk load into Amazon Redshift with Colored coded CLI
Support for Script file – Use multi lines
Easy to use help file system
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
- Supported Client Operating Systems: Windows 7, Windows 8, Windows 8.1, Windows 10