Hive--sql中的explode()函数和posexplode()函数

实现多列转多行

1. 先创建一个txt文件（最好是用notepad++，注意将编码设置为utf-8）如下：
   
2. 将该文件放到hive下的一个目录中（可以自己指定目录），我是将它放在一个data目录中
   
3. 在hive的一个数据库中创建一个表来进行操作，指定表名test.a，加入两个字段id和tim，并在一行中用空格分隔，每行之间用\n进行分隔。

create table if not exists test.a(
     id STRING,
     tim STRING
)
row format delimited fields terminated by '-' 
lines terminated by '\n';

4. 将开始创建的文件中的数据加载进新建的表中

load data local inpath 'opt/data/shijian.txt' into table test.a;

5. 现在可以看看我们新创建的这个表中的数据

hive> load data local inpath 'opt/data/shijian.txt' into table test.a;
Loading data to table test.a
Table test.a stats: [numFiles=1, numRows=0, totalSize=56, rawDataSize=0]
OK
Time taken: 0.654 seconds
hive> select * from test.a;                                           
OK
a.id	a.tim
a,b,c,d	2:00,3:00,4:00,5:00
f,b,c,d	1:10,2:20,3:30,4:40
Time taken: 0.125 seconds, Fetched: 2 row(s)

6. 现在先进行一列转多行的操作，这里就用到了explode()函数，将第二列tim中的数据用逗号切分并成为第三列，操作如下

select id,tim,single_tim 
from test.a lateral view explode(split(tim,',')) t as single_tim

id	tim	single_tim
a,b,c,d	2:00,3:00,4:00,5:00	2:00
a,b,c,d	2:00,3:00,4:00,5:00	3:00
a,b,c,d	2:00,3:00,4:00,5:00	4:00
a,b,c,d	2:00,3:00,4:00,5:00	5:00
f,b,c,d	1:10,2:20,3:30,4:40	1:10
f,b,c,d	1:10,2:20,3:30,4:40	2:20
f,b,c,d	1:10,2:20,3:30,4:40	3:30
f,b,c,d	1:10,2:20,3:30,4:40	4:40
Time taken: 51.289 seconds, Fetched: 8 row(s)

可以看出上面的代码实现了对第二列的多行转换，现在如果想实现对两列听同事进行多行转换，那么用explode()函数就不能实现了，但可以用posexplode()函数，因为该函数可以将index和数据都取出来，使用两次posexplode并令两次取到的index相等就行了。

7. 使用一次posexplode()函数效果如下：

select id,tim,single_id_index,single_id from test.a 
lateral view posexplode(split(id,',')) t as single_id_index, single_id;d;

id	tim	single_id_index	single_id
a,b,c,d	2:00,3:00,4:00,5:00	0	a
a,b,c,d	2:00,3:00,4:00,5:00	1	b
a,b,c,d	2:00,3:00,4:00,5:00	2	c
a,b,c,d	2:00,3:00,4:00,5:00	3	d
f,b,c,d	1:10,2:20,3:30,4:40	0	f
f,b,c,d	1:10,2:20,3:30,4:40	1	b
f,b,c,d	1:10,2:20,3:30,4:40	2	c
f,b,c,d	1:10,2:20,3:30,4:40	3	d
Time taken: 43.6 seconds, Fetched: 8 row(s)

8. 使用两次posexplode()函数实现多列转多行

select id,tim,single_id,single_tim from test.a 
lateral view posexplode(split(id,',')) t as single_id_index, single_id
lateral view posexplode(split(tim,',')) t as single_yim_index, single_tim
where single_id_index = single_yim_index;

id	tim	single_id	single_tim
a,b,c,d	2:00,3:00,4:00,5:00	a	2:00
a,b,c,d	2:00,3:00,4:00,5:00	b	3:00
a,b,c,d	2:00,3:00,4:00,5:00	c	4:00
a,b,c,d	2:00,3:00,4:00,5:00	d	5:00
f,b,c,d	1:10,2:20,3:30,4:40	f	1:10
f,b,c,d	1:10,2:20,3:30,4:40	b	2:20
f,b,c,d	1:10,2:20,3:30,4:40	c	3:30
f,b,c,d	1:10,2:20,3:30,4:40	d	4:40

这样就可以实现了。