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

thesasuser — Tue, 30 Jul 2024 13:49:28 GMT

I am trying to establish a libname connection to Databricks in SAS Studio on SAS Viya 4 POC on AWS. Getting error.
Requesting assistance from the community.
My code and error messages are reproduced below

libname brklib jdbc classpath="/opt/sas/viya/home/lib64/accessclients/jdbc"
class="cdata.jdbc.databricks.DatabricksDriver"
URL="jdbc:databricks://servername:443;httpPath='/sql/1.0/warehouses/xxxxxx';ssl=1;AuthMech=3;UID=user@example.com;PWD=xxxxxxxxxx";

ERROR: Error trying to establish connection: java.sql.SQLClientInfoException: 'useragententry' is not a valid connection property.
ERROR: Error in the LIBNAME statement.

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

Mitesh73941 — Wed, 14 Aug 2024 12:28:31 GMT

It looks like you're encountering an issue with the libname statement in SAS when trying to connect to Databricks using JDBC. The error message indicates that there's a problem with one of the connection properties.

Here are a few things you can check and adjust to resolve the issue:

1. JDBC Driver and Classpath

Make sure you have the correct JDBC driver for Databricks and that the path to the JDBC driver in your classpath is accurate. Verify that the JDBC driver file (.jar) is located in the specified directory.

2. Connection Properties

The error message suggests that 'useragententry' might be an invalid property. Double-check the connection properties used in the URL. For Databricks, the properties should be correctly formatted and valid for the driver being used.

3. Correct URL Format

Ensure that your JDBC URL is properly formatted. Based on Databricks documentation, your URL should look something like this:

jdbc:databricks://<hostname>:<port>/;transportMode=http;ssl=1;AuthMech=3;UID=<username>;PWD=<password>;httpPath=<httpPath>;

In your case, it should be:

jdbc:databricks://servername:443;httpPath=/sql/1.0/warehouses/xxxxxx;ssl=1;AuthMech=3;UID=user@example.com;PWD=xxxxxxxxxx;

4. Check for Valid Properties

Make sure that all properties in the connection string are valid. According to Databricks JDBC documentation:

httpPath should not have single quotes around it.
Ensure ssl is set to 1 if using SSL (which is typically required).
AuthMech=3 is correct for username/password authentication.

5. SAS and JDBC Driver Compatibility

Ensure that the JDBC driver version you're using is compatible with SAS Viya 4 and Databricks. Sometimes issues arise from version mismatches.

6. Debugging Steps

Test the JDBC Connection Independently: Use a simple Java program or a JDBC client to test the connection outside of SAS to ensure that the driver and URL work.
Check SAS Logs: Look for more detailed error messages in the SAS log which might give more context to the problem.
Consult Documentation: Refer to both SAS and Databricks documentation for any specific configurations or known issues related to JDBC connections.

Example Updated LIBNAME Statement

Here's how your updated libname statement might look:

libname brklib jdbc classpath="/opt/sas/viya/home/lib64/accessclients/jdbc"
class="cdata.jdbc.databricks.DatabricksDriver"
URL="jdbc:databricks://servername:443;httpPath=/sql/1.0/warehouses/xxxxxx;ssl=1;AuthMech=3;UID=user@example.com;PWD=xxxxxxxxxx";

Ensure that you replace servername, xxxxxx, user@example.com, and xxxxxxxxxx with your actual Databricks server name, warehouse ID, user credentials, and password.

By following these steps, you should be able to troubleshoot and resolve the connection issue. If problems persist, post the complete logs.

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

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

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