Hive 行列转换

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;