基于条件连接多个Spark数据帧

Question

基于“SC”代码，我需要将SRCTable与RefTable-1或RefTable-2连接起来

条件：如果SC为“D”，则SRCTable在KEY=KEY1上与RefTable-1连接以获得值。否则，如果SC为“U”，则SRCTable与键=键2上的RefTable-2连接

这是输入spark数据帧。

SRCTable:
    -------------
    KEY |SC  |FK 
    -------------
    66  |D   | a
    67  |U   | b
    70  |D   | y
    71  |U   | q
    -------------
 RefTable-1:
    --------------
    KEY1 |Value  | 
    --------------
    66   |xyz1   | 
    67   |abc1   | 
    68   |fgr1   |
    69   |yte1   |
    70   |erx1   |
    71   |ter1   |
    --------------
 RefTable-2:
    --------------------
    KEY2 |KEY3  |Value  | 
    --------------------
    66   | a    |xyz2   | 
    67   | c    |abc2   | 
    67   | b    |fgr2   |
    69   | g    |yte2   |
    70   | y    |erx2   |
    71   | q    |ter2   |
    --------------------

预期产出：

    --------------------
    KEY |SC  |FK |Value |
    -------------------- 
    66  |D   | a |xyz1  |
    67  |U   | b |fgr2  |
    70  |D   | y |erx1  |
    71  |U   | q |ter2  |
    ---------------------

注意：输入表将有数百万条记录，因此需要一个优化的解决方案

Answer 1

下面是一段代码，您只能在DataFrame

val SRCTable = Seq((66, "D", "a"), (67, "U", "b"), (70, "D", "y"), (71, "U", "q")).toDF("KEY", "SC", "FK")
val RefTable1 = Seq((66, "xyz1"),(67, "abc1"),(68, "fgr1"),(69, "yte1"),(70, "erx1"),(71, "ter1")).toDF("KEY1", "Value")
val RefTable2 = Seq((66, "a", "xyz2"), (67, "c", "abc2"), (67, "b", "fgr2"), (69, "g", "yte2"), (70, "y", "erx2"), (71, "q", "ter2")).toDF("KEY2", "KEY3", "Value")

val join1 = SRCTable.where(SRCTable.col("SC").equalTo("D")).join(RefTable1, SRCTable.col("KEY") === RefTable1.col("KEY1")).select("KEY", "SC", "FK", "Value")
val join2 = SRCTable.where(SRCTable.col("SC").equalTo("U")).join(RefTable2, SRCTable.col("KEY") === RefTable2.col("KEY2") && SRCTable.col("FK") === RefTable2.col("KEY3") ).select("KEY", "SC", "FK", "Value")

join1.unionAll(join2).show 

如果你有任何性能问题，我建议你看看如何很好地分区你的数据，如果你的数据帧很小，也看看广播对象