how to change the active database

[djouallah]

I am using this code to read delta table from fabric onelake and expose it as an ibis connection

import ibis
from glob import glob
access_token = notebookutils.credentials.getToken('storage')
storage_options=     {"bearer_token": access_token, "use_fabric_endpoint": "true"}
def attach_lakehouse(ws,catalog): 
    fabric = ibis.duckdb.connect(catalog)
    list_tables = glob("/lakehouse/default/Tables/*/*/", recursive=True)
    for table_path in list_tables:
        parts = table_path.strip("/").split("/")
        db    = parts[-2]
        table = parts[-1]
        fabric.create_database(db,force=True)
        fabric.raw_sql(f'USE {db} ')
        fabric.read_delta(f'abfss://{ws}@onelake.dfs.fabric.microsoft.com/{catalog}.Lakehouse/Tables/{db}/{table}', table_name=table,storage_options = storage_options)
    return fabric
attach_lakehouse(ws,catalog)

my problem is, I don't know how to tell ibis to create those tables in the database db, currently it goes to main by default ?

[ncclementi]

ibis.duckdb.connect() takes as input the database, what you are passing there as catalog, should be the database you want to connect to.

What is catalog for you in the call attach_lakehouse(ws,catalog)?

[djouallah]

in onelake, we have three naming convention, catalog, database then table

[djouallah]

see more details, I want the table to be under dbo and ny not main , notice, catalog and database works as expected, the only issue is how to create the table under the correct location, catalog.database

[gforsyth]

When you use any read_* method, it isn't going to create a persistent table (this is true across all backends) -- it either creates a temporary table or a temporary view (for DuckDB, it's a temp view).

The view is going to be created in the database main -- we could probably add an option to target where the view is created, but to persist the table elsewhere, you can pass the view object to create_table and specify which database you want to persist the table in, e.g.

>>> import ibis

>>> con = ibis.duckdb.connect()

>>> con.list_databases()
['information_schema', 'main', 'pg_catalog']

>>> con.create_database("new")

>>> con.list_databases()
['information_schema', 'main', 'new', 'pg_catalog']

>>> # load data from wherever, which creates a view in `main`

>>> t = con.read_parquet("ci/ibis-testing-data/parquet/awards_players.parquet")

>>> # now persist that data to a table in a different `database`

>>> con.create_table("awards", t, database="new")
DatabaseTable: memory.new.awards
  playerID string
  awardID  string
  yearID   int64
  lgID     string
  tie      string
  notes    string

>>> con.list_tables(database="new")
['awards']

[djouallah]

I see, thanks, but i don't want to persist the data, I want to keep it as a view

[ncclementi]

You can use

con.create_view("awards", t, database="new")

it'll still show in con.list_tables(database="new") because at the moment it list everything, tables and views. There is a PR in the works to separate the listing of views vs tables though.

[djouallah]

thanks, that works !!!