How to read/write tables from/to Microsoft Fabric?

[csubhodeep]

I am trying to interact with Microsoft Fabric using ibis.

For starters I only have a SQL endpoint URL as Fabric does not allow plain SQL user-id/password based authentication like a normal SQL Server.

For searching for a solution, I came across this article and was able to read a small table using a plain pyodbc connection.

Is there a way to use ibis to interact with Fabric using the mssql backend?

I have also gone through the comments from this #7317 PR. But couldn't make it work.

It would be nice if anyone could provide some help or suggest a workaround. Thanks !

[csubhodeep]

I have tried the following, from the article

import struct
from itertools import chain, repeat

import ibis
import ibis.backends.mssql
import pyodbc
from azure.identity import InteractiveBrowserCredential

credential = InteractiveBrowserCredential()

sql_endpoint = "XXX.datawarehouse.fabric.microsoft.com" 
database = "<NAME OF WAREHOUSE>"
user = "[email protected]"
port = 1433
driver = "{ODBC Driver 18 for SQL Server}"
connection_string = f"Driver={driver};Server={sql_endpoint},{port};Database={database};Encrypt=Yes;TrustServerCertificate=No"

token_object = credential.get_token("https://database.windows.net//.default") 
token_as_bytes = bytes(token_object.token, "UTF-8") 
encoded_bytes = bytes(chain.from_iterable(zip(token_as_bytes, repeat(0)))) 
token_bytes = struct.pack("<i", len(encoded_bytes)) + encoded_bytes 
attrs_before = {1256: token_bytes}  


connection = pyodbc.connect(connection_string, attrs_before=attrs_before)

cursor = connection.cursor()
cursor.execute("SELECT TOP(10) DayOfMonth, DaySuffix FROM dbo.Date")
rows = cursor.fetchall()
print(rows) # this prints the right results

Now, while looking at the internals I saw this line, and I was tempted to try out the below hack.

ibis_conn = ibis.backends.mssql.Backend()

ibis_conn.con = connection

ibis_conn.list_catalogs() # gives some legitimate outputs, see below

['<NAME OF WAREHOUSE>', '<NAME OF LAKEHOUSE>', 'master']

Upon, doing

ibis_conn.list_databases()

I got the famous ProgrammingError

{
	"name": "ProgrammingError",
	"message": "('42S02', \"[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name '<NAME OF WAREHOUSE>.information_schema.schemata'. (208) (SQLExecDirectW)\")",
	"stack": "---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
Cell In[16], line 1
----> 1 conn.list_databases()

File /workspaces/project/.venv/lib/python3.11/site-packages/ibis/backends/mssql/__init__.py:432, in Backend.list_databases(self, like, catalog)
    422 def list_databases(
    423     self, like: str | None = None, catalog: str | None = None
    424 ) -> list[str]:
    425     query = sg.select(C.schema_name).from_(
    426         sg.table(
    427             \"schemata\",
   (...)
    430         )
    431     )
--> 432     with self._safe_raw_sql(query) as cur:
    433         results = list(map(itemgetter(0), cur.fetchall()))
    434     return self._filter_with_like(results, like=like)

File /usr/local/lib/python3.11/contextlib.py:137, in _GeneratorContextManager.__enter__(self)
    135 del self.args, self.kwds, self.func
    136 try:
--> 137     return next(self.gen)
    138 except StopIteration:
    139     raise RuntimeError(\"generator didn't yield\") from None

File /workspaces/project/.venv/lib/python3.11/site-packages/ibis/backends/mssql/__init__.py:269, in Backend._safe_raw_sql(self, query, *args, **kwargs)
    266     query = query.sql(self.dialect)
    268 with self.begin() as cur:
--> 269     cur.execute(query, *args, **kwargs)
    270     yield cur

ProgrammingError: ('42S02', \"[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name '<NAME OF WAREHOUSE>.information_schema.schemata'. (208) (SQLExecDirectW)\")"
}

I get almost the same error when I invoke the list_tables method.

When I try

t = ibis_conn.table(name="Date", database=database, schema="dbo")

I get

{
	"name": "ProgrammingError",
	"message": "('42S02', \"[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name '<NAME OF WAREHOUSE>.information_schema.columns'. (208) (SQLExecDirectW)\")",
	"stack": "---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
Cell In[27], line 1
----> 1 t = ibis_conn.table(name=\"Date\", database=database, schema=\"dbo\")

File /workspaces/project/.venv/lib/python3.11/site-packages/ibis/backends/sql/__init__.py:137, in SQLBackend.table(self, name, schema, database)
    134     catalog = table_loc.catalog or None
    135     database = table_loc.db or None
--> 137 table_schema = self.get_schema(name, catalog=catalog, database=database)
    138 return ops.DatabaseTable(
    139     name,
    140     schema=table_schema,
    141     source=self,
    142     namespace=ops.Namespace(catalog=catalog, database=database),
    143 ).to_expr()

File /workspaces/project/.venv/lib/python3.11/site-packages/ibis/backends/mssql/__init__.py:163, in Backend.get_schema(self, name, catalog, database)
    141     conditions.append(sg.column(\"table_schema\").eq(sge.convert(database)))
    143 query = (
    144     sg.select(
    145         \"column_name\",
   (...)
    160     .order_by(\"ordinal_position\")
    161 )
--> 163 with self._safe_raw_sql(query) as cur:
    164     meta = cur.fetchall()
    166 if not meta:

File /usr/local/lib/python3.11/contextlib.py:137, in _GeneratorContextManager.__enter__(self)
    135 del self.args, self.kwds, self.func
    136 try:
--> 137     return next(self.gen)
    138 except StopIteration:
    139     raise RuntimeError(\"generator didn't yield\") from None

File /workspaces/project/.venv/lib/python3.11/site-packages/ibis/backends/mssql/__init__.py:269, in Backend._safe_raw_sql(self, query, *args, **kwargs)
    266     query = query.sql(self.dialect)
    268 with self.begin() as cur:
--> 269     cur.execute(query, *args, **kwargs)
    270     yield cur

ProgrammingError: ('42S02', \"[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name '<NAME OF WAREHOUSE>.information_schema.columns'. (208) (SQLExecDirectW)\")"
}

[gforsyth]

Hi @csubhodeep -- I made a few fixes recently to handling catalog + database + table creation in MSSQL -- are you using Ibis 9.1?

[csubhodeep]

are you using Ibis 9.1?

Yes !

[csubhodeep]

@gforsyth is this rather a bug?

[gforsyth]

Hi @csubhodeep -- you can certainly open an issue. I would classify this more as a feature-request for Fabric support.

[csubhodeep]

Issue opened at #9494

I have added a temporary hack to read tables here