hive-列转行

Lateral view explode转换数组数据到多行。
举例说明：

创建测试表

create table test_explode(
    name varchar(100),
    phones array<string>,
    cities array<string>
);

添加测试数据

insert overwrite table  test_explode select 'AAA',array("123456","678901"),array("city1","city2")

查看数据

name	phones	cities
AAA	[“123456”, “678901”]	[“city1”,“city2”]

使用explode

select 
    name, 
    phone_number, 
    city 
from default.test_explode
lateral view explode(phones) pn1 as phone_number
lateral view explode(cities) pn2 as city 

AAA     123456  city1
AAA     123456  city2
AAA     678901  city1
AAA     678901  city2

使用posexplode

select
    name, 
    phone_number, 
    city 
from default.test_explode
lateral view posexplode(phones) pn as pos_phone, phone_number
lateral view posexplode(cities) pn as pos_city, city 
where 
    pos_phone == pos_city

AAA     123456  city1
AAA     678901  city2