Databricks connection patterns in Workbench using M2M OAuth authentication

These access patterns enable you to connect to and read from Databricks databases or schemas in Posit Workbench and Posit Connect using Machine-to-Machine (M2M) OAuth Authentication. 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 Databricks workspace administrator must create a service principal and generate secrets for your use. Request the client ID and client secret to configure this authentication approach. The administrator must also grant the service principal appropriate permissions to access Databricks clusters and SQL warehouses.

For more details, see Authenticate access to Databricks with a service principal using OAuth (OAuth M2M) and the Databricks admin guide.

M2M OAuth authentication provides secure access to Databricks using OAuth tokens and service principals. Service principals are established at the workspace level and, unlike user credentials, are appropriate for one-to-many authentication scenarios. This method offers enhanced security compared to long-lived Personal Access Tokens (PATs) and works in both development and deployment environments.

Define environment variables for the service principal credentials using an .env or .Renviron file, for example, and refer to them in your code. Never hard-code credentials directly in your code.

You must include the following environment variables for authentication:

DATABRICKS_HOST
DATABRICKS_CLIENT_ID
DATABRICKS_CLIENT_SECRET

You will also 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

This connection method will be supported in a future release of sparklyr.

Spark connection patterns

Use Databricks Connect to create a DatabricksSession. With M2M authentication, Databricks Connect will use the service principal’s Client ID and Client Secret to authenticate the connection.

Tip

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

.env

DATABRICKS_HOST=<your Databricks host>
DATABRICKS_CLIENT_ID=<your client ID>
DATABRICKS_CLIENT_SECRET=<your client secret>
DATABRICKS_CLUSTER_ID=<your cluster ID>

requirements.txt

pandas
databricks-sdk
databricks-connect==16.4.*  # Or X.Y.* to match your cluster version
python-dotenv

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

load_dotenv()

config = Config(
    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)

This connection method will be supported in a future release of sparklyr.

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 provides the oauth_service_principal function, which handles the OAuth flow to retrieve an OAuth token using the client ID and client secret. You can use this token with either the Databricks SQL Connector or SQLAlchemy.

.env

DATABRICKS_HOST=<your Databricks host>
DATABRICKS_CLIENT_ID=<your client ID>
DATABRICKS_CLIENT_SECRET=<your client secret>
DATABRICKS_HTTP_PATH=<your HTTP path>

Find the http_path 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, oauth_service_principal
from dotenv import load_dotenv

load_dotenv()

config = Config(
    host=os.environ["DATABRICKS_HOST"],
    client_id=os.environ["DATABRICKS_CLIENT_ID"],
    client_secret=os.environ["DATABRICKS_CLIENT_SECRET"],
)


def credential_provider():
    return oauth_service_principal(config)


con = sql.connect(
    server_hostname=config.host,
    http_path=os.environ["DATABRICKS_HTTP_PATH"],
    credentials_provider=credential_provider,
)

# 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

from databricks.sdk.core import Config, oauth_service_principal
from sqlalchemy import create_engine
import pandas as pd
from dotenv import load_dotenv
import os

load_dotenv()

config = Config(
    host          = os.environ["DATABRICKS_HOST"],
    client_id     = os.environ["DATABRICKS_CLIENT_ID"],
    client_secret = os.environ["DATABRICKS_CLIENT_SECRET"])

def credential_provider():
  return oauth_service_principal(config)

# Get OAuth token
token = credential_provider()

# Create SQLAlchemy engine with OAuth token
http_path = os.getenv("DATABRICKS_HTTP_PATH", "<your http path>")
engine = create_engine(
    f"databricks://token:{token.access_token}@{config.host}",
    connect_args={
        "http_path": http_path,
        "_user_agent_entry": "PySQL"
    }
)

# 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_HOST=<your Databricks host>
DATABRICKS_CLIENT_ID=<your client ID>
DATABRICKS_CLIENT_SECRET=<your client secret>
DATABRICKS_HTTP_PATH=<your HTTP path>

Find the http_path 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")
)

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

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