Create table with Collections columns

 sample Unstructure.csv

10 first:Amit,Last:Mishra Blr,1 'Hadoop','OBIEE'
20 first:Ramesh,Last:Nayak Mas,2 'oracle','Chocolate'

move the above to the local file system.

create a table in Hive shell.

hive> create table T_UNSTRUCTURE (emp_id int,name map<string,string>,addr struct<City:String,Pin:int>,skill_set array<string>) row format delimited fields terminated by '\t' collection items terminated by ',' map keys terminated by ':';

Load data into the table with the below statement.

LOAD DATA LOCAL INPATH '/home/hadoop/Unstructure.csv' OVERWRITE INTO table T_UNSTRUCTURE;

Look at the description of the table

hive> desc T_UNSTRUCTURE;

OK

col_name        data_type       comment

emp_id                  int

name                    map<string,string>

addr                    struct<City:string,Pin:int>

skill_set               array<string>

Access the elements of table.

hive> select * from T_UNSTRUCTURE;

OK

t_unstructure.emp_id    t_unstructure.name      t_unstructure.addr      t_unstructure.skill_set

10      {"first":"Amit","Last":"Mishra"}        {"city":"Blr","pin":1}  ["'Hadoop'","'OBIEE'"]

20      {"first":"Ramesh","Last":"Nayak"}       {"city":"Mas","pin":2}  ["'oracle'","'Chocolate'"]

Time taken: 0.216 seconds, Fetched: 2 row(s)