hive行转列与列转行_hive 列转行

**

第一部分：行转列*

• 1.简单行转列：将一个融合多个信息的字段拆分成多行

步骤：先用split切开按照，进行分隔成数组的形式 ["aaa","bbb","ccc"]
	  然后再用explode函数炸开，将一行分成多行
1
2

（1）将一个融合多个信息的字段拆分为多行	
1

结果展示：aaa,bbb,ccc  ===>  aaa
                   bbb
                   ccc
1
2
3

	（2）实现方法：利用内置UDTF函数explode实现
1

结果展示：
split('aaa,bbb,ccc',','); //根据逗号进行分割，将复合字段转化为hive数组类型
explode(split('aaa,bbb,ccc',',')); //对得到的数据使用explode函数拆分为多行

1
2
3
4

	（3）实现结果
1

hive> select split('aaa,bbb,ccc',',');
OK
["aaa","bbb","ccc"]
----------------------------------------------
hive> select explode(split('aaa,bbb,ccc',','));
OK
aaa
bbb
ccc
1
2
3
4
5
6
7
8
9

2.复杂行转列
将转换后的多行信息与原表字段关联查询
lateral view侧视图即产生一张虚拟表
explode(split(hobby,’,’))temp as x此与句本身返回的就是一张虚拟表，记为temp，可以为temp表的列指定字段名X
student lateral view temp 使用lateral view可以直接关联两张表，保持原有的映射关系
select后边的是字段，我们炸开的是原student表的hobby字段，此字段炸开后就可以用lateral view形成一个与原表字段连接的桥梁
select student.name.temp.x
from student lateral view explode(split(hobby,’,’)) temp as x;//完整的
实例
（1）需求：将转换后的多行信息与原表字段关联查询。
比如：原始数据student表

name hobby
tom 唱歌,跳舞,弹琴
jack 唱歌,绘画,打篮球
1
2
3

（2）转换后数据

tom 唱歌
tom 跳舞
tom 弹琴
jack 唱歌
jack 绘画
jack 打篮球
1
2
3
4
5
6

(3)拆分后的字段与原表的name字段是没有办法直接关联的，因为它们不是一张表。可以使用later view侧视图进行关联查询

explode(split(hobby,',')) temp as x   //此语句本身返回的就是一张虚拟表 记为temp 可以为temp表的列指定字段名x
student lateral view temp     //使用later view可以直接关联两张表，保持原有映射关系
select student.name,temp.x from student lateral view explode(split(hobby,',')) temp as x;//完整写法
1
2
3

(4)创建student表并加载数据

create external table student
(
   name string,
   hobby string
) row format delimited fields terminated by ' '; 
load data local inpath '/root/student.txt' into table student;
1
2
3
4
5
6

(5)执行sql语句 查看结果

hive> select student.name,temp.x from student lateral view explode(split(hobby,',')) temp as x;
OK
tom     唱歌
tom     跳舞
tom     弹琴
jack    唱歌
jack    绘画
jack    打篮球
1
2
3
4
5
6
7
8

**

第二部分 列转行

**
1.简单列转行
将多行（一列）数据融合为一行
collect_list不去重
collect_set(column)将某一列所有的数据转化为一个集合（去重）
concat_ws(’,’,collect_set(column))将集合中的所有元素以,号分割连接为字符串
实例：

aaa   ===>   aaa,bbb,ccc
bbb
ccc
1
2
3

实现方法：

collect_set(column) //将某一列所有数据转化为一个集合（去重）
concat_ws(',',collect_set(column))  //将集合中所有元素以逗号分割连接为字符串
1
2

将dept表的dep列转为一行 展示结果

select dep from dept;
销售
测试
开发
算法
--------------------------------------------------------
select concat_ws(',',collect_set(dep)) from dept;
销售,测试,开发,算法
1
2
3
4
5
6
7
8

此外还有一个聚合函数 collect_list 不去重

2.复杂列转行
转换后的一行信息与原始行字段关联查询
实例：
（1）原始数据

name hobby
tom 唱歌
tom 跳舞
tom 弹琴
jack 唱歌
jack 绘画
jack 打篮球
1
2
3
4
5
6
7

（2）结果数据

tom 唱歌,跳舞,弹琴
jack 唱歌,绘画,打篮球
1
2

（3）根据name字段进行分组，在每个分组内部name是唯一的可以直接查询，hobby字段就可以使用collect_set与concat_ws进行连接为复合字段。
创建stu表导入数据

create external table stu
(
   name string,
   hobby string
) row format delimited fields terminated by ' '; 
load data local inpath '/root/stu.txt' into table stu;
1
2
3
4
5
6

（4）查询

查询：select name,concat_ws(’,’,collect_set(hobby)) from stu group by name;
1

（5）结果如下

OK
jack    唱歌,绘画,打篮球
tom     唱歌,跳舞,弹琴
1
2
3