Join of two Dataframes using multiple columns as keys stored in an Array in Apache Spark

Question

How to calculate the join of two Dataframes using multiple columns as key? For example DF1 , DF2 are the two dataFrame.

This is the way by which we can calculate the join,

JoinDF = DF1.join(DF2, DF1("column1") === DF2("column11") && DF1("column2") === DF2("column22"), "outer")

But my problem is how to access the multiple columns if they are stored in an arrays like :

DF1KeyArray=Array{column1,column2}
DF2KeyArray=Array{column11,column22}

then It is not possible to calculate the join by this method

JoinDF = DF1.join(DF2, DF1(DF1KeyArray)=== DF2(DF2KeyArray), "outer")

In this case error was :

<console>:128: error: type mismatch;
found   : Array[String]
required: String

Is there any way to access multiple columns as keys stored in an Array for calculation of join?

Please format you question ! This is not readable. Add the programming language tag too ! — eliasah
– eliasah, Commented Feb 2, 2016 at 10:14

4 revs · Accepted Answer · 2017-05-23 12:10:34Z

10

You can simply create joinExprs programmatically:

val df1KeyArray: Array[String] = ???
val df2KeyArray: Array[String] = ???

val df1: DataFrame = ???
val df2: DataFrame = ???

val joinExprs = df1KeyArray
  .zip(df2KeyArray)
  .map{case (c1, c2) => df1(c1) === df2(c2)}
  .reduce(_ && _)

df1.join(df2, joinExprs, "outer")

See also Including null values in an Apache Spark Join

edited May 23, 2017 at 12:10

community wiki

4 revs
zero323

Sign up to request clarification or add additional context in comments.

2 Comments

Zahiro Mor Over a year ago

nice use of .reduce(_ && _)

Panayotis Over a year ago

Best way to join using multiple columns. @zero323 Please add that if someone wants to return true (rather than NULL) if both inputs are NULL, then === must be replaced with <=> (EqualNullSafe)

Collectives™ on Stack Overflow

Join of two Dataframes using multiple columns as keys stored in an Array in Apache Spark

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related