Hive-Bucketing

Note:For emp dataset please refer to previous posts.

Bucketing
---------
A bucket is a file where the keys are mainatained in a Hashtable
in the form of a directory.

In bucketing all similar keys are placed into same bucket also we choose how many buckets we need based on available records so that we can keep the size of the buckets  in control.

Bucket is nothing but a kind of partitioning.

To implement bucketing in hive we should set  a property

hive>set hive.enforce.bucketing=true;

How to create a bucket table
----------------------------
we create a bucketed table by using command clustered by also we should specify how many buckets we should create.

syntax
------
create table tablename(col1 type,col2 type,...)
clustered by(col1,...)
into n buckets

n-->no of buckets.

example
-------
hive> create table emp_buckets(eno int,name string,sal int,gender string,dno int)
    > clustered by(dno)
    > into 3 buckets;

inserting data into bucketed table
----------------------------------
hive> insert overwrite table emp_buckets
      select *from empdata;

hive>select *from emp_buckets;
In hdfs 3 bucket files are created
 /user/hive/warehouse/emp_buckets/000000_0
                                  000001_0
                                  000002_0

creating hive table with multiple columns
------------------------------------------
hive> create table emp_buckets12(eno string,name string,sal double,gender string,dno int)
    > clustered by(dno,gender)
    > into 4 buckets;

hive>insert overwrite table emp_buckets12
     select *from empdata;

In hdfs,4 data files are created
/user/hive/warehouse/emp_buckets12/000000_0
                                   000001_0
                                   000002_0
                                   000003_0

hive>select *from emp_bucktes12;

creating table combining partition and bucketing
------------------------------------------------
hive> create table emp_part_buckets(eno int,name string,sal double,dno int)
    > partitioned by(gender string)
    > clustered by(dno)
    > into 3 buckets
    > row format delimited fields terminated by ',';


loading data file partition buckets
hive> insert overwrite table emp_part_buckets
    > partition(gender)
    > select eno,name,sal,dno,gender from empdata;

In hdfs
  /user/hive/warehouse/emp_part_buckets/gender=m/000000_0
                                                 000001_0
                                                 000002_0
                                                 

  /user/hive/warehouse/emp_part_buckets/gender=f/000000_0
                                                 000001_0
                                                 000002_0
                                               
         
hive>select *from emp_part_buckets

Hive-Partition_tables

Partition table
---------------
Dividing a table with large datasets into parts called as partitions according to a column(s) of a table is known as partition tables.

Partition tables reduces the search time of a query in a table with large datasets according to a partition insted of searching all the records of a table.

Partitions are logical division of a table.

creating a partition table
--------------------------
We can create a partition table by using the commmad
"partitioned by" along with a create statement.

syntax
------
create table tablename(col1 type,col2 type,.....)
partitioned by(col1 type,col2,....)
row format delimited fields terminated by ',';

ex:
hive> create table emp_part(eno int,name string,sal double,gender string,dno int)
    > partitioned by(g string)
    > row format delimited fields terminated by ',';

describe emp_part
-----------------
eno                 int                                  
name                 string                                
sal                 double                                
gender               string                                
dno                 int                                  
g                   string                                

# Partition Information
# col_name             data_type           comment            

g                   string

In the above the firts part is physical partition and second part is logical partition.

Here col names of table and partition colname must not be same.

creating a non partition table
------------------------------
hive> create table empdata(eno int,name string,sal double,gender string,dno int)
   > row format delimited fields terminated by ',';

cat > emp
305,akanksha,80000,f,15
101,mano,26000,m,11
102,sajay,25000,f,11
103,naveen,13000,m,13
104,chakrismart,8000,f,12
105,ravi,6000,m,13
101,hari,10000,f,12
201,aaa,30000,m,12
202,bbbb,50000,f,13
203,ccc,10000,f,13
204,ddddd,50000,m,13
304,xxx,70000,m,14


hive>load data local inpath 'emp' into table empdata;

Loading data into a partition table from a non partition table
--------------------------------------------------------------
syntax
------
insert overwrite table tablename
partition(col1=val,col2=val)
select query ....;


hive> insert overwrite table emp_partition
    > partition(g='f')
    > select *from  from empnp where gender='f';


hive> insert overwrite table emp_partition
    > partition(g='m')
    > select *from empnp where gender='m';


Here emp_partition is created as directory
Every partition is created as a sub directory in emp_partition
Inside partition a file is created as 000000_0.

In hdfs
-------
/user/hive/warehouse/emp_partition/g=f/000000_0-->female partitiom
All records of female are available in this.

/user/hive/warehouse/emp_partition/g=m/000000_0-->male partitiom
All records of male are available in this.


selecting records from partition
--------------------------------
hive>select *from emp_partition;
displays all records

hive>select *from emp_partition where g='m';
displays all records from male partition.


hive>select *from emp_partition where g='f';
displays all records from female partition.

Multiple partition in hive tables
---------------------------------
create a table with partitions using multiple cols dno,gender

hive> create table emp_mpart(eno int,name string,sal double)
    > partitioned by(dno int,gender string)
    > row format delimited fields terminated by ',';

load data into partitions from a non partition table
----------------------------------------------------
hive> insert overwrite table emp_mpart
    > partition(dno=11,gender='m')
    > select eno,name,sal from empdata where dno=11 and gender='m';

hive> insert overwrite table emp_mpart
    > partition(dno=11,gender='f')
    > select eno,name,sal from empdata where dno=11 and gender='f';


selecting records
-----------------
hive>select *from emp_mpart;
displays all records

hive>select *from emp_mpart where dno=11;
displays both male and female records from dept 11

hive>select *from emp_mpart where dno=11 and gender='m';
displays all male records from dept 11


hive>desc emp_mpart
eno                 int                                  
name                 string                                
sal                 double                                
dno                 int                                  
gender               string                                

# Partition Information
# col_name             data_type           comment            

dno                 int                                  
gender               string    

In hdfs partitions are created according to dno and gender

/user/hive/warehouse/emp_mpart/dno=11---->g=m-->000000_0
                                     ---->g=f-->000000_0

Here dno is a subdirectory to table.
g=m,g=f are subdirectory to dno.
000000_0 is data partition file according to gender.


Multiple inserts into partition table
-------------------------------------
we can perform all inserts at a time

hive>from empdata
     insert overwrite table emp_mpart
     partition(dno=11,gender='m')
     select eno,name,sal where dno=11 and gender='m')
     insert overwrite table emp_mpart
     partition(dno=12,gender='m')
     select eno,name,sal where dno=12 and gender='m')
     insert overwrite table emp_mpart
     partition(dno=13,gender='m')
     select eno,name,sal where dno=13 and gender='m')
     insert overwrite table emp_mpart
     partition(dno=14,gender='m')
     select eno,name,sal where dno=14 and gender='m')

dynamic partition
-----------------
suppose if we have 200 depts,here we have create 200 partitions we should load datasets explicitly (manually) dept wise ,to avoid this we go for dynamic partition.

In dynamic partition,partition of table is implicitly done based on a column(s).

To create dynamic partition we should set 2 properties
hive>set hive.exec.dynamic.partition=true;
hive>set hive.exec.dynamic.partition.mode=nonstrict;

create a dynamic table
----------------------
create table emp_dpart(eno int,name string,sal double)
partitioned by(dno int,gender string)
row form ......;

copy data from a non partition table
------------------------------------
insert overwrite table emp_dpart
partition(dno,gender)
select eno,name,sal,dno,gender from empdata;

Here we are inserting values into partion without specifying dno or gender explicitly i,e static representation.


examine data
-----------
hive>select *from emp_dpart;


In hdfs

/user/hive/warehouse/emp_dpart/dno=11-->gender='m'-->000000_0
                                     -->gender='f'-->000000_0

Here dno=11 is subdirectory of emp_dpart
gender='m' and gender='f' is a sub directory of dno=11

similary for each dno partitions are created as shown above.


querying partition dno
querying partition dno and subpartition gender

hive> select *from emp_dpart where depno=11;
OK
101 AAAAA 1000.0 11 m
Time taken: 0.126 seconds, Fetched: 1 row(s)
hive> select *from emp_dpart where depno=11 and gender='m';
OK
101 AAAAA 1000.0 11 m
Time taken: 0.262 seconds, Fetched: 1 row(s)
hive> select *from emp_dpart where depno=11 and gender='f';
OK

Limitations
 -----------
As number of partitions increases,metadata size of namenode increases which becomes a burdon on namenode.

suppose we 100 dno and 100 cities and we are doing partition based on (dno,city) it creates 10000 partitions which eventually increases the size of metadata.

To overcome this we can implement bucketing.

Hive-Merging tables

Merging tables
--------------
union
unionall


union merges 2 tables but doesn't allow duplicates.
union all merges 2 tables, allow duplicates.
union is not suppoted in hive.

while applying unionall we should apply a subquery along with an alias.

create table tab1(line string);
insert into tab1 values('Manohar is Good person');

create table tab2(line string);
insert into tab2 values('Manohar lives for teaching');

create table tab3(line string);
hive> insert overwrite table tab3
    > select * from(
    > select *from tab1
    > union all
    > select *from tab2) tab;





Assignment
----------
Merging 2 tables into a table with same cols
Merging 2 tables into a table with  change in cols order.
Merging 2 tables into a table with  change in table strcuture.

Hive-joins

Joins
-----
A Join is used to retrieve data from 2 or more tables based on a common columns

inner join
----------
Displays all the matching rows between 2 tables based on some criteria.

[cloudera@quickstart ~]$ cat > emp.txt
eno  name  sal    g dno
101,AAAAA,1000.00,m,11
102,BBBBB,2000.00,m,12
103,CCCCC,3000.00,f,13
104,DDDDD,4000.00,f,14
105,EEEEE,5000.00,m,15
106,FFFFF,6000.00,f,16
107,GGGGG,7000.00,m,17
108,HHHHH,8000.00,m,18
109,xxxxx,1000.00,f,11
110,yyyyy,2000.00,f,18
111,zzzzz,2000.00,f,15

create 2 table
empdata
eno
name
sal
gender
dno
tax

dept
dno
dname
dloc

syntax
------
select reference1.col1,refence1.col2,....reference2.col1,... from tablename1 reference1 inner join tablename2 reference2 on condition;

hive> select e.eno,e.name,e.sal,e.dno,d.dname,d.dloc from emp_data e inner join dept d on e.dno=d.dno;

hive>create table dept(dno int,dname string,dloc string);
     row format delimited fields terminated by ',';

hive>insert into dept values(11,'training','hyd');
hive>insert into dept values(13,'IT','hyd');
hive>insert into dept values(14,'Automobile','hyd');
hive>insert into dept values(15,'Finance','hyd');


hive> select *from dept;
OK
cat > dept.txt
11,training,hyd
13,IT,hyd
14,Automobile,hyd
16,Finance,hyd

Task:
Displays employee,dept details matching between both tables.

example:
hive>hive> select eno,name,sal,d.dno,d.name from empdata e join dept d on(e.dno=d.dno);

101 AAAAA 1000.0 11 training
103 CCCCC 3000.0 13 IT
104 DDDDD 4000.0 14 Automobile
106 FFFFF 6000.0 16 Finance



outer join
left outer join
select reference1.col1,refence1.col2,....reference2.col1,... from tablename1 reference1 left join tablename2 reference2 on condition;


hive> select e.eno,e.name,e.sal,d.name,d.dno,d.loc from empdata e left join dept  d on(e.dno=d.dno);


OK
1 aaaa 1000.0 training 11 hyd
2 bbbb 2000.0 NULL NULL NULL
3 cccc 3000.0 finance 13 hyd
4 dddd 4000.0 hr 14 chennai
5 eeee 5000.0 training 11 hyd
6 ffff 2000.0 analyst 15 delhi
7 gggg 3000.0 finance 13 hyd
8 hhhh 4000.0 NULL NULL NULL
1 cccc NULL NULL NULL NULL



right outer join
select reference1.col1,refence1.col2,....reference2.col1,... from tablename1 reference1 right join tablename2 reference2 on condition;

hive> select eno,name,sal,d.dno,dname from empdata e right outer join dept d on(e.dno=d.dno);

full outer join
---------------
select reference1.col1,refence1.col2,....reference2.col1,... from tablename1 reference1 full outer join tablename2 reference2 on condition;


hive> select eno,name,sal,d.dno,dname from empdata e full outer join dept d on(e.dno=d.dno);

cross join
----------
cartesian product
select reference1.col1,refence1.col2,....reference2.col1,... from tablename1 reference1 cross  join tablename2 reference2;


hive> select e.eno,e.name,e.sal,d.name,d.dno,d.loc from empdata e cross join dept  d;

self join
---------
hive> select e.eno,e.name,e.sal from empdata e  join empdata  e1 on(e.eno=e1.eno);


Displays matching rows between two cols of same table.
We can apply both inner as well as outer join on self join.

Hive-swicthcase

[cloudera@quickstart ~]$ cat > emp.txt
eno  name  sal    g dno
101,AAAAA,1000.00,m,11
102,BBBBB,2000.00,m,12
103,CCCCC,3000.00,f,13
104,DDDDD,4000.00,f,14
105,EEEEE,5000.00,m,15
106,FFFFF,6000.00,f,16
107,GGGGG,7000.00,m,17
108,HHHHH,8000.00,m,18
109,xxxxx,1000.00,f,11
110,yyyyy,2000.00,f,18
111,zzzzz,2000.00,f,15

hive>create table empdata (eno int,name string,sal double,gender string,dno int)
        row format delimited fields terminated by '.';

hive>load data local inpath 'emp.txt' into table empdata;

hive> select col1,col2...,case colname
    > when val1 then statement
    > when val2 then statement
    > when val3 then statement
    > else statement
    > end
    > from tablename;




hive> select name,case dno
    > when 11 then 'marketing'
    > when 12 then 'finance'
    > when 13 then 'hr'
    > when 14 then 'abc'
    > when 15 then 'xyz'
    > when 16 then 'training'
    > else 'select Proper choice'
    > end
    > from empdata;


case:

hive> select dno,case dno
    > when 11 then 'maeketing'
    > when 12 then 'hr'
    > when 13 then 'finance'
    > else 'others'
    > end
    > from empdata;

hive> select name,case gender
    > when 'm' then 'male'
    > when 'f' then 'female'
    > else gender
    > end
    > from empdata

Hive-coalesce

coalesce
--------
it returns first non null value from a list of values
it returns null if all values are null.

cat  > data.txt
,1,,12345
rama,3,8790,
vidhatri,4,,
,,,,

syntax
------
select coalesce(col1,col2,.....);


hive> select coalesce(name,a,b,c) from coaldata;
OK
Mano
rama
vidhatri

Hive-cleaningNulls/Handling null

cleaning null values from a hive table
-------------------------------------------
[cloudera@quickstart ~]$ cat nulldata.txt
1,,20
20,30,
,50,60

syntax
-----
null check
---------
colname is null

not null check
---------------
colname is not null


hive>create table nulldata(a int,b int,c int)
     row format delimited fields terminated by ',';

hive>load data  local inpath 'nulldata.txt' into table nulldata;

hive>select *from nulldata;

hive>
insert overwrite table nulldata
select if(a is null,0,a),if(b is null,0,b),if(c is null,0,c) from nulldata;

or

insert overwrite table nulldata
select nvl(a,0),nvl(b,0),nvl(c,0) from nulldata;

Note:
nvl()---It is a method used to check null.

hive> select name from empdata where name like '%A'.

Task:
in gender col 'm' to be transformed to male
and f to be transformed to female.

dno
11-->Marketing
12-->HR
13-->finance

hive> select if(gender='m','male','female'),if(dno=11,'marketing',if(dno=12,'hr',if(dno=13,'finance',dno))) from empdata;