How to use SAS and Redshift JDBC driver ?

[pathanfahim1986]

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. 

[Anand_V]

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&docset...

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&docset...

Hope this helps!

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

[sathya66]

Hi ,

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

Thanks,

SS

[SASKiwi]

@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.

[sathya66]

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;

[SASKiwi]

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.   

[sathya66]

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

[SASKiwi]

@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...

[sathya66]

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.

[sathya66]

All,

Redshift connection is success

Steps that I followed

1. Download the latest redshift driver file (zip file  https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-download-driver.html .
2. unzip the file to a location on the compute server.
3. make sure security groups or ACLs are setup correctly.
4. 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";