Data Services with Amazon S3/Redshift as target

[ankit_sharma88]

Hello Experts,

I have a requirement for which i am not able to figure out the best possible way ahead. Any light you shed on the problem will be really appreciated. I am using DS 4.1.1

I have a application hosted on the SQL Server 2008. I need to load millions the data from this db to Amazon Redshift. Below are the 2 solution i could think of :-

1) Using a third party data driver:- Using one of the many ODBC data drives(e.g DataDirect Redshift Driver etc) available online to transfer data from the Sql Server to a staging and then in turn into the Resdhift DB. Because as far my knowledge there isn't any adapater shipped along with SAP for this kind of a requirement. Please correct me if i am wrong. Without the use of an external ODBC driver the data load will take ages to get loaded. Since db link is not an option with Redshift, so i am not able to push down the complete data flow. The Bulk Load tab also does not appear in the target tables imported from Redshift.

2) Creating file format outputs and generating a file as the output of the data flow and then running a Java prog to transfer that file from a shared drive to S3 and then running the COPY Command to move the data from S3 to Redshift. But in this approach i am not sure how to call the Java Prog from BODS and then the process to move the file from a shared drive to S3 is also vague.

Please, can anyone put some light to this problem.

Thanks,

[Former Member]

Ankit,

Is this still an issue you are tackling or have you found a solution?

If not, I can suggest an approach.

Shaunak

[Former Member]

Hi Shaunak,

If you have solution to connect AWS Redshift using JDBC/ODBC drivers with DS4.2 SP5, please provide solution.

[Former Member]

Sorry solution required for DS4.2 SP4

[former_member198401]

Any update!!

Did you try the above steps!!

Regards

Arun Sasi

[ankit_sharma88]

Hello Arun,

I have not managed to try the above steps. I will keep you updated about the JDBC steps. But the link suggests "SAP Data Services, as of 4.2 SP2, now supports the use of JDBC drivers as a data source". and we have the 4.1 version. Do you think it will be an issue?

I currently don't have access to the Management Console,hence i am unable to carry the above mentioned steps. I have asked for one. I will let you know once i get access and if the above steps worked!!

Meanwhile i tried installing the trial version of ODBC Diver available from DataDirect. I am able to connect it but my data flow throws error. As per my understanding of the error messages, there is no value which is null for the column as suggested in the screen shot of the error message. Can you please suggest what else might be causing the issue ?

Regards,

Ankit

[former_member198401]

Hi Ankit,

Sorry for the late reply.

Not sure DataDirect ODBC for RedShift is supported with DS 4.1 or higher version. It may work or sometimes not.

Can you raise a ticket with SAP Support Team to check for any possible workarounds!!

I havent tried connecting to this driver so will be difficult to suggest

Regards

Arun Sasi

[ankit_sharma88]

Thanks for getting back Arun.

I guess you are correct in saying that DataDirect driver might work sometime and sometime won't, because this driver works fine for one of my colleague but does not work for me. Strange!!

I am now looking for options to transfer data to redshift via S3 using Java Program. Because i have failed to get any options working for me right now. I am not able to find any documentation regarding the steps that needs to be followed for creating a Java adapater. Can you guide me to any such documentation/blog for 4.1.1?

Regards,

Ankit

[former_member198401]

Hi Ankit,

Wont be able to help you with 4.1 as JDBC Adapter is available from 4.2 onwards.

As a workaround can you please try deploying sqljdbc4.jar and the sqljdbc_xa.dll as a workaround. These are the JAR files which are seen in 4.2 server under the location C:\Program Files (x86)\SAP BusinessObjects\Data Services\ext\lib. If this is installed then you might be able to see JDBC Adapter

Regards

Arun Sasi

[former_member198401]

Hi Ankit,

I have never tried using Redshit JDBC but you can give a try. None of your options might work as ODBC connectivity using third party driver has its limitations. Second option will be tedious.

You can use JDBC connectivity to connect with AWS RedShift. You need to have redshift JDBC drivers or supporting drivers from vendor.

Refer to below link for configuring JDBC in Data Services

You can download JDBC drivers from amazon portal based on version.

http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html

Here is the direct link to download .Jar file

https://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC4-1.1.1.0001.jar

You JDBC URL should include the database name

JDBC URL: jdbc:redshift://xxxxx.redshift.amazonaws.com:0000/database

JDBC Class: com.amazon.redshift.jdbc4.Driver

Regards

Arun Sasi