Databricks connection patterns in Workbench using Workbench Managed Credentials

These access patterns enable you to connect to and read from Databricks databases or schemas in Posit Workbench using Workbench Managed Credentials. They include both Python and R code examples for connecting to Databricks using Spark and SQL.

Choose a different pattern by referring to the table in Choose a connection for Databricks.

Not sure which pattern to use?

For an overview of credential types and scope in different environments, see Credential types and Managing data access from development to deployment.

Overview

Administrator configuration required

Your Workbench Administrator must configure Workbench managed credentials for Databricks in Workbench. See the Workbench Managed Credentials section of the Data Sources admin guide for details.

When authenticating with Workbench managed credentials, your Databricks user credentials are available in your session via the workbench profile specified in the Databricks config file. Workbench creates and manages this configuration profile file for you. See the Managed Credentials Overview in the Workbench User Guide for more information.

You will need to provide:

For Spark connections, the cluster ID
For SQL warehouse connections, the HTTP path

You can store these as environment variables such as DATABRICKS_CLUSTER_ID or DATABRICKS_HTTP_PATH, or explicitly specify them in your code.

Spark cluster connection

Spark connection prerequisites

Install the required Python packages for Databricks Spark connections:

databricks-connect - The primary library that enables remote connections to Databricks clusters from your local environment. This package must match your Databricks runtime (DBR) version (see Install the Databricks Connect client for version compatibility details).
databricks-sdk - The Databricks SDK for Python, which provides authentication and configuration management for connecting to Databricks services.

uv add \
  databricks-sdk \
  "databricks-connect==16.4.*"  # Or X.Y.* to match your cluster version.

pip install \
  databricks-sdk \
  "databricks-connect==16.4.*"  # Or X.Y.* to match your cluster version.

Think ahead for deploying your code to Connect. If you intend to use Connect Viewer or service account OAuth authentication patterns, you will also need the posit-sdk package:

uv add posit-sdk

pip install posit-sdk

Install the required R packages for Databricks Spark connections:

sparklyr - An R interface to Spark that provides dplyr-compatible syntax for working with Spark DataFrames and integrates with Databricks Connect.
pysparklyr - Manages the Python environment and databricks-connect package automatically for sparklyr, ensuring version compatibility with your Databricks cluster.

install.packages(c("sparklyr", "pysparklyr"))

For a thorough description of sparklyr and connections to Databricks, see the Getting Started documentation for sparklyr with Databricks.

Think ahead for deploying your code to Connect. If you intend to use Connect Viewer or Service Account OAuth authentication patterns, you’ll also need the connectcreds package:

install.packages("connectcreds")

Spark connection patterns

Tip

Use the Databricks extension in VS Code or Positron to interact with your Databricks environment.

.env

DATABRICKS_CLUSTER_ID=<your cluster ID>

from databricks.connect import DatabricksSession
from databricks.sdk.core import Config
import pandas as pd
import os

config = Config(
    profile="workbench",
    cluster_id=os.getenv("DATABRICKS_CLUSTER_ID"),
)

spark = (
  DatabricksSession
  .builder
  .sdkConfig(config)
  .getOrCreate()
)

# Read a table located at "my_catalog.my_schema.my_table"
df = spark.read.table("samples.nyctaxi.trips")
df.show(5)

# Convert the Spark table to a Pandas DataFrame
pandas_df = df.limit(5).toPandas()  # Limit to 5 rows for efficiency

# Display the Pandas DataFrame
print(pandas_df)

Tip

Use the Databricks pane in RStudio Pro to view, start, stop, and connect to your clusters. The spark_connect snippet can be automatically generated from a button in the Databricks pane.

.Renviron

DATABRICKS_CLUSTER_ID=<your cluster ID>

library(sparklyr)
library(pysparklyr)
library(dplyr)
library(dbplyr)

# Version note: requires `pysparklyr` >= 0.1.8

sc <- spark_connect(
    cluster_id = Sys.getenv("DATABRICKS_CLUSTER_ID"),
    method = "databricks_connect"
)

# Interact with the Spark table at "my_catalog.my_schema.my_table" as a dplyr data frame
data <- tbl(sc, dbplyr::in_catalog("samples", "nyctaxi", "trips"))
head(data)

# Use Collect to bring the first 100 rows in memory
results <- data |> head(100) |> collect()
results

SQL warehouse connection

SQL connection prerequisites

The Databricks SQL Connector Python library allows you to run SQL commands against your Databricks SQL warehouse or Databricks clusters. The connector integrates directly with pandas using pd.read_sql() to execute queries and load results into DataFrames.

You can also use the SQLAlchemy dialect for Databricks, which provides a SQL toolkit/ORM interface. pandas and other popular libraries can directly consume SQLAlchemy’s engine.

The connection patterns below demonstrate both approaches.

Install the required Python packages:

uv add \
  databricks-sdk \
  databricks-sql-connector \
  databricks-sqlalchemy  # Optional, only needed for SQLAlchemy approach

pip install \
  databricks-sdk \
  databricks-sql-connector \
  databricks-sqlalchemy  # Optional, only needed for SQLAlchemy approach

Think ahead for deploying your code to Connect. If you intend to use Connect Viewer or service account OAuth authentication patterns, you will also need the posit-sdk package:

uv add posit-sdk

pip install posit-sdk

The best method for creating a database connection to a Databricks SQL warehouse is to use the odbc package and the odbc::databricks() function. The odbc package uses an ODBC driver installed on your system to create the connection.

Administrator configuration required

An administrator must install and configure database drivers on the server before you can use them in your code. See the Data sources admin guide for more information.

Install the odbc package:

install.packages("odbc")

Think ahead for deploying your code to Connect. If you intend to use Connect Viewer or service account OAuth authentication patterns, you will also need the connectcreds package:

install.packages("connectcreds")

SQL connection patterns

The Databricks SDK for Python manages authentication using Workbench-managed credentials via the profile="workbench" configuration. You can use this authentication with either the Databricks SQL Connector or SQLAlchemy.

.env

DATABRICKS_HTTP_PATH=<your HTTP path>

The http_path can be found in the Databricks UI under ⁠⁠Advanced Options > JDBC/ODBC⁠. It will look something like this: /sql/1.0/warehouses/xxxxxxxxxx.

requirements.txt

pandas
databricks-sdk
databricks-sql-connector
python-dotenv

import os

import pandas as pd
from databricks import sql
from databricks.sdk.core import Config

config = Config(profile="workbench")

con = sql.connect(
    server_hostname=config.hostname,
    http_path=os.getenv("DATABRICKS_HTTP_PATH"),
    access_token=config.token,
)

# Sample SQL query for a table at "my_catalog.my_schema.my_table"
query = "SELECT * FROM samples.nyctaxi.trips LIMIT 10"

df = pd.read_sql(query, con)

print(df)

requirements.txt

pandas
databricks-sdk
databricks-sqlalchemy
python-dotenv

import os

import pandas as pd
from databricks.sdk.core import Config
from sqlalchemy import create_engine

config = Config(profile="workbench")

http_path = os.getenv("DATABRICKS_HTTP_PATH")
engine = create_engine(
    url=f"databricks://token:{config.token}@{config.hostname}?http_path={http_path}"
)

# Sample SQL query for a table at "my_catalog.my_schema.my_table"
query = "SELECT * FROM samples.nyctaxi.trips LIMIT 10"

df = pd.read_sql(query, engine)
print(df)

.Renviron

DATABRICKS_HTTP_PATH=<your HTTP path>

The http_path can be found in the Databricks UI under ⁠Advanced Options > JDBC/ODBC⁠. It will look something like this: /sql/1.0/warehouses/xxxxxxxxxx.

library(dplyr)
library(dbplyr)

con <- DBI::dbConnect(
  odbc::databricks(),
  httpPath = Sys.getenv("DATABRICKS_HTTP_PATH")
)

# Use dplyr to query data at "my_catalog.my_schema.my_table"
data <- tbl(con, DBI::Id("samples", "nyctaxi", "trips"))
head(data)

# Use Collect to bring the first 100 rows in memory
results <- data |> head(100) |> collect()
results