 Hive

Hadoop and Hive Interview Cheat Sheet 1

This entry was posted in Hadoop Hive on October 10, 2015 by Siva

Hive SQL Based Datawarehouse app built on top of hadoop(select,join,groupby…..) It is a platform used to develop SQL type scripts to do MapReduce operations. PARTITIONING Partition tables changes how HIVE structures the data storage *Used for distributing load horizantally ex: PARTITIONED BY (country STRING, state STRING); A subset of a table’s data set where one column has the same value for all records in the subset. In Hive, as in most databases […]

String Functions in Hive 3

This entry was posted in Hive on July 17, 2015 by Siva

This post is about basic String Functions in Hive with syntax and examples. Creating Table in HIVE:

MySQL
hive> create external table Tri100(id int,name string,location varchar(30),sal int,Hike int)
> row format delimited
> fields terminated by ','
> lines terminated by '\n'
> stored as textfile location '/Jayanth/Jay/';

				1
2
3
4
5
6

						 
hive> create external table Tri100(id int,name string,location varchar(30),sal int,Hike int)
> row format delimited
> fields terminated by ','
> lines terminated by '\n'
> stored as textfile location '/Jayanth/Jay/';

					

String Functions and Normal Queries:

MySQL
hive> select * from Tri100;
1       rahul   Hyderabad       30000     40000
2       Mohit   Banglore        22000     25000
3       Rohan   Banglore        33000     40000
4       Ajay    Bangladesh      40000     45000
5       srujay  Srilanka        25000     30000

				1
2
3
4
5
6
7

						 
hive> select * from Tri100;
1       rahul   Hyderabad       30000     40000
2       Mohit   Banglore        22000     25000
3       Rohan   Banglore        33000     40000
4       Ajay    Bangladesh      40000     45000
5       srujay  Srilanka        25000     30000

					

ASCII ASCII Function converts the first character of the string into its numeric ASCII value.

MySQL
hive> select ASCII(‘hadoop’) from Tri100 where sal=22000;
104
hive> select ASCII(‘Hadoop’) from Tri100 where sal=22000;
72
hive> select ASCII(‘A’) from Tri100 where sal=22000;
65

				1
2
3
4
5
6
7

						 
hive> select ASCII(‘hadoop’) from Tri100 where sal=22000;
104
hive> select ASCII(‘Hadoop’) from Tri100 where sal=22000;
72
hive> select ASCII(‘A’) from Tri100 where sal=22000;
65

					

CONCAT The CONCAT function concatenates all the strings/columns.

MySQL
hive> select CONCAT(name,'+',location) from Tri100;
rahul+Hyderabad
Mohit+Banglore
Rohan+Banglore
Ajay+Bangladesh
srujay+Srilanka

				1
2
3
4
5
6
7

						 
hive> select CONCAT(name,'+',location) from Tri100;
rahul+Hyderabad
Mohit+Banglore
Rohan+Banglore
Ajay+Bangladesh
srujay+Srilanka

					

MySQL
hive> select concat(name,'+',sal) from Tri100;
rahul+30000
Mohit+22000
Rohan+33000
Ajay+40000
srujay+25000

				1
2
3
4
5
6
7

						 
hive> select concat(name,'+',sal) from Tri100;
rahul+30000
Mohit+22000
Rohan+33000
Ajay+40000
srujay+25000

					

MySQL
hive> select CONCAT(name,' ','Got Hike of',' ',Hike,' ','from',' ',sal) from Tri100;
rahul Got Hike of 40000 from 30000
Mohit Got Hike of 25000 from 22000
Rohan Got Hike of 40000 from 33000
Ajay Got Hike of 45000 from 40000
srujay Got Hike of 30000 from 25000

				1
2
3
4
5
6
7

						 
hive> select CONCAT(name,' ','Got Hike of',' ',Hike,' ','from',' ',sal) from Tri100;
rahul Got Hike of 40000 from 30000
Mohit Got Hike of 25000 from 22000
Rohan Got Hike of 40000 from 33000
Ajay Got Hike of 45000 from 40000
srujay Got Hike of 30000 from 25000

					

CONCAT_WS Syntax: “CONCAT_WS(string delimiter, string str1,str2……)" The CONCAT_WS function concatenates all the strings only strings and Column with datatype string.

MySQL
hive> select CONCAT_WS('+',name,location) from Tri100;
rahul+Hyderabad
Mohit+Banglore
Rohan+Banglore
Ajay+Bangladesh
srujay+Srilanka

				1
2
3
4
5
6
7

						 
hive> select CONCAT_WS('+',name,location) from Tri100;
rahul+Hyderabad
Mohit+Banglore
Rohan+Banglore
Ajay+Bangladesh
srujay+Srilanka

					

[…]

Hive Aggregate Functions 1

This entry was posted in Hive on June 24, 2015 by Siva

Creating Table in HIVE :

MySQL
hive> create external table Tri100(id int,name string,location varchar(30),sal int,Hike int)
       > row format delimited
       > fields terminated by ','
       > lines terminated by '\n'
       > stored as textfile location '/Jayanth/Jay/';

				1
2
3
4
5
6

						 
hive> create external table Tri100(id int,name string,location varchar(30),sal int,Hike int)
       > row format delimited
       > fields terminated by 