0
0

【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');

输出：

8

（6）day 获取日期中的日

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

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

输出：

8

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

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

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

输出：

8

（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】单行函数、聚合函数、窗口函数、自定义函数、炸裂函数”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？

文章导航