SQL in Ibis

Let's think about this statement a bit.

tbl_duckdb.pipe(counter, 'date', 'state')

Under the hood, in order to execute this command, Ibis must generate and run SQL code. If you want, you can als see this SQL code by calling the ibis.to_sql method on the result.

ibis.to_sql(tbl_duckdb.pipe(counter, 'date', 'state'))

Here is what the SQL looked like on our laptop.

SELECT
  *
FROM (
  SELECT
    "t0"."date",
    "t0"."state",
    SUM("t0"."births") AS "sum",
    AVG("t0"."births") AS "mean"
  FROM "ibis_read_csv_qqgyzooif5exlpgcmykkyzk2ga" AS "t0"
  GROUP BY
    1,
    2
) AS "t1"
ORDER BY
  "t1"."date" ASC,
  "t1"."state" ASC

However, if you're using a SQL backend, you can also execute this SQL code directly.

sql_statement = """
SELECT
  *
FROM (
  SELECT
    "t0"."date",
    "t0"."state",
    SUM("t0"."births") AS "sum",
    AVG("t0"."births") AS "mean"
  FROM "tbl" AS "t0"
  GROUP BY
    1,
    2
) AS "t1"
ORDER BY
  "t1"."date" ASC,
  "t1"."state" ASC
"""

tbl_duckdb.alias("tbl").sql(sql_statement)

This code will run, but pay attention to the alias that we're adding to the table before running the SQL. This is because the SQL code references the table as tbl.

Note that this can be a powerful feature when you are dealing with a legacy backend that already implements a bunch of SQL code. You can use Ibis to run the preprocessing SQL first and then continue with the Ibis API from Python.