11、Hive 教程 - Hive 内置函数集合

1、内置函数

2、数学函数

round（四舍五入）：

hive> select round(45.3456,2),round(6.56787,-1);
+------------------+-------------------+
| round(45.3456,2) | round(6.56787,-1) |
+------------------+-------------------+
|            45.35 |                10 |
+------------------+-------------------+
1 row in set (0.00 sec)

ceil（向上取整）:

hive> select ceil(56.9);
+------------+
| ceil(56.9) |
+------------+
|         57 |
+------------+
1 row in set (0.00 sec)

floor（向下取整）:

hive> select ceil(56.9);
+------------+
| ceil(56.9) |
+------------+
|         56 |
+------------+
1 row in set (0.00 sec)

3、字符函数

lower（转小写）：

hive> select lower('HAHA');
+---------------+
| lower('HAHA') |
+---------------+
| haha          |
+---------------+
1 row in set (0.00 sec)

upper（转大写）：

hive> select upper('haha');
+---------------+
| upper('haha') |
+---------------+
| HAHA          |
+---------------+
1 row in set (0.00 sec)

length（字符串长度，字符数）:

hive> select length('Are you OK?');
+-----------------------+
| length('Are you OK?') |
+-----------------------+
|                    11 |
+-----------------------+
1 row in set (0.00 sec)

concat（字符串拼接）:

hive> select concat('Are','you OK');
+------------------------+
| concat('Are','you OK') |
+------------------------+
| Areyou OK              |
+------------------------+
1 row in set (0.00 sec)

substr（求子串）:

substr(a,b)：从字符串a中，第b位开始取，取右边所有的字符

substr(a,b,c)：从字符串a中，第b为开始取，取c个字符

注意：空格算占用一个字符的空间

hive> select substr('Are you OK?',3);
+-------------------------+
| substr('Are you OK?',3) |
+-------------------------+
| e you OK?               |
+-------------------------+
1 row in set (0.00 sec)
hive> select substr('Are you OK?',3,5);
+---------------------------+
| substr('Are you OK?',3,5) |
+---------------------------+
| e you                     |
+---------------------------+
1 row in set (0.00 sec)

trim（去前后空格）:

hive> select trim(' Are you OK ');
+----------------------+
| trim(' Are you OK ') |
+----------------------+
| Are you OK           |
+----------------------+
1 row in set (0.00 sec)
mysql> 

lpad（左填充）rpad（右填充）

**hive> select lpad('abc',10,'*');
+--------------------+
| lpad('abc',10,'*') |
+--------------------+
| *******abc         |
+--------------------+
1 row in set (0.00 sec)
hive> select rpad('abc',10,'*');
+--------------------+
| rpad('abc',10,'*') |
+--------------------+
| abc*******         |
+--------------------+
1 row in set (0.00 sec)
**

4、集合函数

size：返回map集合的个数

格式：size（map（<key,value>,<key,value>））

hive> select str_to_map('a:1,b:2,c:3');
OK
{
  
    "a":"1","b":"2","c":"3"}
Time taken: 0.457 seconds, Fetched: 1 row(s)
hive> select size(str_to_map('a:1,b:2,c:3'));
OK
3

map_keys:返回map集合数

hive> select map_keys(str_to_map('a:1,b:2'));
OK
["a","b"]
Time taken: 0.034 seconds, Fetched: 1 row(s)

array_contains:判断键是否存在

hive> select array_contains(map_keys(str_to_map('a:1,b:2')),'a');

sort_array:数组排序

hive> select sort_array(split('45,23,78,43,12',','));

5、转换函数

cast：转换数据类型

hive> select cast('2018-06-28' as date);
+----------------------------+
| cast('2018-06-28' as date) |
+----------------------------+
| 2018-06-28                 |
+----------------------------+
1 row in set (0.00 sec)

6、日期函数

• to_date:从一个字符串中取出为日期的部分
• year、month、day:从一个日期中取出相应的年、月、日

hive> select year(from_unixtime(unix_timestamp(),'yyyy-MM-dd'));

• weekofyear:返回输入日期在该年中是第几个星期
• datediff:两个日期相减，返回相差天数
• date_add:在一个日期基础上增加天数
• date_sub:在一个日期基础上减去天数

7、条件函数

if：如果testCondition为 true 就返回 valueTrue，否则返回 valueFalseOrNull

hive> select if
    > (1>2,'hehe','xixi');
OK
xixi
Time taken: 0.061 seconds, Fetched: 1 row(s)

nvl： value为null 返回default_value 否则返回value

hive> select nvl(null,1);
OK
1
Time taken: 0.036 seconds, Fetched: 1 row(s)
hive> select nvl(1,3);
OK
1
Time taken: 0.039 seconds, Fetched: 1 row(s)
hive> select nvl(null,null);
OK
NULL
Time taken: 0.034 seconds, Fetched: 1 row(s)

coalesce：从左到右返回第一个不为null的值

hive> select coalesce(null,null,null);
OK
NULL
Time taken: 0.042 seconds, Fetched: 1 row(s)
hive> select coalesce(null,null,null,123,34,456);
OK
123
Time taken: 0.041 seconds, Fetched: 1 row(s)

case…when…：条件表达式

hive> select name,orderdate,cost,case cost when 29 then 'hehe' else 'xixi' end from order;

语法格式：case A when B then C [when D then E]* [else F] end

解释：对于A来说，如果判断为B则返回C，如果判断为D则返回E（此处判断条件可为多个），如果以上都不是则返回F。注意，最后还有还有一个end结束符。

png)

运行结果：

8、聚合函数

count：返回行数

sum：组内某列求和

min：组内某列最小值

max：组内某列最大值

avg：组内某列平均值

以工资sal为例：

select count(*),sum(sal),max(sal),min(sal),avg(sal) from emp;

9、表生成函数

explode：把map集合中每个键值对或数组中的每个元素都单独生成一行的形式

二、Hive 全部函数集合