Hive Conditional Functions with Examples

Hive conditional functions are used to apply conditions on one or more columns and the conditions are executed for every row on a table.

In this article, you will learn Hive conditional functions isnull, isnotnull, nvl, nullif, case when e.t.c with examples.

1. Hive Conditional Functions List

Select the link to know more about the function along with examples.

2. Hive Conditional Functions Examples

Before we jump into examples, first, let’s create a Hive table.

CREATE TABLE IF NOT EXISTS employee (
 id int,
 name string,
 age int,
 gender string,
 salary decimal
)
COMMENT 'Employee Table'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

Create a records.csv file @ /tmp folder with the following data. To make it simple I have created this file in my local file system, not HDFS.

1,James,30,M,10000
2,Ann,40,F,20000
3,Jeff,41,M,30000
4,Jennifer,20,F,40000
5,Kum,30,M,
7,Jeni,,,

Load into the table using the below command.

jdbc:hive2://> LOAD DATA LOCAL INPATH '/tmp/record.csv' INTO TABLE employee;
jdbc:hive2://> SELECT * FROM employee;
+--------------+----------------+---------------+------------------+------------------+
| employee.id  | employee.name  | employee.age  | employee.gender  | employee.salary  |
+--------------+----------------+---------------+------------------+------------------+
| 1            | James          | 30            | M                | 10000            |
| 2            | Ann            | 40            | F                | 20000            |
| 3            | Jeff           | 41            | M                | 30000            |
| 4            | Jennifer       | 20            | F                | 40000            |
| 5            | Kum            | 30            | M                | NULL             |
| 7            | Jeni           | NULL          |                  | NULL             |
+--------------+----------------+---------------+------------------+------------------+
6 rows selected (0.149 seconds)

Now let’s get into action with examples.

2.1 isnull( a )

This returns a true when the value of a (column) is NULL otherwise it returns false.

Syntax: isnull(column)

0: jdbc:hive2://> SELECT salary,isnull(salary) FROM employee
. . . . . . . . > WHERE gender='M';
OK
+---------+--------+
| salary  |  _c1   |
+---------+--------+
| 10000   | false  |
| 30000   | false  |
| NULL    | true   |
+---------+--------+

Above example column _C1 is derived based on salary column, if you notice isnull() function return true for value NULL and false for non NULL values.

2.2 isnotnull ( a )

This returns a true when the value of a (column) is NOT NULL otherwise it returns false.

Syntax: <em>isnotnull(column)</em>

0: jdbc:hive2://> SELECT salary,isnotnull(salary) FROM employee
. . . . . . . . > WHERE gender='M';
OK
+---------+--------+
| salary  |  _c1   |
+---------+--------+
| 10000   | true   |
| 30000   | true   |
| NULL    | false  |
+---------+--------+

2.3 If(boolean testCondition, T valueTrue, T valueFalseOrNull)

Similar to IF condition existing on SQL queries. This returns valueTrue when testCondition is true, otherwise returns valueFalseOrNull

Syntax: <em>if(boolean testCondition, T valueTrue, T valueFalseOrNull)</em>

0: jdbc:hive2://> SELECT salary,if(isnull(salary),'No Salary','Present Salary')
. . . . . . . . > FROM employee WHERE gender='M';
OK
+---------+-----------------+
| salary  |       _c1       |
+---------+-----------------+
| 10000   | Present Salary  |
| 30000   | Present Salary  |
| NULL    | No Salary       |
+---------+-----------------+

2.4 nvl(T value, T default_value)

This replaces all null values of a column T with the default value.

Syntax: nvl(T value, T default_value)

0: jdbc:hive2://> SELECT salary,Nvl(salary,-1) FROM employee
. . . . . . . . >  WHERE gender='M';
OK
+---------+--------+
| salary  |  _c1   |
+---------+--------+
| 10000   | 10000  |
| 30000   | 30000  |
| NULL    | -1     |
+---------+--------+

2.5 COALESCE(T v1, T v2, …)

Returns the first v that is not NULL, or NULL if all v’s are NULL.

Syntax: COALESCE(T v1, T v2, ...)

//TO-DO

2.6 CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

This is similar to switch statement where when a = b, returns c; when a = d, returns e; else returns f.

Syntax: <em>CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END</em>

Returns

0: jdbc:hive2://> SELECT salary,CASE salary WHEN 10000 THEN 1
. . . . . . . . > WHEN 30000 THEN 2
. . . . . . . . > ELSE 0
. . . . . . . . > END
. . . . . . . . > FROM employee where gender='M';
OK
+---------+------+
| salary  | _c1  |
+---------+------+
| 10000   | 1    |
| 30000   | 2    |
| NULL    | 0    |
+---------+------+

If you are familiar with Spark, you can read Spark CASE WHEN and WHEN OTHERWISE example

2.7 CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

When a = true, returns b; when c = true, returns d; else returns e.

Syntax: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

0: jdbc:hive2://> select salary,gender,
. . . . . . . . > case when salary=10000 then 1
. . . . . . . . > when gender='M' then 2
. . . . . . . . > else '-1' END
. . . . . . . . > from employee where gender = 'M';
OK
+---------+---------+------+
| salary  | gender  | _c2  |
+---------+---------+------+
| 10000   | M       | 1    |
| 30000   | M       | 2    |
| NULL    | M       | 2    |
+---------+---------+------+
3 rows selected (0.167 seconds)

2.8 nullif( a, b )

Returns NULL if a=b; otherwise returns a (as of Hive 2.3.0).
Shorthand for: CASE WHEN a = b then NULL

Syntax: nullif( a, b )

0: jdbc:hive2://> select nullif(1,1);
OK
+-------+
|  _c0  |
+-------+
| NULL  |
+-------+
1 row selected (0.144 seconds)
0: jdbc:hive2://> select nullif(1,2);
OK
+------+
| _c0  |
+------+
| 1    |
+------+
1 row selected (0.168 seconds)

2.9 assert_true(Boolean Condition)

When the condition is true it does nothing but when it is false it throws an exception
Though this function says it doesn’t return anything, when the value is true it returns NULL.

Syntax: assert_true(Boolean Condition)

jdbc:hive2://> select assert_true(true);
jdbc:hive2://> select assert_true(false);// returns exception
jdbc:hive2://> select assert_true(1==2);// returns exception

Related Articles

• Hive Date and Timestamp Functions | Examples
• Hive Aggregate Functions (UDAF) with Examples
• Why Hive Table is loading with NULL values?
• Hive – Create Database from Java Example
• Hive – Create Database Examples
• Hive Temporary Table Usage And How to Create?
• Hive Create Table Syntax & Usage with Examples
• Hive Create Partition Table Explained
• Hive Relational | Arithmetic | Logical Operators

Reference

• https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF