question

RoohiyaDudekula-3399 avatar image
0 Votes"
RoohiyaDudekula-3399 asked YangChowmun-0538 commented

Does Azure Synapse Analytics have support for Python?



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?



azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RoohiyaDudekula-3399 avatar image
0 Votes"
RoohiyaDudekula-3399 answered YangChowmun-0538 commented

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!

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @RoohiyaDudekula-3399,

From the error message it looks like External table 'dbo' is not accessible because content of directory cannot be listed.

I'm able to query the external tables created in Azure Synapse from local.

From Azure Portal:

103802-image.png

From Local using VS Code:

103697-image.png

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


1 Vote 1 ·
image.png (76.6 KiB)
image.png (111.0 KiB)

Thank you so much!!

I hadn't included scoped credentials for my external tables initially.

It actually worked after I created a scoped credential and use that to create datasource and the external tables on Azure Synapse.

Thanks for your help again 😊

0 Votes 0 ·

Hello @RoohiyaDudekula-3399,

Glad to know that it helped.

If the above answers were helpful, click “Accept Answer” or “Up-Vote”, which might be beneficial to other community members reading this thread.

0 Votes 0 ·

@RoohiyaDudekula-3399

Can you share with the code you use the create the scoped credentials?
When you query the synapse database using python, the credentials you used for username and password is it the same as the one we set when setting up synapse workspace?

Thanks

0 Votes 0 ·
PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered YangChowmun-0538 commented

Hello @RoohiyaDudekula-3399,

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.

103424-image.png

Key Service Capabilities in Azure Synapse Analytics:

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

103405-image.png

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.


image.png (76.3 KiB)
image.png (118.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hey, added my comment as an answer below :)

0 Votes 0 ·

@PRADEEPCHEEKATLA-MSFT , I am getting the same issue. But I am not able to solve it. I am trying to query the synapse database from jupyter notebook.

Anw, do you have any suggestion on how we could compare the performance in between synapse and MySQL?

Thanks

0 Votes 0 ·