SQLAlchemy Dialect for Databricks

0.2.0 · active · verified Mon Apr 13

SQLAlchemy-Databricks is a SQLAlchemy dialect for connecting to Databricks. It enables Python applications to interact with Databricks SQL Endpoints and Unity Catalog using the familiar SQLAlchemy ORM or Core API. The current version is 0.2.0, supporting Python 3.8 and above. The release cadence appears to be infrequent, with two major releases to date.

Warnings

gotcha Incorrect `server_hostname` or `http_path` format in the connection string is a frequent cause of connection failures. The dialect expects these to be correctly extracted from your Databricks SQL Endpoint details.
Fix: Always double-check the "Connection Details" tab of your Databricks SQL Endpoint for the exact `server_hostname` and `http_path` values. The `http_path` should typically start with `/sql/1.0/endpoints/`.
gotcha Version 0.2.0 fixed handling of `server_hostname` and `http_path` in the connection string. Users upgrading from 0.1.0 who had workarounds for previously incorrect parsing might experience connection issues if their connection string no longer aligns with the corrected parsing logic.
Fix: Verify your connection string against the official documentation examples for `0.2.0`, especially the `server_hostname` and `http_path` components, even if it worked in `0.1.0`.
gotcha While `sqlalchemy-databricks` pins a compatible range, breaking changes or specific issues in the underlying `databricks-sql-connector` can impact connections or query execution.
Fix: If encountering low-level connection issues, ensure your `databricks-sql-connector` version is within the compatible range (currently `>=2.0.0`) and check its release notes for any relevant changes. Consider explicitly installing the exact version pinned by `sqlalchemy-databricks` if issues persist.
gotcha Connections will fail if the target Databricks SQL Endpoint is not running. This is an external dependency issue.
Fix: Verify the status of your Databricks SQL Endpoint in the Databricks workspace UI and ensure it is in a 'Running' state before attempting to connect.

Install

pip install sqlalchemy-databricks Install latest version

Imports

create_engine
```
from sqlalchemy import create_engine
```
The `sqlalchemy-databricks` package registers the 'databricks://' dialect automatically upon import, making it available for `create_engine`.
text
```
from sqlalchemy import text
```
Used for executing raw SQL queries with SQLAlchemy Core.

Quickstart

This quickstart demonstrates how to establish a connection to a Databricks SQL Endpoint using `sqlalchemy-databricks`. It uses environment variables for sensitive connection details and executes a simple `SELECT 1` query to verify connectivity. Ensure your Databricks SQL Endpoint is running and your token has the necessary permissions.

from sqlalchemy import create_engine, text
import os

# Databricks connection details
# These are typically found in the Databricks SQL Endpoint connection details.
# server_hostname is like 'dbc-xxxxxxxx-yyyy.cloud.databricks.com'
# http_path is like '/sql/1.0/endpoints/zzzzzzzzzzzzzzzz'
# token is a Databricks Personal Access Token or Azure AD Token

DATABRICKS_SERVER_HOSTNAME = os.environ.get('DATABRICKS_SERVER_HOSTNAME', 'your_databricks_hostname')
DATABRICKS_HTTP_PATH = os.environ.get('DATABRICKS_HTTP_PATH', 'your_http_path')
DATABRICKS_TOKEN = os.environ.get('DATABRICKS_TOKEN', 'your_databricks_token')

# Construct the connection string
connection_string = f"databricks://token:{DATABRICKS_TOKEN}@{DATABRICKS_SERVER_HOSTNAME}/{DATABRICKS_HTTP_PATH}"

# Create the engine
try:
    engine = create_engine(connection_string)

    # Establish a connection and execute a simple query
    with engine.connect() as connection:
        result = connection.execute(text("SELECT 1")) # Simple test query
        print("Connection successful! Query result:", result.scalar())

    print("\nSuccessfully connected to Databricks and executed a query.")

except Exception as e:
    print(f"Error connecting to Databricks: {e}")
    print("Please ensure DATABRICKS_SERVER_HOSTNAME, DATABRICKS_HTTP_PATH, and DATABRICKS_TOKEN environment variables are set correctly.")

view raw JSON →