Getting started with connector.databricks

The connector.databricks package provides a convenient interface for accessing and interacting with Databricks tables and volumes directly from R.

Introduction

This vignette will guide you through the process of connecting to Databricks, retrieving data, and performing various operations using this package.

Connecting to Databricks

Without connector package

To get started, you need to establish a connection to your Databricks SQL warehouse or volume storage. Use:

connector_databricks_sql() function to connect to tables through your Databricks SQL warehouse
connector_databricks_volume() function to connect to your Databricks volume storage

Here’s an example of how to do this:

library(connector.databricks)

# Connect to databricks tables using a SQL warehouse
con <- connector_databricks_sql(
  warehouse_id = "your-warehouse-id",
  catalog = "my_catalog",
  schema = "my_schema"
)

# Connect to databricks volume
con <- connector_databricks_volume(
  catalog = "my_catalog",
  schema = "my_schema",
  path = "path-to-file-storage"
)

Both connections authenticate through the brickster package. See Authentication for how to set up your credentials. warehouse_id defaults to the DATABRICKS_WAREHOUSE_ID environment variable.

Tables can also be accessed through a cluster with connector_databricks_table(), which uses the odbc::databricks() driver. See Choosing between ConnectorDatabricksSQL and ConnectorDatabricksTable for the differences between the two.

With connector package (recommended)

If you are using the connector package, you can connect to a Databricks datasources using the connect() function. This function based on a configuration file or a list creates a connectors() object with a connectorfor each of the specified datasources (for detailed explanation have a look at the connector package).

Configuration file for connecting to Databricks should look like this:

metadata:
  catalog: "databricks_catalog"
  warehouse_id: "your-warehouse-id"
  path: "path-to-file-storage"
  project: "project_name"
  trial: "trial_name"

datasources:
  - name: "tables"
    backend:
      type: "connector.databricks::connector_databricks_sql"
      warehouse_id: "{metadata.warehouse_id}"
      catalog: "{metadata.catalog}"
      schema: "{metadata.project}_{metadata.trial}_adam"
  - name: "volume"
    backend:
      type: "connector.databricks::connector_databricks_volume"
      path: "{metadata.path}"
      catalog: "{metadata.catalog}"
      schema: "{metadata.project}_{metadata.trial}_tfl"

Save this to _connector.yml file and use the connect() function to connect to Databricks:

library(connector)

# Create connector object
db <- connect()

Now you can access the Databricks tables and volume using the db object and tables or volume field, respectively.

# Connection to Databricks tables. This will print object details
db$tables

# Connection to Databricks volume. This will print object details
db$volume

After the setup is done we can use these connections to manipulate Databricks data.

Listing data

You can do some basic directory operations, such as creating, removing, and listing data inside directory, or listing tables inside database.

# Create a directory
db$volume |>
  create_directory_cnt("new_directory")

# Remove a directory
db$volume |>
  remove_directory_cnt("new_directory")

# List content inside volume directory
db$volume |>
  list_content_cnt()

# List tables inside database
db$tables |>
  list_content_cnt()

Reading and writing data

The connector packages provide a set of functions to read and write data from/to the datasources. They all have similar interface, so it’s easy to switch between them.

Now, we will show how to read and write different types of data from/to Databricks. In these examples we will be using iris and mtcars datasets.

Here is an example of writing data to tables:

library(dplyr)

# Manipulate data

## Iris data
setosa <- iris |>
  filter(Species == "setosa")

mean_for_all_iris <- iris |>
  group_by(Species) |>
  summarise_all(list(mean, median, sd, min, max))

## mtcars data
cars <- mtcars |>
  filter(mpg > 22)

mean_for_all_mtcars <- mtcars |>
  group_by(gear) |>
  summarise(across(
    everything(),
    list(
      "mean" = mean,
      "median" = median,
      "sd" = sd,
      "min" = min,
      "max" = max
    ),
    .names = "{.col}_{.fn}"
  )) |>
  tidyr::pivot_longer(
    cols = -gear,
    names_to = c(".value", "stat"),
    names_sep = "_"
  )

## Store data
db$tables |>
  write_cnt(setosa, "setosa", overwrite = TRUE)

db$tables |>
  write_cnt(mean_for_all_iris, "mean_iris", overwrite = TRUE)

db$tables |>
  write_cnt(cars, "cars_mpg", overwrite = TRUE)

db$tables |>
  write_cnt(mean_for_all_mtcars, "mean_mtcars", overwrite = TRUE)

Now, let’s read the data back manipulate it a bit and write it to the Databricks volume. This way we can save different types of data in different formats.

library(gt)
library(tidyr)
library(ggplot2)

# List and load data from tables
db$tables |>
  list_content_cnt()

table <- db$tables |>
  read_cnt("mean_mtcars")

gttable <- table |>
  gt(groupname_col = "gear")

# Save non-tabular data to databricks volume
tmp_file <- tempfile(fileext = ".docx")
gtsave(gttable, tmp_file)
db$volume |>
  upload_cnt(tmp_file, "tmeanallmtcars.docx")

# Manipulate data
setosa_fsetosa <- db$tables |>
  read_cnt("setosa") |>
  filter(Sepal.Length > 5)

fsetosa <- ggplot(setosa_fsetosa) +
  aes(x = Sepal.Length, y = Sepal.Width) +
  geom_point()

## Store data into output location
db$volume |>
  write_cnt(fsetosa$data, "fsetosa.csv")
db$volume |>
  write_cnt(fsetosa, "fsetosa.rds")

tmp_file <- tempfile(fileext = ".png")
ggsave(tmp_file, fsetosa)
db$volume |>
  upload_cnt(tmp_file, "fsetosa.png")