Hive中如何实现行转列、列转行？

第一篇笔记是关于自定义函数的内容，在提到UDTF自定义表生成函数的时候，讲了UDTF函数的编写过程，因为当时理解完UDF编写过程后就有点学习疲惫了(哈哈哈哈这个精神力还是得提高呀)。提到了UDTF的两种使用方式，第一种是直接select后面使用，但不支持嵌套使用、不支持和其他字段一起使用、不支持和分组、排序一起使用；第二种是和lateral view一起使用(其本质是将udtf后的结果看成一张虚拟表，再和其他表的字段拼在一起使用，可以解决单独用udtf函数时不能实现的情况)。这里，初步补充一下和lateral view、行转列、列转行有关的知识点(理解的不是太明白，有时间再加深一下)。

1. 行转列: UDF/UDAF函数

(1) 相关函数

• concat(str1,str2,...) :
  ① 返回输入字符串连接后的结果，支持任意个输入字符串(将多个字符串里连接成一个字符串)
  ② 如果有任何一个参数为NULL,则返回值也为NULL，可以有一个或多个参数
• concat_ws(separator, str1, str2, ...) :
  ① 是特殊形式的concat()。
  ② 第一个参数是其他参数的分隔符；分隔符的位置放在要连接的两个字符串之间；
  ③ 分隔符可以是一个字符串，也可以是其他参数。
  ④ 如果分隔符是NULL，返回值也为NULL，
  ⑤ 这个函数会跳过(忽略)分隔符参数后的任何NULL值，但是不会忽略任何空字符串
• collect_set(col) :
  只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段(即对去重字段返回一个数组)

示例:

--1.数据准备
name    constellation    blood_type
孙悟空  白羊座            A
大海    射手座            A
宋宋    白羊座            B
猪八戒  白羊座            A
凤姐    射手座            A
--2.创建表并导入数据
create table person_info(
    name string,
    constellation string,
    blood_type string
)row format delimited fields terminated by '\t';
load data local inpath '/home/hadoop/constellation.txt' into table person_info;

--3.需求
把星座和血型相同的人归类在一起，结果如下:
射手座，A    大海 | 凤姐
白羊座，A    孙悟空 | 猪八戒
白羊座，B    宋宋
--4.实现:
select name,concat(constellation,',',blood_type) as base from person_info;

select t1.base concat_ws('|',collect_set(t1.name)) name from
(select name,concat(constellation,',',blood_type) as base from person_info) t1
group by t1.base;

2. 列转行: UDTF函数

(1)相关函数

explode(col) 炸裂函数: 将hive一列中复杂的array或者map结构拆分成多行

select explode(arraycol) as newcol from tablename;
--explode()函数中参数传入的是array数据类型的列名，newcol是给转换成列命名一个新的名字，用于代表转换后的列名

lateral view 侧写函数: 配合split，explode等UDTF函数一起使用，能够将一列数据拆成多行数据，并且对拆分后的结果进行聚合，即将多行结果组合成一个支持别名的虚拟表。

--语法:
lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*
--解释:
lateral view在UDTF前使用，表示连接UDTF所分裂的字段，
UDTF(expression): 使用的UDTF函数，例如explode()
tableAlias: 表示UDTF函数转换的虚拟表的名称(临时表名)
columnAlias: 表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可;如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开。(临时表的字段)

示例: 

--1.数据准备
movie    category
《疑犯追踪》    悬疑，动作，科幻，剧情
《Lie to me》    悬疑，警匪，动作，心理，剧情
《战狼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 '/home/hadoop/movie.txt' into table movie_info;

--3.需求
--按照类型分组，把电影名放在集合中
select tbl.type,collect_list(movie) from movie_info 
lateral view explode(split(category,',')) tbl as type
group by tbl.type;
tbl.type    _c1
剧情    ["《疑犯追踪》","《Lie to me》"]
动作    ["《疑犯追踪》","《Lie to me》","《战狼2》"]
心理    ["《Lie to me》"]
悬疑    ["《疑犯追踪》","《Lie to me》"]
战争    ["《战狼2》"]
灾难    ["《战狼2》"]
科幻    ["《疑犯追踪》"]
警匪    ["《Lie to me》"]

select m.movie, tbl.type from movie_info m 
lateral view explode(split(category,',')) tbl as type;
m.movie    tbl.type
《疑犯追踪》    悬疑
《疑犯追踪》    动作
《疑犯追踪》    科幻
《疑犯追踪》    剧情
《Lie to me》    悬疑
《Lie to me》    警匪
《Lie to me》    动作
《Lie to me》    心理
《Lie to me》    剧情
《战狼2》    战争
《战狼2》    动作
《战狼2》    灾难