Using ibis as a query builder without a backend

[sh-rp]

Hey ibis team, dlthub developer here! With some delay I'm currently working on integrating ibis into our new dataset access feature. My current prototype basically has some glue code that creates an ibis backend from connection credentials that we know anyway, and the user can then use that backend/connection to do regular ibis stuff.

I was wondering however if it is at all possible to use ibis just as a query builder. By that I mean constructing a query (in the form of a sqlglot-ast or some kind of other sqlglot object) with ibis expressions and then getting the sql statement in the dialect of your choosing. From what I can tell from reading your code, you can only build expressions tied to a backend and part of building an expression is also that reflection of the actual materialized schema on the database is done. I am thinking more of the way sqlalchemy works where you can build a query and then execute it at the end. Currently it look like I'd have to build my own backend to be able to achieve this, is that correct?

Some pseudocode of what I want I'd like to do to make it a bit clearer:

db = query_builder_backed()
# the two lines below should build an expression without connection to any database
t = db.table("penguins")
t.group_by("species", "island").agg(count=t.count()).order_by("count")
# the line below should return a sql statement for postgres which I can then use for my own pg connection
t.to_sql(dialect="postgres")

[cpcloud]

Thanks for asking!

This is definitely a core use case of ibis, that is, using it to build expressions without a backend.

The primary way you do this is by constructing what are called unbound tables, using the ibis.table function.

You can then compile the expression using ibis.to_sql, passing the dialect argument to compile the expression to a different SQL dialect.

Usually most folks want to execute these expressions in some way, and the way to do that is with one of the to_* methods like con.to_pandas. This method accepts both unbound expressions and bound expressions. Similarly for con.to_pyarrow.

The primary thing you as the user needs to provide to ibis.table is the schema.

[sh-rp]

Hey @cpcloud! Thanks for the quick and comprehensive answer. That is indeed pretty nice and exactly what I need :) Out of curiousity I have one followup question: In the code base I saw quite a bit of code that seems to be specifically taylored to certain databases / datasources. Is this to support sql / expression features that are not supported natively by sqlglot?

[gforsyth]

Hey @sh-rp -- sometimes it's to support things that sqlglot doesn't explicitly support, but that are easy enough to spell. A lot of our backend-specific code is also aimed at trying to get consistent results, so standardizing on things like a 0-indexed day-of-week to match Python semantics

[sh-rp]

Alright thanks!

[sh-rp]

I have one additional question: Is there a way to select a default database (database as defined in the ibis world) for an ibis backend? Similar to how you can set the default schema on postgres with "SET search_path TO myschema;"? Or is it advisable to always provide the database name when listing and retrieving tables?

[gforsyth]

Hey @sh-rp -- setting the database you connect to can almost always be handled in the connect call. So something like

con = ibis.postgres.connect(..., database="default_database_when_listing")

For the local options like DuckDB, that's more a function of which duckdb file you pass to connect, but for the "traditional" remote engines, the way to specify the default is in the connection string.

[sh-rp]

Hey @gforsyth, thanks! I saw that but since it works a bit different for each connection and most table constructors take database as an argument, i thought maybe there would be a unified interface to set a database for an SQLBackend that then gets applied when getting or saving a table. But I think we will let the users to that for now.

[sh-rp]

Apart from that, if you're interested you can follow my integration work in these two approaches, which I hope will be on the devel branch of dlt in the foreseable future :)

Ibis integration 1:
These are basically some helpers to create an ibis backend from credentials already known from a dlt destination configuration: dlt-hub/dlt#2004.

Ibis integration 2:
Here I am only using the query building part of Ibis to access what we call datasets (databases in your world + a known dlt schema with additional hints) but the actual execution of the query is done via our own dbapi cursor wrappers and arrow / pandas / python tuple iterators. This will probably support all of our sql based destinations except for dremio which does not seem to have a sqlglot dialect, but maybe another dialect will partly work for that: dlt-hub/dlt#2046

[sh-rp]

I have another question about the Expressions. Given a couple of unbound tables with a schema I provided and one expression deriving from those tables, let's say a Join, a column subselection, a renaming of one columns and a limit, how can I trace which of the resulting columns came from which original table and column name? I saw that it probably is possible to traverse the Expression tree, but I have not found a fairly straightforward way to do this. Is this possible at the moment?

[cpcloud]

We have a collection of traversal utilities in ibis.common.graph. The main entry points are any public functions e.g., traverse, bfs, dfs and the Graph class.

Note that the module itself is not public and so its API cannot be relied upon for stability across major releases.

That said, the ibis.common.graph API almost never changes in practice.

[sh-rp]

@cpcloud, thanks for pointing me in this direction! I'll have to do a bit more digging on how to achieve what I need then :) If we use the graph helpers, we will surely have tests in place to be alerted if this stuff changes. You probably know this anyway, but ibis is a pretty awesome project I must say :)

[sh-rp]

@cpcloud & @gforsyth just to let you know, we have now two ibis integrations in dlt:

1. A handover of credentials to an ibis backend for easy transition into ibis world from dlt: https://dlthub.com/docs/general-usage/dataset-access/ibis-backend

2. We make use of ibis expressions for data access within dlt: https://dlthub.com/docs/general-usage/dataset-access/dataset#modifying-queries-with-ibis-expressions