Hive常用函数

 

往期推荐

Flink中Checkpoint和Savepoint 的 3 个不同点

Flink实现固定时长或消息条数的触发器

Flink方案设计中的4大误区

使用 Broadcast State 的 4 个注意事项

3种Flink State Backend | 你该用哪个？

一文搞定 Flink 异步 I/O

Flink State 使用的4点建议

Flink在开发中的7点建议

 

转载是一种动力 分享是一种美德, 欢迎关注 大数据与数据仓库公众号, 回复 spark 领取资料

 

操作手册：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

一、常用命令

select upper(empname) from emp;
select unix_timestamp(trackTime) from bflog limit 3 ;
select year(hiredate) from emp ;
select month(hiredate) from emp ;
select hour(hiredate) from emp ;
select substr(hiredate,1,4) from .emp ;
select split(hiredate,'-')[1] from emp ;
select reverse(hiredate) from emp ;
select concat(empno,'-',empname) from emp ;

1. 显示所有函数

# 显示所有内置函数：
show functions;

1. 显示函数信息

# 显示concat函数的使用描述信息：
desc function concat;

1. 显示函数扩展信息

# 显示concat使用示例：
desc function extended concat;

二、Hive内置函数

（一）创建虚表

dual表的概念来自oracle，数据库建立时即与数据字典一起初始化，该表只有一个varchar2类型名为dummy的字段，表数据只有一行“X”，用来查询一些系统信息，如select sysdate from dual; select user from dual;select seq.nextval from dual等。
为了能在hive中测试一些时间、数学、聚合函数，可以仿照oracle创建dual表。
创建一个名叫DUAL的表。

hive> create table dual (dummy string);
# 插入一条记录，否则没有返回值

直接退出hive，回到Linux里面。生成一个dual.txt的文件

root@hadoopmaster:~# echo 'X' > dual.txt

把dual.txt放到hadoop的hdfs里面去，跟dual表关联起来。

hive> load data local inpath '/root/dual.txt' overwrite into table dual;

使用虚表：

hive> select 'hello' from dual;

（二）简单函数

1. 数学函数

1) rand

取随机数函数: rand

2) floor

向下取整函数: floor

3) ceil

向上取整函数: ceil

4) round

四舍五入

2. 类型转换

1) cast

cast类型转换：
--将浮点转为int

select cast(1.5 as int) from dual;

2) json

json函数：
get_json_object
$.name 取值
(注意数据格式)

# 单字段
hive> select get_json_object('{"name":"张三","age":"20"}','$.name')
     from dual limit ;
# 多字段
0: jdbc:hive2://192.168.71.130:10000> select get_json_object('{"name":"张三","age":20}','$.name')  name,get_json_object('{"name":"张三","age":20}','$.age') age from dual;
OK
+-------+------+
| name  | age  |
+-------+------+
| 张三    | 20   |
+-------+------+
1 row selected (0.394 seconds)

3) parse_url

parse_url('http://baidu.com/path')
通过url可以取值：

select parse_url('http://facebook.com/path/p1.php?query=1', 'PROTOCOL') from dual;   --http
select parse_url('http://facebook.com/path/p1.php?query=1', 'HOST') from dual;---facebook.com​
select parse_url('http://facebook.com/path/p1.php?query=1', 'REF') from dual;---空
select parse_url('http://facebook.com/path/p1.php?query=1', 'PATH') from dual;---/path/p1.php
select parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY') from dual;---空
select parse_url('http://facebook.com/path/p1.php?query=1', 'FILE') from dual;---/path/p1.php?query=1
select parse_url('http://facebook.com/path/p1.php?query=1', 'AUTHORITY') from dual;---facebook.com
select parse_url('http://facebook.com/path/p1.php?query=1', 'USERINFO') from dual;​---空

3. 字符函数

1) length

2) concat

字符串连接函数：
concat

3) concat_ws

带分隔字符串连接函数
concat_ws
使用-进行连接字符串数组中的字符

select concat_ws('-',字符串数组)
from dual;

4) 收集字符串

collect_list

--不去重
collect_list()
select collect_set(id) from winfunc;

collect_set

--去重
collect_set()
select collect_set(id) from winfunc;

5) substring

字符串截取函数：substr,substring

hive> select substr('abcde',3) from dual;

6) regexp_replace

正则表达式替换函数： regexp_replace

hive> select regexp_replace('foobar ', 'oo|ar','') from dual;

7) regexp_extract

正则表达式解析函数： regexp_extract

hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from dual;

8) space

空格字符串函数：space
语法: space(int n)
返回值: string
说明：返回长度为n的字符串
举例：

hive> select space(10) from dual;
hive> select length(space(10)) from dual;

9) repeat

重复字符串函数：repeat
语法: repeat(string str, int n)
返回值: string
说明：返回重复n次后的str字符串

举例：

hive> select repeat('abc',5) from dual;

10) ascii

首字符ascii函数：ascii
语法: ascii(string str)
返回值: int
说明：返回字符串str第一个字符的ascii码
举例：

hive> select ascii('abcde') from dual;

4. 条件函数

1) COALESCE

非空查找函数: COALESCE
返回值: T
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

select if(2>1,'v1','v2') from dual;

2) if

if(条件，值1，值2)
--当条件成立，返回值1，不成立，返回值2
示例：

select if(2>1,'v1','v2') from dual;

3) case

case when 判断条件 then 结果
when 判断条件 then 结果
else 结果
end
示例：

select case when id='1001' then 
'v1' 
when id='1002' then 'v2'
else 'v3'
end

5. 日期函数

1) year

日期转年函数: year

hive> select year('2011-12-08 10:03:01') from dual;

2) day

日期转天函数: day

hive> select day('2011-12-08 10:03:01') from dual;

3) datediff

日期比较函数: datediff

hive> select datediff('2012-12-08','2012-05-09') from dual;

4) date_add

日期减少函数: date_add
语法: date_add(string startdate, int days)
返回值: string
说明:返回开始日期startdate减少days天后的日期。

hive> select date_add('2012-12-08',10) from dual;
2012-11-28

5) date_sub

日期减少函数: date_sub
语法: date_sub (string startdate, int days)
返回值: string
说明:返回开始日期startdate减少days天后的日期。

hive> select date_sub('2012-12-08',10) from dual;
2012-11-28

6）unix_timestamp

6. 集合函数

1) size

hive> select size(t) from udftest;

2) map_keys

map_keys(Map<K.V>)                  
返回一个数组，元素是map的key

3) map_values

map_values(Map<K.V>)                
返回一个数组，元素是map的值

4) array_contains

array_cont