-
- 拆解json字段
hive (hive_explode)> select explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')) as sale_info from explode_lateral_view; 然后我们想用get_json_object来获取key为monthSales的数据: hive (hive_explode)> select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as sale_info from explode_lateral_view; 然后挂了FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions UDTF explode不能写在别的函数内 如果你这么写,想查两个字段,select explode(split(area,',')) as area,good_id from explode_lateral_view; 会报错FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'good_id' 使用UDTF的时候,只支持一个字段,这时候就需要LATERAL VIEW出场了
配合LATERAL VIEW使用
配合lateral view查询多个字段
hive (hive_explode)> select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2; 其中LATERAL VIEW explode(split(goods_id,','))goods相当于一个虚拟表,与原表explode_lateral_view笛卡尔积关联
也可以多重使用
hive (hive_explode)> select goods_id2,sale_info,area2 from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2 LATERAL VIEW explode(split(area,','))area as area2;也是三个表笛卡尔积的结果
最终,我们可以通过下面的句子,把这个json格式的一行数据,完全转换成二维表的方式展现
hive (hive_explode)> select get_json_object(concat('{',sale_info_1,'}'),'$.source') as source,get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales,get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales from explode_lateral_view LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_1;
总结:
Lateral View通常和UDTF一起出现,为了解决UDTF不允许在select字段的问题。 Multiple Lateral View可以实现类似笛卡尔乘积。 Outer关键字可以把不输出的UDTF的空结果,输出成NULL,防止丢失数据。
行转列
相关参数说明:
CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果,支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...):它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段。
数据准备:
name | constellation | blood_type |
孙悟空 | 白羊座 | A |
老王 | 射手座 | A |
宋宋 | 白羊座 | B |
猪八戒 | 白羊座 | A |
凤姐 | 射手座 | A |
需求: 把星座和血型一样的人归类到一起。结果如下:
射手座,A 老王|凤姐 白羊座,A 孙悟空|猪八戒 白羊座,B 宋宋
实现步骤:
-
- 创建本地constellation.txt,导入数据
node03服务器执行以下命令创建文件,注意数据使用\t进行分割 cd /export/servers/hivedatas vim constellation.txt 数据如下: 孙悟空 白羊座 A 老王 射手座 A 宋宋 白羊座 B 猪八戒 白羊座 A 凤姐 射手座 A
-
- 创建hive表并导入数据
创建hive表并加载数据 hive (hive_explode)> create table person_info( name string, constellation string, blood_type string) row format delimited fields terminated by "\t"; 加载数据 hive (hive_explode)> load data local inpath '/export/servers/hivedatas/constellation.txt' into table person_info;
-
- 按需求查询数据
hive (hive_explode)> select t1.base, concat_ws('|', collect_set(t1.name)) name from (select name, concat(constellation, "," , blood_type) base from person_info) t1 group by t1.base;
列转行
所需函数:
EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释:用于和split, explode等UDTF一起使用,它能够将一列数据拆成多行数据,在
此基础上可以对拆分后的数据进行聚合。
数据准备:
cd /export/servers/hivedatas vim movie.txt 文件内容如下: 数据字段之间使用\t进行分割 《疑犯追踪》 悬疑,动作,科幻,剧情 《Lie to me》 悬疑,警匪,动作,心理,剧情 《战狼2》 战争,动作,灾难
需求: 将电影分类中的数组数据展开。结果如下:
《疑犯追踪》 悬疑 《疑犯追踪》 动作 《疑犯追踪》 科幻 《疑犯追踪》 剧情 《Lie to me》 悬疑 《Lie to me》 警匪 《Lie to me》 动作 《Lie to me》 心理 《Lie to me》 剧情 《战狼2》 战争 《战狼2》 动作 《战狼2》 灾难
实现步骤:
-
- 创建hive表
create table movie_info( movie string, category array<string>) row format delimited fields terminated by "\t" collection items terminated by ",";
-
- 加载数据
load data local inpath "/export/servers/hivedatas/movie.txt" into table movie_info;
-
- 按需求查询数据
select movie, category_name from movie_info lateral view explode(category) table_tmp as category_name;
reflect函数
reflect函数可以支持在sql中调用java中的自带函数,秒杀一切udf函数。
需求1: 使用java.lang.Math当中的Max求两列中最大值
实现步骤:
-
- 创建hive表
create table test_udf(col1 int,col2 int) row format delimited fields terminated by ',';
-
- 准备数据并加载数据
cd /export/servers/hivedatas vim test_udf 文件内容如下: 1,2 4,3 6,4 7,5 5,6
-
- 加载数据
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/test_udf' overwrite into table test_udf;
-
- 使用java.lang.Math当中的Max求两列当中的最大值
hive (hive_explode)> select reflect("java.lang.Math","max",col1,col2) from test_udf;
需求2: 文件中不同的记录来执行不同的java的内置函数
实现步骤:
-
- 创建hive表
hive (hive_explode)> create table test_udf2(class_name string,method_name string,col1 int , col2 int) row format delimited fields terminated by ',';
-
- 准备数据
cd /export/servers/hivedatas vim test_udf2 文件内容如下: java.lang.Math,min,1,2 java.lang.Math,max,2,3
-
- 加载数据
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/test_udf2' overwrite into table test_udf2;
-
- 执行查询
hive (hive_explode)> select reflect(class_name,method_name,col1,col2) from test_udf2;
需求3: 判断是否为数字
实现方式:
使用apache commons中的函数,commons下的jar已经包含在hadoop的classpath中,所以可以直接使用。
select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123")
Hive 窗口函数
窗口函数最重要的关键字是 partition by 和 order by
具体语法如下:XXX over (partition by xxx order by xxx)
特别注意:over()里面的 partition by 和 order by 都不是必选的,over()里面可以只有partition by,也可以只有order by,也可以两个都没有,大家需根据需求灵活运用。
窗口函数我划分了几个大类,我们一类一类的讲解。
1. SUM、AVG、MIN、MAX
讲解这几个窗口函数前,先创建一个表,以实际例子讲解大家更容易理解。
首先创建用户访问页面表:user_pv
create table user_pv( cookieid string, -- 用户登录的cookie,即用户标识 createtime string, -- 日期 pv int -- 页面访问量 );
给上面这个表加上如下数据:
cookie1,2021-05-10,1 cookie1,2021-05-11,5 cookie1,2021-05-12,7 cookie1,2021-05-13,3 cookie1,2021-05-14,2 cookie1,2021-05-15,4 cookie1,2021-05-16,4
- SUM()使用
执行如下查询语句:
select cookieid,createtime,pv, sum(pv) over(partition by cookieid order by createtime) as pv1 from user_pv;
结果如下:(因命令行原因,下图字段名和值是错位的,请注意辨别!)
执行如下查询语句:
select cookieid,createtime,pv, sum(pv) over(partition by cookieid ) as pv1 from user_pv;
结果如下:
第一条SQL的over()里面加 order by ,第二条SQL没加order by ,结果差别很大
所以要注意了:
- over()里面加 order by 表示:分组内从起点到当前行的pv累积,如,11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号;
- over()里面不加 order by 表示:将分组内所有值累加。
AVG,MIN,MAX,和SUM用法一样,这里就不展开讲了,但是要注意 AVG,MIN,MAX 的over()里面加不加 order by 也和SUM一样,如 AVG 求平均值,如果加上 order by,表示分组内从起点到当前行的平局值,不是全部的平局值。MIN,MAX 同理。
2. ROW_NUMBER、RANK、DENSE_RANK、NTILE
还是用上述的用户登录日志表:user_pv
,里面的数据换成如下所示:
cookie1,2021-05-10,1 cookie1,2021-05-11,5 cookie1,2021-05-12,7 cookie1,2021-05-13,3 cookie1,2021-05-14,2 cookie1,2021-05-15,4 cookie1,2021-05-16,4 cookie2,2021-05-10,2 cookie2,2021-05-11,3 cookie2,2021-05-12,5 cookie2,2021-05-13,6 cookie2,2021-05-14,3 cookie2,2021-05-15,9 cookie2,2021-05-16,7
- ROW_NUMBER()使用:
ROW_NUMBER()从1开始,按照顺序,生成分组内记录的序列。
SELECT cookieid, createtime, pv, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn FROM user_pv;
结果如下:
- RANK 和 DENSE_RANK 使用:
RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位。
DENSE_RANK()生成数据项在分组中的排名,排名相等会在名次中不会留下空位。
SELECT cookieid, createtime, pv, RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1, DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 FROM user_pv WHERE cookieid = 'cookie1';
结果如下:
- NTILE的使用:
有时会有这样的需求:如果数据排序后分为三部分,业务人员只关心其中的一部分,如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足。
ntile可以看成是:把有序的数据集合平均分配到指定的数量(num)个桶中, 将桶号分配给每一行。如果不能平均分配,则优先分配较小编号的桶,并且各个桶中能放的行数最多相差1。
然后可以根据桶号,选取前或后 n分之几的数据。数据会完整展示出来,只是给相应的数据打标签;具体要取几分之几的数据,需要再嵌套一层根据标签取出。
SELECT cookieid, createtime, pv, NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1, NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2, NTILE(4) OVER(ORDER BY createtime) AS rn3 FROM user_pv ORDER BY cookieid,createtime;
结果如下:
3. LAG、LEAD、FIRST_VALUE、LAST_VALUE
讲解这几个窗口函数时还是以实例讲解,首先创建用户访问页面表:user_url
CREATE TABLE user_url ( cookieid string, createtime string, --页面访问时间 url string --被访问页面 );
表中加入如下数据:
cookie1,2021-06-10 10:00:02,url2 cookie1,2021-06-10 10:00:00,url1 cookie1,2021-06-10 10:03:04,1url3 cookie1,2021-06-10 10:50:05,url6 cookie1,2021-06-10 11:00:00,url7 cookie1,2021-06-10 10:10:00,url4 cookie1,2021-06-10 10:50:01,url5 cookie2,2021-06-10 10:00:02,url22 cookie2,2021-06-10 10:00:00,url11 cookie2,2021-06-10 10:03:04,1url33 cookie2,2021-06-10 10:50:05,url66 cookie2,2021-06-10 11:00:00,url77 cookie2,2021-06-10 10:10:00,url44 cookie2,2021-06-10 10:50:01,url55
- LAG的使用:
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值。
第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
SELECT cookieid, createtime, url, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn, LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time, LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time FROM user_url;
结果如下:
解释:
last_1_time: 指定了往上第1行的值,default为'1970-01-01 00:00:00' cookie1第一行,往上1行为NULL,因此取默认值 1970-01-01 00:00:00 cookie1第三行,往上1行值为第二行值,2021-06-10 10:00:02 cookie1第六行,往上1行值为第五行值,2021-06-10 10:50:01 last_2_time: 指定了往上第2行的值,为指定默认值 cookie1第一行,往上2行为NULL cookie1第二行,往上2行为NULL cookie1第四行,往上2行为第二行值,2021-06-10 10:00:02 cookie1第七行,往上2行为第五行值,2021-06-10 10:50:01
- LEAD的使用:
与LAG相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值。
第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)
SELECT cookieid, createtime, url, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn, LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time, LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time FROM user_url;
结果如下:
- FIRST_VALUE的使用:
取分组内排序后,截止到当前行,第一个值。
SELECT cookieid, createtime, url, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn, FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1 FROM user_url;
结果如下:
- LAST_VALUE的使用:
取分组内排序后,截止到当前行,最后一个值。
SELECT cookieid, createtime, url, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn, LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1 FROM user_url;
结果如下:
如果想要取分组内排序后最后一个值,则需要变通一下:
SELECT cookieid, createtime, url, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn, LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1, FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2 FROM user_url ORDER BY cookieid,createtime;
注意上述SQL,使用的是 FIRST_VALUE 的倒序取出分组内排序最后一个值!
结果如下:
此处要特别注意order by
如果不指定ORDER BY,则进行排序混乱,会出现错误的结果
SELECT cookieid, createtime, url, FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2 FROM user_url;
结果如下:
上述 url2 和 url55 的createtime即不属于最靠前的时间也不属于最靠后的时间,所以结果是混乱的。
4. CUME_DIST
先创建一张员工薪水表:staff_salary
CREATE EXTERNAL TABLE staff_salary ( dept string, userid string, sal int );
表中加入如下数据:
d1,user1,1000 d1,user2,2000 d1,user3,3000 d2,user4,4000 d2,user5,5000
- CUME_DIST的使用:
此函数的结果和order by的排序顺序有关系。
CUME_DIST:小于等于当前值的行数/分组内总行数。 order默认顺序 :正序
比如,统计小于等于当前薪水的人数,所占总人数的比例。
SELECT dept, userid, sal, CUME_DIST() OVER(ORDER BY sal) AS rn1, CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2 FROM staff_salary;
结果如下:
解释:
rn1: 没有partition,所有数据均为1组,总行数为5, 第一行:小于等于1000的行数为1,因此,1/5=0.2 第三行:小于等于3000的行数为3,因此,3/5=0.6 rn2: 按照部门分组,dpet=d1的行数为3, 第二行:小于等于2000的行数为2,因此,2/3=0.6666666666666666
5. GROUPING SETS、GROUPING__ID、CUBE、ROLLUP
这几个分析函数通常用于OLAP中,不能累加,而且需要根据不同维度上钻和下钻的指标统计,比如,分小时、天、月的UV数。
还是先创建一个用户访问表:user_date
CREATE TABLE user_date ( month STRING, day STRING, cookieid STRING );
表中加入如下数据:
2021-03,2021-03-10,cookie1 2021-03,2021-03-10,cookie5 2021-03,2021-03-12,cookie7 2021-04,2021-04-12,cookie3 2021-04,2021-04-13,cookie2 2021-04,2021-04-13,cookie4 2021-04,2021-04-16,cookie4 2021-03,2021-03-10,cookie2 2021-03,2021-03-10,cookie3 2021-04,2021-04-12,cookie5 2021-04,2021-04-13,cookie6 2021-04,2021-04-15,cookie3 2021-04,2021-04-15,cookie2 2021-04,2021-04-16,cookie1
- GROUPING SETS的使用:
grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。
等价于将不同维度的GROUP BY结果集进行UNION ALL。
SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID FROM user_date GROUP BY month,day GROUPING SETS (month,day) ORDER BY GROUPING__ID;
注:上述SQL中的GROUPING__ID,是个关键字,表示结果属于哪一个分组集合,根据grouping sets中的分组条件month,day,1是代表month,2是代表day。
结果如下:
上述SQL等价于:
SELECT month, NULL as day, COUNT(DISTINCT cookieid) AS uv, 1 AS GROUPING__ID FROM user_date GROUP BY month UNION ALL SELECT NULL as month, day, COUNT(DISTINCT cookieid) AS uv, 2 AS GROUPING__ID FROM user_date GROUP BY day;
- CUBE的使用:
根据GROUP BY的维度的所有组合进行聚合。
SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID FROM user_date GROUP BY month,day WITH CUBE ORDER BY GROUPING__ID;
结果如下:
上述SQL等价于:
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM user_date UNION ALL SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM user_date GROUP BY month UNION ALL SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM user_date GROUP BY day UNION ALL SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM user_date GROUP BY month,day;
- ROLLUP的使用:
是CUBE的子集,以最左侧的维度为主,从该维度进行层级聚合。
比如,以month维度进行层级聚合:
SELECT month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID FROM user_date GROUP BY month,day WITH ROLLUP ORDER BY GROUPING__ID;
结果如下:
把month和day调换顺序,则以day维度进行层级聚合:
SELECT day, month, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID FROM user_date GROUP BY day,month WITH ROLLUP ORDER BY GROUPING__ID;
结果如下:
这里,根据日和月进行聚合,和根据日聚合结果一样,因为有父子关系,如果是其他维度组合的话,就会不一样。