Introduction

The union() method of the DataFrame is used to combine two DataFrames with the same structure/schema. If schemas are not the same, it returns an error.

unionAll() is deprecated since Spark “2.0.0” version and replaced with union().

Note: In traditional SQL, Union eliminates duplicates, but UnionAll combines two datasets, including duplicate records. However, in Spark, both behave the same.

Before using these methods, it's essential to understand StructType and StructField, which define the schema of DataFrames.

• StructType is a collection of StructFields that define the column name, column type, nullable property, and metadata.
• StructField is essentially a field containing a column's metadata, including column name and data type.

First, let’s create two DataFrames with the same schema.

import spark.implicits._

val simpleData = Seq(
  ("James", "Sales", "NY", 90000, 34, 10000),
  ("Michael", "Sales", "NY", 86000, 56, 20000),
  ("Robert", "Sales", "CA", 81000, 30, 23000),
  ("Maria", "Finance", "CA", 90000, 24, 23000)
)

val df = simpleData.toDF("employee_name", "department", "state", "salary", "age", "bonus")
df.printSchema()
df.show()

root
 |-- employee_name: string (nullable = true)
 |-- department: string (nullable = true)
 |-- state: string (nullable = true)
 |-- salary: integer (nullable = false)
 |-- age: integer (nullable = false)
 |-- bonus: integer (nullable = false)

+-------------+----------+-----+------+---+-----+
|employee_name|department|state|salary|age|bonus|
+-------------+----------+-----+------+---+-----+
|        James|     Sales|   NY| 90000| 34|10000|
|      Michael|     Sales|   NY| 86000| 56|20000|
|       Robert|     Sales|   CA| 81000| 30|23000|
|        Maria|   Finance|   CA| 90000| 24|23000|
+-------------+----------+-----+------+---+-----+

Now, let’s create a second DataFrame with new records and some records from the above DataFrame but with the same schema.

val simpleData2 = Seq(
  ("James", "Sales", "NY", 90000, 34, 10000),
  ("Maria", "Finance", "CA", 90000, 24, 23000),
  ("Jen", "Finance", "NY", 79000, 53, 15000),
  ("Jeff", "Marketing", "CA", 80000, 25, 18000),
  ("Kumar", "Marketing", "NY", 91000, 50, 21000)
)

val df2 = simpleData2.toDF("employee_name", "department", "state", "salary", "age", "bonus")

This yields below output:

+-------------+----------+-----+------+---+-----+
|employee_name|department|state|salary|age|bonus|
+-------------+----------+-----+------+---+-----+
|        James|     Sales|   NY| 90000| 34|10000|
|        Maria|   Finance|   CA| 90000| 24|23000|
|          Jen|   Finance|   NY| 79000| 53|15000|
|         Jeff|Marketing |   CA| 80000| 25|18000|
|        Kumar|Marketing |   NY| 91000| 50|21000|
+-------------+----------+-----+------+---+-----+

1. Combine two or more DataFrames using union

DataFrame union() method combines two DataFrames and returns a new DataFrame with all rows from two DataFrames regardless of duplicate data.

// Combine two or more DataFrames using union
val df3 = df.union(df2)
df3.show(false)

As you see below, it returns all records:

+-------------+----------+-----+------+---+-----+
|employee_name|department|state|salary|age|bonus|
+-------------+----------+-----+------+---+-----+
|        James|     Sales|   NY| 90000| 34|10000|
|      Michael|     Sales|   NY| 86000| 56|20000|
|       Robert|     Sales|   CA| 81000| 30|23000|
|        Maria|   Finance|   CA| 90000| 24|23000|
|        James|     Sales|   NY| 90000| 34|10000|
|        Maria|   Finance|   CA| 90000| 24|23000|
|          Jen|   Finance|   NY| 79000| 53|15000|
|         Jeff|Marketing |   CA| 80000| 25|18000|
|        Kumar|Marketing |   NY| 91000| 50|21000|
+-------------+----------+-----+------+---+-----+

2. Combine DataFrames using unionAll

The DataFrame unionAll() method is deprecated since Spark “2.0.0” version and recommends using the union() method.

// Combine DataFrames using unionAll
val df4 = df.unionAll(df2)
df4.show(false)

Returns the same output as above.

3. Combine without Duplicates

Since the union() method returns all rows without distinct records, we will use the distinct() function to return just one record when duplicates exist.

// Combine without Duplicates
val df5 = df.union(df2).distinct()
df5.show(false)

Yields the output below. As you see, this returns only distinct rows:

+-------------+----------+-----+------+---+-----+
|employee_name|department|state|salary|age|bonus|
+-------------+----------+-----+------+---+-----+
|        James|     Sales|   NY| 90000| 34|10000|
|        Maria|   Finance|   CA| 90000| 24|23000|
|         Jeff|Marketing |   CA| 80000| 25|18000|
|          Jen|   Finance|   NY| 79000| 53|15000|
|        Kumar|Marketing |   NY| 91000| 50|21000|
|      Michael|     Sales|   NY| 86000| 56|20000|
|       Robert|     Sales|   CA| 81000| 30|23000|
+-------------+----------+-----+------+---+-----+

This complete example is also available at the GitHub project.