Data Services with Amazon Redshift.

[former_member244146]

Have seen a lot of posts on this not to many good answers.

Since I am starting a project on this I thought i would detail the project as it happens as it may help others.

We are migrating our Data Warehose to Amazon Redshift. We are using Data Services to load the data and Webbi to run reports from it.

I will start by what I have already done.

First trying to load data into Redshift directly is very slow and I don't recommend it, nor is it the suggested way to do it. You want to incorporate Amason S3 as your starting point.

The latest version of DS 4.2 has a native driver for S3. Set up a bucket then start your load there. We use CDC so we are loading our Changed data to S3. The different between this and the odbc into Redshift was as follow. Maybe a 100 rows a second if your lucky into the odbc. 300,000 to 400,000 per second into S3. Once the data is there you can set it up to copy over to Redshift from within Amazon. If all you want to do is get the data into Redshift then you are done here and it works great. The S3 is not very expensive either.

If you are using Redshift as your Data Warehouse then continue to read.

Once the data is in S3 then copy it to Redshift. We have done this it works well.

I am in the process of getting the SAP ODBC that comes with the BI tools as the native redshift ODBC is not 100% completely compliant(meaning not everything in DS works through it)

Hopefully with the SAP ODBC this will be better. My goal is to be able to push down all the sql so I dont have to host DS on Amazon. I will add to this after I have tested this part.

[former_member323452]

Hello Mark,

Thank you for starting this thread. Can you a provide details on how to set up connectivity between DS and S3. You mentioned we have a native driver for S3 in latest DS? Can you confirm what version of DS ?

I was only able to find out Redshift odbc drivers and establish connectivity with DS

Thanks,
Sarath

[former_member244146]

Sorry I did not come back to this. As S3 is not really a database but a file system you will see the connection for it under formats and then create a new file location then select amazon s3 cloud storage under file locations. I believe this has been available at least since 4.2 sp6 I think. I know its been out for over a year.