Catalog creation - DuckDB

[vmsaipreeth]

Hi Team,

is there a way/workaround to create catalogs for DuckDB backend?
We noticed that, for duckdb.Backend, we have create_database, create_table, create_view. However, we do not see any support for creating catalogs.

To give more context:
Here is our requirement or what we are trying to do:

We have two individual backends: Trino and DuckDB

Let’s assume we have a Trino executable/compatible SQL(following trino’s catalog/schema/tables structure) as below:

SELECT * FROM catalog_1.schema_1.table_1 

We would like to have the same SQL to be executed for DuckDB backend as well, given we have DuckDB setup. We are able to achieve this using the following code:

con_duckdb = ibis.duckdb.connect(“catalog_1.duckdb”)
# loading the data
test_data = con_duckdb.read_parquet(“data.parquet”)
# creating database in duckdb : similar to a trino structure
con_duckdb.create_database(“schema_1”)
# creating a table under catalog and database 
con_duckdb.create_table(name=“table_1”, obj=test_data, database=“catalog_1.schema_1”)
# above steps complete the DuckDB setup based on our Trino SQL

# running the Trino compatible SQL on DuckDB
con_duckdb.sql(“SELECT * FROM catalog_1.schema_1.table_1”)

However, there may be a case where we have multiple catalogs in the SQL query:

SELECT
  # select columns
  FROM catalog_1.schema_1.table_1 as one
    INNER JOIN catalog_2.schema_2.table_2 as two
  WHERE
    # where condition 

In order for us to use the above SQL on DuckDB, we need the support for creating catalogs in DuckDB.

Please feel free to suggest if there is any better/alternative way to achieve this. TIA!

[gforsyth]

DuckDB supports the notion of catalogs but you can't have multiple catalogs in a given DuckDB file -- each file (or resource) corresponds to an individual catalog.

In DuckDB SQL you can do ATTACH IF NOT EXISTS 'catalog_1.db' as catalog_1; and that will create a new DuckDB file called catalog_1.db if it doesn't already exists and make it available under the catalog_1 identifier.

We wouldn't expose this as create_catalog because the semantics are a bit different from other systems that support catalogs -- e.g. if you connect to file1.db as a session, and then ATTACH IF NOT EXISTS file2.db as file2;, that association only lasts as long as the current session, so you would have to re-run the ATTACH command the next time you wanted to run a query that relied on both of those catalogs being available.

We do have the attach method on the DuckDB backend, although I don't believe it exposes the IF NOT EXISTS functionality at the moment.

Reference docs are here: https://duckdb.org/docs/sql/statements/attach.html