topic Re: SAS/Access to ODBC and AWS Athena in SAS Programming

SAS/Access to ODBC and AWS Athena

AdamT — Wed, 10 Apr 2019 19:16:06 GMT

Has anyone had any good luck in pulling data from Athena back to SAS? Any tricks to optimizing the connection string or library options to speed it up?

I've tried using the same Simba ODBC driver with SAS and a few other products, but the same query that'll take a few minutes with an R ODBC connection will take a few hours to pull to SAS. Even with a SAS instance hosted in an AWS environment.

Here's the connection string I've been using so far. It works to pull data, but slowly compared to using the same query and connection string in other tools:

%LET ap_connstr = %STR(%')
        Driver=Simba Athena ODBC Driver;
        AwsRegion=&ap_region;
        S3OutputLocation=&ap_s3path;
        AuthenticationType=IAM Credentials;
        AwsProfile=&ap_profile;
        UseResultsetStreaming=0;
        RowsToFetchPerBlock=1000;
        StringColumnLength=32;
        schema=&ap_schema;
        UID=&ap_user;
        PWD=&ap_pass;
     %STR(%');

LIBNAME ap ODBC COMPLETE=%UNQUOTE(&ap_connstr) SCHEMA=&ap_schema SQL_FUNCTIONS=all;

Re: SAS/Access to ODBC and AWS Athena

SASKiwi — Wed, 10 Apr 2019 20:05:00 GMT

A problem like this is likely going to require some diagnostics so I'd suggest opening a Tech Support track if you haven't done so already.

IMO moving to the cloud definitely has some advantages, but troubleshooting isn't one of them!

Re: SAS/Access to ODBC and AWS Athena

AdamT — Wed, 10 Apr 2019 21:47:48 GMT

IMO moving to the cloud definitely has some advantages, but troubleshooting isn't one of them!

Haha, isn't that the truth!

But thanks for the tip on opening a ticket with tech support, I'll be sure to do that.

Re: SAS/Access to ODBC and AWS Athena

JBailey — Fri, 04 Oct 2019 12:40:57 GMT

Hi @AdamT

Make sure you add the INSERTBUFF= and READBUFF= options to your LIBNAME statement. Finding the "best" - if there is such a thing - setting takes experimentation. I would begin by setting each option to 10000.

Best wishes,
Jeff