How to connect SAP Analytics Cloud with AWS Redshift via JDBC

[carlospinto]

In this blog post, I will explain how to connect SAP Analytics Cloud with AWS Redshift using SAP Cloud Connector and Cloud Agent. I have ordered and completed the information found on the SAP Help Portal based on my personal experience to clarify the doubts raised in this question.

This is an Import Data connection that extracts the data from the source system and upload it to the cloud. If you need a Live Data connection, you can use a HANA Cloud instance on BTP or a third-party software like APOS Live Data Gateway.

I had some issues with the SAC Agent Simple Deployment Kit, so I decided to install the components separately: Cloud Connector, Cloud Agent, Apache Tomcat, JVM (Java Virtual Machine), JDK (Java Development Kit) and JDBC driver.

It is recommended to install them together on a dedicated server to avoid slowness or downtime. Once all the components are up and running, we will be able to create a connection from SAC to Redshift.

Here is the link I mainly used and these are the steps to follow. If you are struggling with some point, please leave a comment, and I will try to help.

1. Install JVM


2. Install Cloud Connector


3. Install JDK


4. Install Apache Tomcat


5. Deploy Cloud Agent on Apache Tomcat


6. Configure Cloud Connector


7. Configure Cloud Agent in SAC


8. Install JDBC driver


9. Create connection in SAC

Integration architecture

 

Step 1 - Install JVM

1.1.- Download the JVM here. I chose sapjvm-8.1.090-windows-x64.zip

1.2.- Extract it to C:\1_JVM\sapjvm_8

 

Step 2 - Install Cloud Connector

This step is explained here. Please find below the steps to follow:

2.1.- Download the Cloud Connector here. I chose sapcc-2.14.2-windows-x64.msi

2.2.- Extract it to C:\2_SCC

2.3.- Install it following this link. I installed it on Microsoft Windows OS. I selected the JVM folder (C:\1_JVM\sapjvm_8) for the JDK during the installation

2.4.- After installation, you can start and stop it via shortcuts on the desktop, or by using the Windows Services manager

 

Step 3 - Install JDK

3.1.- Download the JDK here. I chose jdk-19_windows-x64_bin.msi

3.2.- Extract it to C:\3_JDK

 

Step 4 - Install Apache Tomcat

4.1.- Download the Apache Tomcat here. I chose 64-bit Windows zip (apache-tomcat-9.0.67.exe)

4.2.- Double-click the .exe file to start the installation

4.3.- During the installation, enter the Administrator login credentials, select C:\3_JDK folder for the JDK, and C:\4_Tomcat folder as destination

4.4.- Start your Web browser and open http://localhost:8080 to verify the installation

 

Step 5 - Deploy Cloud Agent on Apache Tomcat

This step is explained here. Please find below the steps to follow:

5.1.- Download the Cloud agent here

5.2.- Extract the package and copy the file C4A_AGENT.war to C:\4_Tomcat\webapps

5.3.- Create a user with the Services role in tomcat-users.xml file. This user will be needed when creating the connection in SAC

5.4.- Restart the Tomcat service and test if the deployment was successful by opening http://localhost:8080/C4A_AGENT/deploymentInfo. The version of the Cloud agent should be displayed.

 

Step 6 - Configure Cloud Connector

Access the Cloud Connector administration at https://localhost:8443 and follow the steps explained here.

 

Step 7 - Configure Cloud Agent in SAC

Log in to SAC and follow the steps explained here.

 

Step 8 - Install JDBC driver

This step is explained here. Please find below the steps to follow:

8.1.- Please check how to configure connections in Amazon Redshift here and download the latest Redshift JDBC driver from here. I chose RedshiftJDBC42-no-awssdk-1.2.47.1071.jar

8.2.- Copy the jar file to C:\5_Drivers\drivers

8.3.- Create the file DriverConfig.properties in C:\5_Drivers\drivers containing this line:

Generic JDBC datasource=C:\5_Drivers\drivers\RedshiftJDBC42-no-awssdk-1.2.47.1071.jar

8.4.- Add the environment variable below and restart the Tomcat service:

SAP_CLOUD_AGENT_PROPERTIES_PATH=C:\5_Drivers\drivers\DriverConfig.properties

 

Step 9 - Create connection in SAC

Finally you can create the JDBC connection in SAC following these steps.

9.1.- Select the Location (localhost:8080)

9.2.- Add the JDBC URL (jdbc:redshift://endpoint:port/database)

9.3.- Add the JDBC Class (com.amazon.redshift.jdbc42.Driver)

9.4.- Add the credentials of the user created in 5.3

 

Now you are ready to create a SAC model and import data from Redshift.

 

Kind regards,

Carlos