星火中的数据融合

Question

我有两个Dataframes，比如A& B。我想加入到一个键列中，并创建另一个Dataframe。当A & B键匹配时，我需要的是B行，而不是A行。

例如：

DataFrame A：

Employee1, salary100
Employee2, salary50
Employee3, salary200

DataFrame B

Employee1, salary150
Employee2, salary100
Employee4, salary300

由此产生的DataFrame应该是：

DataFrame C：

Employee1, salary150
Employee2, salary100
Employee3, salary200
Employee4, salary300

我如何在Spark & Scala中做到这一点？

Answer 1

尝试：

dfA.registerTempTable("dfA")
dfB.registerTempTable("dfB")

sqlContext.sql("""
SELECT coalesce(dfA.employee, dfB.employee), 
       coalesce(dfB.salary, dfA.salary) FROM dfA FULL OUTER JOIN dfB
ON dfA.employee = dfB.employee""")

或

sqlContext.sql("""
SELECT coalesce(dfA.employee, dfB.employee),
  CASE dfB.employee IS NOT NULL THEN dfB.salary
  CASE dfB.employee IS NOT NULL THEN dfA.salary
  END FROM dfA FULL OUTER JOIN dfB
ON dfA.employee = dfB.employee""")

Answer 2

假设dfA和dfB有2列emp和sal。您可以使用以下内容：

import org.apache.spark.sql.{functions => f}

val dfB1 = dfB
  .withColumnRenamed("sal", "salB")
  .withColumnRenamed("emp", "empB")

val joined = dfA
  .join(dfB1, 'emp === 'empB, "outer")
  .select(
    f.coalesce('empB, 'emp).as("emp"),
    f.coalesce('salB, 'sal).as("sal")
  )

注意:对于emp的给定值，每个Dataframe应该只有一行。