How to Handle Null Values During Apache Spark Joins?

How to Include Null Values in an Apache Spark Join

Apache Spark doesn't include rows with null values by default during joins. This can cause issues when you want to retain all data, including nulls. This article explores the solution to this problem.

Default Spark Behavior

When you perform a join on two DataFrames, Spark will exclude rows with null values. For example, consider the following DataFrames:

val numbersDf = Seq(
  ("123"),
  ("456"),
  (null),
  ("")
).toDF("numbers")

val lettersDf = Seq(
  ("123", "abc"),
  ("456", "def"),
  (null, "zzz"),
  ("", "hhh")
).toDF("numbers", "letters")

If we perform a join on these DataFrames, we will get the following output:

+-------+-------+
|numbers|letters|
+-------+-------+
|    123|    abc|
|    456|    def|
|       |    hhh|
+-------+-------+

As you can see, the row with null in the numbers column has been excluded from the result.

Solution

Spark provides a special null-safe equality operator for handling joins with null values:

numbersDf
  .join(lettersDf, numbersDf("numbers") <=> lettersDf("numbers"))
  .drop(lettersDf("numbers"))

This operator will return true if both operands are null or if they are equal. Using this operator, we can get the desired output:

+-------+-------+
|numbers|letters|
+-------+-------+
|    123|    abc|
|    456|    def|
|   null|    zzz|
|       |    hhh|
+-------+-------+

Additional Options

Spark 2.3.0 :

• PySpark: Use Column.eqNullSafe
• SparkR: Use %<=>%
• SQL: Use IS NOT DISTINCT FROM

Earlier Spark Versions:

Prior to Spark 1.6, null-safe joins required a Cartesian product.

The above is the detailed content of How to Handle Null Values During Apache Spark Joins?. For more information, please follow other related articles on the PHP Chinese website!