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