Hive lateral view with sample example with hive table having 1 column as array

Question

My use case is I am having one table in hive which has one column as INT and one as Array data type. I want to display it horizontally..

Answer 1

Explode function displays each element of collection data type as single row.

CREATE TABLE Products
(id INT, ProductName STRING, ProductColorOptions ARRAY<STRING>);
select * from products;

1 Watches [“Red”,”Green”]

2 Clothes [“Blue”,”Green”]

3 Books [“Blue”,”Green”,”Red”]

select explode(ProductColorOptions ) from products;

Red

Green

But I want to join with other columns like id but that leads to an error.

In that case, we need to use Lateral View.Lateral view creates a virtual table for exploded columns and make join with the base table.

We need not to worry about the virtual table as it is done by hive internally.

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