PySpark用三个独立键关联两个DataFrame时出现无法解析KEY4报错

• 多轮关联操作中没有给DataFrame设置别名，Spark解析KEY4这类字段时无法明确归属的数据源，最终抛出解析失败错误
• 未显式指定字段来源的情况下，多次关联存在同名字段覆盖的潜在风险，会进一步加剧字段解析异常

import pyspark.sql.functions as F

data1 = [("10/05/21", 1, "White", 3000), ("10/06/21", 2, "Blue", 4100), ("10/07/21", 3, "Green", 6200)]
df1 = spark.createDataFrame(data1, ["START", "KEY1", "Color", "OTHER"])

data2 = [(1, 2, 3, 3000), (2, 3, 2, 4100), (3, 1, 2, 6200)]
df2 = spark.createDataFrame(data2, ["KEY2", "KEY3", "KEY4", "NUMBER"])

# 给每个参与关联的DataFrame设置别名，明确字段归属
df_result = df1.alias("a").withColumnRenamed("START", "DATE1") \
    .join(df2.alias("b"), F.col("a.KEY1") == F.col("b.KEY2")) \
    .select("DATE1", "b.KEY3", "b.KEY4") \
    .join(df1.alias("c").withColumnRenamed("START", "DATE2"), F.col("c.KEY1") == F.col("KEY3")) \
    .select("DATE1", "DATE2", "KEY4") \
    .join(df1.alias("d").withColumnRenamed("START", "DATE3"), F.col("d.KEY1") == F.col("KEY4")) \
    .select("DATE1", "DATE2", "DATE3")

df_result.show()

+--------+--------+--------+
|   DATE1|   DATE2|   DATE3|
+--------+--------+--------+
|10/05/21|10/06/21|10/07/21|
|10/06/21|10/07/21|10/06/21|
|10/07/21|10/05/21|10/06/21|
+--------+--------+--------+

• 多表关联场景下，建议给每个DataFrame设置唯一别名，字段引用时统一带上别名前缀，从根源上避免列名冲突和解析错误
• 数据量较大时，可以提前提取df1中KEY1和START的映射关系做成维度表，避免重复扫描df1，提升关联性能

内容的提问来源于stack exchange，提问作者lunbox