BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AdamT
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
JBailey
Barite | Level 11

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

View solution in original post

3 REPLIES 3
SASKiwi
PROC Star

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
Calcite | Level 5

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
Barite | Level 11

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1794 views
  • 0 likes
  • 3 in conversation