Hive--行转列（Lateral View explode()）和列转行（collect_set() 去重）

忘是亡心i 2021-11-05 18:44 545阅读 0赞

一行转多行

说明：lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。

1.行转列

1.1 问题引入：

如何将

a       b       1,2,3

c       d       4,5,6

变为：

a       b       1

a       b       2

a       b       3

c       d       4

c       d       5

c       d       6

1.2 原始数据：

test.txt

a b 1,2,3

c d 4,5,6

1.3 解决方法

方案1：

drop table test\_jzl\_20140701\_test;

建表：

create table test\_jzl\_20140701\_test

(

col1 string,

col2 string,

col3 string

)

row format delimited fields terminated by ' '

stored as textfile;

加载数据：

load data local inpath '/home/jiangzl/shell/test.txt' into table test\_jzl\_20140701\_test;

查看表中所有数据：

select \* from test\_jzl\_20140701\_test

a       b       1,2,3

c       d       4,5,6

遍历数组中的每一列

select col1,col2,name

from test\_jzl\_20140701\_test

lateral view explode(split(col3,',')) col3 as name;

a       b       1

a       b       2

a       b       3

c       d       4

c       d       5

c       d       6

方案2：

drop table test\_jzl\_20140701\_test1;

建表：

create table test\_jzl\_20140701\_test1

(

col1 string,

col2 string,

col3 array<int>

)

row format delimited

fields terminated by ' '

collection items terminated by ','   //定义数组的分隔符

stored as textfile;

加载数据：

load data local inpath '/home/jiangzl/shell/test.txt' into table test\_jzl\_20140701\_test1;

查看表中所有数据：

select \* from test\_jzl\_20140701\_test1;

a       b       \[1,2,3\]

c       d       \[4,5,6\]

遍历数组中的每一列：

select col1,col2,name

from test\_jzl\_20140701\_test1

lateral view explode(col3) col3 as name;

a       b       1

a       b       2

a       b       3

c       d       4

c       d       5

c       d       6

1.4补充知识点：

select \* from test\_jzl\_20140701\_test;

a       b       1,2,3

c       d       4,5,6

select t.list\[0\],t.list\[1\],t.list\[2\] from (

select (split(col3,',')) list from test\_jzl\_20140701\_test)t;

1       2       3

4       5       6

\--查看数组长度

select size(split(col3,',')) list from test\_jzl\_20140701\_test;

2.列转行

2.1问题引入：

hive如何将

a       b       1  
a       b       2  
a       b       3  
c       d       4  
c       d       5  
c       d       6

变为：

a       b       1,2,3  
c       d       4,5,6

2,2原始数据：

test.txt

a       b       1  
a       b       2  
a       b       3  
c       d       4  
c       d       5  
c       d       6

2.3 解决方法：

drop table tmp\_jiangzl\_test;

建表：

create table tmp\_jiangzl\_test  
(  
col1 string,  
col2 string,  
col3 string  
)  
row format delimited fields terminated by '\\t'  
stored as textfile;  
加载数据：  
load data local inpath '/home/jiangzl/shell/test.txt' into table tmp\_jiangzl\_test;

处理：

select col1,col2,concat\_ws(',',collect\_set(col3))  
from tmp\_jiangzl\_test    
group by col1,col2;