Choosing between ConnectorDatabricksSQL and ConnectorDatabricksTable • connector.databricks

Introduction

connector.databricks has two connectors for Databricks tables:

ConnectorDatabricksSQL, created with connector_databricks_sql(). It connects to a SQL warehouse through the brickster::DatabricksSQL() DBI driver.
ConnectorDatabricksTable, created with connector_databricks_table(). It connects to a cluster through the odbc::databricks() DBI driver.

ConnectorDatabricksSQL is the preferred way of working with tables and is where new development happens. ConnectorDatabricksTable is still available for existing code and for features ConnectorDatabricksSQL does not have yet. This article compares the two and shows how to move from one to the other.

At a glance

	`ConnectorDatabricksSQL`	`ConnectorDatabricksTable`
Constructor	`connector_databricks_sql(warehouse_id, catalog, schema)`	`connector_databricks_table(http_path, catalog, schema)`
DBI driver	`brickster::DatabricksSQL()`	`odbc::databricks()`
Compute	The SQL warehouse given by `warehouse_id`	The cluster given by `http_path`, and the first SQL warehouse returned by `brickster::db_sql_warehouse_list()`
System requirements	None	Databricks ODBC driver
Authentication	brickster	odbc and brickster
`...` is passed to	`DBI::dbConnect()` for `brickster::DatabricksSQL()`	`DBI::dbConnect()` for `odbc::databricks()`
`write_cnt()`	`brickster::dbWriteTable()`, optionally through a staging volume	Always through a temporary volume
Appending rows	`append = TRUE`	Not supported
`read_cnt()`	`DBI::dbReadTable()`	Arrow results from the SQL warehouse
`list_content_cnt()`	`DBI::dbListTables()`	Unity Catalog API
`remove_cnt()`	`DBI::dbRemoveTable()`	Unity Catalog API
`tbl_cnt()`	dbplyr, executed on the SQL warehouse	dbplyr, executed on the cluster
whirl logging	Not supported yet	Supported

Compute and authentication

ConnectorDatabricksSQL does everything on one SQL warehouse, and only needs the brickster authentication described in this article. No system libraries are needed. warehouse_id defaults to the DATABRICKS_WAREHOUSE_ID environment variable, and the IDs of the warehouses you can use are listed by brickster::db_sql_warehouse_list().

ConnectorDatabricksTable opens its DBI connection to the cluster in http_path with odbc::databricks(), which needs the Databricks ODBC driver. That connection is only used by tbl_cnt(). Reading, writing, listing, removing and tagging tables are done with brickster, on the first SQL warehouse returned by brickster::db_sql_warehouse_list(). It therefore needs both odbc and brickster authentication, and you cannot choose which warehouse is used. See vignette("articles/authentication") for how to set up both.

Writing

ConnectorDatabricksSQL writes with brickster::dbWriteTable(). Without a staging volume the data is sent inside the SQL statement, which is slow for large data and limited to 50,000 rows. With staging_volume set, the data is uploaded as Parquet files to the volume first. Use a staging volume for anything but small tables:

con <- connector_databricks_sql(
  warehouse_id = "your-warehouse-id",
  catalog = "my_catalog",
  schema = "my_schema",
  staging_volume = "/Volumes/my_catalog/my_schema/my_volume"
)

ConnectorDatabricksTable always writes through a temporary volume that it creates and removes for every write.

See vignette("articles/read_write") for details on both.

Driver parameters

Both constructors pass ... on to DBI::dbConnect() for their driver.

For ConnectorDatabricksSQL these are the arguments of the brickster driver, for example disposition, fetch_timeout, max_active_connections, show_progress, host or token:

con <- connector_databricks_sql(
  warehouse_id = "your-warehouse-id",
  catalog = "my_catalog",
  schema = "my_schema",
  fetch_timeout = 600,
  show_progress = FALSE
)

For ConnectorDatabricksTable these are the arguments of odbc::databricks(), for example workspace, uid, pwd or driver. useNativeQuery defaults to FALSE and can be overridden:

con <- connector_databricks_table(
  http_path = "path-to-cluster",
  catalog = "my_catalog",
  schema = "my_schema",
  workspace = "https://<workspace>.cloud.databricks.com"
)

Moving from ConnectorDatabricksTable to ConnectorDatabricksSQL

Replace http_path with the ID of the SQL warehouse you want to use. If your http_path already points to a SQL warehouse, it has the form /sql/1.0/warehouses/<warehouse-id> and the last part is the ID.

# Before
con <- connector_databricks_table(
  http_path = "/sql/1.0/warehouses/abc123",
  catalog = "my_catalog",
  schema = "my_schema"
)

# After
con <- connector_databricks_sql(
  warehouse_id = "abc123",
  catalog = "my_catalog",
  schema = "my_schema"
)

In a connector configuration file, change the backend type and replace http_path with warehouse_id:

# Before
datasources:
  - name: "tables"
    backend:
      type: "connector.databricks::connector_databricks_table"
      http_path: "/sql/1.0/warehouses/abc123"
      catalog: "my_catalog"
      schema: "my_schema"

# After
datasources:
  - name: "tables"
    backend:
      type: "connector.databricks::connector_databricks_sql"
      warehouse_id: "abc123"
      catalog: "my_catalog"
      schema: "my_schema"

Calls to read_cnt(), write_cnt(), list_content_cnt(), remove_cnt() and tbl_cnt() stay the same, except for the timepoint, version and tags arguments, which ConnectorDatabricksSQL does not have. Consider setting staging_volume if you write tables with more than a few thousand rows.