Meta-issue: More readers

[thomasp85]

To keep taps on ideas for readers that comes in, we will keep a list here:

• Druid (@jimhester)
• Trino (@jimhester)
• ADBC Drivers (Arrow support in future plans? #331)
• Drill
• DataFusion
• Exasol (@mkcorneli, Feature Request: Exasol database connection (co-developed) #330)
• PostgreSQL (could be through ODBC, but maybe a direct integration is better)
• MonetDB (@simonaubertbd, MonetDB Support #509)

Some of these might work fine through ODBC or future ADBC.

Both Druid and Trino have commercial ODBC drivers but might need their own Dialect.

Drill has non-commercial a ODBC driver but might need Dialect

[jimhester]

I have a POC for trino via ADBC, will try to get a PR up sometime this week.

[thomasp85]

You are not wasting a minute :-) Looking forward to it

[jimhester]

Ok, so I've got a good POC working internally now, it required some additional changes outside of just the driver itself, but I think they are good improvements in general. Its probably easiest to land these as a series of related, but distinct PRs. The biggest thing is our trino service implements a read only connection, and also trino in general doens't have the concept of a temp table in the syntax, so the existing approach wouldn't really work. What we did instead is break up the data pull and chart into a hybrid reader, which reads the data into a side car duckdb table rather than a temp table. This lets you use ggsql even without write access to the database. Additionally we added support for caching this data, so you can change the viz portion of the SQL and reuse the data in the side car, which lets you iterate on the viz very rapidly, I think it is a big win for the usability of this system. Anyway, here's claude's summary of the changes:

ADBC reader support, plus a composable staging layer and query-result cache

What's landing

	What	LOC	Depends on
PR1 #422	AdbcReader<D: Driver> — generic over any ADBC driver, off-by-default adbc feature	~700	—
PR2	HybridReader — primary Reader + in-process DuckDBReader staging	~750	—
PR3	HybridReader query-result cache + Reader::clear_cache() + Jupyter -- @uncache + VL v5/v6 mime	~600	PR2
PR4 (later)	ggsql-python PyO3 bindings	~2,000	PR1–3

Each PR is reviewable in one sitting and builds with cargo test on a fresh clone — no external infrastructure required. The work originated against a Netflix-internal Flight SQL service called Quiver that proxies to Trino; the abstractions are general, the Quiver-specific integration (auth, headers, URI parsing) stays on our fork.

HybridReader

Some backends (Flight SQL, anonymous-port Trino, Snowflake-without-write-auth) accept reads but reject register(). HybridReader wraps a Box<dyn Reader> (the data side) and a DuckDBReader (staging): register() writes to staging; execute_sql() looks at the SQL text and routes whole queries to whichever side they reference. The mental model is "stage the side you want to iterate on locally" — same Reader interface, no caller-visible difference, callers don't need to know the wrapper exists.

Reader::dialect() returns staging's dialect (DuckDB) for compiled SQL, which is the right answer because ggsql's internally-generated SQL (stat transforms, scale resolution, layer filtering) runs against staged tables by the time it executes; for SQL targeted at the remote source, data_dialect() exposes the primary's dialect separately. Limitations are enforced rather than implicit: one SQL statement can't reference both sides (cross-backend joins require staging one side first), staging is in-process and dropped with the reader, no spill-to-disk in v1.

Cache

Viz iteration re-runs identical SQL against the primary on every DRAW / SCALE / FACET tweak — wasted network for remote backends. The cache memoizes (reader_uri, sql) → DataFrame in the staging DuckDB: a small meta table tracks entries, each cached result lives in its own __ggsql_cache_<hex> table keyed by SHA-256(reader_uri ‖ sql) truncated to 64 bits. TTL defaults to 300s; byte budget to 512 MB with LRU eviction by last-access; both configurable. Cache hits are sub-millisecond — DuckDB reading from a local table — and misses fall through transparently to the primary. Manual invalidation via HybridReader::clear_cache() (Rust) or -- @uncache (Jupyter); disable globally with GGSQL_HYBRID_CACHE_DISABLED=1.

Scoped to HybridReader specifically: other Reader implementations don't have a place to store it, and for a local-DuckDB primary the cache wouldn't add anything over what DuckDB itself already does. On by default for HybridReader — people rarely flip defaults, and opt-in mostly means people not getting the speedup.

Testing without external infrastructure

adbc_datafusion (pure-Rust, in-process) covers routing / conversion / dialect at unit scope, but its 0.23 release has gaps that limit how much confidence it alone can deliver: Statement::bind_stream is todo!(), OptionStatement::IngestMode is rejected as unrecognized, and Statement::execute .unwrap()s on DataFusion planning errors instead of returning an ADBC error code. That forces our register() onto a workaround path (CREATE TABLE from the Arrow schema + per-batch bind + execute_update) and means error-handling tests against datafusion can't exercise the real status-code surface.

PR1 therefore adds an equivalence suite using adbc_driver_duckdb loaded via ManagedDriver: same query through AdbcReader<DuckDB> and through DuckDBReader direct, assert identical output. That exercises the standard bind_stream + IngestMode ingest path, validates real ADBC error returns, and uses a known-trustworthy DuckDB as the oracle — no creds, just the libduckdb the duckdb crate already links.

For PR2/PR3, DuckDBReader::memory() as both primary and staging exercises every routing / cache code path offline.

Deferred

A generic adbc:// URI scheme for CLI / Jupyter — naming and option-passing deserve their own discussion separate from PR1.

Open question

PR1 equivalence-test driver: adbc_driver_duckdb (piggybacks on the libduckdb already linked) or adbc_driver_sqlite (canonical reference, adds a test-only dep)? Defaulting to the first.

[thomasp85]

Thanks @jimhester — we have had ongoing discussions around how to deal with read-only backends and lack of TEMP support. The HybridReader is an interesting concept we haven't explored before. I might be a bit worried about the caveats of not being able to mix all tables at will even if it is an edge case, but I can probably get a more informed opinion once I've spend a bit more time with the idea

[jimhester]

It works pretty well in my testing, particularly with the cache, being able to change something in the visualization and have it update in the Jupyter notebook basically instantly is nice UX.

Also I noticed my comment above references testing against adbc_driver_duckdb, we actually ended up using adbc_driver_sqllite in the PR as it was easier to get running in the CI, but same basic idea, test against a known working path but through adbc rather than a native or ODBC binding.

[thomasp85]

yeah, the more I think about this the more it fits into my plans for interactivity where we also (in general) would like to work on a subset of data rather than directly on the backend (unless you are filtering on huge, huge data)

[jimhester]

It got auto-linked here, but just wanted to call it out I added an example of the query cache via the hybrid reader at jimhester#2, in case you wanted to see how it worked in practice.

[amoeba]

Hi @thomasp85 and @jimhester, I work on ADBC and am excited to see how ggsql is using ADBC and dbc! Since this thread was created, the number of available ADBC drivers has increased dramatically and we've recently tried to document them all at https://arrow.apache.org/adbc/current/driver/index.html. A few of the drivers on your list could be checked off and our team at Columnar is always working on new drivers and all of them will be available with dbc.

Are there any ADBC things we could help with? I'd be happy to help coordinate any bug reports, feature requests, or driver requests.