How to join Spark dataframe without duplicate columns in JAVA

Question

How can we merge 2 dataframes without duplicate columns

a.show()

+-----+-------------------+--------+------+
| Name|           LastTime|Duration|Status|
+-----+-------------------+--------+------+
|  Bob|2015-04-23 12:33:00|       1|logout|
|Alice|2015-04-20 12:33:00|       5| login|
+-----+-------------------+--------+------+

b.show()
+-----+-------------------+--------+------+
| Name|           LastTime|Duration|Status|
+-----+-------------------+--------+------+
|  Bob|2015-04-24 00:33:00|       1|login |
+-----+-------------------+--------+------+

I want to form a new dataframe by using whole data in Dataframe A but update rows using data in B

+-----+-------------------+--------+------+
| Name|           LastTime|Duration|Status|
+-----+-------------------+--------+------+
|  Bob|2015-04-24 00:33:00|       1|login |
|Alice|2015-04-20 12:33:00|       5| login|
+-----+-------------------+--------+------+

I am able to join and form dataframe in scala. But not able to do in JAVA.

DataFrame f=a.join(b,a.col("Name").equalsTo(b.col("Name")).and a.col("LastTime).equalsTo(b.col("LastTime).and(a.col("Duration").equalsTo(b.col("Duration"),"outer")

I am getting duplicate columns while performing JOIN like this.

score 2 · Answer 1 · answered Jun 20 '17 at 13:16

According to this SO answer a Seq of column names solve this in Scala.

Hance, converting a Java List to Scala Seq should do it or you. Here would be your corrected example code:

DataFrame f = a.join(b,
    // Convert Java List to Scala Seq
    scala.collection.JavaConverters.asScalaIteratorConverter(
        Arrays.asList("Name", "LastTime", "Duration").iterator()
    ).asScala().toSeq(),
    "outer"
)

score 1 · Answer 2 · answered Aug 26 '20 at 13:07

1

Correct way is : (tested)

Dataset<Row> f = a.join(b,
// Convert Java List to Scala Seq
JavaConverters.collectionAsScalaIterableConverter(
    Arrays.asList("Name", "LastTime", "Duration"))
.asScala().toSeq(),
"outer"
)

answered Aug 26 '20 at 13:07

Renats Razumilovs

21
2

score 1 · Answer 3 · answered Aug 26 '20 at 15:31

I think we can try it through Spark SQL and it could be executed through java as well.

spark.sql("""SELECT a.Name as Name,
CASE WHEN b.Name is null THEN a.LastTime ELSE b.LastTime END AS LastTime,
CASE WHEN b.Name is null THEN a.Duration ELSE b.Duration END AS Duration,
CASE WHEN b.Name is null THEN a.Status ELSE b.Status END AS Status 
FROM a a left outer join  b b on a.Name=b.Name 
""").show(false)

+-----+-------------------+--------+------+
|Name |LastTime           |Duration|Status|
+-----+-------------------+--------+------+
|Bob  |2015-04-24 00:33:00|1       |login |
|Alice|2015-04-20 12:33:00|5       |login |
+-----+-------------------+--------+------+

One can update the join condition as per usecase

score 0 · Answer 4 · answered May 23 '19 at 10:17

0

You can perform a left semi join ("leftsemi") to avoid duplicate columns from b dataset.

See here for reference: http://www.techburps.com/misc/apache-spark-dataset-joins-in-java/129

answered May 23 '19 at 10:17

NiharGht

151
5
10

How to join Spark dataframe without duplicate columns in JAVA

4 Answers4