Building a remote lazy DF with client- and server-side filtering

[YStrauch]

I am trying to achieve a remote lazy DF with Ibis and I am super close!

This is what I have so far.
The clients request data. The server already does some pre-processing, such as filtering and joins. This bit is important: I don't want clients to have full access to the table! So I keep track of the resources that were created and create a UUID for this specific resource.

resource_registry: dict[str, ibis.Table] = {}

@app.get("/data")
def get_data():
    """
    Create a filtered dataset and register it lazily.
    """
    table = con.table("house-price")
    # Some server-side pre-processing
    df = table.filter(table.mainroad == "yes")
    # Generate a random ID for the client
    dataset_id = str(uuid.uuid4())
    # Register the lazy DF so we can fetch it later
    resource_registry[dataset_id] = df

    # Return schema + ID to client
    return {
        "dataset_id": dataset_id,
        "schema": {col: str(dtype) for col, dtype in df.schema().items()},
    }

So the client only knows the random ID + schema - enough to create an unbound table on the client.
Imagine clients do further processing there, maybe even join multiple resources together (all fetched in the same way).
I then serialize their client-side pipeline to SQL and send it back to the server. (I would have loved to use substrait but didn't find a way to de-serialize it server-side with a catalog, so SQL will do for now).

The client-side SQL looks something like

SELECT * FROM "4c9c66a6-6223-48b6-9b4c-e1b0c4c1f3ce" AS "t0" WHERE  "t0"."price" > 11000000"

Lastly, I "just" need to execute the SQL in the context of the table registry I built above.

This is what I have so far:

# Parse the SQL plan to unbound expression
client_expr_unbound = ibis.parse_sql(
    sql_from_client,
    catalog={
        dataset_id: expr.schema() for dataset_id, expr in resource_registry.items()
    }
)

# This fails:
con.execute(client_expr_unbound)

The con.execute raises a catalog error duckdb.duckdb.CatalogException: Catalog Error: Table with name 4c9c66a6-6223-48b6-9b4c-e1b0c4c1f3ce does not exist!.
Which makes sense, the frontend SQL is built using the UUID as a table name, which of course is not mapped back to the table.

So I need to execute with a select catalog of bound expressions (I have them in resource_registry).
Can I create a temporary catalog that only contains the bound expressions from above?

Or, stepping back from this implementation, how else can I achieve my goal?

Bear in mind that I want clients to be able to join multiple pipelines together - anything in resource_registry is "sanctioned" for use. So it wouldn't be enough to just apply the SQL to one of the entries of the registry.
I also can't send the actual server-side query to the client for security reasons - or else I would give them full access to the DB!

[YStrauch]

One way to solve this would be to use views I guess.

Somehow this all feels hacky - having SQL to communicate plans, adding views (no idea how databases implement views - does that already start some internal optimizations?)

I now found a doc that explains how to execute substrait server-side, but the API does not allow me to define my own catalog with whitelisted view IDs. I feel like I'm missing a part somewhere

[Sara25776]

We can use alias property to achieve this

import ibis
from ibis import _
ibis.options.interactive = True
import uuid

# Need to remove '-' as it makes table name invalid, also prefix with some string as few uuid fails. For ex 57e5f11339d04ff683f5a1da500dafc0
dataset_id = str(uuid.uuid4()).replace("-", "") 
dataset_id = f"client_{dataset_id}"

table = ibis.examples.penguins.fetch(table_name="penguins")
df = table.filter(table.flipper_length_mm == 181)

client_query = f"select * from {dataset_id} where species='Adelie'"
df = df.alias(dataset_id).sql(client_query)
df

[YStrauch]

AHA! I didn't see the alias operator. (Which internally creates a view but that is apparently subject to be changed).
Thank you very much, this is very helpful!