topic Re: Performance enhancement for Read Operation from AWS_RedShift in SAS Data Management

Performance enhancement for Read Operation from AWS_ReadShift

vishakh — Mon, 20 May 2019 16:25:24 GMT

when we query a table from the RedShift – we can see that our server memory (60Gb) gets exhausted. And when we terminate the query execution all the memory will be freed-up. So, from this looks, we can observe that the query result is being computed on our local SAS server thus causing for significant resource consumption.

ODBC-Drivers used:

PostgreSQL: psqlodbc.so, libodbcpsqlS.so

MySQL: libmyodbc5.so, libodbcmyS.so

MySQL Unicode: libmyodbc5w.so

MySQL ANSI Driver: libmyodbc5a.so

Re: Performance enhancement for Read Operation from AWS_RedShift

ChrisHemedinger — Tue, 21 May 2019 14:00:37 GMT

Hi, it sounds like you're using a generic ODBC (or Postgres) driver for this from SAS. Even though Redshift is Postgres-like, it's not the same as Postgres.

SAS has a specialized engine for Redshift that's more performant -- read about it here. If you don't have access to this engine, then I think you'll need to rely on explicit passthrough (use the CONNECT statement in your PROC SQL) to pass exactly the SQL you want the database to process. The Redshift engine will help to optimize -- without this specific engine, the burden is on you to know more about how the Redshift database works.

Chris

Re: Performance enhancement for Read Operation from AWS_RedShift

vishakh — Tue, 21 May 2019 14:48:11 GMT

How can we determine that we have access to this specialized Redshift Engine?

Re: Performance enhancement for Read Operation from AWS_RedShift

ChrisHemedinger — Tue, 21 May 2019 14:57:38 GMT

In your SAS environment, run:

proc product_status;
run;

If it's licensed/installed, you'll see it listed in the log.

Re: Performance enhancement for Read Operation from AWS_RedShift

vishakh — Tue, 21 May 2019 16:06:15 GMT

Just to brief-in: Currently, I am using UnixODBC Driver Manager of version 2.2.14 and as we know to install the redshift driver we need to have a UnixODBC manager of minim. version 2.3.0

I have installed the newest version of manager.

Can you please let me know the files and locations of SAS config files where I need to change the path of the odbc path to pick-up new version?

Re: Performance enhancement for Read Operation from AWS_RedShift

vishakh — Wed, 22 May 2019 13:19:58 GMT

Can you please let me know the locations/files that I need to update with the new unixODBC driver path

Re: Performance enhancement for Read Operation from AWS_RedShift

vishakh — Wed, 22 May 2019 15:14:19 GMT

Attached is the screenshot showing our current LD_LIBRARY_PATH and PATH

Re: Performance enhancement for Read Operation from AWS_RedShift

ChrisHemedinger — Thu, 23 May 2019 13:55:48 GMT

See the doc for the different environment variables and ODBC.INI options that influence where SAS finds/loads your ODBC driver libraries.

I'm not sure we can tell you exactly how to change this for your environment, given that only you will know exactly where these libraries reside. But perhaps there are common conventions and someone can chime in.

This article about UnixODBC (with lots of linked articles) might also help.