Data Federation Between SAP Data Warehouse Cloud and Amazon Redshift

[former_member401468]

In this installment, we will show you how to create a data federation between Amazon Redshift and SAP Data Warehouse Cloud through SAP Smart Data Integration (SDI) Camel JDBC Adapter.

System Architecture Diagram:

Prerequisites:

1. Amazon Redshift instance and the network have been configured for external access.


2. SAP Data Warehouse Cloud instance


3. SAP Smart Data Integration DP Agent installed 

Here are the main steps to set up this data flow:

Create a new on-premise agent in SAP Data Warehouse Cloud:

Administration ->Data Integation -> On-Premise Agents

 

Note down the above agent connection information for SAP Smart Data Integration DP Agent configuration in the following steps.

 

Step for Amazon Redshift:

Get JDBC URL from Amazon Redshift instance service page:

 

Steps For SAP HANA Smart Data Integration DP Agent

Download Amazon Redshift JDBC driver:

The latest Amazon Redshift JDBC driver can be found from this link https://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html#download-jdbc-driver.

Copy Amazon Redshift JDBC driver into file folder <DPA_install_dir>\camel\lib on SAP Smart Data Integration DP Agent server:



 

Use DP Agent Configuration Tools to setup the connection to SAP Data Warehouse Cloud:

select item 6 (SAP HANA Connection) and then select item 2 (connection to SAP Data Warehouse Cloud via JDBC)



The connection information is from the first step which creates the on-premise agent in SAP Data Warehouse Cloud

 

Check the on-premise agent status in SAP Data Warehouse Cloud and register CamelJdbcAdapter in on-premise agent setting:

Administration ->Data Integation -> On-Premise Agents

Check CamelJdbcAdapter and Save it

 

Upload Amazon Redshift ODBC driver in SAP Data Warehouse for data flow building:

Administration ->Data Integration -> Third-Party Drivers

Create local connections in SAP Data Warehouse Cloud:

Space Management-> <Your working Space> -> Connections -> Local Connections

 

Create a new local connection for Amazon Redshift

 

 

Import remote table in SAP Data Warehouse Cloud:



 

Select the tables in Amazon Redshift and import it.

Now the remote tables for Amazon Redshift are available in SAP Data Warehouse Cloud. These remote tables can be used in view, analytical dataset, or the other places.

The query against these remote tables will be pushed down to Amazon Redshift through the SAP Smart Data Integration DP Agent and it also brings the query result back to SAP Data Warehouse Cloud for further use.

Thanks for reading. If you have any questions, please reach out to ci_sce@sap.com.