9 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. The Databricks ODBC driver supports OAuth flows, but the minimum driver version differs by flow:

9.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