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 […]
This post is about basic String Functions in Hive with syntax and examples. Creating Table in HIVE:
|
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:
|
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.
|
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.
|
1 2 3 4 5 6 7 |
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,'+',sal) from Tri100; rahul+30000 Mohit+22000 Rohan+33000 Ajay+40000 srujay+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.
|
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 |
[…]
Creating Table in HIVE :
|
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/'; |
Aggregated Functions and Normal Queries: