How to connect SAP Analytics Cloud with AWS Redshift via JDBC
Blog post published by our consultant Carlos Pinto in the SAP Community
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 will need 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.
- Install JVM
- Install Cloud Connector
- Install JDK
- Install Apache Tomcat
- Deploy Cloud Agent on Apache Tomcat
- Configure Cloud Connector
- Configure Cloud Agent in SAC
- Install JDBC driver
- Create connection in SAC
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:
Amazon Redshift=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
Leave a comment