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..

Solution 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

Solution 2

Simple explanation, suppose your table having data like name and qualification

Table content:

Amar    ["Btech","Mtech"]
Amala   ["Bsc","Msc","Mtech"]
Akash   ["Btech","Mba"]

Table Creation:

create table raw2(name string, qual  array<string>);

Query:

select name, myq from <table> lateral view explode(qual) q as myq;

Solution 3

creating table with name Favourites, id of int type, name of string type & fav_song of Array of String type.

hive> CREATE TABLE Favourites
> (id INT, Name STRING, fav_song ARRAY<STRING>);

In general, Lateral view distributes the array elements in sequential rows keeping the common rows as it is.

For Example:

hive> select * from Favourites;

id name fav_song

1 Akshay ['Rang De Basanti','Live it Up']

2 Sonal ['All the Stars','1000 years']

The rows post the query will be displayed as:

hive> SELECT p.id,p.name,colors.my_fav_song FROM default.Favourites P
LATERAL VIEW EXPLODE(p.fav_song) fv as my_fav_song;

id name my_fav_song

1 Akshay Rang De Basanti

1 Akshay Live it Up

2 Sonal All the Stars

2 Sonal 1000 years

Comments

[Regressor]

why do u have colors keyword in SELECT p.id,p.name,colors.my_fav_song FROM default.Favourites P LATERAL VIEW EXPLODE(p.fav_song) fv as my_fav_song; query ?

[Rahul]

Hi Ankur ,I have the same kind of data set and i am trying get multiple rows with the values in the array.

[Mez13]

What if we want to join a table to the one you have created at the end? It seems to be refused by hive