Ibis & Substrait & ADBC, how does all that play along together?

[astrojuanlu]

today I saw that one can use Polars to query Snowflake directly using ADBC https://pola-rs.github.io/polars/py-polars/html/reference/api/polars.read_database.html

and started thinking what should be the relationship between ADBC and Ibis. I saw various closed PRs on the Ibis side (#5010, #4267) so initially this was not clear to me.

Digging a bit more, I also came across Substrait and initially it was not obvious to me what the relationship between Substrait and ADBC was. I finally found this:

ADBC allows applications to pass queries either as SQL strings or Substrait plans.

https://substrait.io/community/powered_by/

Do you have any more insight on what are the overall plans and roadmap in this space of the whole Ibis -> Backend -> Substrait -> ADBC -> Database tower?

[gforsyth]

I'm on mobile but I can write more on this when I'm back at a keyboard.

I don't think we understand the boundaries yet of where ibis, substrait and ADBC interact, just yet.

We have ibis-substrait, which compiles ibis expressions into substrait plans, and these plans can (usually) be consumed and executed by DuckDB and Acero (pyarrow.compute).

ADBC and Substrait are both efforts to avoid the many-to-many translation efforts that currently make up a lot of Ibis' backend code (and similarly, Trino's pile of connectors).

I don't think using both ADBC and Substrait in the same execution pipeline makes sense for Ibis, but I could be wrong about that.

I suspect we'll end up with an ADBC "backend" and a Substrait "backend". These will be a bit different than the existing backends, though, as they aren't an engine by themselves, so it would always be e.g. ADBC+snowflake or Substrait+DuckDB.

One difference that comes to mind is that ADBC is (in my understanding) a provider of a more "direct" connection. You connect to an engine via ADBC and it handles communication and translation.
Substrait can function that way (with support from the producer and consumer) but is also suited to serializing plans that may be executed at a different time (or perhaps executed on a regular basis, without the need for the client connection that initially provided the plan).

CC @lidavidm who can correct anything I've said that's totally wrong on the ADBC front

[lidavidm]

I broadly agree (though I'd appreciate if you could clarify what ADBC handling translation means to you). I think ADBC and Substrait aren't backends by themselves, but machinery to make supporting new backends easier. I do think you will want to use them together, though.

ADBC provides the connection for you and handles getting Arrow data, but you still need to feed it SQL or Substrait. So the two backends are complementary. (Not every database supported by ADBC can support Substrait (in fact none currently do, unfortunately), and there may be databases that support Substrait but not ADBC. Over time we'd like to improve that.)

[astrojuanlu]

Thanks for the blazing fast answer as always folks. If you want to elaborate more from the keyboard @gforsyth that's great, otherwise I think I got a clear picture.

In particular, this insight from @lidavidm

Not every database supported by ADBC can support Substrait (in fact none currently do, unfortunately), and there may be databases that support Substrait but not ADBC.

makes it clear that things are very much in flux still. How to accommodate for the matrix of possible cases (supports ADBC yes/no, supports Substrait yes/no) will be a bit of a challenge I assume.

[gforsyth]

In re: "translation":

In my head, Ibis generates ADBC compatible-SQL, and ADBC then handles executing that plan on whichever backend it is connecting to. But ibis doesn't have to provide SQL dialect specific to the query engine, but only the SQL dialect that ADBC supports.

[lidavidm]

Ahhhh, we should clarify that.

ADBC doesn't fix a SQL dialect. It just passes the SQL through to the backend. (So it's not like ODBC in that regard, and more like JDBC.) I consider Substrait to be the solution to that problem.

[lostmygithubaccount]

Created an issue, we should look to explain this in conceptual documentation: #6618.