Hive supports three types of conditional functions. These functions are listed below:
1. IF( Test Condition, True Value, False Value )
The IF condition eva luates the “Test Condition” andifthe “Test Condition” is true, thenit returns the “True Value”. Otherwise, it returns the False Value.
Example: IF(1=1, 'working', 'not working') returns 'working'
2. COALESCE( value1,value2,… )
The COALESCE function returns the fist notNULL value from the list of values. Ifall the values in the list are NULL, then it returns NULL.
Example: COALESCE(NULL,NULL,5,NULL,4) returns 5
3. CASE Statement
The syntax for the case statement is:
CASE [ expression ]
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionn THEN resultn
ELSE result
END
Here expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition1, condition2, ... conditionn).
All the conditions must be of same datatype. Conditions are eva luated in the order listed. Once a condition is found to be true, the case statement will return the result andnot eva luate the conditions any further.
All the results must be of same datatype. This is the value returned once a condition is found to be true.
IF no condition is found to be true, then the case statement will return the value in the ELSE clause. If the ELSE clause is omitted and no condition is found to be true, then the case statement will returnNULL
Example:
CASE Fruit
WHEN'APPLE' THEN'The owner is APPLE'
WHEN'ORANGE' THEN'The owner is ORANGE'
ELSE'Itis another Fruit'
END
The other form ofCASEisCASEWHEN Fruit = 'APPLE' THEN'The owner is APPLE'
WHEN Fruit = 'ORANGE' THEN'The owner is ORANGE'
ELSE'Itis another Fruit'
END