Data science Software Course Training in Ameerpet Hyderabad

Data science Software Course Training in Ameerpet Hyderabad

Tuesday, 5 July 2016

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









4 comments:

  1. please update hive lab 3 related to partitions

    ReplyDelete
  2. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
    Big Data Hadoop Training in electronic city

    ReplyDelete