How to read/write tables from/to Microsoft Fabric? #9454
-
|
I am trying to interact with Microsoft Fabric using 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 Is there a way to use 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 ! |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 7 replies
-
|
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 resultsNow, 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 {
"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 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)\")"
} |
Beta Was this translation helpful? Give feedback.
-
|
Hi @csubhodeep -- I made a few fixes recently to handling catalog database table creation in MSSQL -- are you using Ibis 9.1? |
Beta Was this translation helpful? Give feedback.
-
Yes ! |
Beta Was this translation helpful? Give feedback.
-
|
@gforsyth is this rather a bug? |
Beta Was this translation helpful? Give feedback.
-
|
Hi @csubhodeep -- you can certainly open an issue. I would classify this more as a feature-request for Fabric support. |
Beta Was this translation helpful? Give feedback.
-
|
Issue opened at #9494 I have added a temporary hack to read tables here |
Beta Was this translation helpful? Give feedback.
Hi @csubhodeep -- you can certainly open an issue. I would classify this more as a feature-request for Fabric support.