How to call remote SQL function inside PySpark or Scala databriks notebook

Question

I am writing databriks scala / python notebook which connect SQL server database. and i want to execute sql server function from notebook with custom paramters.

import com.microsoft.azure.sqldb.spark.config.Config
import com.microsoft.azure.sqldb.spark.connect._

val ID = "1"
val name = "A"

val config = Config(Map(
  "url"            -> "sample-p-vm.all.test.azure.com",
  "databaseName"   -> "DBsample",
  "dbTable"        -> "dbo.FN_cal_udf",
  "user"           -> "useer567",
  "password"       -> "pppp@345%",
  "connectTimeout" -> "5", //seconds
  "queryTimeout"   -> "5"  //seconds
))

val collection = sqlContext.read.sqlDB(config)
collection.show()

here function is FN_cal_udf which stored in sql server database -'DBsample'

I got error : jdbc.SQLServerException: Parameters were not supplied for the function

How i can pass parameter and call SQL function inside notebook in scala or pyspark.

Mr. Bhosale · Accepted Answer · 2020-06-19 07:42:17Z

1

Here you can first make query string which stores function calling statement with dynamic parameters. and then use in congig.

    import com.microsoft.azure.sqldb.spark.config.Config
    import com.microsoft.azure.sqldb.spark.connect._

    val ID = "1"
    val name = "A"
    val query = " [dbo].[FN_cal_udf]('"+ID+"','"+name+"')"
    val config = Config(Map(
      "url"            -> "sample-p-vm.all.test.azure.com",
      "databaseName"   -> "DBsample",
      "dbTable"        -> "dbo.FN_cal_udf",
      "user"           -> "useer567",
      "password"       -> "pppp@345%",
      "connectTimeout" -> "5", //seconds
      "queryTimeout"   -> "5"  //seconds
    ))

    val collection = sqlContext.read.sqlDB(config)
    collection.show()

answered Jun 19, 2020 at 7:42

Mr. Bhosale

3,1161 gold badge19 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to call remote SQL function inside PySpark or Scala databriks notebook

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related