【Hive_03】单行函数、聚合函数、窗口函数、自定义函数、炸裂函数

导航：

【Hive_01】hive关于数据库和表的语法

【Hive_02】查询、分组以及四个by

【Hive_03】单行函数、聚合函数、窗口函数、自定义函数、炸裂函数

【Hive_04】分区分桶表以及文件格式

【Hive_05】企业调优1（资源配置、explain、join优化）

1、函数简介

Hive会将常用的逻辑封装成函数给用户进行使用，类似于Java中的函数。

好处：避免用户反复写逻辑，可以直接拿来使用。

重点：用户需要知道函数叫什么，能做什么。

Hive提供了大量的内置函数，按照其特点可大致分为如下几类：单行函数、聚合函数、炸裂函数、窗口函数。

以下命令可用于查询所有内置函数的相关信息。

1）查看系统内置函数

hive>show functions;

查看和string有关的函数：

show functions like'string%';

2）查看内置函数用法

hive>descfunction upper;--查看upper函数的用法

3）查看内置函数详细信息

hive>descfunctionextended substring;

在这里插入图片描述

2、单行函数

单行函数的特点是一进一出，即输入一行，输出一行。进：传的参数，出：返回值。

单行函数按照功能可分为如下几类: 日期函数、字符串函数、集合函数、数学函数、流程控制函数等。

2.1 算术运算函数

运算符描述A+BA和B相加A-BA减去BA*BA和B 相乘A/BA除以BA%BA对B取余A&BA和B按位取与A|BA和B按位取或A^BA和B按位取异或~AA按位取反

--算术运算函数select1&0;--结果为0select3&2;--结果为2。先把3和2转换为2进制，再把处理结果转换为十进制。

查询出所有员工的薪水后加1显示:

hive (default)>select sal +1from emp;

2.2 数值函数

1）round：四舍五入

hive>selectround(3.3);3

在这里插入图片描述

2）ceil：向上取整

hive>select ceil(3.1);4

3）floor：向下取整

hive>select floor(4.8);4

2.3 字符串函数

（1）substring 截取字符串

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

案例实操：

（1）获取第二个字符以后的所有字符

hive>select substring("wenxin",2);

输出：

enxin

（2）获取倒数第三个字符以后的所有字符

hive>select substring("wenxin",-3);

输出：

xin

（3）从第3个字符开始，向后获取2个字符

hive>select substring("wenxin",3,2);

输出：

nx

（2）replace 替换

语法：

replace(string A, string B, string C)

返回值：

string

说明：将字符串A中的子字符串B替换为C。

hive>selectreplace('wenxinwenxin','w','W')

输出：

hive> WenxinWenxin

在这里插入图片描述

（3）regexp_replace 正则替换

语法：

regexp_replace(string A, string B, string C)

返回值：

string

说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符。

案例实操：

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

上面这个涉及反斜杠转义字符的问题，所有有两个反斜杠

输出：

hive> num-num

在这里插入图片描述

正则表达式可视化网址

在这里插入图片描述

（4）regexp 正则匹配

语法：字符串 regexp 正则表达式

返回值：boolean

说明：若字符串符合正则表达式，则返回true，否则返回false。

（1）正则匹配成功，输出true

hive>select'dfsaaaa'regexp'dfsa+'

输出：

hive>true

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

hive>select'dfsaaaa'regexp'dfsb+';

输出：

hive>false

（5）repeat 重复字符串

语法：

repeat(string A, int n)

返回值：

string

说明：将字符串A重复n遍。

hive>selectrepeat('123',3);

输出：

hive>123123123

（6）split 字符串切割

语法：

split(string str, string pat)

返回值：array

说明：按照正则表达式pat匹配到的内容分割str，分割后的字符串，以数组的形式返回。

hive>select split('a-b-c-d','-');

输出：

hive>["a","b","c","d"]

在这里插入图片描述

（7）nvl 替换null值

语法：

nvl(A,B)

说明：若A的值不为null，则返回A，否则返回B。可以把其理解为给默认值的操作。

hive>select nvl(null,0);

输出：

hive>0

（8）concat 拼接字符串

语法：

concat(string A, string B, string C, ……)

返回：string

说明：将A,B,C……等字符拼接为一个字符串

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

输出：

hive> beijing-shanghai-shenzhen

在这里插入图片描述

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

语法：

concat_ws(string A, string…| array(string))

返回值：string
说明：使用分隔符A拼接多个字符串，或者一个数组的所有元素。

hive>select concat_ws('-','beijing','shanghai','shenzhen');

输出：

hive> 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具体数据

hive>select get_json_object('[{"name":"大海海","sex":"男","age":"25"},{"name":"小宋宋","sex":"男","age":"47"}]','$.[0].name');

在这里插入图片描述

输出：

hive> 大海海

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

hive>select get_json_object('[{"name":"大海海","sex":"男","age":"25"},{"name":"小宋宋","sex":"男","age":"47"}]','$.[0]');

输出：

hive> {"name":"大海海","sex":"男","age":"25"}

2.4 日期函数

（1）unix_timestamp 返回当前或指定时间的时间戳

语法：unix_timestamp()
返回值：bigint
案例实操：

hive>select unix_timestamp('2022/08/08 08-08-08','yyyy/MM/dd HH-mm-ss');

输出：

1659946088

说明：-前面是日期后面是指，日期传进来的具体格式
在这里插入图片描述

时间戳有10位的和13位的：

10位的时间戳单位是秒
13位的单位是毫秒

无论是把时间戳转换为时间字符串，还是将时间字符串转换为时间戳，都需要考虑时区的问题。

（2）from_unixtime

用法：转化UNIX时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式

语法：

from_unixtime(bigint unixtime[, string format])

返回值：string

案例实操：

hive>select from_unixtime(1659946088);

输出：

2022-08-0808:08:08

在这里插入图片描述

（3）current_date 当前日期

hive>selectcurrent_date;

输出：

2022-07-11

（4）current_timestamp 当前的日期加时间，并且精确的毫秒

hive>selectcurrent_timestamp;

输出：

2022-07-1115:32:22.402

（5）month：获取日期中的月

语法：month (string date)
返回值：int
案例实操：

hive>selectmonth('2022-08-08 08:08:08');

输出：

（6）day 获取日期中的日

语法：day (string date)
返回值：int
案例实操：

hive>selectday('2022-08-08 08:08:08')

输出：

（7）hour 获取日期中的小时

语法：hour (string date)
返回值：int
案例实操：

hive>selecthour('2022-08-08 08:08:08');

输出：

（8）datediff 两个日期相差的天数（结束日期减去开始日期的天数）

语法：datediff(string enddate, string startdate)
返回值：int
案例实操：

hive>select datediff('2021-08-08','2022-10-09');

输出：

-427

在这里插入图片描述

（9）date_add：日期加天数

语法：

date_add(string startdate, int days)

返回值：string

说明：返回开始日期 startdate 增加 days 天后的日期

案例实操：

hive>select date_add('2022-08-08',2);

输出：

2022-08-10

（10）date_sub：日期减天数

语法：

date_sub (string startdate, int days)

返回值：string

说明：返回开始日期startdate减少days天后的日期。

案例实操：

hive>select date_sub('2022-08-08',2);

输出：

2022-08-06

（11）date_format:将标准日期解析成指定格式字符串

hive>select date_format('2022-08-08','yyyy年-MM月-dd日')

输出：

2022年-08月-08日

2.5 流程控制函数

（1）case when：条件判断函数

语法一：

case when a then b [when c then d]* [else e] end

返回值：T
说明：如果a为true，则返回b；如果c为true，则返回d；否则返回 e

hive>selectcasewhen1=2then'tom'when2=2then'mary'else'tim'endfrom tabl eName; 
mary

语法二：

case a when b then c [when d then e]* [else f] end

返回值: T
说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f

hive>selectcase100when50then'tom'when100then'mary'else'tim'endfrom t ableName; 
mary

case在执行的时候，是逐个条件去判断的，先判断第一个when，不满足才去判断第二个when。

（2）if: 条件判断，类似于Java中三元运算符

语法：

if（boolean testCondition, T valueTrue, T valueFalseOrNull）

返回值：T

说明：当条件testCondition为true时，返回valueTrue；否则返回valueFalseOrNull

（1）条件满足，输出正确

hive>selectif(10>5,'正确','错误');

输出：正确

（2）条件满足，输出错误

hive>selectif(10<5,'正确','错误');

输出：错误

2.6 集合函数

（1）size 集合中元素的个数

hive> select size(friends) from test; --2/2 每一行数据中的friends集合里的个数

在这里插入图片描述

（2）map 创建map集合

语法：map (key1, value1, key2, value2, …)

说明：根据输入的key和value对构建map类型

案例实操：

hive>select map('xiaohai',1,'dahai',2);

输出：

hive> {"xiaohai":1,"dahai":2}

在这里插入图片描述

（3）map_keys 返回map中的key

hive>select map_keys(map('xiaohai',1,'dahai',2));

输出：

hive>["xiaohai","dahai"]

（4）map_values 返回map中的value

hive>select map_values(map('xiaohai',1,'dahai',2));

输出：

hive>[1,2]

（5）array 声明array集合

语法：array(val1, val2, …)

说明：根据输入的参数构建数组array类

案例实操：

hive>select array('1','2','3','4');

输出：

hive>["1","2","3","4"]

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

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

输出：

hive> true

（7）sort_array 将array中的元素排序

hive>select sort_array(array('a','d','c'));

输出：

hive>["a","c","d"]

在这里插入图片描述

（8）struct 声明struct中的各属性

语法：struct(val1, val2, val3, …)

说明：根据输入的参数构建结构体struct类

案例实操：

hive>select struct('name','age','weight');

输出：得到带有字段名的结构体

hive> {"col1":"name","col2":"age","col3":"weight"}

（9）named_struct 声明struct的属性和值

hive>select named_struct('name','xiaosong','age',18,'weight',80);

输出：

hive> {"name":"xiaosong","age":18,"weight":80}

3、高级聚合函数

聚合效果：多进一出（多行传入，一个行输出）。

1）普通聚合 count/sum

3.1 collect_list 函数

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

hive>select 
  sex,
  collect_list(job)from
  employee
groupby 
  sex

结果：

女    ["行政","研发","行政","前台"]
男    ["销售","研发","销售","前台"]

在这里插入图片描述

3.2 collect_set 函数

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

hive>select 
  sex,
  collect_set(job)from
  employee
groupby 
  sex

结果：

女    ["行政","研发","前台"]
男    ["销售","研发","前台"]

4、炸裂函数

在这里插入图片描述

4.1 expload 函数

在这里插入图片描述

4.2 posexplode 函数

posexplode相比于explode还会返回索引。

在这里插入图片描述

4.3 inline 函数

inline里面接受的是一个数组，数组里面的元素是结构体

在这里插入图片描述

4.4 Lateral View

在这里插入图片描述

5、窗口函数（开窗函数）

5.1 概述

在这里插入图片描述

这里窗口范围描述的上一行到当前行指的是：计算当中实际参与的数据的上一行到当前行，而指的不是原表的上一行到当前行。
并且对于基于值的窗口函数，只有当between和and当中有包含数值的时候，必须保证order by后面的column是数值型或者字符串的。
total_amount：相当于截止到每一个下单日期的总额。

在这里插入图片描述

由于进行了分区，因此当到达4的时候，和前面的3不在同一个分区，因此只包含其自身。
有分区之后的第一行到当前行指的是：该分区当中的第一行到当前行。

在这里插入图片描述

对于order by：在基于行和基于值两方面是不同的。在基于行上，如果不写order by，则是随机的，不确定，这样后面写range…between也没有意义【从负无穷到正无穷】。

5.2 常用窗口函数

按照功能，常用窗口可划分为如下几类：聚合函数、跨行取值函数、排名函数。

（1）聚合函数

max：最大值。
min：最小值。
sum：求和。
avg：平均值。
count：计数。

（2）跨行取值函数

（1）lead和lag
在这里插入图片描述

注：lag和lead函数不支持自定义窗口。

（2）first_value和last_value

在这里插入图片描述

（3）排名函数

在这里插入图片描述

注：rank 、dense_rank、row_number不支持自定义窗口。

6、自定义函数

在hive当中有多种函数：比如单行函数、聚合函数、UDTF函数、窗口函数。而这四类函数都可以进行自定义。

在具体使用时候，一般较少自定义，如果有也是自定义单行函数。

1）Hive自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。

2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

3）根据用户自定义函数类别分为以下三种：

UDF（User-Defined-Function）：一进一出。
UDAF（User-Defined Aggregation Function）：用户自定义聚合函数，多进一出。类似于：count/max/min
UDTF（User-Defined Table-Generating Functions）：用户自定义表生成函数，一进多出。如lateral view explode()

4）官方文档地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins

5）编程步骤

（1）继承Hive提供的类

org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

（2）实现类中的抽象方法

（3）在hive的命令行窗口创建函数

添加jar。

add jar linux_jar_path

创建function。

create[temporary]function[dbname.]function_name AS class_name;

（4）在hive的命令行窗口删除函数

drop[temporary]function[ifexists][dbname.]function_name;

7、自定义UDF函数

需求：

自定义一个UDF实现计算给定基本数据类型的长度，例如：

hive(default)>select my_len("abcd");4

1）创建一个Maven工程Hive

2）导入依赖

<dependencies><dependency><groupId>org.apache.hive</groupId><artifactId>hive-exec</artifactId><version>3.1.3</version></dependency></dependencies>

3）创建一个类

在这里插入图片描述

publicclassMyUDFextendsGenericUDF{/**
     * 判断传进来的参数的类型和长度
     * 约定返回的数据类型
     */@OverridepublicObjectInspectorinitialize(ObjectInspector[] arguments)throwsUDFArgumentException{if(arguments.length !=1){thrownewUDFArgumentLengthException("please give me  only one arg");}if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){thrownewUDFArgumentTypeException(1,"i need primitive type arg");}returnPrimitiveObjectInspectorFactory.javaIntObjectInspector;}/**
     * 解决具体逻辑的
     */@OverridepublicObjectevaluate(DeferredObject[] arguments)throwsHiveException{Object o = arguments[0].get();if(o==null){return0;}return o.toString().length();}@Override// 用于获取解释的字符串publicStringgetDisplayString(String[] children){return"";}}

4）创建临时函数

（1）打成jar包上传到服务器/opt/module/hive/datas/myudf.jar

（2）将jar包添加到hive的classpath，临时生效

hive (default)>add jar /opt/module/hive/datas/myudf.jar;

（3）创建临时函数与开发好的java class关联

hive (default)>createtemporaryfunction my_len 
as"com.wenxin.hive.udf.MyUDF";

（4）即可在hql中使用自定义的临时函数

hive (default)>select 
    ename,
    my_len(ename) ename_len 
from emp;

（5）删除临时函数

hive (default)>droptemporaryfunction my_len;

注意：临时函数只跟会话有关系，跟库没有关系。只要创建临时函数的会话不断，在当前会话下，任意一个库都可以使用，其他会话全都不能使用。

5）创建永久函数

（1）创建永久函数

注意：因为add jar本身也是临时生效，所以在创建永久函数的时候，需要制定路径（并且因为元数据的原因，这个路径还得是HDFS上的路径）。

hive (default)>createfunction my_len2 
as"com.atguigu.hive.udf.MyUDF"using jar "hdfs://hadoop102:8020/udf/myudf.jar";

（2）即可在hql中使用自定义的永久函数

hive (default)>select 
    ename,
    my_len2(ename) ename_len 
from emp;

（3）删除永久函数

hive (default)>dropfunction my_len2;

注意：永久函数跟会话没有关系，创建函数的会话断了以后，其他会话也可以使用。

永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。

永久函数使用的时候，需要在指定的库里面操作，或者在其他库里面使用的话加上，库名.函数名。

8、其他

8.1 hive当中去重的手段？面试题

（1）distinct 去重

selectdistinct
    user_id,
    create_date
from order_info

distinct后面直接跟多个字段，则会根据多个字段进行去重。
对多个字段进行去重的时候，不需要加括号！

（2）group by

select 
    user_id,
    create_date
from order_info
group byuser_id,create_date

按照两个字段去重，则会按照两个字段进行分组，因为group by会把两个字段相同的分到一组。并且一组只会返回一条数据。

（3）利用窗口函数去重

select
    user_id,
    create_date,
    row_number()over(partitionby user_id,create_date) rn
from order_info

在这里插入图片描述

将每个分区里面排名为1的取出来即可。

select
    user_id,
    create_date,from(select
        user_id,
        create_date,
        row_number()over(partitionby user_id,create_date) rn
    from order_info
)t1
where rn=1;

标签： hive hadoop 数据仓库

本文转载自: https://blog.csdn.net/wxfighting/article/details/134999831
版权归原作者 温欣2030 所有，如有侵权，请联系我们删除。

【Hive_03】单行函数、聚合函数、窗口函数、自定义函数、炸裂函数

1、函数简介

2、单行函数

2.1 算术运算函数

2.2 数值函数

2.3 字符串函数

（1）substring 截取字符串

（2）replace 替换

（3）regexp_replace 正则替换

（4）regexp 正则匹配

（5）repeat 重复字符串

（6）split 字符串切割

（7）nvl 替换null值

（8）concat 拼接字符串

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

（10）get_json_object 解析json字符串

2.4 日期函数

（1）unix_timestamp 返回当前或指定时间的时间戳

（2）from_unixtime

（3）current_date 当前日期

（4）current_timestamp 当前的日期加时间，并且精确的毫秒

（5）month：获取日期中的月

（6）day 获取日期中的日

（7）hour 获取日期中的小时

（8）datediff 两个日期相差的天数（结束日期减去开始日期的天数）

（9）date_add：日期加天数

（10）date_sub：日期减天数

（11）date_format:将标准日期解析成指定格式字符串

2.5 流程控制函数

（1）case when：条件判断函数

（2）if: 条件判断，类似于Java中三元运算符

2.6 集合函数

（1）size 集合中元素的个数

（2）map 创建map集合

（3）map_keys 返回map中的key

（4）map_values 返回map中的value

（5）array 声明array集合

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

（7）sort_array 将array中的元素排序

（8）struct 声明struct中的各属性

（9）named_struct 声明struct的属性和值

3、高级聚合函数

3.1 collect_list 函数

3.2 collect_set 函数

4、炸裂函数

4.1 expload 函数

4.2 posexplode 函数

4.3 inline 函数

4.4 Lateral View

5、窗口函数（开窗函数）

5.1 概述

5.2 常用窗口函数

（1）聚合函数

（2）跨行取值函数

（3）排名函数

6、自定义函数

7、自定义UDF函数

8、其他

8.1 hive当中去重的手段？面试题

（1）distinct 去重

（2）group by

（3）利用窗口函数去重

发表评论

“【Hive_03】单行函数、聚合函数、窗口函数、自定义函数、炸裂函数”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航