Performance enhancement for Read Operation from AWS_ReadShift

[vishakh]

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

[ChrisHemedinger]

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

[vishakh]

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

[ChrisHemedinger]

In your SAS environment, run:

proc product_status;
run;

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

[vishakh]

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?

[vishakh]

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

[vishakh]

Attached is the screenshot showing our current LD_LIBRARY_PATH and PATH

[ChrisHemedinger · Accepted solution]

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.