Hive常用函数

最新推荐文章于 2024-02-28 09:00:00 发布

欢乐海豚

最新推荐文章于 2024-02-28 09:00:00 发布

阅读量1.1k

点赞数

分类专栏： SQL数据处理文章标签： hive hadoop 数据仓库大数据

本文链接：https://blog.csdn.net/m0_37689903/article/details/133046929

版权

SQL数据处理专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一字符串函数

1 substring：截取字符串

语法一：substring(string A, int start)
返回值：string
说明：返回字符串A从start位置到结尾的字符串
语法二：substring(string A, int start, int len)
返回值：string
说明：返回字符串A从start位置开始，长度为len的字符串

select substring("hello",2);   -- 获取第二个字符以后的所有字符
ello
select substring("hello",-3);   -- 获取倒数第三个字符以后的所有字符
llo
select substring("hello",3,2);  -- 从第3个字符开始，向后获取2个字符
ll

2 replace ：替换

语法：replace(string A, string B, string C)
返回值：string
说明：将字符串A中的子字符串B替换为C。

select replace('hello', 'h', 'H')
Hello

3 regexp_replace：正则替换

语法：regexp_replace(string A, string B, string C)
返回值：string
说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符。

select regexp_replace('100-200', '(\\d+)', 'num')
num-num

4 regexp：正则匹配

语法：字符串 regexp 正则表达式
返回值：boolean
说明：若字符串符合正则表达式，则返回true，否则返回false。
1）正则匹配成功，输出true

 select 'dfsaaaa' regexp 'dfsa+'
 true

2）正则匹配失败，输出false

select 'dfsaaaa' regexp 'dfsb+';
false

5 repeat：重复字符串

语法：repeat(string A, int n)
返回值：string
说明：将字符串A重复n遍。

select repeat('123', 3);
123123123

6 split ：字符串切割

语法：split(string str, string pat)
返回值：array
说明：按照正则表达式pat匹配到的内容分割str，分割后的字符串，以数组的形式返回。

select split('a-b-c-d','-');
["a","b","c","d"]

7 nvl ：替换null值

语法：nvl(A,B)
说明：若A的值不为null，则返回A，否则返回B。

select nvl(null,1); 
1

8 concat ：拼接字符串

语法：concat(string A, string B, string C, ……)
返回：string
说明：将A,B,C……等字符拼接为一个字符串

select concat('beijing','-','shanghai','-','shenzhen');
beijing-shanghai-shenzhen

9 concat_ws：以指定分隔符拼接字符串或者字符串数组

语法：concat_ws(string A, string…| array(string))
返回值：string
说明：使用分隔符A拼接多个字符串，或者一个数组的所有元素。

select concat_ws('-','beijing','shanghai','shenzhen');
beijing-shanghai-shenzhen
select concat_ws('-',array('beijing','shenzhen','shanghai'));
beijing-shanghai-shenzhen

10 get_json_object：解析json字符串

语法：get_json_object(string json_string, string path)
返回值：string
说明：解析json的字符串json_string，返回path指定的内容。如果输入的json字符串无效，那么返回NULL

1）获取json数组里面的json具体数据

select get_json_object('[{"name":"悟空","sex":"男","age":"25"},{"name":"八戒","sex":"男","age":"47"}]','$.[0].name');
悟空

2）获取json数组里面的数据

select get_json_object('[{"name":"悟空","sex":"男","age":"25"},{"name":"八戒","sex":"男","age":"47"}]','$.[0]');
{"name":"悟空","sex":"男","age":"25"}

二集合函数

1 size：集合中元素的个数

2 map：创建map集合

语法：map (key1, value1, key2, value2, …)
说明：根据输入的key和value对构建map类型

select map('xiaohai',1,'dahai',2); 
{"xiaohai":1,"dahai":2}

3 map_keys：返回map中的key

select map_keys(map('xiaohai',1,'dahai',2));
["xiaohai","dahai"]

4 map_values: 返回map中的value

select map_values(map('xiaohai',1,'dahai',2));
[1,2]

5 array 声明array集合

语法：array(val1, val2, …)
说明：根据输入的参数构建数组array类

select array('1','2','3','4');
["1","2","3","4"]

6 array_contains: 判断array中是否包含某个元素

select array_contains(array('a','b','c','d'),'a');
true

7 sort_array：将array中的元素排序

select sort_array(array('a','d','c'));
["a","c","d"]

8 struct声明struct中的各属性

语法：struct(val1, val2, val3, …)
说明：根据输入的参数构建结构体struct类

select struct('name','age','weight');
{"col1":"name","col2":"age","col3":"weight"}

9 named_struct声明struct的属性和值

select named_struct('name','xiaosong','age',18,'weight',80);
{"name":"xiaosong","age":18,"weight":80}

三高级聚合函数

数据准备

-- 建表语句
create  table  employee(
    name string,  --姓名
    sex  string,  --性别
    birthday string, --出生年月
    hiredate string, --入职日期
    job string,   --岗位
    salary double, --薪资
    bonus double,  --奖金
    friends array<string>, --朋友
    children map<string,int> --孩子
)

-- 插入数据
insert into employee  
  values('张无忌','男','1980/02/12','2022/08/09','销售',3000,12000,array('阿朱','小昭'),map('张小无',8,'张小忌',9)),
        ('赵敏','女','1982/05/18','2022/09/10','行政',9000,2000,array('阿三','阿四'),map('赵小敏',8)),
        ('宋青书','男','1981/03/15','2022/04/09','研发',18000,1000,array('王五','赵六'),map('宋小青',7,'宋小书',5)),
        ('周芷若','女','1981/03/17','2022/04/10','研发',18000,1000,array('王五','赵六'),map('宋小青',7,'宋小书',5)),
        ('郭靖','男','1985/03/11','2022/07/19','销售',2000,13000,array('南帝','北丐'),map('郭芙',5,'郭襄',4)),
        ('黄蓉','女','1982/12/13','2022/06/11','行政',12000,null,array('东邪','西毒'),map('郭芙',5,'郭襄',4)),
        ('杨过','男','1988/01/30','2022/08/13','前台',5000,null,array('郭靖','黄蓉'),map('杨小过',2)),
        ('小龙女','女','1985/02/12','2022/09/24','前台',6000,null,array('张三','李四'),map('杨小过',2))

数据结构如下
在这里插入图片描述

1 collect_list 收集并形成list集合，结果不去重

select 
  sex,
  collect_list(job) jobs
from
  employee
group by 
  sex

在这里插入图片描述

2 collect_set 收集并形成set集合，结果去重

select 
  sex,
  collect_set(job) jobs
from
  employee
group by 
  sex

在这里插入图片描述

四炸裂函数

EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此
基础上可以对拆分后的数据进行聚合。

数据准备

-- 建表
create table movie_info(
    movie string,     --电影名称
    category string   --电影分类
) ;

-- 插入数据
insert overwrite table movie_info
values ("《疑犯追踪》", "悬疑,动作,科幻,剧情"),
       ("《Lie to me》", "悬疑,警匪,动作,心理,剧情"),
       ("《战狼2》", "战争,动作,灾难");

数据结构
在这里插入图片描述

select
    cate,
    count(*)
from
(
    select
        movie,
        cate
    from
    (
        select
            movie,
            split(category,',') cates
        from movie_info
    )t1 lateral view explode(cates) tmp as cate
)t2
group by cate;