Ingest Data To Azure SQL Database Using Azure Databricks

Introduction

In this tutorial, we are going to discuss multiple ways to connect to Azure SQL Databases from Azure Databricks. We will also go through the code for each method.

Azure SQL Database connectivity with Azure Databricks

There are multiple ways to set up connectivity from Azure Databricks to Azure SQL Database.

SQL Database Connectivity using pyodbc with local user authentication and password

We can use pyodbc to establish connectivity and ingest data to Azure SQL Database using pyodbc.

Source code looks as follows,

import pyodbc
# Setup the connection string for Azure SQL Database
connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER={servername};DATABASE={databasename};Trusted_Connection=yes;'
# Create a cursor object from the connection string and execute the SQL commands
cursor=connection.cursor()
cursor.execute("{define your sql query}")
# Get the Query Result
while 1:
    row = cursor.fetchone()
    if not row:
        break
    print(row.version)

In order to use the pyodbc to connect and work with Azure SQL Database, we have to first import the pyodbc python package. Once the python package is imported, we have to define the SQL Database connection string using pyodc using SQL Server Name, Azure SQL Database Name. Once the connection configurations are created, we have to create a cursor object to set up the connection strings and execute the SQL commands. We can access the cursor query execution output directly or write a loop to read each line one by one.

SQL DataBase connectivity using pyodbc with Service Principal Authentication

In order to use Azure Service Principal using pyodbc with Azure SQL Database, there are a few pre-requisites,

Azure Service Principal should be created as a user of the Azure SQL Database.

Relevant roles to Azure Service Principal user.

context = adal.AuthenticationContext(authority)
token = context.acquire_token_with_client_credentials(resource_app_id_url, service_principal_id, service_principal_secret)
access_token = token["accessToken"]

jdbc_db = spark.read \
        .format("com.microsoft.sqlserver.jdbc.spark") \
        .option("url", url) \
        .option("dbtable", table_name) \
        .option("accessToken", access_token) \
        .option("encrypt", "true") \
        .option("hostNameInCertificate", "*.database.windows.net") \
        .load()

Here, we have to provide Azure AD Service Principal Name and password to generate the Azure AD access token and use this token to connect and query Azure SQL Database.

Using Pyspark to connect to Azure SQL Database