如何在 Scala 和 Apache Spark 中加入两个 DataFrame？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36800174/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to join two DataFrames in Scala and Apache Spark?
提问by gmlvsv
There are two DataFrames (Scala, Apache Spark 1.6.1)
有两个数据帧（Scala、Apache Spark 1.6.1）
1) Matches
1) 比赛
MatchID | Player1 | Player2
--------------------------------
1 | John Wayne | John Doe
2 | Ive Fish | San Simon
2) Personal Data
2) 个人资料
Player | BirthYear
--------------------------------
John Wayne | 1986
Ive Fish | 1990
San Simon | 1974
john Doe | 1995
How could create a new DataFrame with 'BirthYear' for the both players
如何为两个玩家创建一个带有“BirthYear”的新 DataFrame
MatchID | Player1 | Player2 | BYear_P1 |BYear_P2 | Diff
-------------------------------------------------------------
1 | John Wayne | John Doe | 1986 | 1995 | 9
2 | Ive Fish | San Simon | 1990 | 1974 | 16
?
?
I tried
我试过
val df = MatchesDF.join(PersonalDF, MatchesDF("Player1") === PersonalDF("Player"))
then join again for the second player
然后再次加入第二个玩家
val resDf = df.join(PersonalDF, df("Player2") === PersonalDF("Player"))
but it's VERY time consuming operation.
但这是非常耗时的操作。
May be another way to do it in Scala and Apache Spark?
可能是在 Scala 和 Apache Spark 中执行此操作的另一种方法？
采纳答案by Vitalii Kotliarenko
This should perform better:
这应该表现更好：
case class Match(matchId: Int, player1: String, player2: String)
case class Player(name: String, birthYear: Int)
val matches = Seq(
Match(1, "John Wayne", "John Doe"),
Match(2, "Ive Fish", "San Simon")
)
val players = Seq(
Player("John Wayne", 1986),
Player("Ive Fish", 1990),
Player("San Simon", 1974),
Player("John Doe", 1995)
)
val matchesDf = sqlContext.createDataFrame(matches)
val playersDf = sqlContext.createDataFrame(players)
matchesDf.registerTempTable("matches")
playersDf.registerTempTable("players")
sqlContext.sql(
"select matchId, player1, player2, p1.birthYear, p2.birthYear, abs(p1.birthYear-p2.birthYear) " +
"from matches m inner join players p1 inner join players p2 " +
"where m.player1 = p1.name and m.player2 = p2.name").show()
+-------+----------+---------+---------+---------+---+
|matchId| player1| player2|birthYear|birthYear|_c5|
+-------+----------+---------+---------+---------+---+
| 1|John Wayne| John Doe| 1986| 1995| 9|
| 2| Ive Fish|San Simon| 1990| 1974| 16|
+-------+----------+---------+---------+---------+---+
I didn't find the way to express join of 3 tables in Scala DSL.
我没有找到在 Scala DSL 中表达 3 个表的连接的方法。
回答by Sohum Sachdev
This is a solution using spark's dataframe functions:
这是使用 spark 数据框函数的解决方案：
import sqlContext.implicits._
import org.apache.spark.sql.Row
import org.apache.spark.sql.functions.abs
val matches = sqlContext.sparkContext.parallelize(Row(1, "John Wayne", "John Doe"), Row(2, "Ive Fish", "San Simon")))
val players = sqlContext.sparkContext.parallelize(Seq(
Row("John Wayne", 1986),
Row("Ive Fish", 1990),
Row("San Simon", 1974),
Row("John Doe", 1995)
))
val matchesDf = sqlContext.createDataFrame(matches, StructType(Seq(
StructField("matchId", IntegerType, nullable = false),
StructField("player1", StringType, nullable = false),
StructField("player2", StringType, nullable = false)))
).as('matches)
val playersDf = sqlContext.createDataFrame(players, StructType(Seq(
StructField("player", StringType, nullable = false),
StructField("birthYear", IntegerType, nullable = false)
))).as('players)
matchesDf
.join(playersDf, $"matches.player1" === $"players.player")
.select($"matches.matchId" as "matchId", $"matches.player1" as "player1", $"matches.player2" as "player2", $"players.birthYear" as "player1BirthYear")
.join(playersDf, $"player2" === $"players.player")
.select($"matchId" as "MatchID", $"player1" as "Player1", $"player2" as "Player2", $"player1BirthYear" as "BYear_P1", $"players.birthYear" as "BYear_P2")
.withColumn("Diff", abs('BYear_P2.minus('BYear_P1)))
.show()
+-------+----------+---------+--------+--------+----+
|MatchID| Player1| Player2|BYear_P1|BYear_P2|Diff|
+-------+----------+---------+--------+--------+----+
| 1|John Wayne| John Doe| 1986| 1995| 9|
| 2| Ive Fish|San Simon| 1990| 1974| 16|
+-------+----------+---------+--------+--------+----+
回答by gaurhari dass
val df = left.join(right, Seq("name"))
display(df)
