Connecting to Databricks via ODBC

This guide will walk you through the steps to establish a connection with a Databricks cluster via ODBC using both R and Stata in the Secure Data Environment (SDE). We'll cover how to generate a Databricks Personal Access Token (PAT) and use it to authenticate your connection.

Whilst the ability to connect to a Databricks cluster directly from your desktop using tools like RStudio or Stata offers flexibility to work with familiar software, it does also come with trade-offs when compared to using Databricks notebooks.

Consider carefully, the following pros and cons to determine the best workflow for your needs.

Running Databricks cluster

Ensure that your Databricks cluster is running before attempting this process.

You'll need a PAT token for authentication: this process only needs to be completed once.

1. Log In to Databricks

Navigate to your Databricks workspace URL:

Enter your login credentials.

2. Access user settings

Click on your username (usually in the top-right corner).

Select Settings from the dropdown menu.

Select the Developer settings. 

Select Manage Access Tokens.

3. Generate a new token

Click Generate New Token.

Optionally, add a comment (for example ODBC Connection) and set an expiration date.

Click Generate

4. Copy and save the token

Important: Copy the generated token immediately; you won't be able to see it again

When storing your personal access token we recommend using the following best practice. 

1. Place it within your personal Home folder

Store the token in a folder accessible only to your user account (i.e., personal “Home” folder located on your desktop).

Name the file something non-obvious, like db_token.txt

2. Do not hard-code your token in scripts

Do not embed the token directly in your R/Stata scripts or version control.

Instead, paste the token into the ODBC DSN password field (at runtime) and reference to configured DSN connection object (Databricks).

3. Short expiration and rotation

Generate tokens with shorter expiration periods if possible. 

If a token is compromised or no longer needed, revoke it immediately in your Databricks user settings.

The ODBC driver is already installed and pre-configured to work with your databricks cluster: it is assigned under the data source name (DSN) Databricks.

You only need to update it with your PAT token to establish a successful connection.

1. Open ODBC data source administrator

Press Windows Key (Command Key on Mac) + R to open the Run dialog.

Type odbcad32 and press Enter.

2. Locate the Databricks DSN

Go to the User DSN tab.

Select on the Databricks DSN name and click Configure...

3. Enter your PAT token

In the configuration window, locate the Authentication settings.

Paste your PAT token into the Password field.

4.  Test the connection (optional).

Click test to verify the connection.

If successful click ok to save. 

1. Install required packages (If not installed).

install.packages(c("DBI", "odbc"))

2. Load the packages

library(DBI)

library(odbc)

3. Establish the connection

databricks_conn <- dbConnect(odbc(), "Databricks")

4.Test the connection

We can list all the available tables to verify a successful connection:

databases <- dbGetQuery(

  conn =  databricks_conn,

  statement = "SHOW DATABASES"

)

all_tables <- lapply(databases$databaseName, function(x) {

  tbls <- dbGetQuery(

conn = databricks_conn,

statement = paste0("SHOW TABLES IN ", x)

)

  tbls$tablePath <- paste(

tbls$database, tbls$tableName, sep= "."

)

  return(tbls)

  })

all_tables <- do.call(rbind, all_tables)

head(all_tables)

5.Execute queries

To return data you can run SQL queries using dbGetQuery() function from the DBI packages:

query_df <- dbGetQuery(

conn =  databricks_conn,

statement = "SELECT * FROM dss_corporate.calendar LIMIT 10"

)

6. Close the connection

Once you have finished querying your data, you can close the connection using:

dbDisconnect(databricks_conn)

This process is only applicable to users who have an active Stata license.

More information is available on the ODBC Stata package 

1. Open Stata

Launch Stata on your machine.

2. Verify the Databricks ODBC data source

You can list all defined data source names (DSNs) to which Stata can connect and verify the “Databricks” ODBC data source is present:

odbc list

3. List all available table names

odbc query "Databricks"

4. Describe a table from the “Databricks” ODBC source

odbc describe "calendar", dsn("Databricks")

5 .Load a table into memory using an SQL query

odbc load, exec("SELECT * FROM dss_corporate.calendar

LIMIT 10") dsn("Databricks") clear