Below table summarizes the conditional functions supported in Hive.
| Function or statement | Description | Example |
| assert_true(boolean condition) | Do nothing when the condition evaluates to true, else throw an exception. | hive> Select assert_true(true); OK NULL Time taken: 1.16 seconds, Fetched: 1 row(s) hive> SELECT assert_true(false); OK Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.metadata.HiveException: ASSERT_TRUE(): assertion failed. Time taken: 0.063 seconds |
| CASE [ expression ] WHEN val1 THEN result1 WHEN val2 THEN result2 ... WHEN val3 THEN resultn ELSE result END | In Case statement, expression is optional. This is similar to a switch statement. | SELECT rating, CASE rating WHEN True THEN "Bad performer" WHEN False THEN "Good Performer" END FROM emp; |
| coalesce(value1,value2,...) | Return first non-null value from the given values. If all the values are null, then it return null. | hive> SELECT coalesce('Krishna', null, null); OK _c0 Krishna Time taken: 0.042 seconds, Fetched: 1 row(s) |
| if(boolean condition, T trueValue, T falseValue) | This return trueValue when the condition evaluates to true, else falseValue. | SELECT name,if((gender_age.gender=='Male'),'M','F') FROM emp;
|
| isnull(column) | Return true if the value of column is null, else false. | SELECT * FROM emp WHERE isnull(gender_age.age); |
| isnotnull(column) | Return true if the value of column is not null, else false. | SELECT * FROM emp WHERE isnotnull(gender_age.age); |
| nvl(T value, T defaultValue) | Return value if it is not null, else return default value | hive> SELECT gender_age.age, nvl(gender_age.age, 'not_exists') FROM emp; OK age _c1 30 30 38 38 32 32 32 32 NULL not_exists Time taken: 0.052 seconds, Fetched: 5 row(s) |
I am using below sample data to demonstrate the examples.
Table definition
CREATE TABLE emp (
id INT,
name STRING,
hobbies ARRAYSTRING>,
technology_experience MAPSTRING,STRING>,
gender_age STRUCTSTRING,age:INT>,
rating DOUBLE,
salary DOUBLE
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;
Load the data to emp table
empInfo.txt
1|Hari|Football,Cricket|Java:3.4Yrs,C:4.5Yrs|Male,30|-1.5|1000000
2|Chamu|Trekking,Watching movies|Selenium:5.6Yrs|Female,38|3|2500000
3|Sailu|Chess,Listening to music|EmbeddedC:9Yrs|Female,32|2.5|1300000
4|Gopi|Cricket|Datastage:11Yrs|Male,32|-0.7|81000000
5|Rahim|||Male,|-0.7|500000 4|Gopi|Cricket|Datastage:11Yrs|Male,32|-0.7|81000000
5|Rahim||||-0.7|500000
Execute below command to load the data to emp table.
LOAD DATA LOCAL INPATH '/home/cloudera/examples/hive/empInfo.txt' INTO TABLE emp;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.034 seconds, Fetched: 5 row(s)
This post first appeared on Java Tutorial : Blog To Learn Java Programming, please read the originial post: here
