Support Questions

Find answers, ask questions, and share your expertise

Can I use CASE or IF/ESLE in SAPRK dataframes.

Rising Star

I have 2 DF, I want to do multiple select query with condition. Can I get some help

I want to select * from input_file_tmp if (input_file_tmp.ckt_id = gsam_temp.CCKT_NO AND gsam_temp.SEV_LVL = '3') else select * from gsam_temp

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


4 REPLIES 4

Super Guru

You don't want a case statement.

You want a Union (assuming you have the same fields)

Read here: https://www.essentialsql.com/learn-to-use-union-intersect-and-except-clauses/

select * from input_file_tmp

where input_file_tmp.ckt_id = gsam_temp.CCKT_NO AND gsam_temp.SEV_LVL = '3'

union

select * from gsam_temp

See: https://community.hortonworks.com/questions/67557/is-case-statement-in-query-of-spark-sql-ver-20-abo...

For SparkSQL syntax

https://github.com/apache/spark/blob/master/sql/catalyst/src/main/antlr4/org/apache/spark/sql/cataly...

In the latest Spark (2.1)

http://spark.apache.org/docs/latest/sql-programming-guide.html

Rising Star
@Timothy Spann

May I know what am doing wrong here . SEV_LVL should be a String.

It was working when the value was an integer.

I tried with like 'Sen%' also but no luck

 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='Sensitive'")
17/02/13 10:03:12 INFO ParseDriver: Parsing command: SELECT * from input_file_tmp,gsam_temp WHERE input_file_tmp.ckt_id = gsam_temp.CCKT_NO AND gsam_temp.SEV_LVL='Sensitive'
17/02/13 10:03:12 INFO ParseDriver: Parse Completed
x: org.apache.spark.sql.DataFrame = [ckt_id: string, location: string, usage: string, port: string, machine: string, CCKT_NO: string, SEV_LVL: decimal(38,0)]

Super Guru

add the union

Rising Star

Union can only be performed on tables with the same number of columns, but the left table has 5 columns and the right has 2;

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.