Connecting to Azure SQL Databases from Azure Databricks with Screts Scope

azure-databricks-connect-SQLServer.py

# Python code to connect to Azure SQL Databases from Azure Databricks with Screts Scope
# Author: Dhyanendra Singh Rathore

# Declare variables for creating JDBC URL
jdbcHostname = "sql-csv-data-server.database.windows.net" # Replace with your SQL Server name
jdbcPort = 1433 # Replace with your SQL Server port number
jdbcDatabase = "syn-csv-data-dw" # Replace with your database name

# Connection secrets from vault
jdbcUsername = dbutils.secrets.get(scope="CSVProjectKeyVault",key="SQLAdmin") # Replace the scope and key accordingly
jdbcPassword = dbutils.secrets.get(scope="CSVProjectKeyVault",key="SQLAdminPwd") # Replace the scope and key accordingly

# Create JDBC URL
jdbcUrl = "jdbc:sqlserver://{0}:{1};database={2}".format(jdbcHostname, jdbcPort, jdbcDatabase)
connectionProperties = {
  "user" : jdbcUsername,
  "password" : jdbcPassword,
  "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

pushdown_query = "(SELECT 'Hello World' AS TEXTCOL) t"

df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties)
display(df)