Creating a connection to Databricks
Databricks provides a managed service for data processing and transformation on a data lake. It uses Delta Lake, an open-source solution developed by Databricks, which enables the creation, management, and processing of data using the Lakehouse architecture.
You can set up the required credentials and configurations for using Databricks with Data Integration.
When establishing a connection, you can use the Databricks Partner Connect guide. A fully functional Databricks connection is automatically generated in Data Integration.
Authentication methods
Choose one of the following authentication methods when you create the connection.
| Method | Best for | How it works |
|---|---|---|
| Personal access token (PAT) | Quick setup and individual use | Token tied to an individual Databricks user. |
| OAuth2 (User) | Users who sign in interactively | Sign in with the Connect with Databricks popup. |
| OAuth2 (Service Principal) | Automated or unattended pipelines, and organizations that require service principals | Data Integration authenticates as a Databricks service principal by using a client ID and client secret. No sign-in popup or redirect URL is required. |
Existing PAT and OAuth2 (User) connections are unchanged and don't require migration. Switching an existing connection to OAuth2 (Service Principal) is optional.
Prerequisites
A valid Databricks Admin Account and Workspace.
Creating a SQL warehouse
To use Databricks as a target, you must perform operations on the existing SQL Warehouse.
Procedure
- Login into your Databricks workspace.
- Go to SQL console.
- Click SQL Warehouse and then Create SQL Warehouse at the top right corner.
- In the modal opened for new SQL endpoint detail, Name your endpoint (for example, RiverySQLEndpoint), choose the right Cluster Size, and set the Auto Stop to at least 120 minutes of no activity. Click Create.
Configuring data access for the SQL warehouse
- Navigate to the Admin Settings.
- Click the SQL Warehouse Settings tab.
- If you are using an Instance Profile, select the specific one that you wish to use.
- Copy and paste the following configurations for Data Access Configuration:
spark.hadoop.fs.s3a.impl shaded.databricks.org.apache.hadoop.fs.s3a.S3AFileSystem
spark.hadoop.fs.s3n.impl shaded.databricks.org.apache.hadoop.fs.s3a.S3AFileSystem
spark.hadoop.fs.s3n.impl.disable.cache true
spark.hadoop.fs.s3.impl.disable.cache true
spark.hadoop.fs.s3a.impl.disable.cache true
spark.hadoop.fs.s3.impl shaded.databricks.org.apache.hadoop.fs.s3a.S3AFileSystem
- Click Save Changes.
Data Integration supports SQL warehouses with ANSI mode on or off and does not change your ANSI mode setting. For details on how each mode affects data loads, refer to ANSI mode.
Get the SQL warehouse credentials
To ensure compatibility with Data Integration, credentials are necessary for each SQL Warehouse configured within the Databricks console. In Data Integration, each connection typically corresponds to a single SQL Warehouse.
Procedure
- Go to the SQL Warehouses section and select the warehouse that was recently created.
- Access the Connection Details tab and copy the following parameters:
- Server Hostname
- Port
- HTTP Path
Creating a new personal access token
To establish a connection, you must generate a Personal Access Token associated with the user.
Procedure
-
Navigate to User Settings.
-
Click the Access Tokens tab and select Generate New Token.
-
Within the opened modal, provide a name for your token (such as Data Integration) and adjust the lifetime to a duration that guarantees consistent and dependable feature. In the given instance, we have established an lifetime of 1825 days (equal to 5 years).
-
If you are using a Personal Access Token, authenticate your Databricks connection with OAuth 2.0. During the configuration in Databricks, provide the Redirect URL that corresponds to your region:
Region Redirect URL US https://console.rivery.io/api/oauthcallback/databricksEU (Ireland) https://eu-west-1.console.rivery.io/api/oauthcallback/databricksIL (Israel) https://il-central-1.console.rivery.io/api/oauthcallback/databricksAU (Australia) https://ap-southeast-2.console.rivery.io/api/oauthcallback/databricksFor detailed steps on setting up OAuth in Databricks, refer to the Enable custom OAuth applications using the Databricks topic.
Creating a service principal (OAuth2 Service Principal only)
Complete these steps in Databricks. For detailed steps, see Authorize service principal access to Databricks with OAuth (M2M).
Procedure
- Create a service principal in your Databricks account.
- Generate an OAuth secret for the service principal. This provides a Client ID and a Client Secret. Databricks shows the secret only once, so store it securely, as you would a password.
- Assign the service principal to the target workspace.
- Grant the service principal permission to use the target SQL warehouse.
- Grant the Unity Catalog permissions (catalog, schema, and table) required for the operations that Data Integration runs.
A valid token doesn't grant access to SQL warehouses, catalogs, schemas, or tables. Authentication can succeed while a run still fails because of missing permissions.
Configuring an OAuth2 (User) connection in Data Integration
-
Go to Connections, and click + New Connection.
-
Select Databricks.
-
Enter the following details:
- Connection Name
- Server Hostname from your Databricks SQL Warehouse settings.
- Set the Port.
- HTTP Path from your SQL Warehouse details.
- Authentication Type: Select OAuth2 (User).
- Client ID from Databricks.
- Client Secret from Databricks.
- (Optional) You can also specify a Default Catalog and Default Schema to control where data loads within Databricks.
-
Enable the Custom File Zone toggle if required.
-
Click Connect with Databricks. If the connection is successful, you can view Connected with Databricks and Test Connection Passed! Otherwise, an error message appears.
Configuring an OAuth2 (Service Principal) connection in Data Integration
- Go to Connections, and click + New Connection.
- Select Databricks.
- Enter the following details:
- Connection Name
- Server Hostname from your Databricks SQL warehouse settings.
- Port
- HTTP Path from your SQL warehouse details.
- Authentication Type: Select OAuth2 (Service Principal).
- Client ID of the service principal.
- Client Secret of the service principal.
- (Optional) Default Catalog and Default Schema to control where data loads within Databricks.
- Enable the Custom File Zone toggle if required.
- Click Test Connection to confirm that the connection works. No sign-in popup or redirect URL is involved.
- Click Save.
| Field | Description | Source in Databricks |
|---|---|---|
| Client ID | The client ID of the service principal. | Shown when you generate the OAuth secret. |
| Client Secret | The OAuth secret of the service principal. | Shown once when you generate the OAuth secret. |
You can use an OAuth2 (Service Principal) connection for connection tests, loads from sources into Databricks, and Databricks logic steps. Authentication renews automatically, so long-running processes keep working without any action from you. You don't need to re-authorize or reconnect.
Troubleshooting OAuth2 (Service Principal) connections
Data Integration displays the error message from Databricks. Use the following table to resolve common errors.
| Error message or symptom | Likely cause | Fix |
|---|---|---|
| Invalid client credentials | The client ID or client secret is incorrect, or the secret expired or was revoked. | Check the values, or generate a new OAuth secret in Databricks. |
| Service principal not authorized | The service principal isn't assigned to the workspace. | Assign the service principal to the workspace. |
| Authentication succeeds, but the run fails on a warehouse, catalog, schema, or table | The service principal lacks SQL warehouse or Unity Catalog permissions. | Grant the required permissions to the service principal. |
oauth application with client_id ... not available in databricks account | The service principal client ID was entered under OAuth2 (User). | Change Authentication Type to OAuth2 (Service Principal). |
Configuring Databricks for Data Integration IP address access (optional)
If your Databricks workspace has IP address restrictions, open the Data Integration IP addresses to ensure the successful execution of any operations from Data Integration.
To open the Data Integration IP addresses, follow these steps:
- To access the full range of IP address access list operations in Databricks, refer to the Databricks - IP address access lists.
- Allowlist our IP addresses.
- Submit the following
POSTrequest to the API of your Databricks workspace:
curl -X POST -n \
https://`<databricks-instance>`/api/2.0/ip-access-lists
-d '{
"label": "{conKeyRefs.DataIntegration}",
"list_type": "ALLOW",
"ip_addresses": [
"52.14.86.20/32",
"13.58.140.165/32",
"52.14.192.86/32",
"34.254.56.182/32"
]
}'
Custom file zone
A Custom File Zone is a data storage setup enabling organizations to manage and store their data flexibly. Data Integration out-of-the-box Managed File Zone provides this feature effortlessly, while the Custom File Zone provides organizations with greater authority over the specifics of data storage, although it requires setup.
For more details on the setup process, refer to the Custom File Zone topic.