How to call db function inside a package using apache spark

Question

I have a package pkg_multi_parm_price_agg in oracle db which has a function

 FUNCTION FN_GET_GROUP_ID_WRAPPER_F(PI_PARM_CODE_VAL_STR IN VARCHAR2,
                                     PI_PARM_COUNT        IN NUMBER)
    RETURN TXN_DTL_PRITM.PRICEITEM_PARM_GRP_ID%TYPE;

currently iusing JDBC prepared statement i am able to call it successfully in my java project using below query and providing required parameters :

SELECT pkg_multi_parm_price_agg.fn_get_group_id_wrapper_F(?,?) FROM DUAL;

I want to understand if same can be called using Apache spark using Java?

EDIT - This question is regarding calling a DB function using Java using apache spark functionality.Hence I consider it as different question, kindly correct my understanding if otherwise.

@user8371915 here i am calling a function , can you please help me understand how is it duplicate? — Raj, Aug 14 '18 at 11:47
To execute arbitrary statement against the database. This has to be done by providing a query as a reader argument. — Alper t. Turker, Aug 14 '18 at 12:00

score 1 · Answer 1 · answered Aug 14 '18 at 10:41

  val sqlContext = SQLContext.getOrCreate(sc)
  import sqlContext.implicits._

  val jdbcHostname = "localhost"
  val jdbcPort = 3306
  val jdbcDatabase = "test"
  val jdbcUsername = "root"
  val jdbcPassword = "password"
  val jdbcUrl = s"jdbc:mysql://${jdbcHostname}:${jdbcPort}/${jdbcDatabase}?user=${jdbcUsername}&password=${jdbcPassword}"

  val query = """( select query ) foo"""

  val df = sqlContext.read.format("jdbc")
    .option("driver", "com.mysql.jdbc.Driver")
    .option("url", jdbcUrl)
    .option("useUnicode", "true")
    .option("continueBatchOnError", "true")
    .option("dbtable", query).option("user", jdbcUsername)
    .option("password", jdbcPassword)
    .load()

replace select query with your query. It will give you dataframe.

Thanks for your response ...I forget to mention that i need to write same in java. — Raj, Aug 14 '18 at 10:47

How to call db function inside a package using apache spark

1 Answers1