Swetha Swetha - 4 months ago 152
SQL Question

How to check isEmpty on Column Data Spark scala

My data looks like :

[null,223433,WrappedArray(),null,460036382,0,home,home,home]

How do I check if the col3 is empty on query in spark sql ? I tried to explode but when I do that the empty array rows are disappearing. Can some suggest me a way to do this.

I tried :

val homeSet = result.withColumn("subscriptionProvider", explode($"subscriptionProvider"))


where subscriptionProvider(WrappedArray()) is the column having array of values but some arrays can be empty. I need to get the subscriptionProvider with null values and subscriptionProvider array has "Comcast"

Answer

Try:

import org.apache.spark.sql.functions._

val tmp = df.withColumn("subscriptionProvider", 
  when(size($"subscriptionProvider") !== 0, $"subscriptionProvider").otherwise(array(lit(null).cast("string"))))

tmp.withColumn("subscriptionProvider", explode($"subscriptionProvider"))