topic Re: Connect Athena from SAS ODBC in SAS Data Management

Connect Athena from SAS ODBC

skygold16 — Thu, 04 Oct 2018 00:51:37 GMT

I am trying to connect AWS S3 bucket through athena. I have installed the simba ODBC driver provided by AWS.

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 isql command and I am sure I am able to connect to the internet but athena.

@JBailey

Any help is appreciated.

Thanks,

Re: Connect Athena from SAS ODBC

JBailey — Fri, 05 Oct 2018 15:12:34 GMT

Hi @skygold16

Do you see any error messages when you try to connect?

Best wishes,

Jeff

Re: Connect Athena from SAS ODBC

JBailey — Tue, 09 Oct 2018 16:31:44 GMT

Hi @skygold16

UPDATE! I have this working... I cannot believe it..

Part of the problem is that the arguments have different names in the JDBC driver... I copied my code from there.

This actually worked for me (getting the proxy server info was the most difficult part):

libname athena odbc
complete="DRIVER={Simba Athena ODBC Driver};
AwsRegion=us-east-1;
S3OutputLocation=s3://bogus-athena-results/;
AuthenticationType=IAM Profile;
AwsProfile=617292732428-jbsandbox;
UseProxy=1;
ProxyScheme=HTTP;
ProxyHost=111.111.111.111;
ProxyPort=80;
ProxyUID=ProxyUserID;
ProxyPWD=ProxyPassword;" schema=somedb;

I am able to get this to work without specifying the ProxyUID= and ProxyPWD= options.

My experience with this type of stuff is that it takes an embarrassing amount of trial and error;) <-- This is true!

I am going to create an article or a blog post on this...

Best wishes,

Jeff

Re: Connect Athena from SAS ODBC

skygold16 — Tue, 13 Nov 2018 03:50:42 GMT

Thanks @JBailey.

I got this working with another method too by specifying DSN in odbc file and use ODBC DSN in libname.

libname athena odbc DSN="AWS-Athena-XXX-dev" SCHEMA="xxx_dev";

Issue was also with Amazon driver and after downgrading driver I was able to make it working.

Thanks Anyways.

Cheers,