FEATURE REQUEST: Support ducklake as an SQL Backend

[csubhodeep]

As a user, it would be nice if I could use ducklake as an SQL Backend.

[cpcloud]

Is something stopping you from doing this today?

[csubhodeep]

Honestly, before opening this request I did not really check it but to my amazement it JUST WORKS ! 😄 . I just did the following

import ibis

ibis.set_backend(ibis.duckdb.connect())

conn = ibis.get_backend()

conn.raw_sql("""
INSTALL ducklake;

ATTACH 'ducklake:metadata.ducklake' AS my_ducklake;
USE my_ducklake;
""")

t = ibis.memtable(
    {
      'a': [1, 2, 3],
      'b': ['x', 'y', 'z'],
      'c': [10.0, 20.0, 30.0],
    }
)

# creates a `parquet` file under `metadata.ducklake.files` as expected
conn.create_table('my_table', t)

Out:

DatabaseTable: my_ducklake.main.my_table
  a int64
  b string
  c float64

In:

t2 = conn.table('my_table')
t2 = t2.mutate(
    d=t2.c * 2,
    e=t2.a + t2.c,
)
conn.list_databases()

Out:

['information_schema', 'main', 'pg_catalog']

In:

conn.list_catalogs()

Out:

['__ducklake_metadata_my_ducklake', 'memory', 'my_ducklake', 'system', 'temp']

In:

conn.list_tables()

Out:

['ibis_pandas_memtable_snjhlv74qbh6lb7svltkmejupm', 'my_table']

In:

conn.create_table('my_table_transformed', t2)

Out:

DatabaseTable: my_ducklake.main.my_table_transformed
  a int64
  b string
  c float64
  d float64
  e float64

In:

conn.table('my_table_transformed').execute()

Out:

a	b	c	d	e
1	x	10.0	20.0	11.0
2	y	20.0	40.0	22.0
3	z	30.0	60.0	33.0

Is there a better way than using the raw_sql method? Or one may need to sub-class and roll their own SQLBackend by inheriting from ibis.backends.duckdb.Backend?

[cpcloud]

In theory, you don't need to do anything except install the ducklake extension, attach a ducklake db, and then USE that db.

There's a lot of advantage to having no additional code to make this work!