Hive 函数大全（吐血整理）

前言

 
Hive 内部提供了很多函数给开发者使用，包括数学函数，类型转换函数，条件函数，字符函数，聚合函数，表生成函数等等，这些函数都统称为内置函数。

在 hive 中可通过以下命令查看函数信息：

--显示所有的可用函数，包括运算符、内置函数、自定义函数
show functions;

--显示指定函数的描述信息
desc function trim;

--显示指定函数的详细信息
desc function extended trim;

 

关系运算

 

1、等值比较：=

语法：A = B
操作类型：基本类型
描述：如果表达式 A 与表达式 B 相等，则为 TRUE；否则为 FALSE。

select 1 where 1 = 1;

1

2、不等值比较：<>

语法：A <> B
操作类型：基本类型
描述：如果表达式 A 或表达式 B 为 NULL，则返回 NULL；如果表达式 A 与 表达式 B 不相等，则为 TRUE；否则为 FALSE。

select 1 where 1 <> 2;

1

3、小于比较：<

语法：A < B
操作类型：基本类型
描述：如果表达式 A 或表达式 B 为 NULL，则返回 NULL；如果表达式 A 小于表达式 B，则为 TRUE；否则为 FALSE。

select 1 where 1 < 2;

1

4、小于等于比较：<=

语法：A <= B
操作类型：基本类型
描述：如果表达式 A 或表达式 B 为 NULL，则返回 NULL；如果表达式 A 小于或者等于表达式 B，则为 TRUE；否则为 FALSE。

select 1 where 1 <= 1;

1

5、大于比较：>

语法：A > B
操作类型：基本类型
描述：如果表达式 A 或表达式 B 为 NULL，则返回 NULL；如果表达式 A 大于表达式 B，则为 TRUE；否则为 FALSE。

select 1 where 2 > 1;

1

6、大于等于比较：>

语法：A >= B
操作类型：基本类型
描述：如果表达式 A 或表达式 B 为 NULL，则返回 NULL；如果表达式 A 大于或者等于表达式 B，则为 TRUE；否则为 FALSE。

select 1 where 1 >= 1;

1

7、空值判断：IS NULL

语法：A IS NULL
操作类型：所有类型
描述：如果表达式 A 的值为 NULL，则为 TRUE；否则为 FALSE。

select 1 where null is null;

1

8、非空判断：IS NOT NULL

语法：A IS NOT NULL
操作类型：所有类型
描述：如果表达式 A 的值为 NULL，则为 FALSE；否则为 TRUE。

select 1 where 1 is null;

1

9、LIKE 比较：LIKE

语法：A LIKE B
操作类型：字符类型
描述：如果字符串 A 或字符串 B 为 NULL，则返回 NULL；如果字符串 A 符合简单 SQL 正则表达式 B 的语法，则为 TRUE；否则为 FALSE。

字符 ”_” 表示任意单个字符

select 1 where 'football' like 'foot____';

1

字符 ”%” 表示任意数量的字符

select 1 where 'football' like 'foot%';

1

注意：否定比较时候用 NOT A LIKE B

select 1 where not 'football' like 'fff%';

1

10、JAVA 的 LIKE 比较：RLIKE

语法：A RLIKE B
操作类型：字符类型
描述：如果字符串 A 或字符串 B 为 NULL，则返回 NULL；如果字符串 A 符合 JAVA 正则表达式 B 的正则语法，则为 TRUE；否则为 FALSE。

select 1 where 'football' rlike '^f.*l$';

1

11、正则匹配：REGEXP

语法：A REGEXP B
操作类型：字符类型
描述：功能与 RLIKE 相同

select 1 where 'football' regexp '^f.*l$';

1

 

数学运算

 

1、加法操作：+

语法：A + B
操作类型：数值类型
说明：返回 A 与 B 相加的结果。结果的数值类型等于 A 的类型和 B 的类型的最小父类型。比如，int + int 一般结果为 int 类型，而 int + double 一般结果为 double 类型。

select 1 + 2;

3

select 1 + 0.5;

1.5

2、减法操作：-

语法：A - B
操作类型：数值类型
说明：返回 A 与 B 相减的结果。结果的数值类型等于 A 的类型和 B 的类型的最小父类型。比如，int - int 一般结果为 int 类型，而 int - double 一般结果为 double 类型。

select 3 - 2;

1

select 1.5 - 1;

0.5

3、乘法操作：*

语法：A * B
操作类型：数值类型
说明：返回 A 与 B 相乘的结果。结果的数值类型等于 A 的类型和 B 的类型的最小父类型。注意，如果 A 乘以 B 的结果超过默认结果类型的数值范围，则需要通过 cast 将结果转换成范围更大的数值类型。

select 3 * 8;

24

4、除法操作：/

语法：A / B
操作类型：数值类型
说明：返回 A 除以 B 的结果。结果的数值类型为 double。

select 10 / 5;

2.0

注意：hive 中最高精度的数据类型是 double，只精确到小数点后16位，在做除法运算的时候要特别注意

5、取余操作：%

语法：A % B
操作类型：数值类型
说明：返回 A 除以 B 的余数。结果的数值类型等于 A 的类型和 B 的类型的最小父类型。

select 5 % 2;

1

若对浮点数求余，可能会出现如下结果

select 4.2 % 2;

0.20000000000000018

注意：精度在 hive 中是个很大的问题，类似这样的操作最好通过 round 指定精度

select round(4.2 % 2, 2);

0.2

6、位与操作：&

语法：A & B
操作类型：整数类型
说明：返回 A 和 B 按位进行与操作的结果。结果的数值类型等于 A 的类型和 B 的类型的最小父类型。

select 4 & 8;

0

select 6 & 4;

4

7、位或操作：|

语法：A | B
操作类型：整数类型
说明：返回 A 和 B 按位进行或操作的结果。结果的数值类型等于 A 的类型和 B 的类型的最小父类型。

select 4 | 8;

12

select 6 | 8;

14

8、位异或操作：^

语法：A ^ B
操作类型：整数类型
说明：返回 A 和 B 按位进行异或操作的结果。结果的数值类型等于 A 的类型和 B 的类型的最小父类型。

select 4 ^ 8;

12

select 6 ^ 4;

2

9、按位取反操作：~

语法：~ A
操作类型：整数类型
说明：返回 A 按位取反操作的结果。结果的数值类型等于 A 的类型。

select ~ 4;

-5

select ~ 6;

-7

 

逻辑运算

 

1、逻辑与操作：AND

语法：A AND B
操作类型：boolean
说明：如果 A 或 B 为 NULL，则返回 NULL；如果 A 和 B 均为 TRUE，则为 TRUE；否则为 FALSE。

select 1 where 1 = 1 and 2 = 2;

1

2、逻辑与操作：OR

语法：A OR B
操作类型：boolean
说明：如果 A 或 B 为 NULL，则返回 NULL；如果 A 或 B 有一个为 TRUE，则为 TRUE；否则为 FALSE。

select 1 where 1 = 2 or 2 = 2;

1

3、逻辑非操作：NOT

语法：NOT A
操作类型：boolean
说明：如果 A 为 NULL，或者 A 为 FALSE，则为TRUE；否则为FALSE。

select 1 where not 1 = 2;