Athena ODBC.ini

SASUser_86 — Mon, 28 Sep 2020 21:04:16 GMT

Hi, we're currently using an odbc.ini file to store connection / dsn information.

[athena]
Description=Simba Athena ODBC Driver (64-bit)
DSNDriver=driver
AwsRegion=us-east-1
S3OutputLocation=s3://bucket/
Schema=database
AuthenticationType=IAM Profile

AwsProfile=default

I've been testing / vetting out which parameters are required and what I can include in my SAS statement instead of storing it in the ini file.

LIBNAME athena ODBC DATAsrc=athena schema="sas";
proc sql;
SELECT *
FROM athena.sas;

I see that I can use "schema" which will overwrite the schema set ini file. I'm curious if this can be done with region? I tried a few statements without any success:

LIBNAME athena ODBC DATAsrc=athena schema="sas" region="us-east-1";

I tried replacing region with awsregion and BL_REGION, neither of these worked.

Feedback is much appreciated. Thank you.

Re: Athena ODBC.ini

JBailey — Tue, 29 Sep 2020 20:13:38 GMT

You have two options available:

DSN-less ODBC connections - you can read about it here. This is the approach that I used in the post you referenced.
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;" schema=somedb;

Here is an example of conopts=. 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 athena odbc datasrc=athena conopts="AwsRegion=us-east-1" schema=sas;

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

Best wishes,

Jeff

topic Re: Athena ODBC.ini in SAS Enterprise Guide

Athena ODBC.ini

Re: Athena ODBC.ini