8  OAuth 🤝 {odbc}

When using {odbc} to connect to Databricks clusters and SQL warehouses you’ll likely have used a personal access token (PAT). It’s not uncommon for workspace administrators to disable the use of PATs.

If you are unable to create a PAT you are still able to connect to Databricks but you’ll need to use OAuth (either M2M or U2M).

User-to-machine (U2M) is typically what you’d want to use. Good news, the Databricks ODBC driver supports both since 2.7.5.

8.1 U2M Example

Note

OAuth U2M or OAuth 2.0 browser-based authentication works only with applications that run locally. It does not work with server-based or cloud-based applications.

When running this code you should be prompted to login to the workspace or you’ll see a window that says “success”. You can close the window and continue working in R.

library(odbc)
library(DBI)

con <- DBI::dbConnect(
1  drv = odbc::databricks(),
2  httpPath = "/sql/1.0/warehouses/<warehouse-id>",
3  workspace = "<workspace-name>.cloud.databricks.com",
4  authMech = 11,
  auth_flow = 2
)

1

{odbc} recently added odbc::databricks() to simplify connecting to Databricks (requires version >=1.4.0)

2

The httpPath can be found in the ‘Connection Details’ tab of a SQL warehouse

3

workspace refers to the workspace URL, also found in ‘Connection Details’ tab as ‘Server hostname’

4

The docs mention setting AuthMech to 11 and Auth_Flow to 2