1 UDTF之explode函数
1.1 explode语法功能
对于UDTF表生成函数，很多人难以理解什么叫做输入一行，输出多行。
为什么叫做表生成？能够产生表吗？下面我们就来学习Hive当做内置的一个非常著名的UDTF函数，名字叫做explode函数，中文戏称之为“爆炸函数”，可以炸开数据。
explode函数接收map或者array类型的数据作为参数，然后把参数中的每个元素炸开变成一行数据。一个元素一行。这样的效果正好满足于输入一行输出多行。
explode函数在关系型数据库中本身是不该出现的。
因为他的出现本身就是在操作不满足第一范式的数据（每个属性都不可再分）。本身已经违背了数据库的设计原理，但是在面向分析的数据库或者数据仓库中，这些规范可以发生改变。
explode(a) - separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns
explode(array)将array列表里的每个元素生成一行；
explode(map)将map里的每一对元素作为一行，其中key为一列，value为一列；
一般情况下，explode函数可以直接使用即可，也可以根据需要结合lateral view侧视图使用。
1.2 explode函数的使用
select explode(array(11,22,33)) as item;
select explode(map(“id”,10086,“name”,“zhangsan”,“age”,18));
1.3 案例：NBA总冠军球队名单
1.3.1 业务需求
有一份数据《The_NBA_Championship.txt》，关于部分年份的NBA总冠军球队名单：
第一个字段表示的是球队名称，第二个字段是获取总冠军的年份，字段之间以，分割；
获取总冠军年份之间以|进行分割。
需求：使用Hive建表映射成功数据，对数据拆分，要求拆分之后数据如下所示：
并且最好根据年份的倒序进行排序。
1.3.2 代码实现
--step1:建表 create table the_nba_championship( team_name string, champion_year array<string> ) row format delimited fields terminated by ',' collection items terminated by '|'; --step2:加载数据文件到表中 load data local inpath '/root/hivedata/The_NBA_Championship.txt' into table the_nba_championship; --step3:验证 select * from the_nba_championship;
下面使用explode函数：
–step4:使用explode函数对champion_year进行拆分 俗称炸开
select explode(champion_year) from the_nba_championship;
select team_name,explode(champion_year) from the_nba_championship;
1.3.3 explode使用限制
在select条件中，如果只有explode函数表达式，程序执行是没有任何问题的；
但是如果在select条件中，包含explode和其他字段，就会报错。错误信息为：
org.apache.hadoop.hive.ql.parse.SemanticException:UDTF’s are not supported outside the SELECT clause, nor nested in expressions
那么如何理解这个错误呢？为什么在select的时候，explode的旁边不支持其他字段的同时出现？
1.3.4 explode语法限制原因
1、 explode函数属于UDTF函数，即表生成函数；
2、 explode函数执行返回的结果可以理解为一张虚拟的表，其数据来源于源表；
3、 在select中只查询源表数据没有问题，只查询explode生成的虚拟表数据也没问题
4、 但是不能在只查询源表的时候，既想返回源表字段又想返回explode生成的虚拟表字段
5、 通俗点讲，有两张表，不能只查询一张表但是返回分别属于两张表的字段；
6、 从SQL层面上来说应该对两张表进行关联查询
7、 Hive专门提供了语法lateral View侧视图，专门用于搭配explode这样的UDTF函数，以满足上述需要。
2 Lateral View侧视图
2.1 概念
Lateral View是一种特殊的语法，主要用于搭配UDTF类型功能的函数一起使用，用于解决UDTF函数的一些查询限制的问题。
侧视图的原理是将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by 、limit等语句中，不需要再单独嵌套一层子查询。
一般只要使用UDTF，就会固定搭配lateral view使用。
官方链接：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView
2.2 UDTF配合侧视图使用
针对上述NBA冠军球队年份排名案例，使用explode函数+lateral view侧视图，可以完美解决：
–lateral view侧视图基本语法如下
select …… from tabelA lateral view UDTF(xxx) 别名 as col1,col2,col3……;
select a.team_name ,b.year
from the_nba_championship a lateral view explode(champion_year) b as year
–根据年份倒序排序
select a.team_name ,b.year
from the_nba_championship a lateral view explode(champion_year) b as year
order by b.year desc;
3 Aggregation 聚合函数
3.1 基础聚合
HQL提供了几种内置的UDAF聚合函数，例如max（…），min（…）和avg（…）。这些我们把它称之为基础的聚合函数。
通常情况下，聚合函数会与GROUP BY子句一起使用。 如果未指定GROUP BY子句，默认情况下，它会汇总所有行数据。
--------------基础聚合函数------------------- --1、测试数据准备 drop table if exists student; create table student( num int, name string, sex string, age int, dept string) row format delimited fields terminated by ','; --加载数据 load data local inpath '/root/hivedata/students.txt' into table student; --验证 select * from student; --场景1：没有group by子句的聚合操作 select count(*) as cnt1,count(1) as cnt2 from student; --两个一样 --场景2：带有group by子句的聚合操作 注意group by语法限制 select sex,count(*) as cnt from student group by sex; --场景3：select时多个聚合函数一起使用 select count(*) as cnt1,avg(age) as cnt2 from student; --场景4：聚合函数和case when条件转换函数、coalesce函数、if函数使用 select sum(CASE WHEN sex = '男'THEN 1 ELSE 0 END) from student; select sum(if(sex = '男',1,0)) from student; --场景5：聚合参数不支持嵌套聚合函数 select avg(count(*)) from student; --聚合参数针对null的处理方式 --null null 0 select max(null), min(null), count(null); --下面这两个不支持null select sum(null), avg(null); --场景5：聚合操作时针对null的处理 CREATE TABLE tmp_1 (val1 int, val2 int); INSERT INTO TABLE tmp_1 VALUES (1, 2),(null,2),(2,3); select * from tmp_1; --第二行数据(NULL, 2) 在进行sum(val1 + val2)的时候会被忽略 select sum(val1), sum(val1 + val2) from tmp_1; --可以使用coalesce函数解决 select sum(coalesce(val1,0)), sum(coalesce(val1,0) + val2) from tmp_1; --场景6：配合distinct关键字去重聚合 --此场景下，会编译期间会自动设置只启动一个reduce task处理数据 性能可能会不会 造成数据拥堵 select count(distinct sex) as cnt1 from student; --可以先去重 在聚合 通过子查询完成 --因为先执行distinct的时候 可以使用多个reducetask来跑数据 select count(*) as gender_uni_cnt from (select distinct 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 group by sex; select struct(age, name) from student; select struct(age, name).col1 from student; select max(struct(age, name)) from student;
3.2 增强聚合
3.2.1 概述与表数据环境准备
增强聚合的grouping_sets、cube、rollup这几个函数主要适用于OLAP多维数据分析模式中，多维分析中的维指的分析问题时看待问题的维度、角度。
下面我们来准备一下数据，通过案例更好的理解函数的功能含义
字段：月份、天、用户cookieid
--表创建并且加载数据 CREATE TABLE cookie_info( month STRING, day STRING, cookieid STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; load data local inpath '/root/hivedata/cookie_info.txt' into table cookie_info; select * from cookie_info;
3.2.2 Grouping sets
grouping sets是一种将多个group by逻辑写在一个sql语句中的便利写法。
等价于将不同维度的GROUP BY结果集进行UNION ALL。
GROUPING__ID表示结果属于哪一个分组集合。
---group sets--------- SELECT month, day, COUNT(DISTINCT cookieid) AS nums, GROUPING__ID FROM cookie_info GROUP BY month,day GROUPING SETS (month,day) ORDER BY GROUPING__ID; --grouping_id表示这一组结果属于哪个分组集合， --根据grouping sets中的分组条件month，day，1是代表month，2是代表day --等价于 SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month UNION ALL SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day; --再比如 SELECT month, day, COUNT(DISTINCT cookieid) AS nums, GROUPING__ID FROM cookie_info GROUP BY month,day GROUPING SETS (month,day,(month,day)) ORDER BY GROUPING__ID; --等价于 SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month UNION ALL SELECT NULL,day,COUNT(DISTINCT cookieid) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day UNION ALL SELECT month,day,COUNT(DISTINCT cookieid) AS nums,3 AS GROUPING__ID FROM cookie_info GROUP BY month,day;
3.2.3 Cube
cube的语法功能指的是：根据GROUP BY的维度的所有组合进行聚合。
对于cube,如果有n个维度,则所有组合的总个数是：2^n。
比如Cube有a,b,c3个维度，则所有组合情况是：
((a,b,c),(a,b),(b,c),(a,c),(a),(b),©,())。
------cube--------------- SELECT month, day, COUNT(DISTINCT cookieid) AS nums, GROUPING__ID FROM cookie_info GROUP BY month,day WITH CUBE ORDER BY GROUPING__ID; --等价于 SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS nums,0 AS GROUPING__ID FROM cookie_info UNION ALL SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month UNION ALL SELECT NULL,day,COUNT(DISTINCT cookieid) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day UNION ALL SELECT month,day,COUNT(DISTINCT cookieid) AS nums,3 AS GROUPING__ID FROM cookie_info GROUP BY month,day;
3.2.4 Rollup
cube的语法功能指的是：根据GROUP BY的维度的所有组合进行聚合。
rollup是Cube的子集，以最左侧的维度为主，从该维度进行层级聚合。
比如ROLLUP有a,b,c3个维度，则所有组合情况是：
((a,b,c),(a,b),(a),())。
--rollup------------- --比如，以month维度进行层级聚合： SELECT month, day, COUNT(DISTINCT cookieid) AS nums, GROUPING__ID FROM cookie_info GROUP BY month,day WITH ROLLUP ORDER BY GROUPING__ID; --把month和day调换顺序，则以day维度进行层级聚合： SELECT day, month, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID FROM cookie_info GROUP BY day,month WITH ROLLUP ORDER BY GROUPING__ID;
