synapse notebook

@Vineet S

Thanks for reaching out to Microsoft Q&A.

method 1

1. Verify Managed Identity:
   • Ensure that your Azure Synapse workspace has a managed identity enabled. You can check this in the Azure portal under “Workspace settings” -> “Identity.”
   • If it’s not enabled, create one for your Synapse workspace.
2. Reference Linked Service in SQL:
   • In your Synapse notebook, write a SQL query that references the linked service using the fully qualified object name (FQON) format.
   • Here’s an example query:SQL

          SELECT * FROM test2.test1.table1;
     

     Replace test2 with the actual name of your linked service.
   • Make sure that the linked service (test2 in this case) has the necessary permissions to access the target table (test1.table1).

method 2

To use a linked service in an Azure Synapse notebook, you can follow these steps:

1. Initialize a Spark session. This can be done using the following code: Python

      from pyspark.sql import SparkSession
      spark = SparkSession.builder.getOrCreate()
   

2. Configure Synapse’s OAuth provider to authorize the linked service. It uses whatever authentication method you set up in your linked service configuration. Replace LinkedServiceName with the name of your linked service (in your case, it’s test2):

      spark.conf.set("spark.storage.synapse.linkedServiceName", "test2")
      spark.conf.set("fs.azure.account.oauth.provider.type", "com.microsoft.azure.synapse.tokenlibrary.LinkedServiceBasedTokenProvider")
   

3. Specify the URL for the linked service. Replace <container-name> and <storage-account-name> with your specific details:

      base_url = "abfss://<container-name>@<storage-account-name>.dfs.core.windows.net"
      
   

4. Read data from a table. Here’s an example of how to read data from a Parquet file. You can replace example.parquet with your specific file path:

      df_parquet = spark.read.parquet(f"{base_url}/some-folder/example.parquet")
   

for more details refer https://segunakinyemi.com/blog/reference-linked-services-synapse/

In your case, if you want to select all data from test1.table1, you can replace example.parquet with test1.table1.

Please note that you need to replace <container-name> and <storage-account-name> with your specific details. Also, make sure that the linked service test2 has the necessary permissions to access test1.table1.

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

how about sql server query

from pyspark.sql import SparkSession

# Initialize Spark session.
spark = SparkSession.builder.getOrCreate()

# Configure Synapse's OAuth provider to authorize the linked service. It uses whatever authentication method you set up in your linked service configuration.
spark.conf.set("spark.storage.synapse.linkedServiceName", "LinkedServiceName")
spark.conf.set("fs.azure.account.oauth.provider.type", "com.microsoft.azure.synapse.tokenlibrary.LinkedServiceBasedTokenProvider")


LinkedServiceBasedTokenProvider?? values
how to query table in db
how to connect using service principle

@Vineet S

Querying SQL Server Tables in Azure Synapse Notebooks using Service PrincipalHere's a comprehensive guide on querying SQL Server tables within Azure Synapse notebooks leveraging service principal authentication:

Prerequisites:

• Azure Synapse Workspace with Managed Identity: Ensure your workspace has a managed identity enabled. You can check and create one in the Azure portal under "Workspace settings" -> "Identity."
• Service Principal Creation: Create a service principal in Azure Active Directory (AAD) with the necessary permissions to access the SQL Server database. Refer to Microsoft's documentation for detailed steps: https://video2.skills-academy.com/en-us/entra/identity-platform/howto-create-service-principal-portal
• Linked Service Configuration: Create a linked service in your Azure Synapse workspace that points to the SQL Server database. Configure the service principal authentication method within the linked service, specifying the application ID, client secret, and resource URI (SQL Server endpoint).
• SQL Server Permissions: Grant the service principal appropriate permissions (e.g., SELECT) on the specific table(s) you want to query.

Steps:

1. Import Libraries: In your Synapse notebook, import the necessary libraries:

      import pyodbc
      from pyspark.sql import SparkSession
   

2. Create Spark Session (Optional): If using Spark for data processing, create a Spark session:

      spark = SparkSession.builder.getOrCreate()
   

3. Connect to SQL Server using Service Principal: Establish a connection to the SQL Server database using the linked service credentials: Python

      # Retrieve linked service information from Synapse configurations (replace with your values)
      linked_service_name = "your_linked_service_name"
      # Assuming you have retrieved credentials securely (avoid hardcoding)
      application_id = "your_service_principal_application_id"
      client_secret = "your_service_principal_client_secret"
      resource_uri = "your_sql_server_endpoint_uri"
      # Construct the connection string with service principal authentication details
      conn_string = f"""DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={linked_service_name}.database.windows.net;DATABASE={linked_service_name};Authentication=Active Directory Interactive;UID={application_id};PWD={client_secret};{resource_uri}"""
      # Create a connection using pyodbc
      conn = pyodbc.connect(conn_string)
      # Create a cursor for executing queries
      cursor = conn.cursor()
   

   Security Considerations:
   • Avoid hardcoding credentials in your code. Consider using environment variables or secure storage mechanisms to store sensitive information.
   • Grant the service principal least privilege permissions to minimize security risks.
4. Execute SQL Query: Run the desired SQL query using the cursor.execute() method:

      query = "SELECT * FROM dbo.your_table_name"
      cursor.execute(query)
      # Retrieve results
      results = cursor.fetchall()
      # Process results (e.g., print or store in a DataFrame)
      for row in results:
          print(row)  # Example: print each row
          # Alternatively, create a Spark DataFrame for further processing
          # if using Spark:
          #     df = spark.createDataFrame(results, schema=["column1", "column2", ...])
          #     # Perform DataFrame operations...
   

5. Close Connections: After processing, close the cursor and connection to release resources:

      cursor.close()
      conn.close()
      
   

6. Replace placeholders with your actual values (linked service name, credentials, etc.).
7. For Spark processing, you can create a Spark DataFrame from the retrieved results using spark.createDataFrame().
8. Ensure your pyodbc library is installed (pip install pyodbc).Querying SQL Server Tables in Azure Synapse Notebooks using Service Principal

@Vineet S We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.