Equivalent SQL function of IFNULL and IFF in spark dataframe

user_9064240 0
Viewed 1589

I am trying to use a Snowflake column (which has functions like IFFNULL and IFF) in Spark dataframe. I have tried coalesce but its not working. Is there any equivalent function or logic to use in Spark dataframe?

Snowflake SQL: 

SELECT P.Product_ID,
IFNULL(IFF(p1.ProductDesc='',NULL,p1.ProductDesc),
           IFNULL(IFF(p2.PrdDesc='',NULL,p2.PrdDesc),IFF(p3.Product_Desc='',NULL,p3.Product_Desc))
          ) AS Product_Description
FROM Product p
LEFT JOIN Product_table_1 p1 ON p1.Product_ID = p.Product_ID
LEFT JOIN Product_table_2 p2 ON p2.Product_ID = p.Product_ID
LEFT JOIN Product_table_3 p3 ON p3.Product_ID = p.Product_ID

I tried: coalesce(p1.ProductDesc, p2.PrdDesc, p3.Product_Desc) but it is not working

apache-spark-sql apache-spark dataframe sql
1 Answers

IIUC, you can try coalesce + nullif:

nullif(expr1, expr2) - Returns null if expr1 equals to expr2, or expr1 otherwise.

For example (using pyspark):

df = spark.createDataFrame([(None,"","a"),("a","","b"),("",None,"c")],["desc1", "desc2", "desc3"])

df.createOrReplaceTempView("tb")

spark.sql("select *, coalesce(nullif(desc1,''), nullif(desc2,''), nullif(desc3,'')) as desc from tb").show()       
+-----+-----+-----+----+
|desc1|desc2|desc3|desc|
+-----+-----+-----+----+
| null|     |    a|   a|
|    a|     |    b|   a|
|     | null|    c|   c|
+-----+-----+-----+----+

so for your task, use the following:

coalesce(nullif(p1.ProductDesc,''), nullif(p2.PrdDesc,''), nullif(p3.Product_Desc,'')) as Product_Description

BTW. you can also just change all IFF in your original SQL to IF.

user_95107290
Related