How can superset connect Azure Synapse Data Warehouse(not build-in)?

[kachely]

Bug description

I'd like to use superset to connect with Azure Synapse Data Warehouse.(Not Azure SQL Server) I can use the pyodbc python driver to connect it successfully. But I cannot connect it using superset connection string referred here https://superset.apache.org/docs/databases/sql-server

Do we support to connect Azure Data Warehouse? What is the connection string for that? In my understanding, if odbc driver supports the connection, superset should support too. Do we have any other configution here?

How to reproduce the bug

Below are the connection which I am using.

python odbc driver:

import pyodbc

pyodbc.connect()
DRIVER={ODBC Driver 18 for SQL Server};SERVER=myserver.sql.azuresynapse.net;PORT=1433;DATABASE=mydb;UID=user;PWD=password

superset connection string: mssql+pyodbc:///?odbc_connect=DRIVER={ODBC+Driver+18+for+SQL+Server};SERVER=tcp:myserver.sql.azuresynapse.net,1433;DATABASE=mydb;UID=user;PWD=password with error:

SupersetError(message="(pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]111214;An attempt to complete a transaction has failed. No corresponding transaction found. (111214) (SQLEndTran)')\n(Background on this error at: http://sqlalche.me/e/13/f405)", error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Azure Synapse', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})] [546536][139917057312512

Screenshots/recordings

No response

Superset version

3.0.3

Python version

3.9

Node version

16

Browser

Not applicable

Additional context

No response

Checklist

• [X] I have searched Superset docs and Slack and didn't find a solution to my problem.
• [X] I have searched the GitHub issue tracker and didn't find a similar bug report.
• [X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[pamelascr]

Hello @kachely, did you find any solution to this problem?

I am having a similar issue with Azure Synapse Data Warehouse.

If yes, please share it with me. Stuck in this error for a couple of days.

[shakeelansari63]

Did some digging and found this Stackoverflow question where they have same issue.

From this I understood, the problem is with Azure Synapse Data Warehouse as it does not support Transactions. SqlAlchemy has default transaction mode, meaning it will auto begin transactions and commit/rollback internally.

Since, Superset uses SqlAlchemy to build database connections, the issue is observed in Superset as well.

Solution

The solution is also provided in same Stackoverflow, but I will put it here for future reference.
It is suggested to use autocommit=true for Azure SQL Database for SqlAlchemy to work properly.

So your Connection String can look like this -

mssql+pyodbc://user:[email protected]:1433/mydb?driver=ODBC+Driver+18+for+SQL+Server&autocommit=True

or

mssql+pyodbc:///?odbc_connect=DRIVER={ODBC+Driver+18+for+SQL+Server};SERVER=tcp:myserver.sql.azuresynapse.net,1433;DATABASE=mydb;UID=user;PWD=password;Autocommit=true

Please try and let us know if it works for you

[pamelascr]

Hello, @shakeelansari63!

This solution also worked for me, but I needed to install the Pyodbc in my Superset image to work properly.

Thanks for the help 😄

[kachely]

Thank you so much @shakeelansari63 ! mssql+pyodbc://user:[email protected]:1433/mydb?driver=ODBC+Driver+18+for+SQL+Server&autocommit=True' This one works well for me!

[rusackas]

If this is all working, please consider adding some docs here: https://superset.apache.org/docs/databases/installing-database-drivers

We'd appreciate the contribution to help others who follow in your footsteps.

Pending that, should we call it case closed on this Issue?