Connect SAP Cloud analytic to Redshift

[former_member700054]

Hello everyone,

I'm trying to connect SAP Cloud analytic to my AWS Redshift Datawarehouse.

After setting up my SAP Cloud Agent, I did not manage to establish a connection using the Redshift Simba driver (trial version)

Has anyone have been successful in setting up such a connection?

If yes, how?

Thanks for reading!

[anegoescu · Accepted solution]

Hello Hakim

I have never used the Simba JDBC but I'm using the standard JDBC from AWS Redshift.

Please note that in my implementation, I'm using the Generic JDBC implementation solution.

Here I have added the steps I have made to accomplish this task.

Let me know if this works on your side.

Best Regards

Alex

_______________steps below___________

Configuration Generic JDBC (AWS Redshift)

Environment: Windows

Local PC (localhost/port:8080)

__________________________________________________________

Step 1: Setup JDBC Driver.

1. Download the JDBC driver 42-1.2.36.1060 from AWS Link!
2. Rename it from “RedshiftJDBC42-1.2.36.1060” to “redshift-jdbc42-1.2.36.1060” (optional - personal choice)
3. Place it under the Windows path C:\Program Files\SAP\SACAgentKit\drivers

(Linux = use the specific documentation for the path)

/!\ Before downloading the JDBC version, please check the JVM version of your SAC Agent and make sure you download the adapted JDBC version as per AWS documentation.

__________________________________________________________

Step 2: Edit the configuration/properties file c4a_agent_drivers.properties

Location Windows: C:\Program Files\SAP\SACAgentKit\config

(for linux may be different)

__________________________________________________________

Step 3: Properties setup

In the property file add an entry (if not available) for the “Generic JDBC datasource”

Point this entry to the location of the AWS RDS driver:

Generic JDBC datasource= C:\Program Files\SAP\SACAgentKit\drivers\redshift-jdbc42-1.2.36.1060.jar

/!\ NO MORE THAN ONE Generic JDBC datasource in file.

(for linux may be different)

__________________________________________________________

Step 4: Stop/Start SAC Agent

Stop the Tomcat Server and Start it again.

__________________________________________________________

Step 5: Create the connection in SAC

Go to SAC and create a new connection for the AWS Redshift DB

USE:

1. JDBC URL: jdbc:redshift://<HOST>:<PORT>/<Redshift DB Name>
2. JDBC Class: com.amazon.redshift.jdbc42.Driver

Insert the credentials and make the connection

/!\ Make sure you are using the correct JDBC driver class -> as presented in AWS Redshift JDBC documentation.

[former_member700054 · Accepted solution]

Hi

Thanks for your detailed reply, it helps.

I already tried the method via a Generic Driver.

The connection works at first, but when I try later to import or create a model, I have the following error message:

"An internal server error occurred in the Data Acquisition Service

Correlation ID: 32450253-1787-4817-9337-227843606814"

And here is the Error message I'm getting in my Tomcat log file:

Oct 28, 2020 12:32:41 PM com.sap.fpa.logging.CoreLogger log
SEVERE: [Amazon](500150) Error setting/closing connection: Not Connected.
com.sap.connectivity.cs.java.drivers.DBDError: [Amazon](500150) Error setting/closing connection: Not Connected.
	at com.sap.connectivity.cs.java.drivers.DDKExceptionImpl.convertToCSException(DDKExceptionImpl.java:147)
	at com.sap.connectivity.cs.java.JAVAOpenDictionary.qualifiers(JAVAOpenDictionary.java:205)
	at com.sap.connectivity.cs.core.OpenDictionary.Qualifiers(OpenDictionary.java:90)
	at com.sap.connectivity.cs.api.trace.DictionaryLogger.Qualifiers(DictionaryLogger.java:649)
	at com.sap.fpa.cloud.rest.cs.service.CatalogService.getCatalogList(CatalogService.java:66)
	at com.sap.fpa.cloud.rest.cs.service.CatalogService.execute(CatalogService.java:42)
	at com.sap.fpa.cloud.rest.RestCS.csRequest(RestCS.java:64)
	at sun.reflect.GeneratedMethodAccessor19.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.sap.fpa.cloud.service.EpmDispatcherServlet.handleRequest(EpmDispatcherServlet.java:225)
	at com.sap.fpa.cloud.service.EpmDispatcherServlet.doPost(EpmDispatcherServlet.java:93)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:604)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:165)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:452)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1195)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:654)
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2532)
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2521)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:836)
Caused by: com.sap.connectivity.cs.java.drivers.DDKExceptionImpl: [Amazon](500150) Error setting/closing connection: Not Connected.
	at com.sap.connectivity.cs.java.ErrorManager.getError(ErrorManager.java:152)
	at com.sap.connectivity.cs.java.drivers.helpers.ErrorHandler.fillErrorSet(ErrorHandler.java:107)
	at com.sap.connectivity.cs.java.drivers.helpers.ErrorHandler.getErrors(ErrorHandler.java:97)
	at com.sap.connectivity.cs.java.drivers.jdbc.JDBCDriver.getQualifiers(JDBCDriver.java:2650)
	at com.sap.connectivity.cs.java.JAVAOpenDictionary.qualifiers(JAVAOpenDictionary.java:188)
	... 33 more
Caused by: java.sql.SQLException: [Amazon](500150) Error setting/closing connection: Not Connected.
	at com.amazon.redshift.client.PGClient.isConnected(Unknown Source)
	at com.amazon.redshift.client.PGClient.executePreparedStatement(Unknown Source)
	at com.amazon.redshift.client.PGClient.executePreparedStatement(Unknown Source)
	at com.amazon.redshift.dataengine.PGDataEngine.makeNewMetadataSource(Unknown Source)
	at com.amazon.dsi.dataengine.impl.DSIDataEngine.makeNewMetadataResult(Unknown Source)
	at com.amazon.redshift.dataengine.PGDataEngine.makeNewMetadataResult(Unknown Source)
	at com.amazon.jdbc.jdbc42.S42DatabaseMetaData.createMetaDataResult(Unknown Source)
	at com.amazon.jdbc.common.SDatabaseMetaData.getCatalogs(Unknown Source)
	at com.sap.connectivity.cs.java.drivers.jdbc.JDBCAPI$DatabaseMetaData.getCatalogs(JDBCAPI.java:6731)
	at com.sap.connectivity.cs.java.drivers.jdbc.JDBCDriver.getQualifiers(JDBCDriver.java:2646)
	at com.sap.connectivity.cs.java.JAVAOpenDictionary.qualifiers(JAVAOpenDictionary.java:188)
	at com.sap.connectivity.cs.core.OpenDictionary.Qualifiers(OpenDictionary.java:90)
	at com.sap.connectivity.cs.api.trace.DictionaryLogger.Qualifiers(DictionaryLogger.java:649)
	at com.sap.fpa.cloud.rest.cs.service.CatalogService.getCatalogList(CatalogService.java:66)
	at com.sap.fpa.cloud.rest.cs.service.CatalogService.execute(CatalogService.java:42)
	at com.sap.fpa.cloud.rest.RestCS.csRequest(RestCS.java:64)
	at sun.reflect.GeneratedMethodAccessor19.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.sap.fpa.cloud.service.EpmDispatcherServlet.handleRequest(EpmDispatcherServlet.java:225)
	at com.sap.fpa.cloud.service.EpmDispatcherServlet.doPost(EpmDispatcherServlet.java:93)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:604)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:165)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:452)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1195)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:654)
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2532)
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2521)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
Caused by: com.amazon.support.exceptions.GeneralException: [Amazon](500150) Error setting/closing connection: Not Connected.<br>

Update:

After research, it seems that the SSL has to be configured and force into the string connection,

Many thanks, Alexandru !

[anegoescu]

Hi Hakim

I'm glad it worked for you and I hope you are satisfied with it.

Best Regards

Alex

[falleju1]

Hi Hakim,

we have the same error message ("An internal server error occurred in the Data Acquisition Service"). How did you set up the SSL into the string connection, adding "?ssl=true" didn't work for us? Thanks for your feedback.

Best regards,

Juergen

[former_member700054]

Hello!

Could you try adding

;require_ssl=false

at the end of your connection string?

[falleju1]

Hi Hakim,

many thanks for this. We detected that SAC is not allowing to use Boolean fields in the SELECT statement. It worked after we removed the corresponding fields.

Best regards,

Jürgen