本文目录如下：
第8章 函数8.1 系统内置函数8.2 常用内置函数8.2.1 空字段赋值8.2.2 CASE WHEN THEN ELSE END8.2.3 行转列8.2.4 列转行8.2.5 窗口函数（开窗函数）8.2.6 Rank8.2.7 其他常用函数8.3 自定义函数8.4 自定义 UDF 函数8.5 自定义 UDTF 函数第8章 函数
8.1 系统内置函数
(1) 查看系统自带的函数hive> show functions;
(2) 显示自带的函数的用法
hive> desc function upper;
(3) 详细显示自带的函数的用法
hive> desc function extended upper;
8.2 常用内置函数
8.2.1 空字段赋值
(1) 函数说明
NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL，则返回NULL。
(2) 数据准备：采用员工表
(3) 查询：如果员工的comm为NULL，则用-1代替
hive (default)> select comm,nvl(comm, -1) from emp;comm_c1NULL-1.0300.0300.0500.0500.0NULL-1.0...
(4) 查询：如果员工的comm为NULL，则用领导id代替
hive (default)> select comm, nvl(comm,mgr) from emp;OKcomm_c1NULL7902.0300.0300.0500.0500.0NULL7839.0
8.2.2 CASE WHEN THEN ELSE END
(1) 数据准备(2) 需求求出不同部门男女各多少人。结果如下：
dept_Id男女A21B12
(3) 创建本地emp_sex.txt，导入数据
[xqzhao@hadoop100 datas]$ vi emp_sex.txt悟空A男大海A男宋宋B男凤姐A女婷姐B女婷婷B女
(4) 创建hive表并导入数据
创建hive表
create table emp_sex(name string, dept_id string, sex string) row format delimited fields terminated by "\t";
导入数据:
hive (default)> hiveload data local inpath '/opt/module/hive/data/emp_sex.txt' into table emp_sex;
(5) 按需求查询数据
selectdept_id,sum(case sex when '男' then 1 else 0 end) male_count,sum(case sex when '女' then 1 else 0 end) female_countfrom emp_sex group by dept_id;
8.2.3 行转列
(1) 相关函数说明(2) 数据准备(3) 需求CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是NULL，返回值也将为NULL。这个函数会跳过分隔符参数后的任何NULL和空字符串。分隔符将被加到被连接的字符串之间;
注意: CONCAT_WS must be "string or array<string>COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生Array类型字段。
把星座和血型一样的人归类到一起。结果如下：
射手座,A大海|凤姐白羊座,A孙悟空|猪八戒白羊座,B宋宋|苍老师
(4) 创建本地constellation.txt，填入数据
[xqzhao@hadoop100 datas]$ vim person_info.txt孙悟空白羊座A大海射手座A宋宋白羊座B猪八戒白羊座A凤姐射手座A苍老师白羊座B
(5) 创建hive表并导入数据
创建hive表:
create table person_info(name string, constellation string, blood_type string) row format delimited fields terminated by "\t";
导入数据:
hive (default)> load data local inpath "/opt/module/hive/data/person_info.txt" into table person_info;
(6) 按需求查询数据
SELECTt1.c_b,CONCAT_WS("|",collect_set(t1.name))FROM (SELECTNAME,CONCAT_WS(',',constellation,blood_type) c_bFROM person_info)t1GROUP BY t1.c_b
8.2.4 列转行
(1) 函数说明(2) 数据准备
EXPLODE(col)：将hive一列中复杂的Array或者Map结构拆分成多行。
LATERAL VIEW:用法：
LATERAL VIEW udtf(expression) tableAlias AS columnAlias解释：用于和
split,explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
moviecategory《疑犯追踪》悬疑,动作,科幻,剧情《Lie to me》悬疑,警匪,动作,心理,剧情《战狼 2》战争,动作,灾难
(3) 需求
将电影分类中的数组数据展开。结果如下：
《疑犯追踪》悬疑《疑犯追踪》动作《疑犯追踪》科幻《疑犯追踪》剧情《Lie to me》悬疑《Lie to me》警匪《Lie to me》动作《Lie to me》心理《Lie to me》剧情《战狼 2》战争《战狼 2》动作《战狼 2》灾难
(4) 创建本地 movie.txt ，导入数据
[xqzhao@hadoop100 datas]$ vi movie_info.txt《疑犯追踪》悬疑,动作,科幻,剧情《Lie to me》悬疑,警匪,动作,心理,剧情《战狼 2》战争,动作,灾难
(5) 创建hive表并导入数据
创建hive表:
create table movie_info(movie string,category string)row format delimited fields terminated by "\t";
导入数据
hive (default)> load data local inpath "/opt/module/data/movie.txt" into table movie_info;
(6) 按需求查询数据
SELECTmovie,category_nameFROMmovie_infolateral VIEWexplode(split(category,",")) movie_info_tmp AS category_name;
8.2.5 窗口函数（开窗函数）
(1) 相关函数说明(2) 数据准备：
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点
UNBOUNDED PRECEDING表示从前面的起点，
UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n,default_val)：往前第 n 行数据
LEAD(col,n, default_val)：往后第 n 行数据
NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。
name，orderdate，costjack,-01-01,10tony,-01-02,15jack,-02-03,23tony,-01-04,29jack,-01-05,46jack,-04-06,42tony,-01-07,50jack,-01-08,55mart,-04-08,62mart,-04-09,68neil,-05-10,12mart,-04-11,75neil,-06-12,80mart,-04-13,94
(3) 需求
(4) 创建本地(1) 查询在
年4月份购买过的顾客及总人数(2) 查询顾客的购买明细及月购买总额
(3) 上述的场景, 将每个顾客的
cost按照日期进行累加(4) 查询每个顾客上次的购买时间
(5) 查询前
20%时间的订单信息
business.txt，导入数据[xqzhao@hadoop100 datas]$ vi business.txt
(5) 创建hive表并导入数据
创建hive表
create table business(name string,orderdate string,cost int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
导入数据
hive (default)> load data local inpath "/opt/module/data/business.txt" into table business;
(6) 按需求查询数据
(1) 查询在年4月份购买过的顾客及总人数
select name,count(*) over () from businesswhere substring(orderdate,1,7) = '-04'group by name;(2) 查询顾客的购买明细及月购买总额
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;(3) 将每个顾客的 cost 按照日期进行累加
select name,orderdate,cost,sum(cost) over() as sample1,--所有行相加sum(cost) over(partition by name) as sample2,--按 name 分组，组内数据相加sum(cost) over(partition by name order by orderdate) as sample3,--按 name分组，组内数据累加sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一样,由起点到当前行的聚合sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行from business;
rows必须跟在order by子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量(4) 查看顾客上次的购买时间
select name,orderdate,cost,lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2 from business;(5) 查询前
20%时间的订单信息
select * from (select name,orderdate,cost, ntile(5) over(order by orderdate) sortedfrom business) twhere sorted = 1;
8.2.6 Rank
(1) 函数说明(2) 数据准备RANK()排序相同时会重复，总数不会变DENSE_RANK()排序相同时会重复，总数会减少ROW_NUMBER()会根据顺序计算
namesubjectscore孙悟空语文87孙悟空数学95孙悟空英语68大海语文94大海数学56大海英语84宋宋语文64宋宋数学86宋宋英语84婷婷语文65婷婷数学85婷婷英语78
(3) 需求
计算每门学科成绩排名。
(4) 创建本地score.txt，导入数据
[xqzhao@hadoop100 datas]$ vi score.txt
(5) 创建hive表并导入数据
create table score(name string,subject string, score int) row format delimited fields terminated by "\t";load data local inpath '/opt/module/data/score.txt' into table score;
(6) 按需求查询数据
select name,subject,score,rank() over(partition by subject order by score desc) rp,dense_rank() over(partition by subject order by score desc) drp,row_number() over(partition by subject order by score desc) rmpfrom score;
扩展：求出每门学科前三名的学生？
8.2.7 其他常用函数
略…8.3 自定义函数
(1)Hive自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
(2) 当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
(3) 根据用户自定义函数类别分为以下三种：
(1)
UDF（User-Defined-Function）一进一出
(2)
UDAF（User-Defined Aggregation Function）聚集函数，多进一出
类似于：
count/max/min(3)
UDTF（User-Defined Table-Generating Functions）一进多出
如
lateral view explode()
(4) 官方文档地址
点击查看官方文档
(5) 编程步骤：
(1) 继承Hive提供的类
org.apache.hadoop.hive.ql.udf.generic.GenericUDForg.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 [if exists] [dbname.]function_name;
8.4 自定义 UDF 函数
(0) 需求:自定义一个 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.2</version></dependency></dependencies>
(3) 创建一个类
package com.atguigu.hive;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;import org.apache.hadoop.hive.ql.metadata.HiveException;import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;/*** 自定义 UDF 函数，需要继承 GenericUDF 类* 需求: 计算指定字符串的长度*/public class MyStringLength extends GenericUDF {/**** @param arguments 输入参数类型的鉴别器对象* @return 返回值类型的鉴别器对象* @throws UDFArgumentException*/@Overridepublic ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {// 判断输入参数的个数if(arguments.length !=1){throw new UDFArgumentLengthException("Input Args Length Error!!!");}// 判断输入参数的类型if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){throw new UDFArgumentTypeException(0,"Input Args Type Error!!!");}//函数本身返回值为 int，需要返回 int 类型的鉴别器对象return PrimitiveObjectInspectorFactory.javaIntObjectInspector;}/*** 函数的逻辑处理* @param arguments 输入的参数* @return 返回值* @throws HiveException*/@Overridepublic Object evaluate(DeferredObject[] arguments) throws HiveException {if(arguments[0].get() == null){return 0;}return arguments[0].get().toString().length();}@Overridepublic String getDisplayString(String[] children) {return "";} }
(4) 打成jar包上传到服务器/opt/module/data/myudf.jar
(5) 将jar包添加到hive的classpath
hive (default)> add jar /opt/module/data/myudf.jar;
(6) 创建临时函数与开发好的java class关联
hive (default)> create temporary function my_len as "com.atguigu.hive.MyStringLength";
(7) 即可在hql中使用自定义的函数
hive (default)> select ename,my_len(ename) ename_len from emp;
8.5 自定义 UDTF 函数
(0) 需求自定义一个 UDTF 实现将一个任意分割符的字符串切割成独立的单词，例如：
hive(default)> select myudtf("hello,world,hadoop,hive", ",");helloworldhadoophive
(1) 代码实现
package com.atguigu.udtf;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;import org.apache.hadoop.hive.ql.metadata.HiveException;import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;import java.util.ArrayList;import java.util.List;public class MyUDTF extends GenericUDTF {private ArrayList<String> outList = new ArrayList<>();@Overridepublic StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {//1.定义输出数据的列名和类型List<String> fieldNames = new ArrayList<>();List<ObjectInspector> fieldOIs = new ArrayList<>();//2.添加输出数据的列名和类型fieldNames.add("lineToWord");fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);}@Overridepublic void process(Object[] args) throws HiveException {//1.获取原始数据String arg = args[0].toString();//2.获取数据传入的第二个参数，此处为分隔符String splitKey = args[1].toString();//3.将原始数据按照传入的分隔符进行切分String[] fields = arg.split(splitKey);//4.遍历切分后的结果，并写出for (String field : fields) {//集合为复用的，首先清空集合outList.clear();//将每一个单词添加至集合outList.add(field);//将集合内容写出forward(outList);}}@Overridepublic void close() throws HiveException {} }
(2) 打成jar包上传到服务器/opt/module/hive/data/myudtf.jar(3) 将jar包添加到hive的classpath下
hive (default)> add jar /opt/module/hive/data/myudtf.jar;
(4) 创建临时函数与开发好的java class关联
hive (default)> create temporary function myudtf as "com.atguigu.hive.MyUDTF";
(5) 使用自定义的函数
hive (default)> select myudtf("hello,world,hadoop,hive",",");
声明：本文是学习时记录的笔记，如有侵权请告知删除！
原视频地址:/video/BV1EZ4y1G7iL
