Hive: case statement

Syntax

CASE [ expression ]
       WHEN val1 THEN result1
       WHEN val2 THEN result2
       ...
       WHEN val3 THEN resultn
       ELSE result
END

Example

SELECT rating, CASE rating < 0 
    WHEN True THEN "Bad performer"
    WHEN False THEN "Good Performer"
  END
FROM emp;

In the above snippet, if rating is < 0, then Bad performer is returned, else Good performer is returned by case statement.

hive> SELECT * FROM emp;
OK
emp.id	emp.name	emp.hobbies	emp.technology_experience	emp.gender_age	emp.rating	emp.salary
1	Hari	["Football","Cricket"]	{"Java":"3.4Yrs","C":"4.5Yrs"}	{"gender":"Male","age":30}	-1.5	1000000.0
2	Chamu	["Trekking","Watching movies"]	{"Selenium":"5.6Yrs"}	{"gender":"Female","age":38}	3.0	2500000.0
3	Sailu	["Chess","Listening to music"]	{"EmbeddedC":"9Yrs"}	{"gender":"Female","age":32}	2.5	1300000.0
4	Gopi	["Cricket"]	{"Datastage":"11Yrs"}	{"gender":"Male","age":32}	-0.7	8.1E7
5	Rahim	[]	{}	{"gender":"Male","age":null}	-0.7	500000.0
Time taken: 0.033 seconds, Fetched: 5 row(s)
hive> ;
hive> ;
hive> SELECT rating, CASE rating < 0 
    >     WHEN True THEN "Bad performer"
    >     WHEN False THEN "Good Performer"
    >   END
    > FROM emp;
OK
rating	_c1
-1.5	Bad performer
3.0	Good Performer
2.5	Good Performer
-0.7	Bad performer
-0.7	Bad performer
Time taken: 0.094 seconds, Fetched: 5 row(s)

 

Expression is optional in th CASE statement,

 

Example

SELECT rating, CASE 
    WHEN rating < 0 THEN "Bad performer"
    WHEN rating > 0 THEN "Good Performer"
    WHEN rating == 0 THEN "IDLE performer"
  END
FROM emp;

hive> SELECT rating, CASE 
    >     WHEN rating < 0 THEN "Bad performer"
    >     WHEN rating > 0 THEN "Good Performer"
    >     WHEN rating == 0 THEN "IDLE performer"
    >   END
    > FROM emp;
OK
rating	_c1
-1.5	Bad performer
3.0	Good Performer
2.5	Good Performer
-0.7	Bad performer
-0.7	Bad performer
Time taken: 0.055 seconds, Fetched: 5 row(s)

 

 

 

Previous                                                    Next                                                    Home