{"id":360,"date":"2016-02-17T23:42:41","date_gmt":"2016-02-17T23:42:41","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=360"},"modified":"2016-08-31T21:03:05","modified_gmt":"2016-08-31T21:03:05","slug":"amazon-redshift-data-load-in-informatica-powercenter","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/amazon-redshift-data-load-in-informatica-powercenter\/","title":{"rendered":"Amazon Redshift data load in Informatica PowerCenter"},"content":{"rendered":"

Introduction<\/h2>\n

In our previous post<\/a> you learned how to load data into Redshift using SSIS. Now in this post you will learn how to load data into Redshift using Informatica PowerCenter<\/strong>. For PowerCenter we will use ZappyShell Command line for Redshift Data Load<\/a>. This small powerful command line utility can handle load of several millions or billions records in few minutes. It uses parallel execution engine which takes care most tedious steps of loading data into Amazon Redshift.<\/p>\n

Using informatica command task to load data into redshift<\/h2>\n

Informatica PowerCenter has simple task to execute command lines. you can use this task to execute ZappyShell command line. Perform following steps to load data.<\/p>\n

    \n
  1. Download and install ZappyShell for Amazon Redshift from here<\/a><\/li>\n
  2. Now you ready to data into Amazon Redshift<\/li>\n
  3. Drag new command task into your Informatica workflow<\/li>\n
  4. Enter below command to load data from SQL Server to Redshift. If you have ODBC connectivity then use ODBC DSN to read your data.<\/li>\n
  5. You can use script file approach too where each command line parameter can be nicely wrapped in new line.
    \nHere is sample command you can try to load data from SQL Server to Redshift. if you have ODBC connectivity then use ODBC DSN to read your data.<\/p>\n
    c:\\zappyshell\\aws.exe import Db \r\n\t--source-driver ADONET_MSSQL \r\n\t--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\" \r\n\t--source-archivemethod None \r\n\t--source-stage-archivemethod Delete \r\n\t--target-stage-archivemethod Delete \r\n\t--source-stagepath \"c:\\redshift\\stage\" \r\n\t--target-table \"customerdata\" \r\n\t--target-stagepath \"bw-rstest\/cmdstage\" \r\n\t--target-truncate \r\n\t--logfile \"c:\\redshift\\log.txt\" \r\n\t--maxrows-perfile 100000 \r\n\t--region us-east-1 \r\n\t--accesskey \"AKIA*****************\" \r\n\t--secretkey \"lPi+XQ************************\"  \r\n\t--source-connstr \"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;\"  \r\n\t--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;\"\r\n<\/pre>\n<\/li>\n
  6. For complete help on import command check help file \/\/zappysys.com\/onlinehelp\/zappyshell\/scr\/aws\/aws-redshift-import.htm<\/li>\n<\/ol>\n
    \"Informatica<\/a>
    Informatica PowerCenter Redshift Data Load – Amazon Redshift Import Command line tools (COPY, S3, AWS)<\/p><\/div>\n
    ZappyShell Command Line for Redshift<\/h2>\n
    Below are few feature for Command line for Amazon Redshift<\/p>\n
    \u25feImport data to AWS Redshift database from files or relational source (e.g. MySQL, Oracle, SQL Server)
    \n\u25feImport huge amount of data (millions of rows) in few minutes with parallel load techniques
    \n\u25feLoad local flat files to Redshift in just single line command (option to compress data files to *.gz to speed up transfer)
    \n\u25feSupport for Client side encryption using AES 256 Key
    \n\u25feLoad data from any data source (ODBC,ADO.net or OLEDB) using sql query
    \n\u25feImport compressed data files (*.gz) to redshift
    \n\u25feArchive files, error reporting, file splitting, many other features<\/p>\n
    <\/div><\/div>
    <\/div>
    <\/div>
    <\/div><\/div><\/div>