Does Azure Synapse Analytics have support for Python?

Question

What I am trying to do?

Glue-Athena-like process.

1. Data in S3
2. AWS Glue (create metadata tables)
3. Tables can be queried using Athena via boto3 (python library)

Problem I am facing in Azure Cloud

~Trying to replicate the above process using Azure Synapse Analytics~

1. Data in linked Azure Storage container
2. Azure Data Factory (create external tables)
3. How to make T-SQL queries on the external tables using python?

Is there any python library to make T-SQL calls to the external tables created in Azure Synapse workspace?

Answer 1

Thank you so much for your reply!

But PySpark may not be the solution to what we are trying to do:

1. I am trying to use Python code locally in our project to connect and make API calls to Azure Synapse where I would like to query the external tables. I am trying to use the pyodbc to connect to Azure Synapse

import pyodbc

server = <azure synapse serverless endpoint>
database = <db_name>
username = <user_name>
password = <password>
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password+';') as conn:
with conn.cursor() as cursor:
cursor.execute("SELECT top 10 * FROM [dbo].[db_name]")
row = cursor.fetchone()
while row:
print (str(row[0]) + " " + str(row[1]))
row = cursor.fetchone()

But I am running into the issue:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]External table 'dbo' is not accessible because content of directory cannot be listed. (16561) (SQLExecDirectW)")

So, Is this possible to query the external tables created in Azure Synapse from local?
Can we grant 'select' permission on the external tables or on the database?

Point of doing this is to query and get the csv data in tabular form (within Azure Synapse) and use Python locally to use T-SQL commands to retrieve the data and pump it in our own database. But I do not see support to connect to Azure Synapse and query Azure Synapse external tables locally using Python.

Please advise on this? Thanks!

Answer 2

Hello @Roohiya Dudekula ,

Welcome to the Microsoft Q&A platform.

Yes, Azure Synapse Analytics supports python.

Azure Synapse Analytics is a limitless analytics service that brings together data integration, enterprise data warehousing and big data analytics. It gives you the freedom to query data on your terms, using either serverless or dedicated resources—at scale. Azure Synapse brings these worlds together with a unified experience to ingest, explore, prepare, manage and serve data for immediate BI and machine learning needs.

Key Service Capabilities in Azure Synapse Analytics:

Note: Only following magic commands are supported in Synapse pipeline : %%pyspark, %%spark, %%csharp, %%sql.

For more information, refer to What is Azure Synapse Analytics? and Magic commands in Synapse Analytics.

Hope this helps. Do let us know if you any further queries.

---------------------------------------------------------------------------

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.