Built-in HIVE Functions
Types of built-in functions in HIVE
There are three types of built-in functions in HIVE :
- UDF (User defined functions):
UDF's are custom defined functions. i.e. We can define a function in java and can use it in HIVE.
- UDAF (User defined Aggregate functions):
UDAF's are the aggregation functions in HIVE. Say there is a 'salary' column in your table and you have to take the sum of all the salaries.In that case sum() function is used.
- UDTF (User defined table generating functions):
UDTF's are special functions in HIVE where a single row can be split into multiple rows. Example of UDTF's are explode().
- explode() :
The explode() method is used to split the data which is stored inside arrays and maps.
Example :
We have seen in the 'complex data type - Map' example how the data was stored.
CREATE TABLE student_location (
city string,
names map <int,string>
)
row format delimited fields terminated by ','
collection items terminated by '*'
map keys terminated by ':';
If we ran a 'select' statement on it the returned rows were:
SELECT * FROM student_location;
| city |
names |
| Hydrabad |
{1:"Vinay",3:"Ajay",5:"Darshil"} |
| Delhi |
{2:"Sahil",4:"Peter"} |
So, Vinay, Ajay and Darshil with roll numbers 1, 3 and 5 belongs to Hydrabad location. Now let us apply the explode() function to split the values.
SELECT explode(names) as (roll,name) FROM student_location;
| roll |
name |
| 1 |
Vinay |
| 3 |
Ajay |
| 5 |
Darshil |
| 2 |
Sahil |
| 4 |
Peter |
So in the above case we have used 'explode(names) as (roll,name)' to split the contents of the map. We have also given suitable name i.e roll and name to the contents of the map.
Note : The limitation of explode() function is that we cannot specify any other columns with explode(). i.e.
SELECT city,explode(names) as (roll,name) FROM student_location;
The above 'SELECT' statement with city and explode(names) is not allowed in HIVE.
However, this limitation of explode() can be fixed using Lateral views.
