Does/Could Ibis ingest substrait plans?

[jonmmease]

Apologies if this is discussed elsewhere (or already possible and I just missed it), but I'm wondering if it would be possible to input a substrait plan to build an Ibis expression. The use case I have in mind is wondering if there's a future where VegaFusion could generate substrait plans internally, build Ibis expressions from them, and then let Ibis handle the SQL generation for systems that don't support substrait.

I've been chatting with @jcrist about integrating Ibis with VegaFusion using VegaFusion's existing extension points in vega/vegafusion#355, but after learning more about substrait I'm wondering if this would be an alternative future integration path.

[gforsyth]

Hey @jonmmease -- we used to have a "decompiler" to go from substrait expressions to ibis expressions -- initially we built it because there were no substrait consumers so we had to be both a producer and a consumer to test anything.
But after there were consumers, there wasn't much of a reason for us to keep it around.
If it would be useful, it's something we can try to resurrect, but I'm not sure when exactly we might be able to make that happen.

Here's the PR where I ripped out the decompiler (which was already out-of-date and broken at time of removal): #692

[jonmmease]

Thanks for the helpful context @gforsyth! That all makes sense. I might poke around to see how things used to work.

In general, I think it would be nice for substrait adoption if there were a way to convert substrait plans to SQL for backends that don't support it yet. This would make substrait a really appealing target for higher-level systems that would otherwise need to deal with SQL dialects themselves. Does it make sense for Ibis to eventually play this role? Or are there reasons it's not a good fit?

[gforsyth]

I'm not sure, to be honest. Ibis is definitely one possible path -- but it's also its own abstraction layer and we don't always generate the cleanest SQL (although it's getting better).

I can definitely see a role for a direct Substrait -> SQL transpiler, although then you'd still be stuck with the SQL dialect problem.

[jonmmease]

I don't see clean SQL as a priority, or even SQL in and of itself. Just having an adaptor between substrait and the "before substrait" ecosystem of compute engines would be really useful.

[tokoko]

@gforsyth I'm also working on a feature in feast where I need to store user-provided functions in substrait and be able to run them on 1) non-python substrait consumers directly and also 2) bring them back to ibis to extend already existing ibis transformations. (described here). Are you against bringing back the decompiler for some reason or is the maintenance burden the only issue?

P.S. You mentioned "Substrait -> SQL transpiler" above... I remember reading on an ibis page that the plan was to drop internal representation and use substrait instead, which will make ibis itself substrait -> sql transpiler in a way. Is that no longer the plan?

[tokoko]

hey, thanks for the response. I think that was my wrong interpretation of this sentence "In the long-term, we aim for a standard query plan Intermediate Representation (IR) like Substrait to simplify this further." on ibis why page.

Don't want to be too much bother to you, but you're probably the best person to ask this :). Can you point me to some general areas where substrait and ibis IRs fundamentally diverge from one another? I'm trying to understand if/when both compiler and decompiler are in ibis-substrait, what are the types of queries for which the process of back-to-back compilation and decompilation won't work (correctly).

and a quick last question.. substrait was also mentioned in this discussion (by me and couple others) as a way to serialize ibis IR. Is that realistic? Or does a stable ibis IR mean that it will probably at some point have it's own serialized representation?

[gforsyth]

I think that was my wrong interpretation of this sentence "In the long-term, we aim for a standard query plan Intermediate Representation (IR) like Substrait to simplify this further." on ibis why page.

Ahh, gotcha. Yeah, that sentence can probably be rephrased to be clearer. Thanks for pointing that out!

Can you point me to some general areas where substrait and ibis IRs fundamentally diverge from one another?

They are largely compatible in terms of the general building blocks. I don't think Substrait has formalized its thoughts on semi- and anti-joins yet, although that isn't the most common pattern anyway.
I don't think there are any queries where we couldn't round-trip the queries, there's just some difficult bookkeeping to do along the way. Most of that is around how substrait handles subqueries and also the way that it flattens all colums of all tables into a single list. I don't think I ever got proper subquery reconstruction working, just because tracking which columns were associated with which subquery was (at least at the time) very difficult.

and a quick last question.. substrait was also mentioned in this ibis-project/ibis#7704 (by me and couple others) as a way to serialize ibis IR. Is that realistic? Or does a stable ibis IR mean that it will probably at some point have it's own serialized representation?

We're firm believers in open standards, and if there's a standard that can represent an ibis expression with good fidelity, we'll use that before rolling our own. I would be very surprised if we ended up with an ibis-specific serialized representation.

[jonmmease]

... but also we would need to write yet another transpiler to shove Substrait IR into the sqlglot IR to get to sql generation.

Huh, I hadn't thought of this approach. I wonder if this would make more sense as a standalone project for substrait -> sql conversion. @gforsyth, from your experience working with sqlglot and substrait, does this seem like a tractable approach in terms of the level of abstraction between the two?

This might be in interesting approach

[gforsyth]

Hey @jonmmease! Yeah, if I were going to write a Substrait -> SQL I would definitely reach for sqlglot.
I think the abstraction levels are comparable. I would recommend figuring out a topological sort for substrait plans (specifically with regards to subqueries) because things get gnarly when recursing and trying to manage column references.

[tokoko]

This might prove fruitless, but I recently started a side-project here trying to implement ibis API without either ibis IR or sqlglot IR, doing everything directly on substrait. While most of these stuff is already possible with a set of compilers and decompilers, it feels (at least to me) a waste having three different IRs with very similar goals (abstract backend-less IR) instead of just using one. I'm curious to see how far I can go with this.