Skip to contents

Helper for Connecting to Databricks via ODBC

Source: R/driver-databricks.R
databricks.Rd

Connect to Databricks clusters and SQL warehouses via the Databricks ODBC driver.

In particular, the custom dbConnect() method for the Databricks ODBC driver implements a variant of Databricks's unified authentication model when no uid or pwd is supplied, checking for ambient credentials in the following order:

  • Viewer-based or service principal credentials supplied by Posit Connect (requires the connectcreds package).

  • Personal access tokens.

  • Workload identity federation.

  • OAuth machine-to-machine credentials.

  • OAuth user-to-machine credentials suplied via Posit Workbench or the Databricks CLI on desktop.

This aims to provide broad compatibility between odbc and the standard environment variables used by Databricks SDKs in R and other languages.

In addition, on macOS platforms, the dbConnect() method will check for irregularities with how the driver is configured, and attempt to fix in-situ, unless the odbc.no_config_override environment variable is set.

String truncation

The Databricks ODBC driver reports STRING columns to clients as VARCHAR(n), where n is the value of its DefaultStringColumnLength connection attribute. This reported size determines the allocated string buffer, and the driver can silently truncate values that do not fit. Because the driver's own default of 255 is easy to hit in practice, dbConnect() sets this attribute to 65535 by default. Pass defaultStringColumnLength to dbConnect() to choose another limit.

Usage

databricks()

# S4 method for class 'DatabricksOdbcDriver'
dbConnect(
  drv,
  httpPath,
  workspace = Sys.getenv("DATABRICKS_HOST"),
  useNativeQuery = TRUE,
  driver = NULL,
  HTTPPath,
  uid = NULL,
  pwd = NULL,
  ...
)

Arguments

drv

an object that inherits from DBI::DBIDriver, or an existing DBI::DBIConnection object (in order to clone an existing connection).

httpPath, HTTPPath

To query a cluster, use the HTTP Path value found under Advanced Options > JDBC/ODBC in the Databricks UI. For SQL warehouses, this is found under Connection Details instead.

workspace

The URL of a Databricks workspace, e.g. "https://example.cloud.databricks.com".

useNativeQuery

Suppress the driver's conversion from ANSI SQL 92 to HiveSQL. When set to true, we also set the following related flags: EnableNativeParameterizedQuery, and PopulateParametersForNativeQuery. The default (TRUE), gives greater performance but could cause issues with paramterised queries (and hence dbWriteTable()).

driver

The name of the Databricks ODBC driver, or NULL to use the default name.

uid, pwd

Manually specify a username and password for authentication. Specifying these options will disable automated credential discovery.

...

Further arguments passed on to dbConnect().

Value

An OdbcConnection object with an active connection to a Databricks cluster or SQL warehouse.

Examples

if (FALSE) { # \dontrun{
DBI::dbConnect(
  odbc::databricks(),
  httpPath = "sql/protocolv1/o/4425955464597947/1026-023828-vn51jugj"
)

# Use credentials from the viewer or a service principal (when possible) in
# a Shiny app deployed to Posit Connect.
library(connectcreds)
server <- function(input, output, session) {
  conn <- DBI::dbConnect(
    odbc::databricks(),
    workspace = "https://example.cloud.databricks.com",
    httpPath = "sql/protocolv1/o/4425955464597947/1026-023828-vn51jugj"
  )
}
} # }