【Hive】——函数

1 概述

2 内置函数

1. 内置函数（build-in）指的是Hive开发实现好，直接可以使用的函数,也叫做内建函数。
2. 官方文档地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

describefunctionextended get_json_object;------------String Functions 字符串函数------------select concat("angela","baby");--带分隔符字符串连接函数：concat_ws(separator, [string | array(string)]+)select concat_ws('.','www', array('itcast','cn'));--字符串截取函数：substr(str, pos[, len]) 或者  substring(str, pos[, len])select substr("angelababy",-2);--pos是从1开始的索引，如果为负数则倒着数select substr("angelababy",2,2);--正则表达式替换函数：regexp_replace(str, regexp, rep)select regexp_replace('100-200','(\\d+)','num');--正则表达式解析函数：regexp_extract(str, regexp[, idx]) 提取正则匹配到的指定组内容select regexp_extract('100-200','(\\d+)-(\\d+)',2);--URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数select parse_url('http://www.itcast.cn/path/p1.php?query=1','HOST');--分割字符串函数: split(str, regex)select split('apache hive','\\s+');--json解析函数：get_json_object(json_txt, path)--$表示json对象select get_json_object('[{"website":"www.itcast.cn","name":"allenwoon"}, {"website":"cloud.itcast.com","name":"carbondata 中文文档"}]','$.[1].website');--字符串长度函数：length(str | binary)select length("angelababy");--字符串反转函数：reverseselect reverse("angelababy");--字符串连接函数：concat(str1, str2, ... strN)--字符串转大写函数：upper,ucaseselect upper("angelababy");selectucase("angelababy");--字符串转小写函数：lower,lcaseselect lower("ANGELABABY");selectlcase("ANGELABABY");--去空格函数：trim 去除左右两边的空格select trim(" angelababy ");--左边去空格函数：ltrimselect ltrim(" angelababy ");--右边去空格函数：rtrimselect rtrim(" angelababy ");--空格字符串函数：space(n) 返回指定个数空格select space(4);--重复字符串函数：repeat(str, n) 重复str字符串n次selectrepeat("angela",2);--首字符ascii函数：asciiselect ascii("angela");--a对应ASCII 97--左补足函数：lpadselect lpad('hi',5,'??');--???hiselect lpad('hi',1,'??');--h--右补足函数：rpadselect rpad('hi',5,'??');--集合查找函数: find_in_set(str,str_array)select find_in_set('a','abc,b,ab,c,def');----------- Date Functions 日期函数 -------------------获取当前日期: current_dateselectcurrent_date();--获取当前时间戳: current_timestamp--同一查询中对current_timestamp的所有调用均返回相同的值。selectcurrent_timestamp();--获取当前UNIX时间戳函数: unix_timestampselect unix_timestamp();--日期转UNIX时间戳函数: unix_timestampselect unix_timestamp("2011-12-07 13:01:03");--指定格式日期转UNIX时间戳函数: unix_timestampselect unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');--UNIX时间戳转日期函数: from_unixtimeselect from_unixtime(1618238391);select from_unixtime(0,'yyyy-MM-dd HH:mm:ss');--日期比较函数: datediff  日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'select datediff('2012-12-08','2012-05-09');--日期增加函数: date_addselect date_add('2012-02-28',10);--日期减少函数: date_subselect date_sub('2012-01-1',10);--抽取日期函数: to_dateselect to_date('2009-07-30 04:17:52');--日期转年函数: yearselectyear('2009-07-30 04:17:52');--日期转月函数: monthselectmonth('2009-07-30 04:17:52');--日期转天函数: dayselectday('2009-07-30 04:17:52');--日期转小时函数: hourselecthour('2009-07-30 04:17:52');--日期转分钟函数: minuteselectminute('2009-07-30 04:17:52');--日期转秒函数: secondselectsecond('2009-07-30 04:17:52');--日期转周函数: weekofyear 返回指定日期所示年份第几周select weekofyear('2009-07-30 04:17:52');----Mathematical Functions 数学函数---------------取整函数: round  返回double类型的整数值部分 （遵循四舍五入）selectround(3.1415926);--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型selectround(3.1415926,4);--向下取整函数: floorselect floor(3.1415926);select floor(-3.1415926);--向上取整函数: ceilselect ceil(3.1415926);select ceil(-3.1415926);--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数select rand();--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列select rand(3);--二进制函数:  bin(BIGINT a)select bin(18);--进制转换函数: conv(BIGINT num, int from_base, int to_base)select conv(17,10,16);--绝对值函数: absselect abs(-3.9);-------Collection Functions 集合函数----------------集合元素size函数: size(Map<K.V>) size(Array<T>)select size(`array`(11,22,33));select size(`map`("id",10086,"name","zhangsan","age",18));--取map集合keys函数: map_keys(Map<K.V>)select map_keys(`map`("id",10086,"name","zhangsan","age",18));--取map集合values函数: map_values(Map<K.V>)select map_values(`map`("id",10086,"name","zhangsan","age",18));--判断数组是否包含指定元素: array_contains(Array<T>, value)select array_contains(`array`(11,22,33),11);select array_contains(`array`(11,22,33),66);--数组排序函数:sort_array(Array<T>)select sort_array(`array`(12,2,32));-----Conditional Functions 条件函数--------------------使用之前课程创建好的student表数据select*from student limit3;describefunctionextended isnull;--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)selectif(1=2,100,200);selectif(sex ='男','M','W')from student limit3;--空判断函数: isnull( a )select isnull("allen");select isnull(null);--非空判断函数: isnotnull ( a )select isnotnull("allen");select isnotnull(null);--空值转换函数: nvl(T value, T default_value)select nvl("allen","itcast");select nvl(null,"itcast");--非空查找函数: COALESCE(T v1, T v2, ...)--返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULLselectCOALESCE(null,11,22,33);selectCOALESCE(null,null,null,33);selectCOALESCE(null,null,null);--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] ENDselectcase100when50then'tom'when100then'mary'else'tim'end;selectcase sex when'男'then'male'else'female'endfrom student limit3;--nullif( a, b ):-- 如果a = b，则返回NULL，否则返回一个selectnullif(11,11);selectnullif(11,12);--assert_true(condition)--如果'condition'不为真，则引发异常，否则返回nullSELECT assert_true(11>=0);SELECT assert_true(-1>=0);----Type Conversion Functions 类型转换函数-------------------任意数据类型之间转换:castselect cast(12.14asbigint);select cast(12.14as string);select cast("hello"asint);----Data Masking Functions 数据脱敏函数--------------mask--将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。select mask("abc123DEF");select mask("abc123DEF",'-','.','^');--自定义替换的字母--mask_first_n(string str[, int n]--对前n个进行脱敏替换select mask_first_n("abc123DEF",4);--mask_last_n(string str[, int n])select mask_last_n("abc123DEF",4);--mask_show_first_n(string str[, int n])--除了前n个字符，其余进行掩码处理select mask_show_first_n("abc123DEF",4);--mask_show_last_n(string str[, int n])select mask_show_last_n("abc123DEF",4);--mask_hash(string|char|varchar str)--返回字符串的hash编码。select mask_hash("abc123DEF");----- Misc. Functions 其他杂项函数-----------------如果你要调用的java方法所在的jar包不是hive自带的 可以使用add jar添加进来--hive调用java方法: java_method(class, method[, arg1[, arg2..]])select java_method("java.lang.Math","max",11,22);--反射函数: reflect(class, method[, arg1[, arg2..]])select reflect("java.lang.Math","max",11,22);--取哈希值函数:hashselecthash("allen");--current_user()、logged_in_user()、current_database()、version()--SHA-1加密: sha1(string/binary)select sha1("allen");--SHA-2家族算法加密：sha2(string/binary, int)  (SHA-224, SHA-256, SHA-384, SHA-512)select sha2("allen",224);select sha2("allen",512);--crc32加密:select crc32("allen");--MD5加密: md5(string/binary)select md5("allen");

3 自定义函数（UDF）

3.1 实现步骤

1. 写一个java类，继承UDF，并重载evaluate方法，方法中实现函数的业务逻辑；
2. 重载意味着可以在一个java类中实现多个函数功能；
3. 程序打成jar包，上传HS2服务器本地或者HDFS;
4. 客户端命令行中添加jar包到Hive的classpath： hive>add JAR /xxxx/udf.jar;
5. 注册成为临时函数（给UDF命名）：create temporary function 函数名 as ‘UDF类全路径’;
6. HQL中使用函数。

3.2 实现自定义函数案例

3.2.1 依赖

<?xml version="1.0" encoding="UTF-8"?><projectxmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>cn.itcast</groupId><artifactId>hive-udf</artifactId><version>1.0-SNAPSHOT</version><dependencies><dependency><groupId>org.apache.hive</groupId><artifactId>hive-exec</artifactId><version>3.1.2</version></dependency><dependency><groupId>org.apache.hadoop</groupId><artifactId>hadoop-common</artifactId><version>3.1.3</version></dependency></dependencies><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-shade-plugin</artifactId><version>2.2</version><executions><execution><phase>package</phase><goals><goal>shade</goal></goals><configuration><filters><filter><artifact>*:*</artifact><excludes><exclude>META-INF/*.SF</exclude><exclude>META-INF/*.DSA</exclude><exclude>META-INF/*.RSA</exclude></excludes></filter></filters></configuration></execution></executions></plugin></plugins></build></project>

3.2.2 业务代码

publicclassEncryptPhoneNumberextendsUDF{/**
     * 重载evaluate方法 实现函数的业务逻辑
     * @param phoNum  入参：未加密手机号
     * @return 返回：加密后的手机号字符串
     */publicStringevaluate(String phoNum){String encryptPhoNum =null;//手机号不为空 并且为11位if(StringUtils.isNotEmpty(phoNum)&& phoNum.trim().length()==11){//判断数据是否满足中国大陆手机号码规范String regex ="^(1[3-9]\\d{9}$)";Pattern p =Pattern.compile(regex);Matcher m = p.matcher(phoNum);if(m.matches()){//进入这里都是符合手机号规则的//使用正则替换 返回加密后数据
                encryptPhoNum = phoNum.trim().replaceAll("(\\d{3})\\d{4}(\\d{4})","$1****$2");}else{//不符合手机号规则 数据直接原封不动返回
                encryptPhoNum = phoNum;}}else{//不符合11位 数据直接原封不动返回
            encryptPhoNum = phoNum;}return encryptPhoNum;}}

3.2.3 构建jar包

mvn clean install

3.2.4 上传jar包

hive> add jar /opt/software/hive-udf-1.0-SNAPSHOT.jar;

3.2.5 注册成为临时函数

createtemporaryfunction EncryptPhoneNumber as'cn.itcast.hive.udf.EncryptPhoneNumber';

3.2.6 使用函数

4 explode 函数

4.1 概述

1. explode接收map、array类型的数据作为输入，然后把输入数据中的每个元素拆开变成一行数据，一个元素一行。
2. explode执行效果正好满足于输入一行输出多行，所有叫做UDTF函数。
3. 一般情况下，explode函数可以直接单独使用即可；
4. 也可以根据业务需要结合lateral view侧视图一起使用。
5. explode(array) 将array里的每个元素生成一行；
6. explode(map) 将map里的每一对元素作为一行，其中key为一列，value为一列；

4.2 限制

• explode函数属于UDTF表生成函数，explode执行返回的结果可以理解为一张虚拟的表，其数据来源于源表；
• 在select中只查询源表数据没有问题，只查询explode生成的虚拟表数据也没问题，但是不能在只查询源表的时候，既想返回源表字段又想返回explode生成的虚拟表字段；通俗点讲，有两张表，不能只查询一张表但是又想返回分别属于两张表的字段；

5 lateral View（侧视图）

5.1 概述

Lateral View是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的一些查询限制的问题。
一般只要使用UDTF，就会固定搭配lateral view使用。
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView

5.2 原理

• 将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。
• 使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by 、limit等语句中，不需要再单独嵌套一层子查询。

14.5.3 语法

select a.team_name,b.yearfrom the_nba_championship a lateral view explode(champion_year) b asyear

6 aggregate 聚合函数

• 聚合函数的功能是：对一组值执行计算并返回单一的值。
• 聚合函数是典型的输入多行输出一行，使用Hive的分类标准，属于UDAF类型函数。
• 通常搭配Group By语法一起使用，分组后进行聚合操作。

--场景1：没有group by子句的聚合操作--count(*)：所有行进行统计，包括NULL行--count(1)：所有行进行统计，包括NULL行--count(column)：对column中非Null进行统计selectcount(*)as cnt1,count(1)as cnt2 from student;selectcount(sex)as cnt3 from student;--场景2：带有group by子句的聚合操作 注意group by语法限制select sex,count(*)as cnt from student groupby sex;--场景3：select时多个聚合函数一起使用selectcount(*)as cnt1,avg(age)as cnt2 from student;--场景4：聚合函数和case when条件转换函数、coalesce函数、if函数使用selectsum(CASEWHEN sex ='男'THEN1ELSE0END)from student;selectsum(if(sex ='男',1,0))from student;--场景5：聚合参数不支持嵌套聚合函数selectavg(count(*))from student;--场景6：聚合操作时针对null的处理CREATETABLE tmp_1 (val1 int, val2 int);INSERTINTOTABLE tmp_1 VALUES(1,2),(null,2),(2,3);select*from tmp_1;--第二行数据(NULL, 2) 在进行sum(val1 + val2)的时候会被忽略selectsum(val1),sum(val1 + val2)from tmp_1;--可以使用coalesce函数解决selectsum(coalesce(val1,0)),sum(coalesce(val1,0)+ val2)from tmp_1;--场景7：配合distinct关键字去重聚合--此场景下，会编译期间会自动设置只启动一个reduce task处理数据  可能造成数据拥堵selectcount(distinct sex)as cnt1 from student;--可以先去重 在聚合 通过子查询完成--因为先执行distinct的时候 可以使用多个reducetask来跑数据selectcount(*)as gender_uni_cnt
from(selectdistinct sex from student) a;--案例需求：找出student中男女学生年龄最大的及其名字--这里使用了struct来构造数据 然后针对struct应用max找出最大元素 然后取值select sex,max(struct(age, name)).col1 as age,max(struct(age, name)).col2 as name
from student
groupby sex;select struct(age, name)from student;select struct(age, name).col1 from student;selectmax(struct(age, name))from student;

7 增强聚合函数

7.1 grouping sets

grouping sets是一种将多个group by逻辑写在一个sql语句中的便利写法。等价于将不同维度的GROUP BY结果集进行UNION ALL。GROUPING__ID表示结果属于哪一个分组集合。

---group sets---------SELECTmonth,day,COUNT(DISTINCT cookieid)AS nums,
    GROUPING__ID
FROM cookie_info
GROUPBYmonth,day
GROUPING SETS (month,day)ORDERBY GROUPING__ID;--grouping_id表示这一组结果属于哪个分组集合，--根据grouping sets中的分组条件month，day，1是代表month，2是代表day--等价于SELECTmonth,NULL,COUNT(DISTINCT cookieid)AS nums,1AS GROUPING__ID FROM cookie_info GROUPBYmonthUNIONALLSELECTNULLasmonth,day,COUNT(DISTINCT cookieid)AS nums,2AS GROUPING__ID FROM cookie_info GROUPBYday;

SELECTmonth,day,COUNT(DISTINCT cookieid)AS nums,
    GROUPING__ID
FROM cookie_info
GROUPBYmonth,day
GROUPING SETS (month,day,(month,day))ORDERBY GROUPING__ID;--等价于SELECTmonth,NULL,COUNT(DISTINCT cookieid)AS nums,1AS GROUPING__ID FROM cookie_info GROUPBYmonthUNIONALLSELECTNULL,day,COUNT(DISTINCT cookieid)AS nums,2AS GROUPING__ID FROM cookie_info GROUPBYdayUNIONALLSELECTmonth,day,COUNT(DISTINCT cookieid)AS nums,3AS GROUPING__ID FROM cookie_info GROUPBYmonth,day;

7.2 cube

• cube表示根据GROUP BY的维度的所有组合进行聚合。
• 对于cube来说,如果有n个维度,则所有组合的总个数是：2的n次方
• 比如cube有a,b,c 3个维度，则所有组合情况是： (a,b,c),(a,b),(b,c),(a,c),(a),(b),©,()

SELECTmonth,day,COUNT(DISTINCT cookieid)AS nums,
    GROUPING__ID
FROM cookie_info
GROUPBYmonth,dayWITH CUBE
ORDERBY GROUPING__ID;--等价于SELECTNULL,NULL,COUNT(DISTINCT cookieid)AS nums,0AS GROUPING__ID FROM cookie_info
UNIONALLSELECTmonth,NULL,COUNT(DISTINCT cookieid)AS nums,1AS GROUPING__ID FROM cookie_info GROUPBYmonthUNIONALLSELECTNULL,day,COUNT(DISTINCT cookieid)AS nums,2AS GROUPING__ID FROM cookie_info GROUPBYdayUNIONALLSELECTmonth,day,COUNT(DISTINCT cookieid)AS nums,3AS GROUPING__ID FROM cookie_info GROUPBYmonth,day;

7.3 rollup

• cube的语法功能指的是：根据GROUP BY的维度的所有组合进行聚合。
• rollup是cube的子集，以最左侧的维度为主，从该维度进行层级聚合。
• 比如ROLLUP有a,b,c3个维度，则所有组合情况是：(a,b,c),(a,b),(a),()

--比如，以month维度进行层级聚合：SELECTmonth,day,COUNT(DISTINCT cookieid)AS nums,
    GROUPING__ID
FROM cookie_info
GROUPBYmonth,dayWITH ROLLUPORDERBY GROUPING__ID;--把month和day调换顺序，则以day维度进行层级聚合：SELECTday,month,COUNT(DISTINCT cookieid)AS uv,
    GROUPING__ID
FROM cookie_info
GROUPBYday,monthWITH ROLLUPORDERBY GROUPING__ID;

8 windows functions 窗口函数

8.1 概述

• 窗口函数（Window functions）也叫做开窗函数、OLAP函数，其最大特点是：输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。
• 如果函数具有OVER子句，则它是窗口函数。
• 窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行，窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中。

8.2 语法

Function(arg1,..., argn)OVER([PARTITIONBY<...>][ORDERBY<....>][<window_expression>])--其中Function(arg1,..., argn) 可以是下面分类中的任意一个--聚合函数：比如sum max avg等--排序函数：比如rank row_number等--分析函数：比如lead lag first_value等--OVER [PARTITION BY <...>] 类似于group by 用于指定分组  每个分组你可以把它叫做窗口--如果没有PARTITION BY 那么整张表的所有行就是一组--[ORDER BY <....>]  用于指定每个分组内的数据排序规则 支持ASC、DESC--[<window_expression>] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行

sum(...)over()对表所有行求和
sum(...)over(orderby...) 连续累积求和
sum(...)over(partitionby...) 同组内所行求和
sum(...)over(partitionby...orderby...) 在每个分组内，连续累积求和

8.3 案例

8.3.1 sum(…) over( )

--需求：求出网站总的pv数 所有用户所有访问加起来--sum(...) over( )对表所有行求和select cookieid,createtime,pv,sum(pv)over()as total_pv
from website_pv_info;

8.3.2 sum(…) over( partition by… )

----sum+group by普通常规聚合操作------------select dept,sum(salary)as total from employee groupby dept;

----sum+窗口函数聚合操作------------select id,name,deg,salary,dept,sum(salary)over(partitionby dept)as total from employee;

8.3.3 sum(…) over( partition by… order by … )

--需求：求出每个用户截止到当天，累积的总pv数--sum(...) over( partition by... order by ... )，在每个分组内，连续累积求和select cookieid,createtime,pv,sum(pv)over(partitionby cookieid orderby createtime)as current_total_pv
from website_pv_info;

8.4 窗口表达式

• 在sum(…) over( partition by… order by … )语法完整的情况下，进行累积聚合操作，默认累积聚合行为是：从第一行聚合到当前行。
• Window expression窗口表达式给我们提供了一种控制行范围的能力，比如向前2行，向后3行。

sum(...)over(partitionby...orderby...)
关键字是rowsbetween，包括下面这几个选项
-preceding：往前
-following：往后
-currentrow：当前行
-unbounded：边界
-unboundedpreceding 表示从前面的起点
-unboundedfollowing：表示到后面的终点

---窗口表达式select cookieid,createtime,pv,sum(pv)over(partitionby cookieid orderby createtime)as pv1  --默认从第一行到当前行from website_pv_info;--第一行到当前行select cookieid,createtime,pv,sum(pv)over(partitionby cookieid orderby createtime rowsbetweenunboundedprecedingandcurrentrow)as pv2
from website_pv_info;--向前3行至当前行select cookieid,createtime,pv,sum(pv)over(partitionby cookieid orderby createtime rowsbetween3precedingandcurrentrow)as pv4
from website_pv_info;--向前3行 向后1行select cookieid,createtime,pv,sum(pv)over(partitionby cookieid orderby createtime rowsbetween3precedingand1following)as pv5
from website_pv_info;--当前行至最后一行select cookieid,createtime,pv,sum(pv)over(partitionby cookieid orderby createtime rowsbetweencurrentrowandunboundedfollowing)as pv6
from website_pv_info;--第一行到最后一行 也就是分组内的所有行select cookieid,createtime,pv,sum(pv)over(partitionby cookieid orderby createtime rowsbetweenunboundedprecedingandunboundedfollowing)as pv6
from website_pv_info;

8.5 窗口函数排序

用于给每个分组内的数据打上排序的标号，注意窗口排序函数不支持窗口表达式
• row_number：在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复；
• rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置；
• dense_rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置；
• ntile: 将每个分组内的数据分为指定的若干个桶里（分为若干个部分），并且为每一个桶分配一个桶编号。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

-----窗口排序函数SELECT
    cookieid,
    createtime,
    pv,
    RANK()OVER(PARTITIONBY cookieid ORDERBY pv desc)AS rn1,
    DENSE_RANK()OVER(PARTITIONBY cookieid ORDERBY pv desc)AS rn2,
    ROW_NUMBER()OVER(PARTITIONBY cookieid ORDERBY pv DESC)AS rn3
FROM website_pv_info
WHERE cookieid ='cookie1';