topic How to use SAS and Redshift JDBC driver ? in Architecture

How to use SAS and Redshift JDBC driver ?

pathanfahim1986 — Wed, 03 Jun 2020 16:12:15 GMT

Hello SAS expert,

Question : Can we use Redshift JDBC driver with SAS Access to JDBC ?

If it is possible, Can we use Bulkload option with same ?

I checked the available documentation and could not find the example with Redshift JDBC driver with SAS Access to JDBC product and about Bulkload option.

Review the existing SAS and Redshift documentation.

Thanks.

Re: How to use SAS and Redshift JDBC driver ?

AnandVyas — Thu, 04 Jun 2020 06:21:15 GMT

Hi Fahim,

SAS/Access to JDBC in 9.4 was introduced in Nov 2018 release and yes don't see much examples for it connecting to AWS Redshift as SAS also offers a native SAS/Access to Redshift engine.

As per the system requirements documentation for JDBC you would need a driver that comply with the JDBC 4.1 specification or later. It should support below classes for a great chance of success.

source: https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p1soj7yuc1975yn1nndc3s11dhsv.htm&docsetVersion=9.4&locale=en#p1swhxnh666q3fn17f34x6x5dg0k

Bulk load option is supported in JDBC & data is loaded based on how the driver works.

Source: https://documentation.sas.com/?docsetId=acreldb&docsetTarget=n0j1e5bcfyjygxn1av4lu5yshd3s.htm&docsetVersion=9.4&locale=en

Hope this helps!

If your site has SAS/Access to JDBC, give it a shot and do share your experience.

Re: How to use SAS and Redshift JDBC driver ?

sathya66 — Thu, 11 Mar 2021 11:39:34 GMT

Hi ,

Any update on this as we are also wanted use redshift with SAS/ACCESS to JDBC.

Thanks,

Re: How to use SAS and Redshift JDBC driver ?

SASKiwi — Fri, 19 Mar 2021 21:46:32 GMT

@sathya66 - Have you checked the system requirements in the link provided? This is current as of Mar 2021. If you aren't running SAS on Linux then you will need to check the system requirements appropriate for your OS.

Re: How to use SAS and Redshift JDBC driver ?

sathya66 — Thu, 22 Apr 2021 12:22:15 GMT

Yes, We have met the system requirements and We are running SAS on Linux .

few things need to clarify -

do we need to install redshift drivers on our SAS servers? or just SAS/ACCESS to JDBC license is fine ?

what else do we need to enable from SAS server side (any jar files,etc)

For initial test, is below code works?

libname x JDBC driverclass="org.postgresql.Driver"

   URL="jdbc:postgresql://myserver:myport/<postgres-database>" user=myuser

   password="mypwd1" classpath="c:\lib";



proc print data=x.customers;

   where state='CA';

run;

Re: How to use SAS and Redshift JDBC driver ?

SASKiwi — Fri, 23 Apr 2021 00:03:02 GMT

Database access drivers usually have to be installed locally on the SAS App server. Have you tested your code? You would be best to open a track with SAS Tech Support if you are still having problems. We don't have JDBC in our SAS environment so I can't test anything for you.

Re: How to use SAS and Redshift JDBC driver ?

sathya66 — Tue, 27 Apr 2021 13:58:17 GMT

No we are gathering the info. Got any AWS redshift JDBC driver install instructions and best link to download and install.

Re: How to use SAS and Redshift JDBC driver ?

SASKiwi — Tue, 27 Apr 2021 20:07:23 GMT

@sathya66 - Why don't you Google for this or check the Redshift website? Since you have been experimenting with it already you know more than I do...

Re: How to use SAS and Redshift JDBC driver ?

sathya66 — Wed, 05 May 2021 11:30:05 GMT

Thanks for this,
tested with libname statament
now i am getting below ERROR
ERROR: Error trying to establish connection: java.lang.ClassNotFoundException: com.amazon.redshift.jdbc42.Driver
ERROR: Error in the LIBNAME statement.

Re: How to use SAS and Redshift JDBC driver ?

sathya66 — Tue, 11 May 2021 14:53:31 GMT

All,

Redshift connection is success

Steps that I followed

Download the latest redshift driver file (zip file https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-download-driver.html .
unzip the file to a location on the compute server.
make sure security groups or ACLs are setup correctly.

tested with libname

libname test JDBC 
	driverclass="com.amazon.redshift.jdbc.Driver"
    URL="jdbc:redshift:/XXXX:port/database" 
	user=test_user 
    password="test" 
	classpath="/sas/redshift";