【Hive】条件函数

Hive版本: hive-1.1.0-cdh5.14.2

1. if函数

语法：if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
描述：如果testCondition为true，返回valueTrue，否则返回valueFalseOrNull

0: jdbc:hive2://node03:10000> select if(1=2, 'TrueValue', 'FalseOrNull');
+--------------+--+
|     _c0      |
+--------------+--+
| FalseOrNull  |
+--------------+--+

2. 空值判断函数：isnull / isnotnull

语法：isnull( a ) / isnotnull( a )
返回值：boolean
描述：isnull：如果a为空，返回true，否则返回false； isnotnull：如果a不为空，返回true，否则返回false

0: jdbc:hive2://node03:10000> select isnotnull(1) as col1, isnotnull(null) as col2;
+-------+--------+--+
| col1  |  col2  |
+-------+--------+--+
| true  | false  |
+-------+--------+--+

3. 去空函数: nvl

语法：nvl(T value, T default_value)
返回值: T
描述：如果value为空，返回default_value,否则返回value

0: jdbc:hive2://node03:10000> select nvl(null, 'defaultValue') as col1, nvl(1, 'defaultValue') as clo2;
+---------------+-------+--+
|     col1      | clo2  |
+---------------+-------+--+
| defaultValue  | 1     |
+---------------+-------+--+

4. 非空查找函数：coalesce

语法: COALESCE(T v1, T v2, …)
返回值: T
描述：返回第一个不为空的T值，如果都为空，则返回空

0: jdbc:hive2://node03:10000> select coalesce(null,1,2,3) as col1, coalesce(null,null) as col2;
+-------+-------+--+
| col1  | col2  |
+-------+-------+--+
| 1     | NULL  |
+-------+-------+--+

5. 条件判断函数: case when

语法：（1）CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
（2）CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
返回值：T
描述：（1）如果a=b，返回c，如果a=d，返回e，否则返回f；
（2）如果a是true，返回b，如果c为true，返回d，否则返回e；

0: jdbc:hive2://node03:10000> select case 1 when 1 then 1 when 2 then 2 else null end as col1,
. . . . . . . . . . . . . . > case when 1=1 then 1 else null end as col2;
+-------+-------+--+
| col1  | col2  |
+-------+-------+--+
| 1     | 1     |
+-------+-------+--+

6. 断言函数: assert_true

语法: assert_true(boolean condition)
返回值：void
描述: 如果condition成立，返回null，不成立则抛出异常

0: jdbc:hive2://node03:10000> select assert_true(2<1);
Error: java.io.IOException: org.apache.hadoop.hive.ql.metadata.HiveException: ASSERT_TRUE(): assertion failed. (state=,code=0)

0: jdbc:hive2://node03:10000> select assert_true(1<2);
+-------+--+
|  _c0  |
+-------+--+
| NULL  |
+-------+--+