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

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)!

1 ACCEPTED SOLUTION

Accepted Solutions
JBailey
Barite | Level 11

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

View solution in original post

1 REPLY 1
JBailey
Barite | Level 11

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

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 6342 views
  • 1 like
  • 2 in conversation