Selecting Empty Array Values From A Spark Dataframe

Given a DataFrame with the following rows: rows = [ Row(col1='abc', col2=[8], col3=[18], col4=[16]), Row(col2='def', col2=[18], col3=[18], col4=[]), Row(col3='ghi', col

Solution 1:

how to combine where clauses with and

To construct boolean expressions on columns you should use &, | and ~ operators so in your case it should be something like this

~lit(True) & ~lit(False)

Since these operators have higher precedence than the comparison operators for complex expressions you'll have to use parentheses:

(lit(1) > lit(2)) & (lit(3) > lit(4))

how to determine if the array is empty.

I am pretty sure there is no elegant way to handle this without an UDF. I guess you already know you can use a Python UDF like this

isEmpty = udf(lambda x: len(x) == 0, BooleanType())

It is also possible to use a Hive UDF:

df.registerTempTable("df")
query = "SELECT * FROM df WHERE {0}".format(
  " AND ".join("SIZE({0}) > 0".format(c) for c in ["col2", "col3", "col4"]))

sqlContext.sql(query)

Only feasible non-UDF solution that comes to mind is to cast to string

cols = [
    col(c).cast(StringType()) != lit("ArrayBuffer()")
    for c in  ["col2", "col3", "col4"]
]
cond = reduce(lambda x, y: x & y, cols)
df.where(cond)

but it smells from a mile away.

It is also possible to explode an array, groupBy, agg using count and join but is most likely far to expensive to be useful in any real life scenario.

Probably the best approach to avoid UDFs and dirty hacks is to replace empty arrays with NULL.