Selecting empty array values from a Spark DataFrame

Question

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', col2=[], col3=[], col4=[])]

I'd like to remove rows with an empty array for each of col2, col3 and col4 (i.e. the 3rd row).

For example I might expect this code to work:

df.where(~df.col2.isEmpty(), ~df.col3.isEmpty(), ~df.col4.isEmpty()).collect()

I have two problems

how to combine where clauses with and but more importantly...
how to determine if the array is empty.

So, is there a builtin function to query for empty arrays? Is there an elegant way to coerce an empty array to an na or null value?

I'm trying to avoid using python to solve it, either with a UDF or .map().

zero323 · Accepted Answer · 2015-09-09 00:41:07Z

3

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.

edited Sep 9, 2015 at 0:41

answered Sep 8, 2015 at 7:43

zero323

331k108 gold badges982 silver badges958 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rob Cowie Over a year ago

Useful info thanks. I'd be interested to see an example of replacing empty arrays with null. Is there a non-udf way to achieve that?

zero323 Over a year ago

Personally I would use Hive SIZE UDF or clean data on load.

Collectives™ on Stack Overflow

Selecting empty array values from a Spark DataFrame

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related