Introduction

This document describes how to connect to Databricks as a data source in Denodo Virtual DataPort.

Databricks is a cloud-based platform for data analytics and artificial intelligence. It is based on Apache Spark. In this document, we will see different options using a Databricks JDBC data source in Denodo to connect to a Databricks SQL Warehouse.

Denodo supports different authentication methods with Databricks: via the personal access token and via OAuth 2.0.

OAuth 2.0 is an authorization framework that underpins various types of authorization flows such as resource owner password flow (ROPC), client credentials, authorization code grant or implicit flow. For Databricks and OAuth 2.0 in Denodo we can use the Use OAuth authentication (Client credentials) and Use OAuth authentication (ROPC).

For this document we have used Azure Databricks, you can follow the same steps with AWS Databricks and GCP Databricks.

Connecting to Databricks from Denodo Using OAuth

NOTE: In this section we will explain how to connect to Databricks with the “Use Oauth Authentication” option. It is also possible to use the “Use pass-through session credentials” option and then select the “Requires OAuth” option so the credentials used to connect to the data source will be the ones of the user connected to Denodo. With this option when using Azure Entra as Identity Provider it is possible to pick the additional “On-Behal-Of flow” OAuth flow.

In this section, we will be using Microsoft Entra ID as the identity provider used for authentication. In the following figure you can see how Denodo will interact with the Databricks SQL Warehouse including seamless Unity Catalog Integration.

Considerations

• If you choose the distributed driver with Denodo, it is automatically configured with the “driver properties” required by the driver to use OAuth authentication.
• If you use another driver different from the default one, it is recommended to check the vendor’s documentation on the URI.
• You would want to check if the use case needs an OAuth M2M (Machine to Machine) or U2M (User to Machine) workflow. Please use this official documentation from the vendor.
• The M2M uses client credentials authentication flow, whereas U2M uses authorization code credentials authentication flow.
• Databricks recommends that you use managed identities authentication, if your target Databricks tool or SDK supports it, instead of OAuth M2M authentication. This is because managed identities authentication does not expose credentials.

• Consider creating a cluster in Databricks for this connectivity. If you are using an ROPC OAuth flow, you do not need to create a service-principal at Databricks.

The connectivity described in this section is following an ROPC authentication workflow to ask for a token to Microsoft Entra for Databricks. For this,

• From the Denodo Design Studio, create a new Databricks data source by selecting New > Data source.

• This will open the wizard to create a connection to a data source. Now, search for the data source that you want to connect to. Here, we are using Databricks.

• Provide the Name for the Databricks Data source.

For OAuth authentication, the below details will be required:

• Token endpoint: Gather the token endpoint from Microsoft Entra portal. 
• Client identifier and secret: Client identifier should be available under app registrations for Databricks. The secret is generated when you create the platform.
• User identifier and password: This is your AD user and password.
• Scope: 2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default openid offline_access, the default scope as defined in this documentation.

Connecting to Databricks from Denodo Using User/Password

NOTE: In this section we will explain how to connect to Databricks using a fixed username and password. It is also possible to use the “Use pass-through session credentials” option so the credentials used to connect to the data source will be the ones of the user connected to Denodo.

From the Denodo Design Studio, create a new Databricks data source by selecting New > Data source.

• This will open the wizard to create a connection to a data source. Now, search for the data source that you want to connect. Here, we are using Databricks.

• Provide the Name for the Databricks data source.

• By default, the respective Database version is selected based on the data source connection. In this example, Databricks Adapter is selected by default.

• Since Denodo 8.0 update 20220815, the default Driver classpath is databricks-2.
• Next, provide the  Database URI.  
  Format : ‘jdbc:databricks://<host_name>:<port>/<database_name>;’, also, some parameters are added to the URI after the database name like httpPath, SSL etc. To know more about the parameters refer to Configure the Databricks ODBC and JDBC drivers.

• If you are using a Databricks driver version < 2.6.25, you may need to change the Driver class to ‘com.simba.spark.jdbc.Driver’ and the Database URI to ‘jdbc:spark://<host_name>:<port>/<database_name>;’
• You can select what type of Transaction Isolation you want from the options provided. In this example, we use the ‘Database Default’.
•   Authentication: There are three options provided. In our example, we are using the ‘Use login and password’ using a personal access token.
•   In the User, enter ‘token’.
•   In the Password, provide the access key token.
•  After providing the connection, you can test the connection by clicking on the Test     Connection button.
• Save the data source.
• Once saved, click on the ‘Create base view’ tab to introspect source metadata available through the Data Source.
• To incorporate some of the tables into the Denodo virtual schema, you have to check the box near the tables or views you want to import and then click ‘Create selected’.
  
  

• Now, the base view will be created.

• You can also use the ‘Create from query’ option if you wish to custom your base view.

Troubleshooting

Access token not valid

o   Error: Invalid Access token

o   Use the OAuth Credentials wizard from Virtual DataPort Administration tool to accomplish the ROPC. If that works, test the connectivity again from Databricks data source.

o   Clarifying if it is the Cloud Databricks in utilization. Cloud Databricks doesn't support ROPC flow for OAuth 2.0.

Communication link failure: Failed to connect to server

o   Error: Authorization related error response returned, but no token expired.

o   This is an error triggered because of a configuration issue at Databricks end. Check the OAuth connectivity with Databricks using postman request. It should return 200 ok to proceed.

o   Check if there is a service principal created for the user at Databricks end.

o   Delete /recreate the user with Azure AD email at Databrick’s Identity and access management.

Error retrieving data from HTTP error code: 400.

o   Error: com.denodo.parser.connection.ConnectionException: Error retrieving data from 'https://accounts.cloud.databricks.com/oidc/accounts/<tenant_id>/v1/token'. HTTP error code: 400.

o   Elevating the log levels for httpclient.wire.

o   Recreate the platform at Microsoft Entra and check with newly created client id and secret.

o   Obtaining the VQL and checking the USE_OAUTH clause.

o   Verifying if the issue persists with another version of driver.

References

JDBC Sources

Supported JDBC Data Sources

Configure JDBC/ODBC connection