Best way to handle Spark Scala API cross join leading to same columns names for both the right and left data frames

user_8190082 0
Viewed 352

When using crossJoin in Spark Scala API, the output has columns with the same names, which leads to errors due to ambiguity. For example:

val df = Seq((2, "b"), (3, "a"), (5, "z")).toDF("number", "letter")
val dfCrossJoin = df.crossJoin(df)
dfCrossJoined.select("letter")

throws an Exception:

...
Message: Reference 'letter: is ambiguous
...

To avoid these ambiguity errors, a solution could be to have the capability of renaming all the columns for the right or left dataframe. Is there a way to do this with the Scala API? For now I found a solution (see below) with the SQL API, but I was wondering if there is a better way to approach this (whether is a programmatic renaming or a way to select columns without ambiguity).

val df = Seq((2, "b"), (3, "a"), (5, "z")).toDF("number", "letter")
df.createOrReplaceTempView("df")
val dfCrossJoinedSql = spark.sql(s"""
select 
  t1.*, 
  ${df.columns.map(c => s"t2.${c} as ${c}_2").mkString(", ")}
from 
 df t1 cross join df t2
""")
apache-spark dataframe scala
2 Answers

You can alias the dataframe before the join using .as():

val df = Seq((2, "b"), (3, "a"), (5, "z")).toDF("number", "letter")
val dfCrossJoined = df.as("left").crossJoin(df.as("right"))
dfCrossJoined.select("right.letter")
user_12768040

For cleaner way, you can use .withColumnRenamed() to rename column and then drop column.

val df = Seq((2, "b"), (3, "a"), (5, "z")).toDF("number", "letter")
val dfCrossJoin = df.crossJoin(testDF.withColumnRenamed("letter","rletter"))
                 .drop("rletter")

dfCrossJoin.select(col("letter")).show()
user_138181450
Related