Hive complex data types , explode, Lateral view

Create a table with array, struct complex data types as below.

CREATE TABLE student_details(

id_key string,
name string,
subjects array<string>,
address struct<city:string,State:string>
);

Insert sample data as below.

INSERT INTO student_details with below command.

select 

'AA87U',

'BRYAN', 

array('ENG','CAL_1','CAL_2','HST','MUS'),

named_struct('city','Tampa','State','FL');

display the sample data.

select * from student_details;

Explode:  Displaying array using explode method and Lateral view.

select id_key,name,each_subject
from
student_details
lateral view explode(subjects) temp_table as each_subject;

Inline: Displaying struct components using inline.

select id_key,name,add.*
from
student_details
lateral view inline (array(address)) add;

Displaying both array and struct data in 1NF.

select id_key,name,each_subject,add.*
from
student_details
lateral view explode(subjects) temp_table as each_subject
lateral view inline (array(address)) add;

Commonly used functions in array.

select size(subjects),sort_array(subjects),concat_ws('/',subjects),array_contains(subjects,'END') from student_details;