topic Re: Databricks connection error in viya 4 in SAS Studio

Databricks connection error in viya 4

Chethandeepak — Fri, 11 Oct 2024 07:44:27 GMT

Hi,

We are getting issue while connection databricks in viya4 , connection is establishing successfully with cas lib but while using following libname we are getting system error trust store. Please help me out to resolve this.

ERROR: Error trying to establish connection: System error: problem accessing trust store
ERROR: Error in the LIBNAME statement.

libname statement
libname spkdb spark
user='token'
password='xxxxxxxxxxxxxx'
platform='databricks'
server='xxxxxxxxxxxxxxxxxxxxxxxxxxx'
database='xxxxx'
httppath='/sql/1.0/warehouses/xxxxxxx'
properties='QueryPassthrough=true';'DefaultColumnSize=1024';'ssl=true';

Thanks in advance.

Re: Databricks connection error in viya 4

Patrick — Fri, 11 Oct 2024 07:51:48 GMT

Just as an observation: Wouldn't below semicolons not need to be within the single quotes so that the first one doesn't terminate the SAS libname statement?

Re: Databricks connection error in viya 4

Chethandeepak — Fri, 11 Oct 2024 09:41:59 GMT

Hi ,

Tried using correct syntax but still getting same error

Thanks!

Re: Databricks connection error in viya 4

Patrick — Fri, 11 Oct 2024 10:05:33 GMT

I've never done it so don't really know. But just based on the documentation found here it looks to me that at least some of the connection info needs to be passed via parameter URL.

Connecting to Databricks in Microsoft Azure
This example accesses Spark data in Databricks on Microsoft Azure. The Databricks JDBC driver is recommended for accessing Spark data in Databricks.

libname spkdbk spark classpath='/JAR-path'
   driverclass="com.databricks.client.jdbc.Driver"
   bulkload=no
   url=' jdbc:spark://server:port/schema;transportMode=http;ssl=1;HTTPPath=myHttpPath;
   AuthMech=3;defaultStringColumnLength=255;useNativeQuery=1;<options>'
   user=token
   password=mytoken
 ;

Re: Databricks connection error in viya 4

Chethandeepak — Fri, 11 Oct 2024 10:36:57 GMT

Hi,

I have tried suggested libname but it is for azure and im trying in aws databricks how tried with libname getting

ERROR: Error trying to establish connection: java.lang.ClassNotFoundException: com.databricks.client.jdbc.Driver

Re: Databricks connection error in viya 4

Patrick — Sat, 12 Oct 2024 23:52:02 GMT

This error looks to me like it can't find the JDBC driver.

I've never tried this myself so can't really be of further help. If you don't get sufficient answers here in the forum then contact SAS TechSupport directly.

Below what Copilot suggested - which might or might not be correct.

To connect SAS to Databricks on AWS using the LIBNAME statement, you can use the JDBC interface. Here’s an example of how you might set this up:

libname mydblib jdbc 
    classpath="/path/to/jdbc/driver" 
    class="cdata.jdbc.databricks.DatabricksDriver" 
    URL="jdbc:databricks://<hostname>:443;httpPath='/sql/1.0/warehouses/<warehouse_id>';ssl=1;AuthMech=3;UID=<your_username>;PWD=<your_password>";

Replace the placeholders with your actual Databricks server details:

<hostname>: Your Databricks server name.
<warehouse_id>: The ID of your Databricks SQL warehouse.
<your_username>: Your Databricks username.
<your_password>: Your Databricks password.

Make sure the JDBC driver is correctly installed and the classpath points to the location of the driver.

If you encounter any issues, double-check the connection properties and ensure they are correctly formatted. For example, the httpPath should not have single quotes around it, and ssl should be set to 1 if using SSL.

Establishing a libname connection to Databricks from SAS Viya 4 on AWS

How to Speed Up Data Flow Between Databricks and SAS