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;
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
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!
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.