In this blog we are going to see about, how to Connect Azure SQL Database to Databricks. We are going to see the easy way to do this.
Before going into that, let us see what is Azure Sql Database. From the microsoft documentation, it is mentioned as Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement.
Azure Sql Database is based on the Microsoft Sql Server Database Engine. And we know that Databricks is a data platform that lets you collaboratively create and run data analysis projects. Now we are going to see how to connect both and fetch data from Azure Sql Database.
For connection we are going to use the jdbc connector. We are going to see step by step method to connect the Azure Sql Database with Databricks and how to access the table in that DB.
Step 1:
First step is to note down the server name of Azure SQL DB via Azure portal.
Step 2:
Use the below code in Databricks for the connection.
jdbcHostname = <the server name that we have already noted in step 1>
jdbcDatabase=<database_name>
jdbcPort=1433
jdbcUrl="jdbc:sqlserver://{0}:{1};database={2}.format(jdbcHostname , jdbcPort,jdbcDatabase)
connectionProperties={
"user":<database_user_name>,
"password":<database_password>,
"driver":"com.microsoft.sqlserver.jdbc.SQLServerDriver"
}
Usually the user and password must be stored in Azure Key Vault and that can be fetched using utilities by,
dbutils.secrets.get(scope=scope_name,key=key_name)
Step 3:
Now we are going to read the data from Azure Sql DB via Databricks by below code,
df1=spark.read.jdbc(url=jdbcUrl, table="table_name", properties=connectionProperties)
Now dataframe df1 is ready with data present in table that we mentioned as "table_name" . Now we can further proceed with the usual transformation with the created dataframe.
Thus, from this blog we saw the easy way of connecting Azure Sql Database to Databricks and saw the steps need to be done to fetch the data from the Azure sql Database. Hope this might be helpful.
Thank You!!!
0 Comments