BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
srinivask
Calcite | Level 5

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]

1 ACCEPTED SOLUTION

Accepted Solutions
JBailey
Barite | Level 11

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

 

View solution in original post

8 REPLIES 8
JBailey
Barite | Level 11

Hi @srinivask

 

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

 

Best wishes,
Jeff

 

 

srinivask
Calcite | Level 5

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
Barite | Level 11

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
Calcite | Level 5

Hi Jeff,

 

attributes under ---   ~/.aws/credentials

 

[default]
aws_access_key_id = xxxxxxxxxxxxxx
aws_secret_access_key = xxxxxxxxxxxxx

JBailey
Barite | Level 11

Hey @srinivask

 

Were you able to get this to work?

skygold16
Fluorite | Level 6

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
Barite | Level 11

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
Barite | Level 11

Hi @srinivask

 

Were you able to get this to work?

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 39801 views
  • 0 likes
  • 3 in conversation