How can I test if two Ibis expressions are the same?

[binste]

Some context for those familiar with dbt

I'm thinking about ways to make https://github.com/binste/dbt-ibis faster. In a larger dbt deployment with multiple complex Ibis "models", I've noticed that the compilation of the Ibis expressions to SQL can take a while. A complex expression might take ~2-3 seconds. Currently, dbt-ibis just compiles all Ibis expressions to SQL even if the expression has not changed and hence the SQL it generates is the same as is already saved. I could store some information, such as hashes, across runs of dbt-ibis to be more intelligent in which Ibis expressions need to be compiled to SQL.

Question

I'm looking for a way to test if two Ibis expressions will produce the exact same SQL code if they would be compiled, without having to actually compile them. Did someone already try this before? Any ideas are welcome! :) What I thought about so far:

• I can call hash on the expressions and compare that but if I restart the Python interpreter I get different hashes for the same expression and so I don't think this will work. Maybe if I'd use a different hash algorithm from hashlib but do they work for arbitrarily complex Ibis expressions and how would I serialize it first to a bytes representation?
• Follow-up to the above, can I pickle Ibis expressions? Probably depends on what is in the expression, right?
• I could compare/hash the repr of an Ibis expression.

Here some example code to play around with:

import ibis
import datetime as dt
import ibis.expr.types as ir


def model() -> ir.Table:
    # Exact code here does not matter. This just represents a function which returns any arbitrary Ibis Table expression.
    t = ibis.table({"a": "string", "b": "int"}, name="table_1")
    t1 = t.mutate(new=t.a.cast("int") + t.b, max=ibis.greatest(t.a.cast("int"), t.b))
    t2 = t1.filter(t1.a == "foo").filter(
        t1.a.cast("timestamp") == dt.datetime(2017, 1, 1)
    )
    t2 = t2.select(["a", "b"])
    return t2


model_1 = model()
model_2 = model()

hash(model_1) == hash(model_2)  # True
repr(model_1) == repr(model_2)  # True
model_1 == model_2  # False

[lostmygithubaccount]

there is exprA.equals(exprB):

[ins] In [1]: import ibis

[ins] In [2]: t = ibis.examples.penguins.fetch()

[ins] In [3]: a = t.group_by(["species", "island"]).agg(count=t.count()).order_by("count")

[ins] In [4]: b = t.group_by(["species", "island"]).agg(count=t.count()).order_by("count")

[ins] In [5]: a.equals(b)
Out[5]: True

[ins] In [6]: a.equals(t)
Out[6]: False

[lostmygithubaccount]

I've noticed that the compilation of the Ibis expressions to SQL can take a while. A complex expression might take ~2-3 seconds

also are you on main or one of the prereleases? there has been an issue with slow to generate SQL noticed recently: #8484 (comment)

[binste]

I'm using 8.0.0 but thanks for flagging it!

[saschahofmann]

Also see #8446

[binste]

Thank you both! I'll need to up my search game, I didn't find equals and #8446 😅

I'm looking for a way to test equality of expressions which are generated in separate Python processes at separate times, i.e. across Python runs. This was not really clear in my original question and the example code is confusing.

Here a more detailed example. This is some shared code across Python runs:

import ibis
import datetime as dt
import ibis.expr.types as ir


def generate_ibis_expression() -> ir.Table:
    # Exact code here does not matter. This just represents a function
    # which returns any arbitrary Ibis Table expression.
    t = ibis.table({"a": "string", "b": "int"}, name="table_1")
    t1 = t.mutate(new=t.a.cast("int") + t.b, max=ibis.greatest(t.a.cast("int"), t.b))
    t2 = t1.filter(t1.a == "foo").filter(
        t1.a.cast("timestamp") == dt.datetime(2017, 1, 1)
    )
    t2 = t2.select(["a", "b"])
    return t2


def store_some_representation(ibis_expression: ir.Table) -> None:
    # TODO: Figure out how to store a representation of an Ibis expression on disk
    # which can be loaded later and be compared to another one.
    
    with open("expr_representation.txt", "w") as f:
        f.write(hash(ibis_expression))


def compare_expression_to_stored_representation(ibis_expression: ir.Table) -> bool:
    with open("expr_representation.txt", "r") as f:
        stored_hash = f.read()

    # This does not work as hashes are not stable across runs but just to illustrate
    # what I mean:
    return hash(ibis_expression) == stored_hash

The first time the script runs, we just generate the Ibis expression and store some representation (or pickle the whole expression?):

ibis_expression = generate_ibis_expression()
store_some_representation(ibis_expression)

The second time it runs, we do this:

ibis_expression = generate_ibis_expression()
compare_expression_to_stored_representation(ibis_expression)

As it's across runs, hashes won't be stable and it seems to me that .equals uses hash under the hood in https://github.com/ibis-project/ibis/blob/main/ibis/common/grounds.py#L222.

Pickling failed for me in an Ibis expression where I'm referencing a built-in SQL function as described here. I'll need to do some more experiments with it. Any inputs are welcome but of course I fully understand if this is a special case you don't want to think about right now :)

[cpcloud]

Right now there are two options:

1. Pickling, which doesn't work in all cases as you've found.
2. ibis-substrait which doesn't yet support Ibis 9.0.

We've had a few requests for something like this, it might be time to gather the requests and use cases and see if there's something we can implement to support those use cases.

[cpcloud]

It also seems like the issue here isn't necessarily a lack of the ability to freeze expressions across a process, but the speed of compilation.

@binste Is there some acceptable speed at which recompilation wouldn't be noticeable?

[binste]

Yeah I'd agree, if it's fast enough this becomes less relevant. As discussed in #9111, the compilation to SQL already got faster with the move to SQLGlot but instead, some Ibis expressions take longer to be "assembled" before they are being compiled.

It's tough to put a number on it. Due to the lack in dbt of a good plugin system right now for additional languages, dbt-ibis always has to run the Ibis code for all Ibis models and compile to SQL. There might be some movement on the topic. Due to this, even if it's "just" 200ms per query, if you have 50 Ibis dbt models you wait an additional 10 seconds for your dbt run command to start.

A side remark on performance for smaller datasets (fully aware that this is probably not the primary use case for Ibis but thought you might be interested in the experience): As a user, I'm happy with using Ibis for ad-hoc analyses, data processing on large amounts of data, or just in general when overheads of a few hundred milliseconds do not matter. However, I've noticed that for use cases where milliseconds matter, such as when processing smaller datasets in the backend of a web application, I had to move away from Ibis as the overhead was too large compared to plain Pandas or DuckDB. Simply adding 2 columns can incur an overhead of 60ms. Pandas can do it <1ms.