Proposal: ADBC file drivers with pluggable storage

[CurtHagenlocher]

Proposal: ADBC file drivers with pluggable storage

Status: early draft for discussion. Feedback on the direction is wanted
before any detailed specification.

Summary

ADBC today connects to databases and query engines. This proposal extends it
to the large amount of tabular data that lives in files with no server or
engine in front of them. Examples are Excel workbooks, HDF5 and NetCDF,
dBASE, fixed-width text, and a ZIP of CSVs.

It introduces three pieces:

1. File drivers: a kind of ADBC driver that understands one file format
   and exposes its contents as tables. It does not do its own I/O or query
   processing.
2. A file-system interface: a small C ABI that the host hands to a file
   driver. The driver gets all of its bytes through it, which makes storage
   (local disk, S3, GCS, Azure, HTTP, archives) pluggable and shared.
3. A file-system registry in the driver manager. It maps URI schemes to
   dynamically loaded storage providers, much as the driver manager already
   finds and loads drivers. The core ships with a local file-system provider
   only.

Query processing is left to the consumer, such as DataFusion, DuckDB, Polars,
or pandas. The spec would aim to standardize only what is needed to make that efficient:
projection, filter and limit pushdown, statistics, and partitioned reads.

Motivation

Lots of tabular data is not behind a query engine

Spreadsheets, scientific array formats, legacy desktop-database files, and
archives of delimited text are everywhere. Reading them as Arrow today means
finding a format-specific library for each language and engine, if one exists.

ODBC solved this, but without any sharing

ODBC had many file-based drivers: CSV/TSV, fixed-width, Excel, dBASE, Paradox,
Access, XML, JSON, Parquet, ORC, Avro, and more. Each one had to bundle three
separate things:

Component	Role	Specific to the format?
Format logic	Understands sheets, cells, datasets, row groups...	Yes
Storage access	Reads bytes from disk, object stores, etc.	No
Query engine	Parses SQL, filters, joins, aggregates	No

Shipping two such drivers meant shipping two storage layers and two query
engines, or inventing a private way to share them. There was no standard way
for independent drivers to share a common component.

Today's engines repeat the same split, in-process

DataFusion, DuckDB, Arrow Datasets, GDAL, and others each separate "format"
from "file system" internally. That separation lives behind each project's own
in-process API, so every format has to be implemented again for every engine.
There is no standard, cross-language, dynamically loadable boundary for a
file-format adapter.

Databases have been deconstructed

Arrow standardized the data, and ADBC standardized the driver API. The
remaining pieces, format logic and byte access, can now be standardized too.
Then a format adapter written once, in any language, can be loaded by any
ADBC-capable engine and read from any storage the host can reach.

Goals and non-goals

Goals

• A standard, dynamically loadable way to write file-format adapters that
  produce Arrow.
• Pluggable, shareable storage, so that format drivers never implement S3
  or similar themselves.
• Efficient consumption by query engines, through projection and filter
  pushdown, statistics, and parallel partitioned reads.
• Minimal and backward-compatible changes to ADBC.

Non-goals

• A query engine inside the driver. File drivers scan; engines query.
• Replacing engines' native readers for formats like Parquet that already have
  excellent implementations. The value is in the long tail of formats.
• Writing files, in the first version. A publication/write extension can come
  later if bulk ingest into files proves useful.
• A general-purpose virtual file-system standard for everything. The interface
  is shaped for format readers.

Proposal

1. File drivers

A file driver is an ordinary ADBC driver, loaded through the existing driver
manager. It identifies itself as a file driver (for example through GetInfo)
and can declare the file extensions, MIME types, and magic-byte signatures it
recognizes. With that, a driver manager can optionally pick a driver
automatically for a given file.

The driver's contents map onto existing ADBC concepts:

ADBC concept	Meaning for a file driver
Database	A file system, a root location (a file, directory, or glob), and format options
Connection	A session over that root; may cache parsed metadata
GetObjects / GetTableTypes	The tabular objects in the file(s): Excel sheets and named ranges, HDF5 datasets, NetCDF variables, files in a directory or archive
GetTableSchema	The schema of one object, with format-specific inference options
GetStatistics	Row counts, min/max, and null counts where the format knows them
Statement	A scan of one object, optionally with projection, filter, and limit. It is not a SQL query
ExecutePartitions / ReadPartition	Natural splits (sheets, chunks, files, row groups) for parallel reads

Object names are format-specific and opaque to ADBC, for example Sheet1,
Sheet1!A1:C3, /group/dataset, or 2024/part-0.csv. A driver may also
accept a small SQL-like convenience syntax, but this would be optional and
not part of the core contract.

2. The file-system interface

When a file-driver database is created, the host supplies a file-system
object: a versioned C function table, in the same style as the rest of
ADBC. The driver never opens files itself. It calls back through this table
to open, stat, list, and read.

Key properties:

• Small. It covers open, stat, optional listing, ranged reads, and close.
  Implementing a provider, or consuming one from a driver, should be easy.
• Batched range reads. A driver asks for many byte ranges at once. Reads
  from the end of a file are supported, so a footer can be fetched in one
  request. The provider is free to merge, split, or parallelize the ranges.
  This is most of the performance win on high-latency object storage.
• Async with a blocking twin. Reads can be submitted asynchronously and
  complete through a callback. A blocking form is also available. A provider
  implements whichever form is natural for it, and the driver manager supplies
  the other. Drivers built on Read + Seek style libraries use the blocking
  form, while drivers that know their ranges ahead of time overlap I/O
  asynchronously.
• Simple ownership. Buffers are owned by the caller and must stay valid
  until that range's callback fires. Every accepted read gets exactly one
  callback, including on error or cancellation. Callbacks never run inline
  during submission and must only signal completion, never do real work.
  Closing a file cancels outstanding reads and waits for their callbacks.
  These few rules map directly onto futures and tasks in Rust, C++, C#, and Go.
• Exact reads and a version token. A read either returns all requested
  bytes or fails. Files expose an opaque version (an ETag or generation) so
  drivers can cache parsed metadata safely.
• Extensible. Richer capabilities can be negotiated later: zero-copy
  views, streaming input, stronger consistency guarantees, writes, or
  integration with an engine's own I/O scheduler.

3. File-system registry in the driver manager

The driver manager gains a registry of storage providers, keyed by URI
scheme, similar to Hadoop's fs.<scheme>.impl:

• The core driver manager ships only a local file-system provider.
• Other providers (s3, gs, abfss, http(s), hdfs, ...) are separate,
  dynamically loaded libraries, found and installed the same way drivers are.
• Credentials and configuration belong to each provider, not to the read
  interface or the file driver.
• When a user opens a file-driver database with a URI, the driver manager
  resolves the scheme, constructs the provider, and hands it to the driver.
• A host that already has its own storage layer can instead pass its own
  file-system object. An example is an engine exposing its configured object
  store. That way, the engine's credentials and caching are reused.

Archives are file systems, not formats. A zip provider can sit on top
of any other provider. A "ZIP of CSVs on S3" then becomes a composition of
a CSV driver, a zip provider, and an S3 provider, and none of them needs to
know about the others.

4. Pushdown and statistics

Engines keep doing query processing, so the spec covers only what lets them
read less:

• Projection: the columns to return.
• Filter: expressed as a Substrait expression. ADBC already supports
  Substrait. For each filter term the driver reports whether it applied it
  exactly, inexactly (so the engine re-checks), or not at all.
• Limit, for previews.
• Statistics: table-level statistics via the existing GetStatistics.
  A likely addition is per-partition statistics, so engines can skip
  chunks or row groups before reading them.

Example

A DataFusion query joins:

• a named range Prices in C:\data\budget.xlsx, through an Excel file
  driver over the built-in local provider; and
• orders/*.csv inside s3://bucket/archive.zip, through a CSV file driver
  over a zip provider over an S3 provider.

Every driver and provider is loaded dynamically through standard interfaces.
DataFusion pushes the column lists and filters down, reads the partitions in
parallel, and performs the join itself.

Changes required in ADBC

• A specification for the file-system interface (a new header).
• A way to pass a file-system object to a database. A well-known option is
  enough for prototypes; a dedicated, versioned entry point is likely the
  long-term answer.
• Conventions for file drivers: GetInfo identification, table types,
  option names, and how scans and pushdown are expressed on a statement.
• Driver-manager support for the provider registry and the local provider.
• Possibly, per-partition statistics.

Existing drivers and applications are unaffected.

Planned prototypes

• A driver-manager prototype with the registry and local provider.
• Providers: local, one object store (S3), perhaps ZIP to layer over the others.
• File drivers in more than one language: for example Excel (Rust), CSV over
  directories and archives, and HDF5 or NetCDF, which bridges a library that
  has its own I/O layer.
• Consumers: Python via the ADBC driver manager. If super ambitious, a DataFusion
  table provider that shows pushdown and (if possible) parallel partitioned reads.

Some questions for consideration

1. Is this in scope for ADBC, or is it a separate Arrow-adjacent specification
   that ADBC merely uses?
2. Is "a statement is a scan of one object, plus pushdown" the right model?
   What should SetSqlQuery mean for a file driver?
3. Alternatively, is Substrait a better vehicle for filters, and how much of it should a
   file driver be expected to understand?
4. There's clearly a tradeoff between complexity and performance when it comes to the
   details of the file system API. Where is the right place to put this?

(Apologies in advance for a fair chunk of LLM-generated text.)

[CurtHagenlocher]

in conjunction with apache/spark#54603 this could be used as a language-neutral extension point for file parsing from inside Spark.

[CurtHagenlocher]

"Vibe-coded" prototypes in https://github.com/CurtHagenlocher/arrow-adbc/tree/file-api