cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

How can I call a stored procedure in Spark Sql?

mashaye
New Contributor

I have seen the following code:

val url =
   "jdbc:mysql://yourIP:yourPort/test?
   user=yourUsername; password=yourPassword"
   val df = sqlContext
   .read
   .format("jdbc")
   .option("url", url)
   .option("dbtable", "people")
   .load()

But I need to run a stored procedure. When I use

exec
command for the
dbtable
option above, it gives me this error:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'exec'.

6 REPLIES 6

ShaunRyan1
New Contributor II

Hi.

From the docs

The JDBC table that should be read. Note that anything that is valid in a
FROM
clause of a SQL query can be used. For example, instead of a full table you could also use a subquery in parentheses.

So has to be a subquery or alternatively you can use table functions and to achieve the same as a stored procedure.

xsobh
New Contributor II

you can use User Defined function

Hi, could you please elaborate? I understand that unles you bury some dynamic sql into a UDF then you can't do anything other than select data and return it.

Chris

j500sut
New Contributor II

This doesn't seem to be supported. There is an alternative but requires using pyodbc and adding to your init script. Details can be found here:

https://datathirst.net/blog/2018/10/12/executing-sql-server-stored-procedures-on-databricks-pyspark

I have tested this myself and works fine. If anyone has any alternative methods please let me know.

Thanks. I found this article also. I was concerned about it using driver mode and blocking all worker nodes. This sounds quite bad if you have many concurrent jobs running or need to call stored procs frequently. Are you still using this approach or did you find another approach?

Hi @Christian Bracchi, we're still using this approach at the moment and haven't experienced any issues so far. Although we only have one production job running at the moment!

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.