CODEWITHSUNDEEP
apache-sparkpyspark
Prem Rainu
Prem Rainu

Reputation: 31

Spark case statement if/else

I have 2 data frames, I want to do multiple select queries on the two data frames with a condition. Can I get some help

I want to achieve the following: select * from input_file_tmp where input_file_tmp.ckt_id = gsam_temp.CCKT_NO AND gsam_temp.SEV_LVL = '3'

code:

+-----------+--------+-----+----+-------+
|     ckt_id|location|usage|port|machine|
+-----------+--------+-----+----+-------+
|     ckt_id|location|usage|port|machine|
| AXZCSD21DF|     USA|  2GB| 101|   MAC1|
| ABZCSD21DF|     OTH|  4GB| 101|   MAC2|
| AXZCSD21DF|     USA|  6GB| 101|   MAC4|
| BXZCSD21DF|     USA|  7GB| 101|   MAC6|
| CXZCSD21DF|     IND|  2GB| 101|   MAC9|
| AXZCSD21DF|     USA|  1GB| 101|   MAC0|
| AXZCSD22DF|     IND|  9GB| 101|   MAC3|
|ADZZCSD21DF|     USA|  1GB| 101|   MAC4|
| AXZCSD21DF|     USA|  2GB| 101|   MAC5|
| XZDCSD21DF|     OTH|  2GB| 101|   MAC1|
+-----------+--------+-----+----+-------+

+-----------+-------+
|    CCKT_NO|SEV_LVL|
+-----------+-------+
| AXZCSD21DF|      1|
| BXZCSD21DF|      1|
| ABZCSD21DF|      3|
| CXZCSD21DF|      2|
| AXZCSD22DF|      2|
| XZDCSD21DF|      3|
|ADZZCSD21DF|      1|
+-----------+-------+


val x = sql("SELECT * from input_file_tmp,gsam_temp WHERE input_file_tmp.ckt_id = gsam_temp.CCKT_NO AND gsam_temp.SEV_LVL = '3'")

Upvotes: 0

Views: 246

Answers (1)

Amardeep Flora
Amardeep Flora

Reputation: 1380

You need to filter gsam_temp where sev_lvl = 3 and then do an inner join on input_file_tmp.ckt_id = gsam_temp.CCKT_NO

        val df = gsam_temp.join(input_file_tmp,input_file_tmp.ckt_id = gsam_temp.CCKT_NO)
                .filter(col("CCKT_NO")==="3")

Upvotes: 2

Related Questions