How to join nested columns in spark with usingColumns

Question

I have 2 dataframes that I would like to join.

DF1:

root
 |-- myStruct: struct (nullable = true)
 |    |-- id: string (nullable = true)
 |    |-- region: long (nullable = true)
 |-- first_name: string (nullable = true)

DF2:

root
 |-- id: string (nullable = true)
 |-- region: long (nullable = true)
 |-- second_name: string (nullable = true)

My join statement is

df1.join(df2, Seq("id", "region"), "leftouter")

but that fails with

USING column `id` cannot be resolved on the left side of the join. The left-side columns: myStruct, first_name

I am running Spark 2.2 on Scala

Anahcolus · Accepted Answer · 2018-08-25 06:00:58Z

5

You can use . notation to select an element from struct column. so to select id from df1 you will have to do myStruct.id and to select region you have to use myStruct.region.

And since the column names to be used are not same you can use === notation for comparison as

df1.join(df2, df1("myStruct.id") === df2("id") && df1("myStruct.region") === df2("region"), "leftouter")

You should have the joined dataframe with following schema

root
 |-- myStruct: struct (nullable = true)
 |    |-- id: string (nullable = true)
 |    |-- region: long (nullable = false)
 |-- first_name: string (nullable = true)
 |-- id: string (nullable = true)
 |-- region: integer (nullable = true)
 |-- second_name: string (nullable = true)

You can drop the unnecessary columns after join or select only needed columns after join

I hope the answer is helpful

answered Aug 25, 2018 at 6:00

Anahcolus

42.1k6 gold badges75 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chitral Verma · Accepted Answer · 2018-08-25 05:19:21Z

2

This is because in DF1, id is an element of column myStruct which is of type struct. In order to join you can do something like,

val df = df1
.withColumn("id", col("myStruct.id"))
.withColumn("region", col("myStruct.region"))

df.join(df2, Seq("id", "region"), "leftouter")

This essentially extracts id and region from the struct column.

edited Aug 25, 2018 at 5:19

answered Aug 25, 2018 at 5:04

Chitral Verma

2,8631 gold badge20 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to join nested columns in spark with usingColumns

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related