How to avoid key column name duplication in join?

user_6379348 0
Viewed 109

I'm trying to join two tables in spark sql. Each table has 50+ columns. Both has column id as the key.

spark.sql("select * from tbl1 join tbl2 on tbl1.id = tbl2.id")

The joined table has duplicated id column. We can of course specify which id column to keep like below:

spark.sql("select tbl1.id, .....from tbl1 join tbl2 on tbl1.id = tbl2.id")

But since we have so many columns in both tables, I do not want to type all the other column names in the query above. (other than id column, no other duplicated column names).

what should I do? thanks.

apache-spark-sql apache-spark
3 Answers

If id is the only column name in common, you can take advantage of the USING clause:

spark.sql("select * from tbl1 join tbl2 using (id) ")

The using clause matches columns that have the same name in both tables. When using select *, the column appears only once.

user_11440350

Assuming, you want to preserve the "duplicates", you can try to use the internal row-id or equivalents for your help. This helped me in the past, if I had to delete exactly one of two identical rows.

select *,ctid from table;

outputs in postgresql also the internal counter id. Your before exact identical rows become different now. I don't know about spark.sql, but I assume, that you can access a similar attribute there.

user_61107320 
val joined = spark
     .sql("select * from tbl1")
     .join(
          spark.sql("select * from tbl2"),
          Seq("id"),
          "inner" // optional
     )

joined should have only one id column. Tested with Spark 2.4.8

user_118624650
Related