Hive SQL(5)-lateral view 、explode 、reflect

使用explode函数将hive表中的Map和Array字段数据进行拆分

lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。

其中explode还可以用于将hive一列中复杂的array或者map结构拆分成多行

需求：现在有数据格式如下 

zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4

字段之间使用\t分割，需求将所有的child进行拆开成为一列


+----------+--+
| mychild  |
+----------+--+
| child1   |
| child2   |
| child3   |
| child4   |
| child5   |
| child6   |
| child7   |
| child8   |
+----------+--+

将map的key和value也进行拆开，成为如下结果


+-----------+-------------+--+
| mymapkey  | mymapvalue  |
+-----------+-------------+--+
| k1        | v1          |
| k2        | v2          |
| k3        | v3          |
| k4        | v4          |
+-----------+-------------+--+

  • 创建hive数据库
    • 

    创建hive数据库
hive (default)> create database hive_explode;
hive (default)> use hive_explode;

  • 创建hive表，然后使用explode拆分map和array
    • 

    hive (hive_explode)> create  table t3(
  name string,
  children array<string>,
  address Map<string,string>
) row format delimited fields terminated by '\t'  
collection items terminated by ',' 
map keys terminated by ':' 
stored as textFile;

    node03执行以下命令创建表数据文件
 mkdir -p /export/servers/hivedatas/
 cd /export/servers/hivedatas/
 vim maparray
内容如下:
zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4
hive表当中加载数据
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/maparray' into table t3;

  • 使用explode将hive当中数据拆开
    • 

    将array当中的数据拆分开
hive (hive_explode)> SELECT explode(children) AS myChild FROM t3;
将map当中的数据拆分开
hive (hive_explode)> SELECT explode(address) AS (myMapKey, myMapValue) FROM t3;

    使用explode拆分json字符串
    

    需求: 需求：现在有一些数据格式如下：
    

    a:shandong,b:beijing,c:hebei|
1,2,3,4,5,6,7,8,9|
[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},
{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},
{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]

    其中字段与字段之间的分隔符是 |
    

    我们要解析得到所有的monthSales对应的值为以下这一列（行转列）
    

  • 创建hive表
    • 

    hive (hive_explode)> create table explode_lateral_view
                   > (`area` string,
                   > `goods_id` string,
                   > `sale_info` string)
                   > ROW FORMAT DELIMITED
                   > FIELDS TERMINATED BY '|'
                   > STORED AS textfile;

  • 准备数据并加载数据
    • 

    准备数据如下
cd /export/servers/hivedatas
vim explode_json
a:shandong,b:beijing,c:hebei|
1,2,3,4,5,6,7,8,9|
[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},
{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},
{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
加载数据到hive表当中去
hive (hive_explode)> load data local inpath '/export/servers/hivedatas/explode_json' 
                   > overwrite into table explode_lateral_view;

  • 使用explode拆分Array
    • 

    hive (hive_explode)> select explode(split(goods_id,',')) as goods_id from explode_lateral_view;

  • 使用explode拆解Map
    • 

    hive (hive_explode)> select explode(split(area,',')) as area from explode_lateral_view;

  • 拆解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查询多个字段
    

    select 
  goods_id2,sale_info 
  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格式的一行数据，完全转换成二维表的方式展现
    

    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 
  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类型字段。
    

    数据准备:
    
constellation
blood_type
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
                        (select
                            name,
                            concat(constellation, "," , blood_type) base
                            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
    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 
文件内容如下:

    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;
结果如下：（因命令行原因，下图字段名和值是错位的，请注意辨别！）
    

  • 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,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn 
FROM user_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可以看成是：把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
    

    然后可以根据桶号，选取前或后 n分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。
    

    SELECT 
cookieid,
createtime,
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,
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,
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;
结果如下：
    
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;
结果如下：
    
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
结果如下：
    
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 的倒序取出分组内排序最后一个值！
    
结果如下：
    

    上述 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,
CUME_DIST() OVER(ORDER BY sal) AS rn1,
CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
FROM staff_salary;
结果如下：
    
     第三行：小于等于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,
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 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,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID  
FROM user_date 
GROUP BY month,day
WITH ROLLUP 
ORDER BY GROUPING__ID;
结果如下：