<jupyter_start><jupyter_text>Provide connection details<jupyter_code># Located in App Registrations from Azure Portal
tenant_id = "<<enter tenant_id>>"

# Authority
authority = "https://login.windows.net/" + tenant_id

# Located in App Registrations from Azure Portal
resource_app_id_url = "https://database.windows.net/"

# In the below example, we're using a Microsoft Spark utility that facilitates acquiring secrets from a configured Key Vault.  

# Service Principal Client ID - Created in App Registrations from Azure Portal
service_principal_id = mssparkutils.credentials.getSecret('azure key vault name','principalClientId')

# Service Principal Secret - Created in App Registrations from Azure Portal
service_principal_secret = mssparkutils.credentials.getSecret('azure key vault name','principalSecret')

# SQL Server URL
servername = "jdbc:sqlserver://azuresqlserver.database.windows.net"
url = servername + ";" + "databaseName=" + dbname + ";"

# Database Name
dbname = "TestDatabase"

# Database Table Name
dbtable = "dbo.TestTable" 

# SQL Authentication User Name
user = "midesa@midesa"

# SQL Authentication Password
password = <PASSWORD>('azure key vault name','secret name')<jupyter_output><empty_output><jupyter_text>Active Directory Authentication<jupyter_code>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", dbtable) \
        .option("accessToken", access_token) \
        .option("encrypt", "true") \
        .option("hostNameInCertificate", "*.database.windows.net") \
        .load()
jdbc_df = spark.read \
        .format("com.microsoft.sqlserver.jdbc.spark") \
        .option("url", url) \
        .option("dbtable", dbtable) \
        .option("authentication", "ActiveDirectoryPassword") \
        .option("user", "<EMAIL>") \
        .option("password", "<PASSWORD>") \
        .option("encrypt", "true") \
        .option("hostNameInCertificate", "*.database.windows.net") \
        .load()<jupyter_output><empty_output><jupyter_text>Azure SQL Authentication<jupyter_code># Write Data
try:
  df.write \
    .format("com.microsoft.sqlserver.jdbc.spark") \
    .mode("overwrite") \
    .option("url", url) \
    .option("dbtable", dbtable) \
    .option("user", user) \
    .option("password", password) \
    .save()
except ValueError as error :
    print("MSSQL Connector write failed", error)

print("MSSQL Connector write(overwrite) succeeded  ")
# Read Data

print("read data from SQL server table  ")
jdbcDF = spark.read \
        .format("com.microsoft.sqlserver.jdbc.spark") \
        .option("url", url) \
        .option("dbtable", dbtable) \
        .option("user", user) \
        .option("password", password)\
        .load()

jdbcDF.show(5)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1312803889789303, 'fraction_numerical': 0.00032414910858995135, 'mean_word_length': 3.5183016105417275, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 18, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '81073', 'license': 'Apache-2.0', 'n_tokens_mistral': 1000, 'n_tokens_neox': 929, 'n_words': 201, 'path': 'samples/Azure-Synapse-Analytics/Azure Synapse PySpark Connector Example.ipynb', 'repo_name': 'midesa/sql-spark-connector', 'size': '3125'}