Skip to main content

Connect to Databricks SQL

Connect Databricks SQL to Retool and query data in your Databricks SQL warehouses.

Databricks Lakehouse SQL is the data warehousing service of the Databricks Lakehouse platform. It runs SQL queries against your Databricks data through SQL warehouses and enforces access controls with Unity Catalog.

What you can do with Databricks SQL in Retool​

After you create a Databricks SQL resource in Retool, you can:

  • Read and display data from your Databricks catalogs and schemas with SQL queries.
  • Build dashboards and reporting apps on top of your lakehouse data.
  • Automate data workflows that run against a SQL warehouse.
  • Authenticate individual users with OAuth so each person only sees the data they have access to in Databricks.

Before you begin​

To connect Databricks SQL to Retool, you need the following:

  • SQL warehouse: A running Databricks SQL warehouse. Find its server hostname and HTTP path on the warehouse's Connection details tab, as described in the Databricks documentation.
  • Credentials: A Databricks personal access token, or an OAuth app connection if you plan to use OAuth 2.0.
  • Network access: Your Databricks workspace must accept connections from Retool's IP addresses.
  • Retool permissions: Ability to create and manage resources in your organization.

Create a Databricks SQL resource​

Follow these steps to create a Databricks SQL resource in your Retool organization.

Create a new resource​

In your Retool organization, navigate to Resources in the main navigation and click Create new → Resource. Search for "Databricks SQL" and click the Databricks SQL tile to begin configuration. For more information about creating resources, refer to Create a resource.

Best practice

Use folders to organize your resources by team, environment, or resource type. This helps keep your resource list manageable as your organization grows.

Configure general settings​

Specify a name and description for the resource that indicates which warehouse or data it connects to. The description provides more context to users about how to use the resource.

Example nameExample description
Sales lakehouseA Databricks SQL warehouse with curated sales and revenue tables.
Product analytics (read only)A read-only Databricks SQL warehouse used for product usage reporting.

Configure resource credentials​

Configure the connection settings for your Databricks SQL resource.

Databricks SQL connection settings.

Best practice

You can autofill the configuration fields using a Databricks JDBC connection string. Click Autofill using connection string and paste a string in the following format.

Connection string example
jdbc:databricks://dbc-a1b2c3d4-e5f6.cloud.databricks.com:443/default;httpPath=/sql/1.0/warehouses/1a2b3c4d5e6f7890

Host​

The workspace URL of your Databricks instance, without https://.

Example
dbc-a1b2c3d4-e5f6.cloud.databricks.com

Port​

The port to connect on. The default is 443.

HTTP path​

The HTTP path of your SQL warehouse, shown on the warehouse's Connection details tab in Databricks.

Example
/sql/1.0/warehouses/1a2b3c4d5e6f7890

Default catalog​

The catalog to use when a query doesn't specify one. If you leave this empty, Retool uses the default catalog for your Databricks workspace.

Example
hive_metastore

Default schema​

The schema to use when a query doesn't specify one.

Example
default

Connection options​

Key-value pairs that customize the Databricks connection. Click + to add an option. Refer to the Databricks JDBC driver documentation for the available options.

Use SSL/TLS​

Enable this option to encrypt the connection to Databricks. Retool strongly recommends using SSL/TLS whenever it's available, because it protects data in transit and prevents attackers from using invalid server certificates to gain access to your data.

Configure authentication​

Databricks SQL supports API key and OAuth 2.0 authentication. Select a method from the Authentication dropdown.

Authentication methodUse cases
API Key AuthShared access where every Retool user queries Databricks with one set of permissions, and scheduled or automated queries that run without user interaction.
OAuth 2.0 with authorization code grant and PKCEPer-user access where each person signs in to Databricks and can only query the data they have permission to access. Retool uses Databricks Unity Catalog to enforce these access controls.
OAuth 2.0 with client credentialsAccess as a Databricks service principal instead of an individual user, with the permissions granted to that service principal.
Option A: API key

Paste your Databricks personal access token into the API Key field. Retool encrypts the value when you save the resource.

Example
dapi1a2b3c4d5e6f7890abcdef1234567890
Option B: OAuth 2.0 with authorization code grant and PKCE

Use this flow when each user signs in to Databricks with their own account. It's the default OAuth flow for Databricks SQL resources.

Databricks SQL OAuth 2.0 settings.

  1. In Databricks, create a custom OAuth app connection. Refer to the Databricks documentation to choose the redirect URL, scopes, and client secret settings for your app. Retool shows the OAuth callback URL it uses in the resource form.
  2. In Retool, set Authentication to OAuth 2.0, then select Authorization Code Grant with PKCE from the flow dropdown below it.
  3. Confirm Authorization URL and Access Token URL use your Databricks workspace host. Retool fills them in from the Host field, and shows a {databricksHost} placeholder if Host is empty.
  4. Enter the Client ID from your Databricks app connection.
  5. Set Scopes to match the scopes configured for your Databricks app connection.
  6. Optionally add Token request headers, and set Access token lifespan under Advanced.
  7. Click Connect with OAuth and sign in to Databricks to authorize Retool.
Authorization URL example
https://dbc-a1b2c3d4-e5f6.cloud.databricks.com/oidc/v1/authorize
Access Token URL example
https://dbc-a1b2c3d4-e5f6.cloud.databricks.com/oidc/v1/token
Option C: OAuth 2.0 with client credentials

Use this flow when Retool should authenticate as a service principal instead of as an individual user.

  1. In Databricks, create a service principal and an OAuth secret for it. Refer to the Databricks documentation for the steps, and note the Client ID and Client Secret.
  2. In Retool, set Authentication to OAuth 2.0, then select Client Credentials from the flow dropdown below it.
  3. Confirm Access Token URL uses your Databricks workspace host. Retool fills it in from the Host field, and shows a {databricksHost} placeholder if Host is empty.
  4. Enter the Client ID and Client Secret.
  5. Set Scopes to the scopes you want the service principal to request. Refer to the Databricks documentation for the available scopes.
  6. Optionally add Token request headers, and set Access token lifespan under Advanced.

Configure advanced options​

Configure optional settings available under the Advanced options section of the resource configuration form.

Disable converting queries to prepared statements​

Retool converts queries to prepared statements by default to improve performance and prevent SQL injection. Enable this option only if your use case requires disabling that behavior.

Outbound region​

If your organization uses outbound regions, select the region that should be used for requests to Databricks SQL. This controls which geographic region your requests originate from.

Test the connection​

Click Test connection to verify Retool can connect to Databricks SQL. If the test fails, check the following:

  • Network access: Ensure your Databricks workspace accepts connections from Retool's IP addresses (cloud) or your Retool instance's network (self-hosted).
  • Warehouse state: Confirm the SQL warehouse is running and that the HTTP path points to it.
  • Host format: Enter the workspace host without https://.
  • Credentials: Verify the token or OAuth credentials are valid, and that the user or service principal has access to the SQL warehouse in Databricks.
  • Java DB Connector: For self-hosted instances, confirm the Java DB Connector is enabled.
Use Debug Tools for detailed connection information

After testing the connection, click View in console to open the Debug Tools console. The console displays the test query executed, its response, execution time, and error details if the test fails. This information is helpful for troubleshooting connection issues.

Save the resource​

Click Create resource to save your Databricks SQL resource. You can now use it in queries across your Retool apps and automations.

Interact with Databricks SQL data​

Once you've created a Databricks SQL resource, you can interact with Databricks data using Retool.

Switch to the new app builder

Retool recommends using the new app builder so you can use natural language to build React-based apps using AI.

  • To use Databricks SQL data in the app builder, reference the resource in the prompt with @, such as @Sales lakehouse. Refer to the data and functions documentation to learn more.
  • For classic apps, workflows, and agents, refer to the SQL queries documentation to learn how to write a resource query.

Best practices​

Follow these best practices to maintain the security of your Databricks SQL resource.

  • Apply least privilege: Grant the token, user, or service principal only the minimum permissions needed in Unity Catalog. Use read-only credentials for resources used only for reading data.
  • Prefer OAuth for user-specific access: Use OAuth 2.0 when different users need different data access, so Unity Catalog permissions apply to each person instead of one shared credential.
  • Use short-lived credentials: Set an expiration on personal access tokens and rotate them regularly.
  • Use resource environments: Organizations on an Enterprise plan can configure multiple resource environments to maintain separate warehouses or credentials for production, staging, and development.