Athena

[SASUser_86]

Hi, I'm running the following program to connect to Athena odbc:

libname athena odbc
complete="DRIVER={Simba};
AwsRegion=us-east-1;
S3OutputLocation=s3://s3-bucket;
AuthenticationType=IAM Profile;
AwsProfile=Profile;" schema=AwsData;

The program was running for a little over 20 minutes when I killed the process, stopped in EG and retry. In the second attempt, I am experiencing the same wait. Is there anything I can do to triage this issue? It seems abnormal for the connection to take this long. 

[ballardw]

---

@SASUser_86 wrote:

Hi, I'm running the following program to connect to Athena odbc:

libname athena odbc
complete="DRIVER={Simba};
AwsRegion=us-east-1;
S3OutputLocation=s3://s3-bucket;
AuthenticationType=IAM Profile;
AwsProfile=Profile;" schema=AwsData;

The program was running for a little over 20 minutes when I killed the process, stopped in EG and retry. In the second attempt, I am experiencing the same wait. Is there anything I can do to triage this issue? It seems abnormal for the connection to take this long. 

---

Is that the only code you ran? That should only be establishing connection and typically take little time.

If you were moving data across the connection then the size of the data (sets), speed of the connection and options used the code can have some pretty drastic effect on the run time. You might show some of the other code you used and we may suggest some ways to test that with fewer records to see what may help.

[SASUser_86]

Hi, this is the only code I am running. I am trying to test my connection to Athena -- have not gotten as far as grabbing data yet. You did mention that the connection takes some time. I just stopped/killed my last process at 37 minutes. Would it typically run longer than 30 minutes to establish a connection? Thank you.

[SASKiwi]

Is this the first time you have tried getting this working? Has anyone else at your organisation tried and how did they get on?

What version and maintenance release of SAS are you using? SAS 9.4M?? I suspect you will need a recent maintenance release for this to work.

SAS experts like @JBailey might like to chip in.

[SASUser_86]

@SASKiwi 

This is the first time I am attempting to establish a connection. It's pretty much trial and error as our organization is fairly new in the AWS space. We're actually testing this functionality in our SAS Test server which is on 9.4M6 (prod is currently on 9.4M4). If I understood this documentation correctly, it appears the ODBC functionality should be available in 9.4M6: https://support.sas.com/kb/52/777.html

Not sure if this is helpful information, but minutes before, I was able to connect to S3 using the following code:

options set=HTTPS_PROXY="http://username:&password@proxy.com:portnumber";
options set=HTTP_PROXY="http://username:&password@proxy.com:portnumber";

proc s3 AWSCredentials="~/.aws/credentials" AWSCONFIG="~/.aws/config" PROFILE="profile";
GET "/aws/075f0a3.txt" "/home/userid/075f0a3.txt";
run;

@JBailey 

Are there any notes or messages displayed in the SAS log? For clarification, by SAS log, do you mean the log that appears in Enterprise Guide once a program has completed? The program that I executed did not complete, it stayed in "running" state the entire time until I killed it (last attempt, I let it run for 50 minutes). I am actually curious whether there are other logs that I can leverage. I'd like to understand where the program is "stuck". I checked in my user folder on the unix server, I was unable to find anything that appears like a log. Note: I don't have a lot of experience in SAS either.

Are you running SAS in AWS or on-premises? On-premises, I was not aware I can run SAS in AWS. 🙂

Looking at the program, you posted. I see some similarities, but the glaring differences are the addition of proxy information. Would you be able to point me to documentation explaining these options? Are these options available with ODBC only? Oh, also, is it safe to assume that the schema portion is referring to "Database" in Athena as opposed to "Data Source"?

/* this is the skeleton that I posted on the communities */
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;

 Thank you very much!

[JBailey]

Hi @SASUser_86 

SAS 9.4M6 willl work fine. SAS/ACCESS Interface to ODBC has been around a long time. No problems there.

You didn't mention this, but I will bring it up again because it is common. I know because this has hit me more times than I care to admit. I feel like an idiot EVERY SINGLE TIME. Make sure there are no stray white space characters in your code. I usually remove all white space and try it. Something like this...

/* simplified example */
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;

I assume that your SAS server is running on Linux. There may be an issue with the ODBC configuration. I know if you have SAS/ACCESS Interface to MS SQL installed, someone must play tricks with the ODBC Driver Manager.

It is important to note, everything specified in the CONNECT= option (between the "") comes from the ODBC driver documentation (see link below). The example I used is overly complicated for purposes. I assume that you don't need to use a proxy server. Then again, that could actually be the problem - but, I doubt it.

Here is an example that matches what you are trying to do.

/* simplified example */
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;

Simba ODBC Driver Documentation

When I have issues like this, I usually:

• Try to connect using a simple ODBC query tool - dbsquirrel, Excel, etc.
• Put an error (say a bad S3 bucket) in the code to see if the remote source is seeing the connection request. You will know because Athena will complain about the bucket not existing.

SAS can run on cloud environments. We have lots of customers doing it. Check out our partnership with Microsoft. Cool stuff. I haven't announced it, but I moved from Product Management to our SAS Cloud group a couple of months ago. The cloud is a lot of fun. I highly recommend spending some quality time with it. 

This SAS Communities post may help - https://communities.sas.com/t5/Administration-and-Deployment/Connect-SAS-to-AWS-Athena-via-ODBC/td-p...

Let me know how it goes,

Jeff

[JBailey]

Hi @SASUser_86,

20 minutes for a connection is crazy. It should take seconds (actual time depends on networking speeds, etc.).

Are there any notes or messages displayed in the SAS log?

Are you running SAS in AWS or on-premises?

COMPLETE= is very sensitive to white space characters. It may be a good idea to make sure that there are no tabs or spaces in the argument.

I posted the following code snippet in another Communities post. It may help you. It is more complicated than what you are trying to do, but it is a good example. 

/* this is the skeleton that I posted on the communities */
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;

It has been a while since I played with Athena, but I vaguely remember having an issue if I don't place the trailing "/" on the bucket name. You may want to add it. That being said, if that were the problem the code would return and error quickly. 

Post the relevant section of your log or let us know that there aren't messages going into the log.

Best wishes,

Jeff