How to merge two dataframes spark java/scala based on a column?

1
votes

I have a two dataframes DF1 and DF2 with id as the unique column, DF2 may contain a new records and updated values for existing records of DF1, when we merge the two dataframes result should include the new record and a old records with updated values remain should come as it is.

Input example:

id   name
10   abc
20   tuv
30   xyz

and

id   name
10   abc
20   pqr
40   lmn

When I merge these two dataframes, I want the result as:

id   name
10   abc
20   pqr
30   xyz
40   lmn
2
javascalaapache-sparkdataframeapache-spark-sql

2 Answers

2
votes

Use an outer join followed by a coalesce. In Scala:

val df1 = Seq((10, "abc"), (20, "tuv"), (30, "xyz")).toDF("id", "name") 
val df2 = Seq((10, "abc"), (20, "pqr"), (40, "lmn")).toDF("id", "name")

df1.select($"id", $"name".as("old_name"))
  .join(df2, Seq("id"), "outer")
  .withColumn("name", coalesce($"name", $"old_name"))
  .drop("old_name")

coalesce will give the value of the first non-null value, which in this case returns:

+---+----+
| id|name|
+---+----+
| 20| pqr|
| 40| lmn|
| 10| abc|
| 30| xyz|
+---+----+
0
votes
df1.join(df2, Seq("id"), "leftanti").union(df2).show

| id|name|
+---+----+
| 30| xyz|
| 10| abc|
| 20| pqr|
| 40| lmn|
+---+----+