Hive Lateral view explode vs posexplode

Lateral view Explode

Lateral view explode, explodes the array data into multiple rows. for example, let’s say our table look like this, where Telephone is an array of string.

name	phone_numbers	cities
AAA	[“365-889-1234”, “365-887-2232”]	[“Hamilton”][“Burlington”]
BBB	[“232-998-3232”, “878-998-2232”]	[“Toronto”, “Stoney Creek”]

Applying a lateral view explode on the above table will expand the both Telephone and Cities and do a cross join, your final table will look like this.

name	phone_numbers	cities
AAA	365-889-1234	Hamilton
AAA	365-887-2232	Hamilton
AAA	365-889-1234	Burlington
AAA	365-887-2232	Burlington
BBB	232-998-3232	Toronto
BBB	878-998-2232	Toronto
BBB	232-998-3232	Stoney Creek
BBB	878-998-2232	Stoney Creek

Lateral View POSExplode

However, this is not what you probably want, if you want to map first telephone number to first city and second with second one, and that for all the records. Then you can use posexplode (positional explode)

posexplode gives you an index along with value when you expand any error, and then you can use this indexes to map values with each other as mentioned below.

select 
    name, 
    phone_number, 
    city 
from temp.test_laterla_view_posexplode
lateral view posexplode(phone_numbers) pn as pos_phone, phone_number
lateral view posexplode(cities) pn as pos_city, city 
where 
    pos_phone == pos_city

With above query you will get following results, where phone number is mapped with corresponding city.

name	phone_number	city
AAA	365-889-1234	Hamilton
AAA	365-887-2232	Burlington
BBB	232-998-3232	Toronto
BBB	878-998-2232	Stoney Creek