Define abstract queries in Ibis and execute them

[omriel1]

Hi! I posted this question in stackoverflow as well but not sure what you prefer:)

My question is whether it is possible to define "abstract" queries in Ibis against a pre-defined schema and, given a datasource with the matching schema, execute this query?

Ideally, I want to define schemas and abstract queries for them. Then, at a later stage, choose the backend, and finally, execute the queries.

Here's a concrete example:

import ibis
import pandas as pd

customers_schema = ibis.table(
    [("CustomerId", int),
    ("Name", int),
    ("Age", int)]
, name="customers")
abstract_query = customers_schema.filter(customers_schema.Age >= 25)

print(abstract_query) # No problem here

# given actual data
customers_df = pd.DataFrame(
    {"CustomerId": [1, 2, 3],
     "Name": ["john", "jane", "maya"],
     "Age": [28, 35, 22]
     }
)
customers = ibis.memtable(customers_df, name="customers")

# possible to execute abstract_query against this data?

I guess I could use Substrait, but it means currently I can only execute via DuckDB or Acero. Ideally I'd want to use any of the Ibis implemented backends.

Thank you!

[cpcloud]

You should be able to create a connection and then pass an unbound expression to that connection's execute method:

con = ibis.${whatever}.connect(...)

expr = build_query(table1, table2)

con.execute(expr)

Did I understand your question correctly?

[omriel1]

Thank you for the quick answer!
I guess for pandas it is not yet implemented?

customers_schema = ibis.table(
    [("CustomerId", int),
    ("Name", int),
    ("Age", int)]
, name="customers")
abstract_query = customers_schema.filter(customers_schema.Age >= 25)

# given actual data
customers_df = pd.DataFrame(
    {
        "CustomerId": [1, 2, 3],
        "Name": ["john", "jane", "maya"],
        "Age": [28, 35, 22]
     }
)
conn = ibis.pandas.connect({"customers": customers_df})
t = conn.table("customers")

print(t.execute(abstract_query))

And I get:

        if limit != 'default' and limit is not None:
>           raise ValueError(
                'limit parameter to execute is not yet implemented in the '
                'pandas backend'
            )
E           ValueError: limit parameter to execute is not yet implemented in the pandas backend

[omriel1]

and not in duckdb as well? or I'm missing something? sorry for bothering, but I could not find examples

conn = ibis.duckdb.connect()
t = conn.read_in_memory(customers_df, table_name="customers")
print(t.execute(abstract_query))

>> IbisError('Expression contains unbound tables and therefore cannot be executed. Use ibis.<backend>.execute(expr) or assign a backend instance to `ibis.options.default_backend`.')

But could not solve that either

[cpcloud]

I think memtables may be confusing understanding here. Let's stick with named tables inside a database, just for the sake of simplicity.

Here's an example using DuckDB and abstract expressions:

import ibis

# 1. create abstract expressions without a backend
t = ibis.table(dict(key="string", value="float"), name="t")  # the name matters here
expr = t.group_by("key").agg(sum_value=t.value.sum())

# 2. create some data that matches the schemas of the leaf tables involved
con = ibis.duckdb.connect()
with con.begin() as c:
    c.exec_driver_sql("""
        create or replace table t as
        select unnest(string_split('aaaaaabbbb', '')) as key, unnest(range(10)) as value;
    """)

# 3. execute the expressions against that data
df = con.execute(expr)

Running this in IPython:

In [1]: import ibis

In [2]: t = ibis.table(dict(key="string", value="float"), name="t")  # the name matters here

In [3]: expr = t.group_by("key").agg(sum_value=t.value.sum())

In [4]: con = ibis.duckdb.connect()

In [5]: with con.begin() as c:
   ...:     c.exec_driver_sql(
   ...:         """
   ...:         create or replace table t as
   ...:         select unnest(string_split('aaaaaabbbb', '')) as key, unnest(range(10)) as value;
   ...:     """
   ...:     )

In [6]: df = con.execute(expr)

In [7]: df
Out[7]:
  key  sum_value
0   a       15.0
1   b       30.0

[omriel1]

Thank you, it's very helpful!