CODEWITHSUNDEEP
apache-sparkapache-spark-sql
Ga999
Ga999

Reputation: 71

I am getting results with SQL query but getting error with spark.Sql

accountBal.createOrReplaceTempView("accntBal")

    var finalDf = spark.sql(
" SELECT CTC_ID, ACCNT_BAL, PAID_THRU_DT, DAYS(CURRENT_DATE) - DAYS(PAID_THRU_DT) AS DEL_DAYS  FROM accntBal  WHERE ACCNT_BAL > 0  AND PAID_THRU_DT <= CURRENT_DATE AND PAID_THRU_DT > '01/01/2000'  AND PAID_THRU_DT is not null "
)

org.apache.spark.sql.AnalysisException: Undefined function: 'DAYS'

. This function is neither a registered temporary function nor a permanent function registered in the database

Upvotes: 2

Views: 3018

Answers (2)

Ram Ghadiyaram
Ram Ghadiyaram

Reputation: 29145

In spark udf has to be registered to be used in your queries.

Register a function as a UDF

example : 

val squared = (s: Long) => {
  s * s
}
spark.udf.register("square", squared)

since you have not registered days as it was throwing this error.

I assume you have written a custom udf to know number of days between 2 dates.

How to debug ? :

To check your udf is there in the functions registerd with spark or not like this.

You can query for available standard and user-defined functions using the Catalog interface (that is available through SparkSession.catalog attribute).

val spark: SparkSession = ...
scala> spark.catalog.listFunctions.show(false)

it will be display all the functions defined within spark session.

Further reading : UDFs — User-Defined Functions

If not... you can try which is already present in spark functions.scala

static Column datediff(Column end, Column start) Returns the number of days from start to end.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You should be using DATEDIFF to get the difference in days between two date:

SELECT
    CTC_ID,
   ACCNT_BAL,
   PAID_THRU_DT,
   DATEDIFF(CURRENT_DATE, PAID_THRU_T) AS DEL_DAYS
FROM accntBal
WHERE
   ACCNT_BAL > 0 AND
   PAID_THRU_DT > '2000-01-01' AND PAID_THRU_DT <= CURRENT_DATE;

Note: The NULL check on PAID_THRU_DT is probably not necessary, since a NULL value would fail the range check already.

Upvotes: 2

Related Questions