Repository navigation
Meta-issue: More readers #341
Description
Activity
I have a POC for trino via ADBC, will try to get a PR up sometime this week.
You are not wasting a minute :-) Looking forward to it
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-defaultadbcfeature~700 — PR2 HybridReader— primaryReader+ in-processDuckDBReaderstaging~750 — PR3 HybridReader query-result cache + Reader::clear_cache()+ Jupyter-- @uncache+ VL v5/v6 mime~600 PR2 PR4 (later) ggsql-pythonPyO3 bindings~2,000 PR1–3 Each PR is reviewable in one sitting and builds with
cargo teston 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().HybridReaderwraps aBox<dyn Reader>(the data side) and aDuckDBReader(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" — sameReaderinterface, 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/FACETtweak — wasted network for remote backends. The cache memoizes(reader_uri, sql) → DataFramein the staging DuckDB: a small meta table tracks entries, each cached result lives in its own__ggsql_cache_<hex>table keyed bySHA-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 viaHybridReader::clear_cache()(Rust) or-- @uncache(Jupyter); disable globally withGGSQL_HYBRID_CACHE_DISABLED=1.Scoped to
HybridReaderspecifically: otherReaderimplementations 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 forHybridReader— 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_streamistodo!(),OptionStatement::IngestModeis rejected as unrecognized, andStatement::execute.unwrap()s on DataFusion planning errors instead of returning an ADBC error code. That forces ourregister()onto a workaround path (CREATE TABLE from the Arrow schema + per-batchbind+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_duckdbloaded viaManagedDriver: same query throughAdbcReader<DuckDB>and throughDuckDBReaderdirect, assert identical output. That exercises the standardbind_stream+IngestModeingest path, validates real ADBC error returns, and uses a known-trustworthy DuckDB as the oracle — no creds, just the libduckdb theduckdbcrate 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) oradbc_driver_sqlite(canonical reference, adds a test-only dep)? Defaulting to the first.Reacted by Bryce Mecum, eitsupi and AndersThanks @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
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.
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)
Reacted by AndersIt 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.
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.
Reacted by Simon AUBERTFor the wishlist: it'd be a valuable start if the CLI accepted
adbc://src-nameURIs. I understand the complexity of handling ADBC connection strings in URIs; this might be a simple start for ggsql that pushes the driver-specific stuff into an ADBC config file.
To keep taps on ideas for readers that comes in, we will keep a list here:
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