Spark case statement if/else-CodePudding

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'")

CodePudding user response：

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")