Add SQL Server as catalog db #892
Replies: 12 comments 4 replies
-
|
The first step would be to develop a DuckDB extension specifically for SQL Server (currently not available). https://duckdb.org/community_extensions/core_extensions |
Beta Was this translation helpful? Give feedback.
-
|
Or use a generic protocol, perhaps ODBC which is supported through a community extension (https://duckdb.org/community_extensions/extensions/nanodbc.html) ? |
Beta Was this translation helpful? Give feedback.
-
|
+1 for this or if there is anyway to make it work with the Fabric ecosystem. |
Beta Was this translation helpful? Give feedback.
-
|
nanodbc is already able to read from MS SQL / Azure SQL / SQL Server via ODBC as of today. |
Beta Was this translation helpful? Give feedback.
-
|
Nanodbc doesn’t work in Fabric notebooks or systems without odbc. It would be cool to port the official Go driver that meets TDS specs, not sure if there is a C driver that might work for this. |
Beta Was this translation helpful? Give feedback.
-
|
@jojayaro Fabric Spark Runtime come with MSSQL ODBC driver preinstalled ;) But I agree; it would be much nicer if a dedicated MSSQL extension would come "with batteries included" (bring everything that is needed to connect to MSSQL out-of-the-box). |
Beta Was this translation helpful? Give feedback.
-
|
Can confirm that Python notebooks in Fabric has odbc, I'm using it with SQLMesh. |
Beta Was this translation helpful? Give feedback.
-
I didn't plan for DuckLake at the time of writing the extension. There is nothing in the interface to stop ODBC from doing write operations; however, the current version of |
Beta Was this translation helpful? Give feedback.
-
Do you mind sharing how, I was not able to get it working. |
Beta Was this translation helpful? Give feedback.
-
Sure thing: import os
import pyodbc
server = os.getenv('SQL_ENDPOINT')
database = os.getenv('WAREHOUSE_NAME')
client_id = os.getenv('CLIENT_ID')
client_secret = os.getenv('CLIENT_SECRET')
connection_string = (
f"DRIVER={{ODBC Driver 18 for SQL Server}};"
f"SERVER={server};"
f"DATABASE={database};"
f"Authentication=ActiveDirectoryServicePrincipal;"
f"UID={client_id};"
f"PWD={client_secret};"
f"Encrypt=yes;"
f"TrustServerCertificate=no;"
)
with pyodbc.connect(connection_string) as conn:
print("✓ Connection successful!")
cursor = conn.cursor()
cursor.execute("SELECT @@VERSION as version")
row = cursor.fetchone()
print(f"✓ Query executed successfully!")
print(f" SQL Server Version: {row.version}") |
Beta Was this translation helpful? Give feedback.
-
|
Anyone know much work would it be to make a proper SQL Server extension? |
Beta Was this translation helpful? Give feedback.
-
|
FYI someone has recently developed a SQL Server extension that has no external dependencies: https://github.com/hugr-lab/mssql-extension This may be the way of getting ducklake working in a Azure/Fabric environment. |
Beta Was this translation helpful? Give feedback.
-
|
I'll tag @VGSML (author of mssql-extension) here for visibility |
Beta Was this translation helpful? Give feedback.
-
|
Using this new extension what would it take to make it work? |
Beta Was this translation helpful? Give feedback.
-
|
I will check |
Beta Was this translation helpful? Give feedback.
-
|
Thats great! |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
-
Most of our clients are using SQL Server and asking them to get a Postgres server for DuckLake would probably be met with resistance, even if that's the end goal.
It would be nice however if we could use SQL Server as the catalog backend.
Beta Was this translation helpful? Give feedback.
All reactions