LATERAL VIEW EXPLODE IN HIVE

To access individual elements of Array we useĀ Built-in Table-Generating Functions (UDTF) available in Hive. Below explained is simple example with description in each step.

Built-in Table-Generating Functions (UDTF)

To understand details of any function we can run describe command, to get list of available functions we can run show functions;
hive> describe function explode;
explode(a) – separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns

Now we are creating table with name products, id of int type, product name of string type & ProductColorOptions of Array of String type.
hive> CREATE TABLE Products
> (id INT, ProductName STRING, ProductColorOptions ARRAY<STRING>);

We can not directly insert data in table containing arrays, we need to create dummy table & then use that table to insert data as shown below:
hive> create table dummy (a String);

hive> insert into table dummy values (‘a’);

hive> insert into table products select 1,’Watches’,array(‘Red’,’Green’) from dummy;
hive> insert into table products select 2,’Clothes’,array(‘Blue’,’Green’) from dummy;
hive> insert into table products select 3,’Books’,array(‘Blue’,’Green’,’Red’) from dummy;

Now we have 3 entries in products table as seen by select query below;
hive> select * from products;
1 Watches [“Red”,”Green”]
2 Clothes [“Blue”,”Green”]
3 Books [“Blue”,”Green”,”Red”]

Below is select with first index of array:
hive> SELECT id, productname, productcoloroptions[0] FROM default.products;
1 Watches Red
2 Clothes Blue
3 Books Blue

Now the main command LATERAL VIEW EXPLODE which gives below result.
SELECT p.id,p.productname,colors.colorselection FROM default.products P
LATERAL VIEW EXPLODE(p.productcoloroptions) colors as colorselection;
1 Watches Red
1 Watches Green
2 Clothes Blue
2 Clothes Green
3 Books Blue
3 Books Green
3 Books Red

A lateral view first applies the UDTF to each row of base table and then joins resulting output rows to the input rows to form a virtual table having the supplied table alias.