Hive 行列转换
lateral view、explode、posexplode
explode()
方法将数据扁平化 行→列 只能接收array
、map
例如
select explode(split(words,',')) from words
hello,java,hello,java,scala,python
hbase,hadoop,hadoop,hdfs,hive,hive
hbase,hadoop,hadoop,hdfs,hive,hive
↓
hello
java
hello
java
scala
python
hbase
hadoop
hadoop
……
posexplode()
在 explode() 基础上再给结果加上下标
例如
select posexplode(split(words,',')) from words
hello,java,hello,java,scala,python
hbase,hadoop,hadoop,hdfs,hive,hive
hbase,hadoop,hadoop,hdfs,hive,hive
↓
0 hello
1 java
2 hello
3 java
4 scala
0 python
1 hbase
2 hadoop
3 hadoop
……
但是 加上下标 之后,就相当于两列了,怎么去接受新的这一列呢??
可以通过别名的方式获取新字段(posexplode()方法生成的下标)
select posexplode(split(words,',')) as (index,word) from words
lateral view
用法select xx from 表 lateral view udtf() 别名1 as 别名2
udtf() -- 不管是内置的也好,还是自定义的,需要一对多的函数
lateral view 后面的udtf() 就好像创建了一个临时的视图一样,需要给这个‘视图’起一个
别名1
as 后面 需要给 前面的函数生成的多列 起
别名2
来接收这些列 ,注 别名2之间用 ' , ' 隔开且不需要加括号
lateral view
原理就拿下面的代码说明,
1.Lateral View 用于和UDTF函数【explode,split】结合来使用。
2.首先通过UDTF函数将数据拆分成多行,再将多行结果组合成一个支持别名的虚拟表。
3.主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF,不能包含其它字段以及多个UDTF的情况。
4.就像是
select name from testarray2
与explode(weight)
这张虚拟的表做笛卡尔积
create table testArray2(
name string,
weight array<string>
)row format delimited
fields terminated by '\t'
COLLECTION ITEMS terminated by ',';
志凯 "150","170","180"
上单 "150","180","190"
// lateral view
select name,col1 from testarray2 lateral view explode(weight) t1 as col1;
志凯 150
志凯 170
志凯 180
上单 150
上单 180
上单 190
// explode
select key from (select explode(map('key1',1,'key2',2,'key3',3)) as (key,value)) t;
key1
key2
key3
// lateral view、explode
select name,col1,col2 from testarray2 lateral view explode(map('key1',1,'key2',2,'key3',3)) t1 as col1,col2;
志凯 key1 1
志凯 key2 2
志凯 key3 3
上单 key1 1
上单 key2 2
上单 key3 3
// lateral view、posexplode
select name,pos,col1 from testarray2 lateral view posexplode(weight) t1 as pos,col1;
志凯 0 150
志凯 1 170
志凯 2 180
上单 0 150
上单 1 180
上单 2 190
Hive 列转行
collect_list()
-- 列转行聚合函数 要与 分组
group by
一起用
// testLieToLine
name col1
志凯 150
志凯 170
志凯 180
上单 150
上单 180
上单 190
// 建表语句
create table testLieToLine(
name string,
col1 int
)row format delimited
fields terminated by '\t';
// HQL
select name,collect_list(col1) from testLieToLine group by name;
// 结果 testarray2
// name weight
上单 ["150","180","190"]
志凯 ["150","170","180"]
// 将结果 testarray2 → testLieToLine
select t1.name
,collect_list(t1.col1)
from (
select name
,col1
from testarray2
lateral view explode(weight) t1 as col1
) t1 group by t1.name;