Connect SAS to AWS Athena via ODBC

[eirigoyen]

We are trying to connect to Athena where users can dynamically change their connection parameters based on their needs. 

 We can connect when we specify all the required parameters in the odbc.ini file (see 1 below). The UID and PWD parameters are being passed via the libname statement inside EG (See 2 below).

 -------- 1 ------

ODBC.INI

[Simba_Athena]

Driver=/opt/simba/athenaodbc/lib/64/libathenaodbc_sb64.so

AwsRegion=us-east-1

S3OutputLocation=s3://shared-bucket1-us-east-1/user/

AuthenticationType=IAM Credentials

#UID=xxxxxxxx

#PWD=*xxxxxxx

SessionToken=xxxxxxx

------------------------------------------------

  -------- 2 ------

libname atena3 odbc dsn=Simba_Athena schema=xyz001_tst UID=xxxxxxx

PWD=xxxxx readbuff= 10000;

------------------------------------------------

We need to make this more dynamic and have the S3OutputLocation and SessionToken variables available in the libname statement as we can't change the odbc.ini file every day.

 We are using:

1) ODBC Driver Manager:  unixODBC2.3.6

2) SimbaAthenaODBC-64bit.x86_64 1.0.5-1

We read this post; however, we do not use a proxy server.

 We read a couple posts about successful connections but need more detail.

 @JBailey

Any help is appreciated (you responded to the post noted above about successful connection)!

[JBailey · Accepted solution]

Hi @eirigoyen 

You have two options available:

1. DSN-less ODBC connections - you can read about it here. This is the approach that I used in the post you referenced.
2. The CONOPTS= option.

The trick is to look at the documentation for the ODBC driver and find the supported options. These options can be passed via either of these options. 

The following code uses a DSN-less connection to connect to Athena. The information contained in the COMPLETE= option overrides the odbc.ini information.

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;

Here is an example of conopts= (SAS/ACCESS Interface to Impala). The example shows how to pass the ODBC driver options to the driver. Like the DNS-less connection, CONOPTS= overrides the information in the odbc.ini file.

libname impdd  impala server='quickstart.cloudera' driver_vendor=datadirect
                      conopts="AuthenticationMethod=0;uid='Bert';pw='Ernie'";

Check the documentation for the ODBC driver; there may be a way to use environment variables for the security stuff.

If this doesn't help you, let me know and I will try to create an Athena example.

Best wishes,

Jeff