Querying Azure SQL Databases In Databricks Spark Cluster

Please click the "Create Notebook" link in the dashboard and choose Scala as our scripting language.

We can check the JDBC driver connectivity by the following command.

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

After entering the above command, please choose Shift + Enter to execute the command.

By default, Spark Cluster is configured with SQL Server JDBC driver. Our above command will execute successfully.

We can give the server name and database name to the below variable and execute the command.

val jdbcHostname = "{sql server host name}"
val jdbcPort = 1433
val jdbcDatabase = "{sql db name}"
// Create the JDBC URL without passing in the user and password parameters.
val jdbcUrl = s"jdbc:sqlserver://${jdbcHostname}:${jdbcPort};database=${jdbcDatabase}"

We can give the credentials of our database server to the below connectionProperties variable and this variable will be used later in our query execution.

import java.util.Properties
val connectionProperties = new Properties()
connectionProperties.put("user", "{user name}")
connectionProperties.put("password", "{password}")

Check the connectivity to the SQL database using the following command.

val driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
connectionProperties.setProperty("Driver", driverClass)

Now, we are going to query the data from the SQL database. For that, we set a data frame variable customer using the below command.

val customer = spark.read.jdbc(jdbcUrl, "SalesLT.Customer", connectionProperties)

Please note that our customer variable now holds all the data from SalesLT.Customer table.

We can list the table structure using the below command.

customer.printSchema()

We can also list the table values using the below command.

customer.select("CustomerID", "Title", "FirstName", "LastName", "CompanyName").show()

Please note that in the above command, we supplied 5 column names explicitly and we got values for those columns only. You can use all the Spark commands to query the data.

Step 4. Saving the queried data as a CSV file

We can save the above-queried data as a CSV file easily. For that, we need to mount the storage account with DBFS (This is a custom filesystem developed by Databricks to handle the file operations). We must use the below command to mount our existing storage account in DBFS. (You must have an Azure blob storage account, otherwise please create a new one).

dbutils.fs.mount(
  source = "wasbs://{container name}@{storage account name}.blob.core.windows.net/",
  mountPoint = "/mnt/{mount point name}",
  extraConfigs = Map("fs.azure.account.key.{storage account name}.blob.core.windows.net" -> "{storage account access key}")
)

The above command will create a DBFS mount point “mnt/Sarath”. We can use this mount point for our file system operations.

Please note that our Spark Job returned a true value. That means our command is successfully executed. If there is any error during execution, it will show the exception with details.

We can save our data frame using the below command.

customer.coalesce(1)
       .write.format("com.databricks.spark.csv")
       .option("header", "true")
       .save("{your mount point location}.csv")

The above command will save all our data from the customer data frame to the customer.csv file. We can see the new file in Azure Storage Explorer.

Please note that this CSV is saved as a multi-part file. Hence it has a folder also with the same name. This folder has many files as shown below.

We can check the CSV file by getting the data from CSV using a data frame reader. There are various options available to read the data. We used the below method.

val customerFromCSV = spark.read.format("csv").option("header", "true").load("/mnt/sarath/customer.csv")
customerFromCSV.select("CustomerID", "Title", "FirstName", "LastName", "CompanyName").show()

In this article, we created a new Azure Databricks workspace and then configured a Spark cluster. After that, we created a new Azure SQL database and read the data from the SQL database in the Spark cluster using the JDBC driver and later, saved the data as a CSV file. We again checked the data from CSV and everything worked fine.

Hope you enjoyed this article. This is my third article on Azure Databricks. The other two are listed below.

• An Overview Of Azure Databricks Cluster Creation
• Querying Cosmos DB In Azure Databricks Using Cosmos DB Spark Connector