Hive 之 explode 和 posexplode

一. explode, 行转列。

1.1. 用于array类型的数据

• table_name 表名
• array_col 为数组类型的字段
• new_col array_col被explode之后对应的列

select explode(array_col) as new_col from table_name

1.2. 用于map类型的数据

由于map是kay-value结构的，所以它在转换的时候会转换成两列，一列是kay转换而成的，一列是value转换而成的。

• table_name 表名
• map_col 为map类型的字段
• may_key_col, may_value_col 分别对应map_col被拆分之后的map映射里的key 和 value

select explode(map_col) as (may_key_col, may_value_col) from table_name

二. explode函数的局限性

• 不能关联原有的表中的其他字段, 例如:

select other_col, explode(array_col) as new_col from table_name 

• 不能与group by、cluster by、distribute by、sort by联用, 例如:

select explode(array_col) as new_col from table_name group by explode(array_col) 

不能进行UDTF嵌套, 例如:

select explode(explode(array_col)) from table_name 

不允许选择其他表达式, 例如:

select explode("1,2,3,4,5") from table_name 

三. lateral view

lateral view 用于和UDTF相结合使用， 它会将UDTF生成的结果放在一张虚拟表中 (即lateral view里)。虚拟表相当于再和主表关联, 从而达到添加“UDTF生成的字段“以外字段的目的, 即主表里的字段或者主表运算后的字段。

3.1 第一种形式

select o.*, table_view.new_col
from table_origin o
lateral view UDTF(expression) table_view as `new_col_1`, `new_col_2`

• lateral view 表示将UDTF分裂的字段放在虚拟表中, 然后和主表table_origin进行关联。(个人理解，关联条件应该是UDTF里的expression所产生的关联条件)
• UDTF(expression)：复合逻辑规则的UDTF函数，最常用的explode
• table_view : 对应的虚拟表的表名
• new_col: 虚拟表里存放的有效字段
• from子句后面也可以跟多个lateral view语句，使用空格间隔就可以了

四. 行转列

4.1 创建测试表，插入数据

CREATE table student_score(
	stu_id string comment '学号',
	stu_name string comment '姓名',
	courses string comment '各个科目',
	scores string comment '各个分数'
) comment '学生成绩表';

insert into student_score values 
("1001", "张三","语文,数学,英语,历史,地理", "88,87,94,86,84"),
("1002", "李四", "语文,数学,英语,历史,地理", "78,89,75,79,68"),
("1003", "王五", "语文,数学,英语,历史,地理", "98,97,91,93,92"),
("1004", "朱六", "语文,数学,英语,历史,地理", "66,63,64,67,68");

4.2 测试explode 行转列

select a.stu_id, a.stu_name, table_view.course 
from student_score a 
lateral view explode(split(courses, ',')) table_view as `course`;

结果:

4.3 查询每个学生课程对应的分数，使用posexplode函数

先测试使用explode, 看看效果:

select a.stu_id, a.stu_name, 
    table_view1.course, table_view2.score
from student_score a 
lateral view explode(split(courses, ',')) table_view1 as `course` 
lateral view explode(split(scores, ',')) table_view2 as `score`;

结果:

出现这种情况是因为两个并列的explode的sql没办法识别每个科目对应的成绩是多少，对于多个数组的行转列可以使用posexplode函数。
例如使用如下查询语句:

select stu_id, stu_name, course, score 
from student_score 
lateral view posexplode(split(courses, ',')) table_view1 as a, course 
lateral view posexplode(split(scores, ',')) table_view2 as b, score 
where a = b;

结果: