topic Re: Connecting to Databricks using JDBC from SAS Viya 4 in Administration and Deployment

Connecting to Databricks using JDBC from SAS Viya 4

thesasuser — Fri, 14 Feb 2025 21:48:07 GMT

We are using SAS Viya 4 2024.09 LTS on AWS.
I am trying to test connectivity to Databricks on AWS using JDBC but having errors.
My code is as follows

libname libdbrk jdbc classpath="/opt/sas/viya/home/lib64/accessclients/jdbc"
driver="/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-databricks-23.0.8806.0.jar" 
class="cdata.jdbc.databricks.DatabricksDriver"
URL="jdbc:databricks://xyz.cloud.databricks.com:443;AuthMech='11';Auth_Flow='1';
     database=dev_test.test_schema;
     HTTPPath=/sql/1.0/warehouses/abcdxyz; OAuth2ClientId='Client_ID'; OAuth2Secret='Client Secret'";

The log is

WARNING: Libref LBDBRK is not assigned.
30   libname lbdbrk jdbc classpath="/opt/sas/viya/home/lib64/accessclients/jdbc"
31   driver="/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-databricks-23.0.8806.0.jar"
32   class="cdata.jdbc.databricks.DatabricksDriver"
33   URL="jdbc:databricks://xyz.cloud.databricks.com:443;AuthMech='11';Auth_Flow='1';
34        database=dev_test.test_schema;
35        HTTPPath=/sql/1.0/warehouses/abcdxyz;OAuth2ClientId='Client_ID';
35 ! OAuth2Secret='Client-Secret'";
NOTE: The "CLASSPATH" option has been deprecated and will be ignored. Please refer to SAS documentation for instructions on setting 
      the JDBC SAS/ACCESS classs path.
ERROR: Error trying to establish connection: java.sql.SQLClientInfoException: 'useragententry' is not a valid connection property.
ERROR: Error in the LIBNAME statement.

Wondering if anybody can help?

Re: Connecting to Databricks using JDBC from SAS Viya 4

SASKiwi — Sat, 15 Feb 2025 01:12:36 GMT

Here's a link to the LIBNAME JDBC engine options. As your log has already stated, CLASSPATH is ignored so there is no point in including it in your program.

Also if you read the DRIVERCLASS doc you will see that CLASS and DRIVER are aliases for it, so you should remove both DRIVER and CLASS and just use DRIVERCLASS with the default doc value.

I don't use JDBC so I have no idea if my suggestions will help fix your error, but using the correct options is always a good starting point.

Is JDBC the only option available to you regarding Databricks? SAS/ACCESS Interface to Spark and SAS/ACCESS Interface to ODBC are other Viya connectors that should work and all of these are included as standard with Viya.

Re: Connecting to Databricks using JDBC from SAS Viya 4

thesasuser — Mon, 17 Feb 2025 14:32:47 GMT

Thanks @SASKiwi . I appreciate your prompt response.
I followed the instructions at SAS Help Center: LIBNAME Statement for JDBC.
However, this did not work.
The code and logs are reproduced below

libname lbdbrk jdbc  user="client-id" password="secret"
driverclass="/opt/sas/viya/home/lib64/accessclients/jdbc/"
URL="jdbc:databricks://dbc-b5c27db9-dd8c.cloud.databricks.com:443/database.schema"  ;

I tried using the database jar file names too as /opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-databricks-23.0.8806.0.jar.
I also used this name (cdata-jdbc-sparksql-23.0.8705.jar) too. But that did not change the situation either.
The log is reproduced below

WARNING: Libref LBDBRK is not assigned.
29   libname lbdbrk jdbc  user="client-id" password=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
30   driverclass="/opt/sas/viya/home/lib64/accessclients/jdbc/"
31   URL="jdbc:databricks://dbc-b5c27db9-dd8c.cloud.databricks.com:443/database.schema"
32    ;
ERROR: Error trying to establish connection: java.lang.ClassNotFoundException: /opt/sas/viya/home/lib64/accessclients/jdbc/
ERROR: Error in the LIBNAME statement.

I have even tried using the latest driver (DatabricksJDBC42.jar) from the Databricks siteJDBC Drivers Download – Databricks
But that did not help

Re: Connecting to Databricks using JDBC from SAS Viya 4

SASKiwi — Mon, 17 Feb 2025 19:47:17 GMT

I suggest you open a track with Tech Support as these types of problems often require a lot of troubleshooting.

It looks to me like a Java config issue but TS can advise.

Re: Connecting to Databricks using JDBC from SAS Viya 4

gwootton — Tue, 18 Feb 2025 14:38:38 GMT

This error is saying that the engine is adding a connection property to your url called "useragententry" which the driver is rejecting as invalid.

With the cdata databricks driver if you specify the URL directly, you need to use the form:
jdbc:databricks:Server= (no :// in the URL)
This is probably whats causing the engine to add options for a different driver.

Re: Connecting to Databricks using JDBC from SAS Viya 4

thesasuser — Wed, 19 Feb 2025 14:50:49 GMT

Thanks @gwootton . I followed your suggestion and updated my code as follows

libname libdbrk jdbc 
driver="/opt/sas/viya/home/lib64/accessclients/jdbc/cdata-jdbc-databricks-23.0.8806.0.jar" 
class="cdata.jdbc.databricks.DatabricksDriver"
URL="jdbc:databricks:Server=xyz.databricks.com;    
 HTTPPath=/sql/1.0/warehouses/abcdefgh;
 user='client-id';Token='Token'";

The log is

ERROR: Error trying to establish connection: HTTP protocol error. 401 Unauthorized.
ERROR: Error in the LIBNAME statement.

It has no option for using the port (443). Using port=443 or protocol=https raises an error.
Can you please advise.

Re: Connecting to Databricks using JDBC from SAS Viya 4

gwootton — Thu, 20 Feb 2025 18:52:41 GMT

You are receiving a response from the server so this is not an issue of the incorrect port, I believe the driver assumes https/port 443. The response you are receiving is a 401 unauthorized, so appears to be an issue with your authentication options. If you are wrapping your user and token in single quotes you may wish to try without those.

Re: Connecting to Databricks using JDBC from SAS Viya 4

thesasuser — Thu, 20 Feb 2025 19:39:24 GMT

Thanks @gwootton .
I removed the quotes, but the error remains the same.

Re: Connecting to Databricks using JDBC from SAS Viya 4

gwootton — Thu, 20 Feb 2025 21:50:08 GMT

In that case, I would suspect the issue to be with the authentication option values themselves. Are you able to log in to databricks with the 'client-id' and 'Token' values outside of SAS?

Re: Connecting to Databricks using JDBC from SAS Viya 4

thesasuser — Thu, 20 Feb 2025 22:05:41 GMT

Yes I am indeed able to connect from out side SAS.

Re: Connecting to Databricks using JDBC from SAS Viya 4

gwootton — Thu, 20 Feb 2025 22:20:49 GMT

The syntax looks right to me, assuming you are using Basic authentication and not a Personal Access Token, in which case you would not set a User it looks like:
https://cdn.cdata.com/help/LKJ/jdbc/pg_connectionj.htm

Re: Connecting to Databricks using JDBC from SAS Viya 4

thesasuser — Fri, 21 Feb 2025 16:33:20 GMT

Thanks @gwootton . I appreciate your time and efforts in assisting me.
I have already tried that. It does not work

Re: Connecting to Databricks using JDBC from SAS Viya 4

SASKiwi — Fri, 21 Feb 2025 21:46:38 GMT

When you've tried the obvious things and you are still stuck, then IMHO opening a track with SAS Tech Support is your best option if you haven't already. Personally I go direct to them with tricky problems and I often have the answer in a day or two.

Re: Connecting to Databricks using JDBC from SAS Viya 4

thesasuser — Mon, 03 Mar 2025 17:16:18 GMT

Hello
I have been able to successfully connect using the following code

libname libdbrk jdbc preserve_tab_names=yes schema=catalog.schema 
class="cdata.jdbc.databricks.DatabricksDriver"
URL="jdbc:databricks:Server=abcd-efgh.cloud.databricks.com:443;HTTPPath=/sql/1.0/warehouses/1234567890;
Token=Personal_Access_token;"

While the library is successfully created, cannot see any tables.
I ran the proc datasets as follows

proc datasets lib=libdbrk;
run;

The log has

 proc datasets lib=libdbrk;
WARNING: Execute error: 
                                                             Directory
                       Libref         LIBDBRK                                                                
                       Engine         JDBC                                                                   
                       Physical Name  jdbc:cdata:Databricks:Server=abcd-efgh.cloud.databricks.com:443
                       Schema       catalog.schema                                      
WARNING: No matching members in directory.
81 run;

Looking to the community for guidance.
Thanks

Re: Connecting to Databricks using JDBC from SAS Viya 4

thesasuser — Mon, 03 Mar 2025 17:26:07 GMT

Hello
I executed the following code (solution from the forum post Solved: PROC DATASETS "WARNING: No matching members in directory." - SAS Support Communities )

data _null_;
	set sashelp.vtable(where=(libname='LIBDBRK' and MEMTYPE='DATA')) nobs=obs;
	if (obs > 0) then
	  rc = dosubl('proc datasets lib=libdbrk memtype=data kill NOLIST NOWARN;run;quit;');
run;

and this gave the following log

80   data _null_;
81   set sashelp.vtable(where=(libname='LIBDBRK' and MEMTYPE='DATA')) nobs=obs;
82   if (obs > 0) then
83     rc = dosubl('proc datasets lib=libdbrk memtype=data kill NOLIST NOWARN;run;quit;');
84   run;
NOTE: There were 0 observations read from the data set SASHELP.VTABLE.
      WHERE (libname='LIBDBRK') and (MEMTYPE='DATA');
NOTE: DATA statement used (Total process time):
      real time           1.59 seconds
      cpu time            0.03 seconds
      
85   
86   /* region: Generated postamble */
87   /* Close ODS destinations */
88   &graphterm; ;*';*";*/;run;quit;
89   quit;run;
90   ods html5 (id=web) close;
91   ods listing close;
92   %if %sysfunc(fileref(_gsfname)) lt 0 %then %do;
93       filename _gsfname clear;
NOTE: Fileref _GSFNAME has been deassigned.
94   %end;
95   %studio_capture_custom_output;
96   /* endregion */
97

Wondering if I need to make any changes to my libname statement as mentioned in the previous post?

Re: Connecting to Databricks using JDBC from SAS Viya 4

gwootton — Mon, 03 Mar 2025 21:36:27 GMT

Sounds like you're connecting successfully but don't have permission to see anything, or nothing exists.

You may want to add sastrace option to get additional detail on what's being sent.

Re: Connecting to Databricks using JDBC from SAS Viya 4

thesasuser — Wed, 05 Mar 2025 21:33:20 GMT

Thanks everyone for your help and support.
The issue was resolved with the help of SAS Tech Support. Thanks SAS Tech Support for the solution.
The issue was that I was using the jdbc engine whereas SAS Access Interface to Spark provides connectivity to Databricks. The details are given here SAS Help Center: Support for Spark on the SAS Viya Platform.
Look for the section on Databricks. One would need the details about server, httpPath and PersonalAccessToken among other things.