Connecting to Databricks using JDBC from SAS Viya 4

[thesasuser]

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?

[SASKiwi]

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.

[thesasuser]

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

[SASKiwi]

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.

[gwootton]

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.

[thesasuser]

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.

[gwootton]

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.

[thesasuser]

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

[gwootton]

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?

[thesasuser]

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

[gwootton]

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

[thesasuser]

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

[SASKiwi]

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.

[thesasuser]

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

[thesasuser]

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?