How to write Join and where in Spark DataFrame (Converting SQL to DataFrame)

Question

I need to write SQL Query into DataFrame

SQL Query

A_join_Deals = sqlContext.sql("SELECT * FROM A_transactions LEFT 
JOIN Deals ON (Deals.device = A_transactions.device_id) WHERE 
A_transactions.device_id IS NOT NULL AND A_transactions.device_id != '' AND 
A_transactions.advertiser_app_object_id = '%s'"%(adv_id))

Code written up to now

val A_join_Deals = Deals.join(A_transactions,Deals("device") === A_transactions("device_id"),"left")

Now I am stuck how to write the where Clause.

Any Suggestion or Help is Highly appreciated.

Anahcolus · Accepted Answer · 2017-07-07 09:33:16Z

4

you can try as below

val A_join_Deals = Deals.join(A_transactions,Deals("device") === A_transactions("device_id"),"left")
      .where(A_transactions("device_id").isNotNull && A_transactions("device_id") =!= "" && A_transactions("advertiser_app_object_id") === s"${adv_id}")

edited Jul 7, 2017 at 9:33

answered Jul 7, 2017 at 8:57

Anahcolus

42.1k6 gold badges75 silver badges101 bronze badges

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

7 Comments

Bhavesh Over a year ago

Thanks for your help but how to pass a variable [%(adv_id))] ? in the condition in Data-frame ?

koiralo Over a year ago

You can use like function for spark which is silmilar to % in sql.

Bhavesh Over a year ago

@ShankarKoirala Thanks for your suggestion but we dont want to use %sql sparksql since we need to register the dataset as temp table which is taking a lot of Memory since we have data in TeraByte

Daniel de Paula Over a year ago

@Bhavesh If adv_id is a scala variable in scope, you can do A_transactions("advertiser_app_object_id") === Deals(s"$adv_id"))

Bhavesh Over a year ago

@DanieldePaula Thanks for your suggestion i will try to implement it and see once again Thank you for your help DanieldePaula, ShankarKoirala and Ramesh

|

koiralo · Accepted Answer · 2017-07-07 09:29:53Z

2

Here is what you can do

where(A_transactions("device_id").isNotNull &&
   A_transactions("device_id") =!= "" && 
   A_transactions("advertiser_app_object_id") === s"${adv_id}%")

This works if adv_id is a variable and not Column

Hope this helps!

answered Jul 7, 2017 at 9:29

koiralo

23.2k6 gold badges57 silver badges77 bronze badges

Collectives™ on Stack Overflow

How to write Join and where in Spark DataFrame (Converting SQL to DataFrame)

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related