Turn spark.sql query into scala function

TS — Tue, 05 Apr 2022 07:39:13 GMT

Hello,

I'm learning Scala / Spark and try to understand what's wrong with my function:

I have a spark.sql query, stored in a variable:

val uViewName = spark.sql("""
SELECT
  v.Data_View_Name
FROM
  apoHierarchy AS h
  INNER JOIN apoView AS v ON h.View_Name = v.Context_View_Name
WHERE
  1 = 1
  AND h.Step_Number = 1
""")
uViewName.show()

This gives me the right result:

+-------------------------------+
|      Data_View_Name|
+-------------------------------+
|APO_S_VN_E1_APR_U...|
+-------------------------------+

I then want to pack it into a function like this:

def getUniverse(x: Int): String = {
  var sn = x
  var stepNumber = x.toString()
  var uViewName = spark.sql(s"""
SELECT
  v.Data_View_Name
FROM
  apoHierarchy AS h
  INNER JOIN apoView AS v ON h.View_Name = v.Context_View_Name
WHERE
  1 = 1
  AND h.Step_Number = $stepNumber
""")
  return uViewName
}

So, what I try to achieve is:

the StepNumber should be given as parameter
the query takes this parameter and queries the db accordingly

But, why do I get this error message?

command-4245198:14: error: type mismatch;
 found   : org.apache.spark.sql.DataFrame
    (which expands to)  org.apache.spark.sql.Dataset[org.apache.spark.sql.Row]
 required: String
  return uViewName
         ^

And when I change the return statement to:

return uViewName.toString()

it seems to work:

getUniverse: (x: Int)String

And, finally when running the def, shouldn't I get a string "APO_S_VN_E1_APR_U.."?

val x = getUniverse(1)
println(x)

[Data_View_Name: string]
x: String = [Data_View_Name: string]

Thanks for any advice / hint!

Re: Turn spark.sql query into scala function

Hubert-Dudek — Tue, 05 Apr 2022 09:17:08 GMT

try add .first()(0) it will return only value from first row/column as currently you are returning Dataset:

  var uViewName = spark.sql(s"""
SELECT
  v.Data_View_Name
FROM
  apoHierarchy AS h
  INNER JOIN apoView AS v ON h.View_Name = v.Context_View_Name
WHERE
  1 = 1
  AND h.Step_Number = $stepNumber
""").first()(0)

Re: Turn spark.sql query into scala function

TS — Tue, 05 Apr 2022 11:31:36 GMT

Yep, solved. Thank you!

(1) Spark Jobs
APR_S_VN_E1_APR_UNIVERSE
x: String = APR_S_VN_E1_APR_UNIVERSE

Re: Turn spark.sql query into scala function

Hubert-Dudek — Tue, 05 Apr 2022 12:16:54 GMT

Great to hear. If it is possible, please mark my answer as the Best one.

topic Re: Turn spark.sql query into scala function in Data Engineering

Turn spark.sql query into scala function

Re: Turn spark.sql query into scala function

Re: Turn spark.sql query into scala function

Re: Turn spark.sql query into scala function