Hadoop Hive Conditional Functions: IF,CASE,COALESCE,NVL,DECODE

Hadoop Hive supports the various Conditional functions such as IF, CASE, COALESCE, NVL, DECODE etc. You can use these function for testing equality, comparison operators and check if value is null. Following diagram shows various Hive Conditional Functions:

Hadoop Hive Conditional Functions

Hive Conditional Functions

Below table describes the various Hive conditional functions:

Conditional Function	Description
IF(boolean testCondition, T valueTrue, T valueFalseOrNull);	This is the one of best Hive Conditional Functions and is similar to the IF statements in other programming languages. The IF Hive Conditional functions tests an expression and returns a corresponding result depending on whether the result is true, false or null.
isnull( a )	Returns true if a is NULL and false otherwise.
isnotnull ( a )	This function is a negation of isnull function. Returns true if a is not NULL and false otherwise.
CASE WHEN a THEN b [WHEN c THEN d]… [ELSE e] END	Function Tests whether any of a sequence of expressions is true, and returns a corresponding result for the first true expression. You can use OR, IN, REGEXP in the CASE expressions.
CASE a WHEN b THEN c [WHEN d THEN e]… [ELSE f] END	Function Tests whether any of a sequence of expressions is true, and returns a corresponding result for the first true expression. You can use OR, IN, REGEXP in the CASE expressions.
NVL(arg1, arg2)	This function will test if expression is null, it’ll return expression if result is not null otherwise second argument is returned.
coalesce(value1, value 2, …)	Returns the first non-null value for list of values provided as arguments.
decode(<expr>, <search1>,<result1>, …<search N>, <result N>, <default>)	Compares an expression to one or more possible values, and returns a corresponding result when a match is found.

Hive Conditional Functions Examples

Hive IF condition function example

select IF(1=1,'TRUE','FALSE') as IF_CONDITION_TEST;

Hive ISNULL condition function example

select isnull( NULL );

Hive ISNOTNULL condition function example

select isnotnull( NULL );

Hive CASE conditional function examples

select case x 
when 1 then 'one' 
when 2 then 'two' 
when 0 then 'zero' 
else 'out of range' 
end from t1;

select case 
when dayname(now()) in ('Saturday','Sunday') then 'result undefined on weekends' 
when x > y then 'x greater than y' 
when x = y then 'x and y are equal' 
when x is null or y is null then 'one of the columns is null' 
else null end 
from t1;

Hive NVL conditional function examples

select nvl(null,'value is null');

Hive COALESCE conditional function examples

select coalesce(null,'a',null,'b');

Hive DECODE conditional function examples

SELECT event, decode(day_of_week, 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday", 7, "Sunday", "Unknown day") FROM calendar;

Read:

This Post Has 3 Comments

Gopi June 25, 2018

My case is behaving incorrectly when i am using “and” in test expression. As u mentioned we can use only or, in regex in case. is there any specific reason and can u pls share the documentation where its mentioned.
1. Vithal S June 26, 2018
  
  Hi Gopi,
  
  You can use AND, OR or REGEX in CASE statement. I just verified case with AND operator and it is working as expected. Check your data in column that you are using in case statements. CASE statement will not work properly if there is any space in values.
  
  > select case when (1=1) AND (2=1) then True else False end as case_test;
  +————+–+
  | case_test |
  +————+–+
  | false |
  +————+–+
  1 row selected (0.105 seconds)
  
  Let me know how it goes.
  
  Thanks,
  1. Asia July 12, 2018
    
    I am having a similar issue but I am using substring_index in my case statement.
    
    I get an error: FAILED: ParseException line 18:36 cannot recognize input near ‘case’ ‘when’ ‘substring_index’ in joinSourcePart

Comments are closed.