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.
| Conditional Function | Description |
|---|---|
| isnull( a ) | This returns a true when the value of a (column) is NULL otherwise it returns false. Return: boolean |
| isnotnull ( a ) | This returns a true when the value of a (column) is NOT NULL otherwise it returns false. Return: boolean |
| 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 Return: T |
| nvl(T value, T default_value) | This replaces all null values of column T with the default value. Return: T |
| COALESCE(T v1, T v2, …) | Returns the first v that is not NULL, or NULL if all v’s are NULL. Return: T |
| CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | This is similar to the switch statement where when a = b, returns c; when a = d, returns e; else returns f. Return: T |
| 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. Return: T |
| 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 else a Return: T |
| 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. Return: void |
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