hive 列转行和行转列

数据准备

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

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

CREATE table tmp_pv_uv(
    hour string comment 'hour',
	uv bigint comment 'uv',
	pv bigint comment 'pv',
	ip bigint comment 'ip'
) comment 'pu uv数据';

insert into tmp_pv_uv values 
("00", 100, 200,50),
("01", 101, 201,51),
("02", 102, 202,52),
("03", 103, 203,53)
;

列转行：单列转多行

explode

explode 爆炸的意思，可用于list 和map字段的列转行
explode(array)将array列表里的每个元素生成一行；
explode(map)将map里的每一对元素作为一行，其中key为一列，value\

select explode(`array`(11,22,33)) as item;

输出：

select explode(`map`("id",10086,"name","zhangsan","age",18));

输出：

select 1, explode(`map`("id",10086,"name","zhangsan","age",18));

报错：
Semantic analysis exception - only a single expression in the SELECT clause is supported with UDTF’s
错误原因是：
1.explode函数属于UDTF函数，既表生成函数
2.explode函数执行返回的结果可以理解为一张虚拟的表，起数据来源于表
3.在select中只查询源数据没有问题，只查询explode生成的虚拟表数据也没问题
4.但是不能在只查询源表的时候，既想返回源表字段又想返回explode生成的虚拟表字段
5.通俗点讲，有两张表，不能只查询一张表但是返回属于两张表的字段
6.从SQL层面上来说应该对两张表进行关联查询
7.hive专门提供了语法lateral view 侧视图，专门用于搭配explode这样的UDTF函数，以满足上述需要

posexplode

对一列进行炸裂可以使用 explode()函数，但是如果想实现对两列都进行多行转换，
那么用explode()函数就不能实现了，可以用posexplode()函数，因为该函数可以将index和数据都取出来，
使用两次posexplode并令两次取到的index相等就行了。

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`
where stu_id='1001'
;

输出：

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 and stu_id='1001'
;

输出：

行列互转

with p as(
    select hour, key, value 
    from (
        select 
            hour
            ,map('uv', uv, 'pv', pv, 'ip', ip) m
        from tmp_pv_uv
    ) LATERAL view explode(m) t1 as key, value
)
select 
    key
    , sum(case when `hour`='00' then value else 0 end) `00`
    , sum(case when `hour`='01' then value else 0 end) `01`
    , sum(case when `hour`='02' then value else 0 end) `02`
    , sum(case when `hour`='03' then value else 0 end) `03`
from p 
group by key
;

输出过程如下：

解析多个json字段 json_tuple

select
json,device,deviceType,signal,stime
from tb_json_test1
lateral view json_tuple(json,"device","deviceType","signal","time") b
as device,deviceType,signal,stime;

另一种写法是使用GET_JSON_OBJECT()

select
json,GET_JSON_OBJECT(json,'$.device') device
,GET_JSON_OBJECT(json,'$.deviceType') deviceType
,GET_JSON_OBJECT(json,'$.signal') signal
,GET_JSON_OBJECT(json,'$.time') time
from tb_json_test1
lateral view json_tuple(json,"device","deviceType","signal","time") b
as device,deviceType,signal,stime;

行转列：多行转单列

concat(element1,element2,element3……)：用于实现字符串拼接，不可指定分隔符
concat_ws(SplitChar，element1，element2……) ：实现字符串拼接，可以指定分隔符
collect_list（colName）：用于将一列中的多行合并为一行，不进行去重
collect_set（colName）：用于将一列中的多行合并为一行，并进行去重

参考文献

1. https://zhuanlan.zhihu.com/p/514996706
2. https://blog.csdn.net/qq_36039236/article/details/108446974?spm=1001.2101.3001.6650.8&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ERate-8-108446974-blog-124449548.235%5Ev43%5Epc_blog_bottom_relevance_base8&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ERate-8-108446974-blog-124449548.235%5Ev43%5Epc_blog_bottom_relevance_base8&utm_relevant_index=17
3. https://zhuanlan.zhihu.com/p/686089605