Hive Lab 12 : Loading Array, Struct, Map collection Items

[training@localhost ~]$ cat profile1
101,Venu,btech#mtech,25,hyd
102,Veni,bsc#msc#mtech,26,pune
[training@localhost ~]$ 



hive> create database pract;
OK
Time taken: 1.798 seconds
hive> use pract;
OK
Time taken: 0.027 seconds
hive> create table profile1(
    >   id int, name string, 
    >     qual  array<string>, age int,
    >     city string) 
    > row format delimited        
    >     fields terminated by ','
    >     collection items terminated by '#';

hive> load data local inpath 'profile1'
    >   into table profile1;
Copying data from file:/home/training/profile1
Copying file: file:/home/training/profile1
Loading data to table pract.profile1
OK
Time taken: 0.208 seconds
hive> select * from profile1;
OK
101     Venu    ["btech","mtech"]       25      hyd
102     Veni    ["bsc","msc","mtech"]   26      pune
Time taken: 0.199 seconds
hive> 

__________________
[training@localhost ~]$ cat profile2
101,Venu,Vani#25#btech,hyd
102,Varun,Varuna#24#mba,pune
[training@localhost ~]$ 

hive> create table profile2(
    >  id int, name string, 
    >   wife struct<name:string,age:int,qual:string>, city string)
    >   row format delimited 
    >     fields terminated by ','
    >     collection items terminated by '#';
OK
Time taken: 0.044 seconds
hive> load data local inpath 'profile2'
    >   into table profile2;
Copying data from file:/home/training/profile2
Copying file: file:/home/training/profile2
Loading data to table pract.profile2
OK
Time taken: 0.1 seconds
hive> 
hive> select * from profile2;
OK
101     Venu    {"name":"Vani","age":25,"qual":"btech"}   hyd
102     Varun   {"name":"Varuna","age":24,"qual":"mba"}   pune
Time taken: 0.066 seconds
hive> select name, wife.name from profile2;

Venu    Vani
Varun   Varuna

hive> create table info(id int, name string, 
    >   wname string, wage int, wqual string, 
    >   city string)
    > ;
OK
Time taken: 0.039 seconds
hive> insert overwrite table info
    >   select id, name, wife.name, wife.age,
    >   wife.qual , city  from profile2;

hive> select * from info;
OK
101     Venu    Vani    25      btech   hyd
102     Varun   Varuna  24      mba     pune
Time taken: 0.066 seconds
hive> 

____________________________________
[training@localhost ~]$ cat profile3
101,p1#1000$p3#2000$p7#4000,hyd
102,p1#1200$p2#5000,del
[training@localhost ~]$ 

hive> create table  trans(cid int, 
    >   plist map<string,int>,
    >  city string)
    > row format delimited 
    >    fields terminated by ','
    >    collection items terminated by '$'
    >   map keys terminated by '#';
OK
Time taken: 0.048 seconds
hive> load data local inpath 'profile3'
    >   into table trans;
Copying data from file:/home/training/profile3
Copying file: file:/home/training/profile3
Loading data to table pract.trans
OK
Time taken: 0.103 seconds

hive> select cid, plist['p1'] , city from trans;

hive>
101     1000    hyd
102     1200    del


hive> create table sales(cid int, 
    >    prid  array<string>,
    >   pr array<int>);
OK
Time taken: 0.05 seconds
hive> insert overwrite table sales
    >   select cid, map_keys(plist),
    >    map_values(plist)  from trans;


hive> select * from sales;
OK
101     ["p1","p3","p7"]        [1000,2000,4000]
102     ["p1","p2"]     [1200,5000]
Time taken: 0.056 seconds
hive> 
___________________________________