SAS/Access to ODBC and AWS Athena

[AdamT]

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;

[SASKiwi]

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!

[AdamT]

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.

[JBailey · Accepted solution]

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