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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.