ADBC?

Guest Post by Anonymous Rust Dev

You might think I, your friendly neighborhood Anonymous Rust Dev, am having some dyslexic trouble with the alphabet, but alas, no mistake was made. Arrow’s answer to cross-platform database connectivity, the Arrow Database Connectivity API is, to quote Arrow’s docs:

A set of abstract APIs in different languages (C/C++, Go, and Java, with more on the way) for working with databases and Arrow data. For example, query results in ADBC are all returned as Arrow data streams, not row-by-row.
—————

A set of implementations of that API in different languages (C/C++, C#/.NET, Go, Java, Python, and Ruby) that target different databases (e.g. PostgreSQL, SQLite, any database supporting Flight SQL).

What makes this API special, and why would anyone use it over any other available tool? Let’s find out together as we look under the hood of this thing.

---

Overview

Arrow’s blog introduced ADBC in early 2023. At the top, in the first sentences, they offer two compelling reasons you might care:

• ADBC is a columnar, minimal-overhead alternative to JDBC/ODBC for analytical applications.

• In other words, ADBC is a single API for loading and unloading Arrow data across different databases. (emphasis mine)

I’ll admit, when I first heard about this, I was hoping it was ODBC-compatible. Being able to point any of my existing applications at it simply by modifying the connection string seems appealing to me, but that’s not quite how it works. Though technically, I guess that’s not really a good way to think about it, anyway; consider the FAQ on some of the benefits of the ADBC approach:

JDBC uses row-based interfaces like ResultSet. When working with columnar data, like Arrow data, this means that we have to convert the data at least once and possibly twice...

In keeping with Arrow’s “zero-copy” or “minimal-copy” ethos, we would like to avoid these unnecessary conversions. [...] ODBC uses caller-allocated buffers (which often means forcing a data copy), and ODBC specifies data layouts that are not quite Arrow-compatible (requiring a data conversion anyway).

---

Thanks to Delta for sponsoring this newsletter! I use Delta Lake daily, and I believe it represents the future of Data Engineering. Content like this would not be possible without their support. Check out their website below.

---

Hey, as a Rustacean, I love zero-copy. Still, all I’ve seen so far is what ADBC isn’t. Let’s keep reading:

ADBC is a client API specification. It doesn’t define what goes on between your client and the database, just the API calls that you make as an application developer. Under the hood, a driver must handle those API calls and communicate with the actual database.

...an ADBC driver can be written for a database purely as a client library.

Maybe I’m just dense; I’m still not quite getting it. There’s a lot of energy spent in the FAQ comparing ADBC to Arrow Flight SQL, but that still doesn’t quite click for me. It’s not until I encounter the following that I think I understand what’s going on:

What is the ADBC SQL dialect?

Trick question! ADBC is not an SQL dialect. All an ADBC driver needs to do is pass your query string to the database and return the result set as Arrow data. In that respect, it’s like JDBC. (ODBC has a “standard” SQL dialect that it defines; ADBC does not do this.)

So, in a nutshell, it seems ADBC’s job is to abstract the work of running a query and returning a result set in a consistent (Arrow-based) form. It seemingly doesn’t care about the query itself, but rather primarily marshals your request to its destination and back.

---

Getting Started

Rust

I’m gonna start by looking at Rust’s quickstart, since that’s what I know; here’s some copy-pasta demonstrating an ADBC query against DataFusion:

// These traits must be in scope
use adbc_core::{Connection, Database, Driver, Statement};

let mut driver = adbc_datafusion::DataFusionDriver {};
let db = driver.new_database().expect("Failed to create database handle");
let mut conn = db.new_connection().expect("Failed to create connection");

To run queries, we can create a statement and set a query:

let mut stmt = conn.new_statement().expect("Failed to create statement");
stmt.set_sql_query("SELECT 1").expect("Failed to set SQL query");

We can then execute the query to get an Arrow RecordBatchReader:

let reader = stmt.execute().expect("Failed to execute statement");
for batch in reader {
    let batch = batch.expect("Failed to read batch");
    println!("{:?}", batch);
}

If you don’t know Rust well, that’s fine, it’s more the flow of the application I’m interested in than the language itself. As with ODBC/JDBC, it’s as simple as establishing a connection, executing a query, and traversing the resulting recordset. It’s a pretty familiar formula for anyone who’s done garden-variety SQL queries against a database.

Preceding this query, we have the one-time setup necessary to get this working:

Add a dependency on adbc_core and adbc_datafusion: $ cargo add adbc_core adbc_datafusion

This is because ADBC depends on drivers, which are provided via Rust’s Cargo ecosystem.

---

Python

If you’re looking to do things via Python, it’s similarly easy. I’ll pick on DuckDB this time...

Note: According to the ADBC docs, DuckDB work is still in progress; however looking at the referenced PR it seems like it’s been fully implemented.

First, you’ll need to install lib duckdb. The DuckDB docs will point you in the right direction, which for Python consists of:

$ pip install duckdb

Feel free to wipe the sweat from your brow, you’ve earned it. Now, let’s dig into the ADBC documentation for DuckDB. More installation stuff:

$ pip install adbc_driver_manager pyarrow

It should be pretty apparent by now, but because our recordset comes back in Arrow format, we have a dependency on Arrow. After this, it’s ready to go; shown below is their example:

import adbc_driver_duckdb.dbapi
import pyarrow

data = pyarrow.record_batch(
    [[1, 2, 3, 4], ["a", "b", "c", "d"]],
    names = ["ints", "strs"],
)

with adbc_driver_duckdb.dbapi.connect("test.db") as conn, conn.cursor() as cur:
    cur.adbc_ingest("AnswerToEverything", data)

---

Other languages

ADBC driver support for various platforms is available here. Of note, the stable platforms, which generally have PyPI packages available, currently include:

• Apache Arrow Flight SQL

• DuckDB

• PostgreSQL

• SQLite

• Snowflake

How it works

Just a forewarning, the ADBC API Standard documentation focuses primarily on the following languages: C/C++, Java, and Go.

Other languages, such as Python’s adbc_driver_manager, wrap around the C API and offer low-level bindings.

The glossary is also helpful at understanding what the moving parts are. In particular, the driver, driver manager, and wire protocol terms are helpful to understand when dealing with ADBC; in a nutshell: you use a driver manager to handle the selection of drivers (the libraries responsible for connecting to a particular database engine), which themselves implement the correct wire protocol (the implementation details of connecting and transferring data specific to a given database platform).

Or, to put it another way, the driver manager abstracts away the work of interfacing directly with the driver; see the following from the How Drivers and the Driver Manager Work Together document:

...the overall recommended approach is to use the ADBC driver manager. This is a library that pretends to be a single driver that can be linked to and used “like normal”. Internally, it loads the table of function pointers and tracks which database/connection/statement objects need which “actual” driver, making it easy to dynamically load drivers at runtime and use multiple drivers from the same application...

As seen in the Rust and Python examples, the workflow is similar to many OLTP workflows (e.g. ADO or JDBC): establish a connection, prepare a query, execute or fetch results, and enumerate data over a reader interface.

While it’s tough to find in the documentation, my impression is that for row-based data (e.g. PostgreSQL), the ADBC driver is performing a conversion into columnar Arrow data. Google’s AI doesn’t really seem to echo this, though:

— AI Overview -

Arrow Database Connectivity (ADBC) is an open-source, API-standard library designed by the Apache Arrow community to stream datasets directly between applications and databases without row-by-row translation overhead.

The PostgreSQL ADBC Driver wraps the standard libpq client library to read and write PostgreSQL data using highly efficient, columnar Arrow RecordBatches.

It achieves massive speedups over traditional drivers (like psycopg2 or JDBC) by leveraging the PostgreSQL COPY binary protocol natively.

Core Benefits

• Zero Copy Data Movement: Eliminates row-to-column serialization costs by treating incoming bytes as Arrow memory pointers.

• High-Performance Ingestion: Drastically accelerates bulk data insertion or updating via database tables.

• Unified Analytical Language: Seamlessly integrates with modern data tools like Pandas, Polars, DuckDB, and PyArrow.

My suspicion is that the conversion is happening somewhere down the line, but the AI overview separately describes the Arrow format as a distinct selling point.

---

Why ADBC?

As shown above, Arrow's zero-copy nature provides direct access to the dataset returned by the drivers. One of Arrow’s big promises is that it’s an in-memory format that can be transferred between layers as-is... I mean, it’s literally on the Arrow landing page:

Apache Arrow defines a language-independent columnar memory format for flat and nested data, organized for efficient analytic operations on modern hardware like CPUs and GPUs. The Arrow memory format also supports zero-copy reads for lightning-fast data access without serialization overhead.

This is the lingua franca of ADBC. Where another typical database driver might need to fetch data using a C library, and then convert it for use in another language like Python, with Arrow it’s merely a matter of passing a memory pointer around and letting the Arrow library for a given language make sense of it.

Another selling point is the consistent API. In practice, each language has its own concerns and may take some liberties, but just like Arrow itself, if you can master the ADBC API in one language, that knowledge transfers over to the others.

In my opinion, the greatest advantage is the backing of the Apache Foundation. When it comes to FOSS ownership, Apache has repeatedly shown itself to be a good steward of the tools people depend on.

I’d also remind you of a recent interview between Dan and Wes McKinney, one of the principal authors of Arrow.