Slow insertion in Amazon Redshift

[Former Member]

I'm testing the BO Data Services as ETL tool for data extraction having as Amazon Redshift destination. However, the performance for the inserts into Redshift is very bad.

On average, the Data Services inserts about 5 records per second. (I made tests with Talend and it already entered 22000 reg / sec)

We have a BODS running on the Linux platform x64.

For connection, we use an ODBC connection created and configured as Amazon recommendations.

http://docs.aws.amazon.com/redshift/latest/mgmt/install-odbc-driver-linux.html

Does anyone have any idea how I can provide more performance in data inserts in Redshift using bods?

Thanks

[former_member187605]

There's something in the error file. That message might give a hint. Can you post?

[Former Member]

What does your transformation do? Just dump loading on to target? I see a warning on your error screen. Also why did you chose ODBC? What is your DS version, i guess DS 4.2 onwards supports JDBC adapter.

[former_member187605]

The new JDBC Adapter data store is designed for data sources only.

[Former Member]

Hi Arun,

How I am testing ETL tools to put data on Redshift, this job is so simple, have no transformations. Just a query. I am looking for best performance ETL tool to realize it.

About ODBC or JDBC. I tried to create a JDBC adapter, but still can not use it.

I tried to create a JDBC adapter, but still can not use it.

I created a discussion on this forum about this my JDBC problem, but not get answers.

The BO version is 4.2.

I'm running the job again to get the warning messages, yet none was displayed.

Thanks

[former_member198401]

Hi Vitor,

This issue with the odbc redshift driver can be reported to SAP Support team by raising a ticket and JDBC adapter will not help us it will only allow you to use the tables as a source only.

Regards

Arun Sasi

[former_member198401]

Hi Vitor,

If ODBC is still causing issue while loading to RedShift database then you can refer to below link

AWS | Amazon Redshift | FAQs

Regards

Arun Sasi

[Former Member]

Hello Dirk,

As I could read, JDBC Adapter allows us to read from Amazon Redshift, but what we could do if we want to write into it. Use it as target.

Thank you in advance,

Regards,

[former_member187605]

ODBC.

[Former Member]

I have no idea of your target Database but in general either of the following must help

1) increasing number of rows per commit on your target table

2) If your destination supports any bulk loading techniques, you can try using those in the target table bulk loader options.

3) Please check the buffer size set in your target DB which is being loaded.

Thanks

Sameera.

[Former Member]

Hi Satya,

Thanks for answer.

I tried increase rows per commit to 10000, but it not solve.

In the target table, dont have a bulk load option to set. See image below:

Thanks

[former_member198401]

Hi Vitor,

I assume that you are on DS 4.2.

You can give a try with Redshit JDBC but ODBC connectivity might not work using third party driver has its limitations.

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

SAP Data Services and JDBC

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

[Former Member]

Hi Arun,

Yes, I am using BO 4.2.

I am using ODBC because I had some issues trying use the JDBC. I follow all steps from your links but something still wrong.

I created a discussion on this forum about this my JDBC problem, but not get answers.

If I solve this issues, I will try use JDBC connection to put data on Redshift and leave ODBC back.

Thanks

[former_member198401]

Hi Vitor,

I have replied to your old post.

Can you add the necessary jar file RedshiftJDBC41-1.1.7.1007.jar to the job server location and check for the jdbc adapter issue.

ODBC driver for Redshift database may or may not work with DS 4.2.

Regards

Arun Sasi

[Former Member]

No ideias?