Getting error while testing Athena JDBC/ODBC bridge connectivity as suggested by JBailey

[srinivask]

Hi JBailey

MY TASK : Configure SAS Application to access AWS S3 bucket by using Athena jdbc/odbc driver     

Tried below ways : 

Step -1 : Athena ODBC driver -- Failed ( SAS Says currently no plug-in available  )

Step -2 : Athena JDBC driver -- Failed ( Proxy connectivity issue -- as we are connecting through proxy server )

Note: by using AWS CLI we successfully connected to AWS S3 bucket. 

Finally :

as you suggested Contacted progress.com for ODBC to JDBC bridges. installed the software while testing the connection getting below error

Please help me to resolve this connectivity issue, and let me know if you need more information on this.

Our Environment details :

SAS Version : SAS 9.4 M3 

Linux : RHEL 6.7

ERROR MESSAGE : 

log4j:WARN No appenders could be found for logger (com.simba.athena.amazonaws.AmazonWebServiceClient).

log4j:WARN Please initialize the log4j system properly.

log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.

java.sql.SQLException: [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. The request signature we calculated does not match the signature you provided. Check your AWS Secret Access Key and signing method. Consult the service documentation for details.

        at com.simba.athena.athena.api.AJClient.checkAndThrowException(Unknown Source)

        at com.simba.athena.athena.api.AJClient.executeQuery(Unknown Source)

        at com.simba.athena.athena.api.AJClient.checkAthenaConnection(Unknown Source)

        at com.simba.athena.athena.api.AJClient.<init>(Unknown Source)

        at com.simba.athena.athena.core.AJConnection.connect(Unknown Source)

        at com.simba.athena.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)

        at com.simba.athena.jdbc.common.AbstractDriver.connect(Unknown Source)

        at java.sql.DriverManager.getConnection(DriverManager.java:664)

        at java.sql.DriverManager.getConnection(DriverManager.java:247)

        at oajava.passjdbc.jdbcutil.connect(jdbcutil.java:35)

Caused by: com.simba.athena.support.exceptions.GeneralException: [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. The request signature we calculated does not match the signature you provided. Check your AWS Secret Access Key and signing method. Consult the service documentation for details.

        ... 10 more

Caused by: com.simba.athena.amazonaws.services.athena.model.AmazonAthenaException: The request signature we calculated does not match the signature you provided. Check your AWS Secret Access Key and signing method. Consult the service documentation for details. (Service: AmazonAthena; Status Code: 400; Error Code: InvalidSignatureException; Request ID: fd368828-5cdc-11e8-b635-2b0a69392482)

        at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.handleErrorResponse(AmazonHttpClient.java:1639)

        at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.executeOneRequest(AmazonHttpClient.java:1304)

        at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.executeHelper(AmazonHttpClient.java:1056)

        at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.doExecute(AmazonHttpClient.java:743)

        at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.executeWithTimer(AmazonHttpClient.java:717)

        at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.execute(AmazonHttpClient.java:699)

        at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutor.access$500(AmazonHttpClient.java:667)

        at com.simba.athena.amazonaws.http.AmazonHttpClient$RequestExecutionBuilderImpl.execute(AmazonHttpClient.java:649)

        at com.simba.athena.amazonaws.http.AmazonHttpClient.execute(AmazonHttpClient.java:513)

        at com.simba.athena.amazonaws.services.athena.AmazonAthenaClient.doInvoke(AmazonAthenaClient.java:802)

        at com.simba.athena.amazonaws.services.athena.AmazonAthenaClient.invoke(AmazonAthenaClient.java:778)

        at com.simba.athena.amazonaws.services.athena.AmazonAthenaClient.executeStartQueryExecution(AmazonAthenaClient.java:684)

        at com.simba.athena.amazonaws.services.athena.AmazonAthenaClient.startQueryExecution(AmazonAthenaClient.java:660)

        at com.simba.athena.athena.api.AJClient.executeQuery(Unknown Source)

        at com.simba.athena.athena.api.AJClient.checkAthenaConnection(Unknown Source)

        at com.simba.athena.athena.api.AJClient.<init>(Unknown Source)

        at com.simba.athena.athena.core.AJConnection.connect(Unknown Source)

        at com.simba.athena.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)

        at com.simba.athena.jdbc.common.AbstractDriver.connect(Unknown Source)

        at java.sql.DriverManager.getConnection(DriverManager.java:664)

        at java.sql.DriverManager.getConnection(DriverManager.java:247)

        at oajava.passjdbc.jdbcutil.connect(jdbcutil.java:35)

        at oajava.passjdbc.dbpassjdbc.ipConnect(dbpassjdbc.java:311)

SQLSTATE = IM002

NATIVE ERROR = 10101

MSG = [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]java.sql.SQLException: [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. The request signature we calculated does not match the signature you provided. Check your AWS Secret Access Key and signing method. Consult the service documentation for details.[10101]

[JBailey]

Hi @srinivask

It looks like a problem with the AWS credentials. Are you sure they are up-to-date?

Best wishes,
Jeff

[srinivask]

Hi Jeff , 

Thanks for your response.

yes , the credential we are using working fine. tested  through AWS CLI we are able to connect s3 bucket and list the objects.

Currently getting below error :

SQLSTATE = HY000
NATIVE ERROR = 100211
MSG = [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine][JDBC Driver][Simba][AthenaJDBC](100211) Missing credentials error: Either UID/PWD or AwsCredentialsProviderClass must be provided.

[JBailey · Accepted solution]

Hi @srinivask,

The error message looks like the driver can't find the credentials. Are your credentials stored in ~/.aws/credentials directory? If so, can you share the environment variables that are stored in the credentials directory. Not the values - variable names only.

This doc (page 32) may help: https://s3.amazonaws.com/athena-downloads/drivers/ODBC/Simba+Athena+ODBC+Install+and+Configuration+G...

Then again, there may be something on the DataDirect ODBC -> JDBC bridge happening.

Update *** Update *** Update

I was able to get SAS/ACCESS Interface to ODBC to work with Amazon Athena using the Simba ODBC driver. I used the Amazon Athena tutorial to create an Athena databases. The tutorial is available here:

https://docs.aws.amazon.com/athena/latest/ug/getting-started.html

I used a DSN-less connection. My credentials are stored locally. Your credentials will be different than the ones used here and you will need to store them in the default location for your OS. If you use database= you will not see the table name in your SAS library. Schema= is required if you are using a non-default schema.

libname athena odbc
complete="DRIVER={Simba Athena ODBC Driver};
AwsRegion=us-east-1;
S3OutputLocation=s3://my-athena-results-bucket/;
AuthenticationType=IAM Profile;
AwsProfile=117292474239-myprofile;" schema=mydb;

SAS Viya 3.4 includes a new SAS/ACCESS product for JDBC. This product will come to the SAS 9.4 platform in the future. I am planning to write an article showing how it works. 

Best wishes,
Jeff

[srinivask]

Hi Jeff,

attributes under ---   ~/.aws/credentials

[default]
aws_access_key_id = xxxxxxxxxxxxxx
aws_secret_access_key = xxxxxxxxxxxxx

[JBailey]

Hey @srinivask

Were you able to get this to work?

[skygold16]

Hi @JBailey : 

I am trying to do the similar thing and I am not able to get it connected. 

I have installed the Simba driver and trying to connect it using below:

libname athena odbc
complete="DRIVER={/opt/simba/athenaodbc/lib/64/libathenaodbc_sb64.so};
AwsRegion=ap-southeast-2;
S3OutputLocation=s3://temp-XXX-XXXX-XXXX-XXX-query-results/dev/athena_dev_x;
AuthenticationType=IAM Credentials;
UID=myuserid;
PWD=my password;" schema=dev_X;

The only difference I see is, I do not have internet connection on SAS box and hence I am using a proxy server to redirect my request to get it connected to AWS. we have updated the odbc.ini file and specified the proxy server details too.

Driver=/opt/simba/athenaodbc/lib/64/libathenaodbc_sb64.so
AwsRegion=ap-southeast-2
Schema=dev_X
S3OutputLocation=s3://temp-XXX-XXXX-XXXX-XXX-query-results/dev/athena_dev_x
S3OutputEncOption=SSE_KMS
S3OutputEncKMSKey=XXX:aws:XXX:ap-southeast-2:XXXXXXXXXXXXXX:key/XXXXXXXXXXXXXXXXXXXXXXXXX
AuthenticationType=IAM Credentials
UID=XXXXXXXXXXXX
PWD=XXXXXXXXXX
UseProxy=1
ProxyScheme=HTTPS
ProxyHost=proxy server detail
ProxyPort=XXXX

When I run libname, it just keeps running and no result. I have tried connecting internet using unix command and I am sure I am able to connect to the internet but athena. 

Any help is appreciated. 

Thanks,

HD

[JBailey]

Hi @skygold16

This is a solved issue. If you don't mind, create a new thread because it helps make it easier for people to find.

Mention me (@jbailey) in the new post. That way I will be sure to see it.

Best wishes,

Jeff

[JBailey]

Hi @srinivask

Were you able to get this to work?