format: generic table select API (dual of bulk ingest)

[lidavidm]

Inspired by rdbi: a function to SELECT * from a table without providing a query (makes it easier to provide non-query-engine based backends, e.g. a Parquet file backend)

[SamArch27]

I have some suggestions for a generic table SELECT API.

Generic table and schema names

Users should be able to set an option (similar to ADBC_INGEST_OPTION_TARGET_TABLE) to specify a target table and schema to read from. This would make it easy to perform SELECT * FROM "schema"."table" queries which work across multiple systems (even if those systems use different delimiters).

Projection, Filter, and Limit Pushdown

Users should be able to provide a simplified query plan (possibly Substrait) instead of a SQL query string for execution. A plan-based API would enable pushdown of projections (dropping unused columns), filters (pruning of irrelevant rows), and limits (early termination of entire queries) leading to better performance.

[lidavidm]

It might be interesting to accept very restricted Substrait plans and have drivers internally translate them to SQL, avoiding the need for a new API and enabling further extensions down the line.

My slight worry is that by using Protobuf, each driver that wants to support this has to commit to embedding a ton of code; maybe the weight would be less in Rust/Go though.

[SamArch27]

After thinking about it more, LIMIT pushdown doesn’t make much sense without also pushing down ORDER BY clauses to get a deterministic result.

Filter pushdown would be great, but tricky to ensure semantic correctness.

Projection pushdown seems like it would yield the most benefit while being more straightforward to implement. One approach would be to extend the API to accept an array of column indexes.

[CurtHagenlocher]

My slight worry is that by using Protobuf, each driver that wants to support this has to commit to embedding a ton of code; maybe the weight would be less in Rust/Go though.

My hot take is that using auto-generated Protobuf deserialization classes for Substrait is "doing it wrong (TM)". In my Substrait-to-KQL translator, I'm just scanning the Substrait buffer directly; no allocation, no reams of generated code. It's just like walking a parse tree.

[lidavidm]

Nice, especially for a subset that seems to make sense. Or frankly it shouldn't be hard to vibe code a dedicated reader for (a subset of) Substrait and share it among the drivers.

[CurtHagenlocher]

Users should be able to set an option (similar to ADBC_INGEST_OPTION_TARGET_TABLE) to specify a target table and schema to read from. This would make it easy to perform SELECT * FROM "schema"."table" queries which work across multiple systems (even if those systems use different delimiters).

One gap between this request and the notion of using Substrait is that Substrait doesn't currently support unknown or dynamic schemas. I've suggested substrait-io/substrait#1104 as a way to express this.

[tokoko]

fwiw, I don't think we need another API for this and we shouldn't have to choose between sql and substrait either. These sort of drivers should be free to support either just like any other queries:

• for sql, only a subset of sql should be supported, something like this: SELECT <columns | *> FROM [schema.]<table> [WHERE <filter>]. see iceberg example
• for substrait, you would also need support for a subset of substrait operations, maybe a dialect that allows for only ReadRel, ProjectionRel, FilterRel and specific functions.

basically I'm for treating these readers as mini engines that can do limited projection and predicates, but nothing else.

[CurtHagenlocher]

These sort of drivers should be free to support either just like any other queries:

I guess I got so carried away by my personal hobby horses that I didn't notice the overall framing of this issue as being for "non-query-engine based backends, e.g. a Parquet file backend". I had interpreted this as being a generic top-level dialect-agnostic API that would apply to all connectors, even those with a proper QL-based back end. I agree 100% that a driver which does nothing but read Parquet files should implement at least enough query language support to do simple querying.

I'm not 100% sold on the value of a driver which just reads Parquet files, but I know better than to think I can anticipate other people's interesting (or unusual :P) use cases. I^H Claude might prototype one this weekend.

[tokoko]

I'm not 100% sold on the value of a driver which just reads Parquet files, but I know better than to think I can anticipate other people's interesting (or unusual :P) use cases. I^H Claude might prototype one this weekend.

I was just talking to David about this... 😆 I dislike the "just use duckdb/DF" approach even though that's technically very possible of course. If you take that argument further, you can also argue that postgres driver is not necessary as well since duckdb postgres extension exists. there's really no reason for adbc to exist if we're fine with treating duckdb as an api layer + local processing engine. I think we are disregarding the fact that the point of adbc is that it's just an api spec and that matters. A particular reader is free to bundle whatever it needs inside the driver, but shouldn't rely on something on top if that makes sense.

From a practical perspective, you can't really distribute a parquet or iceberg read operation (at least easily) if what you're relying on is a local compute engine, while for native adbc drivers it's a straightforward task. same goes for distributed write which isn't really possible with adbc either today, but at least we can fix that.

[CurtHagenlocher]

From a practical perspective, you can't really distribute a parquet or iceberg read operation (at least easily) if what you're relying on is a local compute engine, while for native adbc drivers it's a straightforward task.

Oh, yes, I see; when you are running in the context of a query engine then you don't need another query engine. And while Parquet might not be the best example due to its ubiquity, the same would not be true of something like Lance.

[lidavidm]

I guess I got so carried away by my personal hobby horses that I didn't notice the overall framing of this issue as being for "non-query-engine based backends, e.g. a Parquet file backend". I had interpreted this as being a generic top-level dialect-agnostic API that would apply to all connectors, even those with a proper QL-based back end.

Just to be clear, I'm interested in both use cases here - I think some use cases would like to interact with drivers generically and not know details about SQL dialects, and that maybe this would also enable (limited in functionality, but minimally viable) drivers for systems that don't have a SQL dialect or query engine per se.