Has anyone staged Amazon Athena (S3) JDBC data source in ServiceNow?

[subash_biswas]

I am looking into connecting to Amazon Athena (S3) as a JDBC data source from ServiceNow. I installed the driver from AWS docs. Below is a scrubbed screen shot of the same. I tried several combinations for the Connection URL & also alternated between the two latest drivers from AWS (AthenaJDBC42-2.0.9.jar & AthenaJDBC41-2.0.9.jar). Also, toggled between using Windows & Linux MIDs.

This seems feasible theoretically but no success thus far. I can connect & execute the same query just fine from SQL Workbench (Build 125 (2019-05-08) locally..

Athena JDBC driver: https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html

Connection URLs attempted:
jdbc:awsathena://athena.us-east-1.amazonaws.com:443?AwsRegion=us-east-1&S3OutputLocation=s3://name-useast1-core-aws-athena-query-results/
jdbc:awsathena://athena.us-east-1.amazonaws.com:443;AwsRegion=us-east-1;S3OutputLocation=s3://name-useast1-core-aws-athena-query-results/
jdbc:awsathena://AwsRegion=us-east-1&S3OutputLocation=s3://name-useast1-core-aws-athena-query-results/
jdbc:awsathena://AwsRegion=us-east-1;S3OutputLocation=s3://name-useast1-core-aws-athena-query-results/
jdbc:awsathena://athena.us-east-1.amazonaws.com:443?AwsRegion=us-east-1&s3_staging_dir=s3://name-useast1-core-aws-athena-query-results/

Error messages:
MID Server reported error: java.sql.SQLException: java.sql.SQLNonTransientConnectionException: [Simba][JDBC](10100) Connection Refused: [Simba][JDBC](11640) Required Connection Key(s): AwsRegion; [Simba][JDBC](11480) Optional Connection Key(s): AwsCredentialsProviderArguments, AwsCredentialsProviderClass, BinaryColumnLength, ComplexTypeColumnLength, ConnectTimeout, EndpointOverride, GlueEndpointOverride, MaxCatalogNameLength, MaxColumnNameLength, MaxErrorRetry, MaxSchemaNameLength, MaxStreamErrorRetry, MaxTableNameLength, ProxyHost, ProxyPort, ProxyPWD, ProxyUID, RowsToFetchPerBlock, S3OutputEncOption, S3OutputLocation, Schema, SocketTimeout, StreamingEndpointOverride, StringColumnLength, UseArraySupport, UseAwsLogger, UseResultsetStreaming, Workgroup
com.simba.athena.exceptions.ExceptionConverter.toSQLException(Unknown Source)

MID Server reported error: java.sql.SQLException: java.sql.SQLException: [Simba][AthenaJDBC](100131) An error has been thrown from the AWS SDK client.
Unable to execute HTTP request: sun.security.validator.ValidatorException: PKIX path building failed: java.security.cert.CertPathBuilderException:
Unable to find certificate chain. [Execution ID not available]

Pointers welcome, thank you!

[SVR2]

Hello @Subash Biswas,

Was there any solution that worked for you?

We are attempting to integrate Amazon S3 to ServiceNow

[Ludovic_L]

Hello @SVR @subash.biswas 

I am also looking into connecting to Amazon Athena (S3) as a JDBC data source from ServiceNow.

Was there any solution that worked for you?

Regards

[Harish V]

I found a solution for this:

1. Your error message says "Required Connection Key(s):" -  so you need to supply those parameters in the connection URL

parameters to be added to the Connection URL - User=[AccessKey];Password=[SecretKey]

2. Have a functioning Mid Server and check with your networking and IT Security if a whitelist/proxy is required

3. Server name can be changed to "glue.us-east-1.amazonaws.com"

4. Here is a reference I used to build my Connection URL - Page 17

https://s3.amazonaws.com/athena-downloads/drivers/JDBC/SimbaAthenaJDBC-2.0.21.1000/docs/Simba+Athena+JDBC+Connector+Install+and+Configuration+Guide.pdf

5. Here is the page I got my JDBC drivers from

https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html

6. I created a sys_Data_Source Format manually with the value 'com.simba.athena.jdbc.Driver'

I am guessing you have uploaded the JAR file already at Mid server > JAR Files (https://dishdev2.service-now.com/ecc_agent_jar_list.do)