Connect Local Machine to Synapse Lake Database using Python

Question

Hi,

I am trying to develop tooling for my use case, where I am using PySpark/Spark SQL on Synapse Analytics to create tables in Lake database. Sample code below:

spark.sql("CREATE TABLE abc AS SELECT * FROM view")

The table abc is created in the Lake Database called default in Synapse analytics, I am able to access this table while using Synapse Notebooks. However, I need to access it from my local machine using an Azure SDK or a connection method that would enable to me run queries retrieve results within my local environment.

Answer 1

Prerequisites

1. Set up your Azure Synapse Analytics workspace set up
2. You need to have the necessary permissions to access the Synapse workspace. You can use a service principal or Azure AD authentication.
3. Python installed on your local machine.

You need to install the necessary Python libraries. You can do this using pip:

pip install pyodbc

pip install azure-identity

pip install azure-synapse

If you are using Azure AD authentication, you can use the azure-identity library to authenticate.

from azure.identity import DefaultAzureCredential

credential = DefaultAzureCredential()

You can use the pyodbc library to connect to Azure Synapse Analytics. Below is an example of how to set up the connection and run a query.

import pyodbc

# Connection parameters

server = '<your-synapse-workspace>.sql.azuresynapse.net'

database = 'default'

username = '<your-username>'

password = '<your-password>'

driver= '{ODBC Driver 17 for SQL Server}'

# Connection string

conn_str = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}'

# Establish the connection

conn = pyodbc.connect(conn_str)

# Create a cursor

cursor = conn.cursor()

# Execute a query

query = "SELECT * FROM abc"

cursor.execute(query)

# Fetch and print the results

rows = cursor.fetchall()

for row in rows:

    print(row)

# Close the connection

cursor.close()

conn.close()

If you prefer using the Azure Synapse SDK, you can use the azure-synapse library to interact with your Synapse workspace.

from azure.synapse import SynapseClient

from azure.identity import DefaultAzureCredential



credential = DefaultAzureCredential()



synapse_client = SynapseClient(credential, '<your-subscription-id>', '<your-resource-group>', '<your-synapse-workspace>')



query = "SELECT * FROM abc"

result = synapse_client.sql_pools.execute('<your-sql-pool-name>', query)



for row in result:

    print(row)