Call a stored procedure from the databricks notebook

Call a stored procedure from the databricks notebook is a databrick programming solution about how to call and execute the external SQL server stored procedure from the databricks notebook using Spark connector. 

We can use the spark connector to connect to any external SQL DB's(Azure SQL DB or SQL server) for read/write operations.

Note: In this post, we just used the stored procedure without any parameters, if we want to pass the parameters to the stored procedure,  just construct the query as per your need.

Prerequisites and steps to connect external SQL server

To execute or run a stored procedure from the databricks notebook, that already exists in Azure SQL DB or SQL server 

Prerequisites:

1. Databricks Workspace 

2. Databricks Cluster

3. Databrciks Notebook (created in Scala/Python/R/SQL)

4. Import and install the libraries required for SQL connectivity

Note: We need to install the required JAR libraries for SQL Bulk copy connectivity.

5. Stored procedure available in Azure SQL DB or SQL server

Solution steps:

1. If the databricks cluster is offline, make sure that it's up and running.

2. Make sure that you have proper access to Azure SQL DB or SQL servers to read/write operations. Without proper access, you may get multiple connectivity errors.

3. Make sure that required libraries if any, installed in the cluster. Pre-check the libraries required for SQL data sources access. Also, you need to have proper access to install the libraries in the cluster.

4. Make sure that proper libraries imported to notebook 

5. Create a query, mention the stored procedure name we want to run in azure SQL database without any parameters.

val query = "EXEC dbo.mystoredprocedure"

6. Construct the config with required DB parameters

val config = Config(Map(
“url”  -> “mysqlserver.database.windows.net”,  //Azure SQL server name
“databasename”   -> “mydatabase”,                // Azure SQL database name
“user”           -> “username”,                          // Azure SQL DB login username
“password”       -> “password”,                       // Azure SQL DB login password
“queryCustom”   -> query
))

7. Just call the 'sqlDBQuery' method from the SQL context class

sqlContext.sqlDBQuery(config) 

8. Run the notebook cell.

Conclusion

If no errors found, check the stored procedure results in Azure SQL DB or SQL server

Happy Coding!!!👍