topic Re: How can I call a stored procedure in Spark Sql? in Data Engineering

How can I call a stored procedure in Spark Sql?

mashaye — Thu, 10 Nov 2016 16:44:22 GMT

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'.

Re: How can I call a stored procedure in Spark Sql?

ShaunRyan1 — Mon, 29 Oct 2018 11:09:00 GMT

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.

Re: How can I call a stored procedure in Spark Sql?

xsobh — Mon, 29 Oct 2018 21:28:22 GMT

you can use User Defined function

Re: How can I call a stored procedure in Spark Sql?

j500sut — Tue, 04 Jun 2019 03:34:16 GMT

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.

Re: How can I call a stored procedure in Spark Sql?

ChristianBracch — Tue, 23 Jul 2019 08:22:38 GMT

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

Re: How can I call a stored procedure in Spark Sql?

ChristianBracch — Tue, 23 Jul 2019 08:23:52 GMT

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?

Re: How can I call a stored procedure in Spark Sql?

j500sut — Fri, 16 Aug 2019 05:35:28 GMT

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!