In this Spark article, you will learn how to union two or more data frames of the same schema which is used to append DataFrame to another or combine two DataFrames and also explain the differences between union and union all with Scala examples.
Dataframe union() – union() method of the DataFrame is used to combine two DataFrame’s of the same structure/schema. If schemas are not the same it returns an error.
DataFrame unionAll() – unionAll() is deprecated since Spark “2.0.0” version and replaced with union().
Note: In other SQL’s, Union eliminates the duplicates but UnionAll combines two datasets including duplicate records. But, in spark both behave the same and use DataFrame duplicate function to remove duplicate rows.
First, let’s create two DataFrame 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()
df.printSchema prints the schema and df.show() display DataFrame to console.
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 the 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|
+-------------+----------+-----+------+---+-----+
Combine two or more DataFrames using union
DataFrame union() method combines two DataFrames and returns the new DataFrame with all rows from two Dataframes regardless of duplicate data.
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|
+-------------+----------+-----+------+---+-----+
Combine DataFrames using unionAll
DataFrame unionAll() method is deprecated since Spark “2.0.0” version and recommends using the union() method.
val df4 = df.unionAll(df2)
df4.show(false)
Returns the same output as above.
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 duplicate exists.
val df5 = df.union(df2).distinct()
df5.show(false)
Yields below output. 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|
+-------------+----------+-----+------+---+-----+
Complete Example of DataFrame Union
package com.sparkbyexamples.spark.dataframe
import org.apache.spark.sql.SparkSession
object UnionExample extends App{
val spark: SparkSession = SparkSession.builder()
.master("local[1]")
.appName("SparkByExamples.com")
.getOrCreate()
spark.sparkContext.setLogLevel("ERROR")
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()
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")
df2.show(false)
val df3 = df.union(df2)
df3.show(false)
df3.distinct().show(false)
val df4 = df.unionAll(df2)
df4.show(false)
}
This complete example is also available at the GitHub project.
Conclusion
In this Spark article, you have learned how to combine two or more DataFrame’s of the same schema into single DataFrame using Union method and learned the difference between the union() and unionAll() functions.
Happy learning !!
I am trying UnionByName on dataframes but it gives weird results in cluster mode. It runs on local as expected.