Hive Lab 11 : Json Array Processing

[training@localhost ~]$ cat json3
{"name":"Ravi","qual":["btech","mtech"]}
{"name":"avani","qual":["btech","mtech","mba"]}
{"name":"avinash","qual":["btech","mba"]}
[training@localhost ~]$ 
hive> create database jsons;
OK
Time taken: 1.747 seconds
hive> use jsons;
OK
Time taken: 0.032 seconds
hive> create table raw(line string);
OK
Time taken: 0.382 seconds
hive>  

hive>  load data local inpath 'json3'
    >  into table raw;

hive> select * from raw;  
OK
{"name":"Ravi","qual":["btech","mtech"]}
{"name":"avani","qual":["btech","mtech","mba"]}
{"name":"avinash","qual":["btech","mba"]}
Time taken: 0.233 seconds
hive> 

hive> create table raw2(name string, 
    >   qual  string);
OK
Time taken: 0.047 seconds
hive> insert overwrite table raw2
    >   select x.* from raw
    > lateral view
    >  json_tuple(line,'name','qual') x
    > as n,q;

hive> select * from raw2;
OK
Ravi    ["btech","mtech"]
avani   ["btech","mtech","mba"]
avinash ["btech","mba"]
Time taken: 0.064 seconds
hive> create table raw3(name string, 
    >   qual  array<string>);
OK
Time taken: 0.037 seconds
hive> insert overwrite table raw3
    >   select name, split(qual,',') from raw2;

hive> select * from raw3;
OK
Ravi    ["[\"btech\"","\"mtech\"]"]
avani   ["[\"btech\"","\"mtech\"","\"mba\"]"]
avinash ["[\"btech\"","\"mba\"]"]
Time taken: 0.063 seconds
hive> create table raw4(name string, qual string);
OK

hive> insert overwrite table raw4
    >   select name, myq from raw3
    > lateral view explode(qual) q as myq;

 > create table info(name string, 
    >    qual string);
OK
Time taken: 0.039 seconds
hive> insert overwrite table info
    >   select name, split(qual,'"')[1]
    >  from raw4;

hive> select * from info;
OK
Ravi    btech
Ravi    mtech
avani   btech
avani   mtech
avani   mba
avinash btech
avinash mba
Time taken: 0.065 seconds
hive> 

Hbase Lab 3 : Hive and Hbase Integration

Hive and Hbase Integration:
____________________________

 adv:- i)  we can process Hbase data using hql queries.
     ii) Hbase shell commands  are used for random operations over columns.
  but not for sequential filters, transformations,  aggregations  etc.

   if Hbase table is integrated with Hive,Impala , we can perform all batch related operations.

   generally, for Hive table backend storage is hdfs, if hive table is integrated with hbase,\
 Hive's tables backend is Hbase table.

 when you submit hql statement, hive starts fetching data from Hbase table, query will be executed in MapReduce Excecution model with parallel process style.

Example of Integration:
______________________

i)  
hive> create table hbaseimg1(k int, v int) 
    > stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
    > with serdeproperties("hbase.columns.mapping"=":key,x:a")
    > ;
OK
Time taken: 3.446 seconds
hive> select * from hbaseimg1;
OK
Time taken: 0.307 seconds
hive> 

hbase(main):006:0> list 'hbaseimg1'
TABLE                                             
hbaseimg1                                         
1 row(s) in 0.0710 seconds

hbase(main):007:0> 


as per above example, both hive and hbase table names are same.

 want to integrate hive table with some existed or different hbase table.

hive> create table hbaseimg2(k int, a int, b int)
    > stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
    > with serdeproperties("hbase.columns.mapping"=":key,x:a,x:b")
    > tblproperties("hbase.table.name"="hbasetabx");
OK
Time taken: 1.255 seconds
hive> 

 now hive table :  hbaseimg2
  its backend hbase table : hbasetabx


hbase(main):009:0> 
hbase(main):009:0> put 'hbasetabx',101,'x:a',10
0 row(s) in 0.0650 seconds

hbase(main):010:0> put 'hbasetabx',101,'x:b',20
0 row(s) in 0.0080 seconds

hbase(main):011:0> put 'hbasetabx',102,'x:a',100
0 row(s) in 0.0040 seconds

hbase(main):012:0> put 'hbasetabx',102,'x:b',200
0 row(s) in 0.0140 seconds

hbase(main):013:0> put 'hbasetabx',103,'x:a',1000
0 row(s) in 0.0090 seconds

hbase(main):014:0> put 'hbasetabx',103,'x:b',2000
0 row(s) in 0.0090 seconds

hbase(main):015:0> 

hbase(main):015:0> scan 'hbasetabx'
ROW           COLUMN+CELL                         
 101          column=x:a, timestamp=1467252561122,
               value=10                           
 101          column=x:b, timestamp=1467252568609,
               value=20                           
 102          column=x:a, timestamp=1467252578422,
               value=100                          
 102          column=x:b, timestamp=1467252583648,
               value=200                          
 103          column=x:a, timestamp=1467252595722,
               value=1000                         
 103          column=x:b, timestamp=1467252603469,
               value=2000                         
3 row(s) in 0.0660 seconds

hbase(main):016:0> 

hive> select * from hbaseimg1;
OK
Time taken: 0.152 seconds
hive> select * from hbaseimg2;
OK
101     10      20
102     100     200
103     1000    2000
Time taken: 0.16 seconds

_______________________________________
 mysql + sqoop + hbase + hive
____________________________

mysql> select * from emp;
+-----+-------+-------+------+------+
| id  | name  | sal   | sex  | dno  |
+-----+-------+-------+------+------+
| 101 | aaa   | 10000 | m    |   11 | 
| 102 | baaa  | 20000 | m    |   12 | 
| 103 | baaav | 30000 | f    |   12 | 
| 104 | xaaav | 50000 | f    |   11 | 
+-----+-------+-------+------+------+
4 rows in set (0.00 sec)

mysql> 
hive> create table demo1(id int, name string,
    >  sal int, sex string, dno int)
    > stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
    > with serdeproperties("hbase.columns.mapping"=":key,e:name,e:sal,e:sex,e:dno")
    > tblproperties("hbase.table.name"="hbdemo1");
OK
Time taken: 1.235 seconds
hive> select * from demo1;
OK
Time taken: 0.154 seconds
hive> 


[training@localhost ~]$ sqoop import \
>  --connect jdbc:mysql://localhost/sqdb \
>  --username root \
>  --table emp \
>  --hbase-table hbdemo1 \
>  --column-family e





hbase(main):018:0> list 'hbdemo1'
TABLE                                             
hbdemo1                                           
1 row(s) in 0.0600 seconds

hbase(main):019:0> scan 'hbdemo1'
ROW           COLUMN+CELL                         
 101          column=e:dno, timestamp=146725340767
              5, value=11                         
 101          column=e:name, timestamp=14672534076
              75, value=aaa                       
 101          column=e:sal, timestamp=146725340767
              5, value=10000                      
 101          column=e:sex, timestamp=146725340767
              5, value=m                          
 102          column=e:dno, timestamp=146725340790
              5, value=12                         
 102          column=e:name, timestamp=14672534079
              05, value=baaa                      
 102          column=e:sal, timestamp=146725340790
              5, value=20000                      
 102          column=e:sex, timestamp=146725340790
              5, value=m                          
 103          column=e:dno, timestamp=146725340905
              9, value=12                         
 103          column=e:name, timestamp=14672534090
              59, value=baaav                     
 103          column=e:sal, timestamp=146725340905
              9, value=30000                      
 103          column=e:sex, timestamp=146725340905
              9, value=f                          
 104          column=e:dno, timestamp=146725340905
              9, value=11                         
 104          column=e:name, timestamp=14672534090
              59, value=xaaav                     
 104          column=e:sal, timestamp=146725340905
              9, value=50000                      
 104          column=e:sex, timestamp=146725340905
              9, value=f                          
4 row(s) in 0.0540 seconds

hbase(main):020:0> 


hive> select * from demo1;
OK
101     aaa     10000   m       11
102     baaa    20000   m       12
103     baaav   30000   f       12
104     xaaav   50000   f       11
Time taken: 0.153 seconds
hive> 

_____________________________________

mysql> select * from ed;
+-----+-------+-------+------+-------+------+
| id  | name  | sal   | sex  | dname | loc  |
+-----+-------+-------+------+-------+------+
| 101 | aaa   | 10000 | m    | mrkt  | hyd  | 
| 102 | baaa  | 20000 | m    | hr    | del  | 
| 103 | baaav | 30000 | f    | hr    | del  | 
| 104 | xaaav | 50000 | f    | mrkt  | hyd  | 
+-----+-------+-------+------+-------+------+
4 rows in set (0.00 sec)

mysql> 

hive> create table demo2(id int, name string,
    >   sal int, sex string, 
    >  dname string, loc string)
    > stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
    > with serdeproperties("hbase.columns.mapping"=":key,e:name,e:sal,e:sex,d:dname,d:loc")
    > tblproperties("hbase.table.name"="hbdemo2");
OK
Time taken: 1.244 seconds
hive> 

[training@localhost ~]$ sqoop import  --connect jdbc:mysql://localhost/sqdb  --username root  --table ed --columns name,sal,sex,id  --hbase-table hbdemo2  --column-family e

hive> select * from demo2;
OK
101     aaa     10000   m       NULL    NULL
102     baaa    20000   m       NULL    NULL
103     baaav   30000   f       NULL    NULL
104     xaaav   50000   f       NULL    NULL
Time taken: 0.143 seconds
hive>

[training@localhost ~]$ sqoop import  --connect jdbc:mysql://localhost/sqdb  --username root  --table ed --columns dname,loc,id  --hbase-table hbdemo2  --column-family d


hive> select * from demo2;
OK
101     aaa     10000   m       mrkt    hyd
102     baaa    20000   m       hr      del
103     baaav   30000   f       hr      del
104     xaaav   50000   f       mrkt    hyd
Time taken: 0.13 seconds
hive> 

HBase Lab2 : Importing data from RDBMS to Hbase

sqoop and hbase
__________________

Importing data from Rdbms to Hbase table.

mysql> create database  sqdb;
Query OK, 1 row affected (0.00 sec)

mysql> use sqdb;
Database changed

mysql> 


mysql> insert into emp values(101,'aaa',10000,'m',11);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(102,'baaa',20000,'m',12);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(103,'baaav',30000,'f',12);
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(104,'xaaav',50000,'f',11);
Query OK, 1 row affected (0.00 sec)

mysql> 

mysql> select * from emp;
+-----+-------+-------+------+------+
| id  | name  | sal   | sex  | dno  |
+-----+-------+-------+------+------+
| 101 | aaa   | 10000 | m    |   11 | 
| 102 | baaa  | 20000 | m    |   12 | 
| 103 | baaav | 30000 | f    |   12 | 
| 104 | xaaav | 50000 | f    |   11 | 
+-----+-------+-------+------+------+
4 rows in set (0.00 sec)

mysql> 

hbase(main):047:0> create 'heroes1','e'
0 row(s) in 1.1840 seconds

hbase(main):048:0> 

[training@localhost ~]$ sqoop import \
>  --connect jdbc:mysql://localhost/sqdb \
>  --username root \
>  --table emp  \
>  --hbase-table heroes1 \
>  --column-family e

hbase(main):048:0> scan 'heroes1'
ROW           COLUMN+CELL                         
 101          column=e:dno, timestamp=146716789297
              1, value=11                         
 101          column=e:name, timestamp=14671678929
              71, value=aaa                       
 101          column=e:sal, timestamp=146716789297
              1, value=10000                      
 101          column=e:sex, timestamp=146716789297
              1, value=m                          
 102          column=e:dno, timestamp=146716789311
              0, value=12                         
 102          column=e:name, timestamp=14671678931
              10, value=baaa                      
 102          column=e:sal, timestamp=146716789311
              0, value=20000                      
 102          column=e:sex, timestamp=146716789311
              0, value=m                          
 103          column=e:dno, timestamp=146716789419
              1, value=12                         
 103          column=e:name, timestamp=14671678941
              91, value=baaav                     
 103          column=e:sal, timestamp=146716789419
              1, value=30000                      
 103          column=e:sex, timestamp=146716789419
              1, value=f                          
 104          column=e:dno, timestamp=146716789419
              1, value=11                         
 104          column=e:name, timestamp=14671678941
              91, value=xaaav                     
 104          column=e:sal, timestamp=146716789419
              1, value=50000                      
 104          column=e:sex, timestamp=146716789419
              1, value=f                          
4 row(s) in 0.0360 seconds

hbase(main):049:0> 
-----

if target table (hbase) not existed.

[training@localhost ~]$ sqoop import  --connect jdbc:mysql://localhost/sqdb  --username root  --table emp   --hbase-table heroes2  --column-family e   --hbase-create-table

----------------------------

if table does not have primary key.



mysql> create table emp2(id int, name char(10),
    ->   sal int, sex char(1), dno int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into emp2 select * from emp;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from emp2;
+------+-------+-------+------+------+
| id   | name  | sal   | sex  | dno  |
+------+-------+-------+------+------+
|  101 | aaa   | 10000 | m    |   11 | 
|  102 | baaa  | 20000 | m    |   12 | 
|  103 | baaav | 30000 | f    |   12 | 
|  104 | xaaav | 50000 | f    |   11 | 
+------+-------+-------+------+------+
4 rows in set (0.00 sec)

mysql> insert into emp2 values(101,'xxxx',100000,'m',13);
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp2;
+------+-------+--------+------+------+
| id   | name  | sal    | sex  | dno  |
+------+-------+--------+------+------+
|  101 | aaa   |  10000 | m    |   11 | 
|  102 | baaa  |  20000 | m    |   12 | 
|  103 | baaav |  30000 | f    |   12 | 
|  104 | xaaav |  50000 | f    |   11 | 
|  101 | xxxx  | 100000 | m    |   13 | 
+------+-------+--------+------+------+
5 rows in set (0.00 sec)

mysql> 

[training@localhost ~]$ sqoop import  --connect jdbc:mysql://localhost/sqdb  --username root  --table emp2 -m 1  --hbase-table heroines --hbase-row-key id   --column-family e   --hbase-create-table

_____________________________

mysql> select * from emp;
+-----+-------+-------+------+------+
| id  | name  | sal   | sex  | dno  |
+-----+-------+-------+------+------+
| 101 | aaa   | 10000 | m    |   11 | 
| 102 | baaa  | 20000 | m    |   12 | 
| 103 | baaav | 30000 | f    |   12 | 
| 104 | xaaav | 50000 | f    |   11 | 
+-----+-------+-------+------+------+
4 rows in set (0.00 sec)

mysql> create table dept(dno int, name char(10),
    ->   loc char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into dept values(11,'mrkt','hyd')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into dept values(12,'hr','del')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dept;
+------+------+------+
| dno  | name | loc  |
+------+------+------+
|   11 | mrkt | hyd  | 
|   12 | hr   | del  | 
+------+------+------+
2 rows in set (0.00 sec)

mysql> create table ed(id int primary key, 
    ->   name char(10),
    ->  sal int,
    ->   sex char(1),
    ->   dname char(10),
    ->  loc char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> 

mysql> insert into ed 
    ->  select id, l.name, sal, sex, r.name,loc    ->   from emp l join dept r
    ->  where (l.dno=r.dno);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from ed;
+-----+-------+-------+------+-------+------+
| id  | name  | sal   | sex  | dname | loc  |
+-----+-------+-------+------+-------+------+
| 101 | aaa   | 10000 | m    | mrkt  | hyd  | 
| 102 | baaa  | 20000 | m    | hr    | del  | 
| 103 | baaav | 30000 | f    | hr    | del  | 
| 104 | xaaav | 50000 | f    | mrkt  | hyd  | 
+-----+-------+-------+------+-------+------+
4 rows in set (0.00 sec)

mysql> 


mysql> select * from ed;
+-----+-------+-------+------+-------+------+
| id  | name  | sal   | sex  | dname | loc  |
+-----+-------+-------+------+-------+------+
| 101 | aaa   | 10000 | m    | mrkt  | hyd  | 
| 102 | baaa  | 20000 | m    | hr    | del  | 
| 103 | baaav | 30000 | f    | hr    | del  | 
| 104 | xaaav | 50000 | f    | mrkt  | hyd  | 
+-----+-------+-------+------+-------+------+
4 rows in set (0.00 sec)

mysql> 

[training@localhost ~]$ sqoop import  --connect jdbc:mysql://localhost/sqdb  --username root  --table ed  --columns name,sal,sex,id --hbase-table heroines2   --column-family e   --hbase-create-table



[training@localhost ~]$ sqoop import  --connect jdbc:mysql://localhost/sqdb  --username root  --table ed  --columns dname,loc,id  --hbase-table heroines2   --column-family d  --hbase-create-table
______________________________

HBase Lab1 : Hbase Shell Commands

Hbase Shell commands:
_____________________

 hbase(main):002:0> list
TABLE                                                     
Hbemp                                                     
Hemp                                                      
hnemp                                                     
htable                                                    
htable1                                                   
raja                                                      
sample                                                    
sample1                                                   
soon                                                      
stab                                                      
test                                                      
test1                                                     
12 row(s) in 0.0320 seconds

hbase(main):003:0> 

hbase(main):003:0> list 'htable'
TABLE                                             
htable                                            
htable1                                           
2 row(s) in 0.0200 seconds

hbase(main):004:0> 

hbase(main):005:0> create  'mytab','cf'
0 row(s) in 1.0690 seconds

hbase(main):006:0> list 'mytab' 
TABLE                                             
mytab                                             
1 row(s) in 0.0580 seconds

hbase(main):007:0> create 'urtab','f1','f2'
0 row(s) in 1.4660 seconds

hbase(main):008:0> list 'urtab'            
TABLE                                             
urtab                                             
1 row(s) in 0.0640 seconds

hbase(main):009:0> 

hbase(main):002:0> describe 'urtab'
DESCRIPTION                       ENABLED         
 {NAME => 'urtab', FAMILIES => [{ true            
 NAME => 'f1', BLOOMFILTER => 'NO                 
 NE', REPLICATION_SCOPE => '0', C                 
 OMPRESSION => 'NONE', VERSIONS =                 
 > '3', TTL => '2147483647', BLOC                 
 KSIZE => '65536', IN_MEMORY => '                 
 false', BLOCKCACHE => 'true'}, {                 
 NAME => 'f2', BLOOMFILTER => 'NO                 
 NE', REPLICATION_SCOPE => '0', C                 
 OMPRESSION => 'NONE', VERSIONS =                 
 > '3', TTL => '2147483647', BLOC                 
 KSIZE => '65536', IN_MEMORY => '                 
 false', BLOCKCACHE => 'true'}]}                  
1 row(s) in 0.0960 seconds

hbase(main):003:0> 


hbase(main):002:0> put 'urtab','r1','f1:a',100 
0 row(s) in 0.0950 seconds

hbase(main):003:0> put 'urtab','r1','f1:b',100
0 row(s) in 0.0100 seconds

hbase(main):004:0> put 'urtab','r1','f2:b',200
0 row(s) in 0.0130 seconds

hbase(main):005:0> put 'urtab','r1','f2:c',300
0 row(s) in 0.0150 seconds

hbase(main):006:0> put 'urtab','r1','f2:d',500
0 row(s) in 0.0050 seconds

hbase(main):007:0> 

hbase(main):008:0> put 'urtab','r2','f1:a',5000
0 row(s) in 0.0100 seconds

hbase(main):009:0> put 'urtab','r2','f1:x',6000
0 row(s) in 0.0130 seconds

hbase(main):010:0> put 'urtab','r2','f2:b',7000
0 row(s) in 0.0100 seconds

hbase(main):011:0> put 'urtab','r2','f2:y',8000
0 row(s) in 0.0110 seconds

hbase(main):012:0> 

hbase(main):014:0> scan 'urtab',{COLUMNS=>'f1:a'}
ROW           COLUMN+CELL                         
 r1           column=f1:a, timestamp=1467165821966
              , value=100                         
 r2           column=f1:a, timestamp=1467165997467
              , value=5000                        
2 row(s) in 0.0240 seconds

hbase(main):015:0> 

hbase(main):022:0> scan 'urtab',{COLUMNS=>['f1:a','f2:y']}
ROW           COLUMN+CELL                         
 r1           column=f1:a, timestamp=1467165821966
              , value=100                         
 r2           column=f1:a, timestamp=1467165997467
              , value=5000                        
 r2           column=f2:y, timestamp=1467166038766
              , value=8000                        
2 row(s) in 0.0380 seconds

hbase(main):023:0> 

hbase(main):023:0> scan 'urtab',{COLUMNS=>'f1:'}
ROW           COLUMN+CELL                         
 r1           column=f1:a, timestamp=1467165821966
              , value=100                         
 r1           column=f1:b, timestamp=1467165828140
              , value=100                         
 r2           column=f1:a, timestamp=1467165997467
              , value=5000                        
 r2           column=f1:x, timestamp=1467166021078
              , value=6000                        
2 row(s) in 0.0250 seconds

hbase(main):024:0> 


hbase(main):024:0> get 'urtab','r2'
COLUMN        CELL                                
 f1:a         timestamp=1467165997467, value=5000 
 f1:x         timestamp=1467166021078, value=6000 
 f2:b         timestamp=1467166032165, value=7000 
 f2:y         timestamp=1467166038766, value=8000 
4 row(s) in 0.0230 seconds

hbase(main):025:0> get 'urtab','r1'
COLUMN        CELL                                
 f1:a         timestamp=1467165821966, value=100  
 f1:b         timestamp=1467165828140, value=100  
 f2:b         timestamp=1467165844182, value=200  
 f2:c         timestamp=1467165878424, value=300  
 f2:d         timestamp=1467165900629, value=500  
5 row(s) in 0.0150 seconds

hbase(main):026:0> 

hbase(main):026:0> get 'urtab','r1','f2:y'
COLUMN        CELL                                
0 row(s) in 0.0110 seconds

hbase(main):027:0> get 'urtab','r2','f2:y'
COLUMN        CELL                                
 f2:y         timestamp=1467166038766, value=8000 
1 row(s) in 0.0110 seconds

hbase(main):028:0> get 'urtab','r2','f2:' 
COLUMN        CELL                                
 f2:b         timestamp=1467166032165, value=7000 
 f2:y         timestamp=1467166038766, value=8000 
2 row(s) in 0.0200 seconds

hbase(main):029:0> 

hbase(main):029:0> get 'urtab','r2',['f1:a','f2:b']
COLUMN        CELL                                
 f1:a         timestamp=1467165997467, value=5000 
 f2:b         timestamp=1467166032165, value=7000 
2 row(s) in 0.0100 seconds

hbase(main):030:0> 

______________________________

hbase(main):031:0> put 'urtab','r2','f1:x',10000
0 row(s) in 0.0090 seconds

hbase(main):032:0> get 'urtab','r2'
COLUMN        CELL                                
 f1:a         timestamp=1467165997467, value=5000 
 f1:x         timestamp=1467166686843, value=10000
 f2:b         timestamp=1467166032165, value=7000 
 f2:y         timestamp=1467166038766, value=8000 
4 row(s) in 0.0140 seconds

hbase(main):033:0> 

_________________________________

hbase(main):044:0> delete  'urtab','r2','f1:x'
0 row(s) in 0.0110 seconds

note:
  to delete a column family, all columns of the family should be deleted.

 to delete a row, all columns families of the row should be deleted.

__________________________________

Hive Lab10 : json data processing using hive

Json Data Processing Using Hive.

[training@localhost ~]$ cat json1
{"name":"Sankar","age":25,"sex":"m"}
{"name":"Sankari","age":24,"sex":"f","city":"hyd"}
[training@localhost ~]$ 

hive> create database jsonlab;
OK
Time taken: 1.694 seconds
hive> use jsonlab;
OK
Time taken: 0.018 seconds
hive> create table raw(line string);
OK
Time taken: 0.219 seconds
hive> load data local inpath 'json1' into table raw;
hive> select * from raw;
OK
{"name":"Sankar","age":25,"sex":"m"}
{"name":"Sankari","age":24,"sex":"f","city":"hyd"}
Time taken: 0.146 seconds
hive> 
hive> select get_json_object(line,'$.name') from raw;
OK
Sankar
Sankari
hive> select                
    >   get_json_object(line,'$.name') ,
    >   get_json_object(line,'$.age') , 
    >   get_json_object(line,'$.sex') ,
    >   get_json_object(line,'$.city')  
    > from raw;
OK
Sankar  25      m       NULL
Sankari 24      f       hyd

hive> create table info(name string, age int,
    >   sex string, city string);
OK
Time taken: 0.04 seconds
hive> insert overwrite table info
    >   select x.* from raw
    >    lateral view json_tuple

(line,'name','age','sex','city') x
    >   as n,a,s,c;

hive> select * from info;                                       

  
OK
Sankar  25      m       NULL
Sankari 24      f       hyd
Time taken: 0.063 seconds
hive> 

[training@localhost ~]$ cat json2
{"name":"ravi","age":25,"wife":

{"name":"banu","age":24},"city":"hyd"}
{"name":"kavi","age":25,"wife":

{"name":"kavitha","age":21},"city":"del"}
[training@localhost ~]$ 

hive> create table jraw(line string);
OK
Time taken: 0.048 seconds
hive> load data local inpath 'json2' into table jraw;
Copying data from file:/home/training/json2
Copying file: file:/home/training/json2
Loading data to table jsonlab.jraw
OK
Time taken: 0.127 seconds
hive> select * from jraw;
OK
{"name":"ravi","age":25,"wife":

{"name":"banu","age":24},"city":"hyd"}
{"name":"kavi","age":25,"wife":

{"name":"kavitha","age":21},"city":"del"}
Time taken: 0.06 seconds
hive> 
hive> create table raw2(name string, age int, wife string, city 

string);
OK
Time taken: 0.033 seconds
hive> insert overwrite table raw2
    >   select x.* from jraw
    >  lateral view 
    >  json_tuple(line,'name','age','wife','city') x as 

n,a,w,c;

hive> select * from raw2;
OK
ravi    25      {"age":24,"name":"banu"}        hyd
kavi    25      {"age":21,"name":"kavitha"}     del
Time taken: 0.061 seconds
hive> create table jinfo(hname string, wname string, 
    >    hage int, wage int, city string);

hive> insert overwrite table jinfo
    >   select name, get_json_object(wife,'$.name'),
    >   age , get_json_object(wife,'$.age'),
    >  city from raw2;

hive> select * from jinfo;
OK
ravi    banu    25      24      hyd
kavi    kavitha 25      21      del
Time taken: 0.079 seconds
hive> 

Hive Lab9: Url Data Processing

$ cat  urls
http://yoursite.com/bigdata/mongodb?

name=Raj&age=25&city=hyd
http://yoursite.com/bigdata/spark?

name=Swapna&city=del&desig=se
http://yoursite.com/bigdata/spark?

name=Satwik&age=26&city=hyd
http://yoursite.com/oops/java?

name=Manoj&sex=m&age=35

Url contains , 3 parts of information.

 i) Host name:  

    yoursite.com

ii) Path:

   ex:    bigdata/mongodb

iii) Query string.

   name=Manoj&sex=m&age=35

Hive hase predefined parsers , 
  to extract basic entities of url.

 i) parse_url() ---> udf
 ii) parse_url_tuple() ---> udtf.


hive> create database urls;
OK
Time taken: 1.727 seconds
hive> use urls;
OK
Time taken: 0.019 seconds
hive> create table raw(line string);
OK
Time taken: 0.349 seconds
hive> load data local inpath 'urls'
    >  into table raw;
Copying data from 

file:/home/training/urls
Copying file: file:/home/training/urls
Loading data to table urls.raw
OK
Time taken: 0.2 seconds
hive> 

hive> select * from raw;
OK
http://yoursite.com/bigdata/mongodb?

name=Raj&age=25&city=hyd
http://yoursite.com/bigdata/spark?

name=Swapna&city=del&desig=se
http://yoursite.com/bigdata/spark?

name=Satwik&age=26&city=hyd
http://yoursite.com/oops/java?

name=Manoj&sex=m&age=35
Time taken: 0.064 seconds
hive> 

hive> select 
    >     parse_url(line,'HOST'),
    >     parse_url(line,'PATH'),
    >     parse_url(line,'QUERY')
    > from raw;

hive> create table raw2(host string, 
    >  path string, 
    >  query string);
OK
Time taken: 0.039 seconds
hive> insert overwrite table raw2
    >   select x.* from raw
    >  lateral view
    > parse_url_tuple

(line,'HOST','PATH','QUERY')
    >  x as h,p,q;

hive> select * from raw2;
OK
yoursite.com    /bigdata/mongodb       

 name=Raj&age=25&city=hyd
yoursite.com    /bigdata/spark  

name=Swapna&city=del&desig=se
yoursite.com    /bigdata/spark  

name=Satwik&age=26&city=hyd
yoursite.com    /oops/java      

name=Manoj&sex=m&age=35
Time taken: 0.063 seconds
hive> 




hive> create table raw3(host string, 
    >  path array<string>,
    >  qmap map<string,string>);



hive> insert overwrite table raw3
    >  select host, 
    >   split(path,'/'),
    >  str_to_map(query,'&','=')
    > from raw2;

hive> select * from raw3;

hive> select * from raw3;
OK
yoursite.com    

["","bigdata","mongodb"]        

{"age":"25","name":"Raj","city":"hyd"}
yoursite.com    ["","bigdata","spark"] 



{"desig":"se","name":"Swapna","city":"

del"}
yoursite.com    ["","bigdata","spark"] 



{"age":"26","name":"Satwik","city":"hy

d"}
yoursite.com    ["","oops","java"]     

 {"sex":"m","age":"35","name":"Manoj"}
Time taken: 0.064 seconds
hive> 



hive> create table info(host string,  
    >  catogiry string,  course 

string,
    >  name string, age int, 
    >  sex string, city string, desig 

string)
    > row format delimited        
    >   fields terminated by ',';
OK
Time taken: 0.038 seconds
hive> insert overwrite table info
    >   select host, path[1], path[2],
    >  qmap['name'], qmap['age'],
    >  qmap['sex'], qmap['city'],
    >  qmap['desig'] from  raw3;

hive> select * from info;
OK
yoursite.com    bigdata mongodb Raj    

 25      NULL      hyd     NULL
yoursite.com    bigdata spark   Swapna 

 NULL    NULL      del     se
yoursite.com    bigdata spark   Satwik 

 26      NULL      hyd     NULL
yoursite.com    oops    java    Manoj  

 35      mNULL     NULL
Time taken: 0.055 seconds

___________________________________

to extract all keys of a map 

collection/

hive> select  map_keys(qmap)      from 

raw3;

["age","name","city"]
["desig","name","city"]
["age","name","city"]
["sex","age","name"]

hive> select  map_values(qmap)      

from raw3;


["25","Raj","hyd"]
["se","Swapna","del"]
["26","Satwik","hyd"]
["m","35","Manoj"]

____________________________


hive> create table raw4(host string, 
    >   k   array<string>,
    >  v array<string>);
OK
Time taken: 0.037 seconds
hive> insert overwrite table raw4
    >   select host, 
    >     map_keys(qmap), map_values

(qmap) 
    >   from raw3;


hive> select * from raw4;
OK
yoursite.com    ["age","name","city"]  

 ["25","Raj","hyd"]
yoursite.com    

["desig","name","city"] 

["se","Swapna","del"]
yoursite.com    ["age","name","city"]  

 ["26","Satwik","hyd"]
yoursite.com    ["sex","age","name"]   

 ["m","35","Manoj"]
Time taken: 0.061 seconds
hive> 


__________________________________