Apache Hive Built-In Functions | Hive Functions

1. Objective

Basically, to perform several operations there are some functions available. Similarly, in Hive also there are some built-in functions available. Such as Hive Collection Functions, Hive Date Functions, Hive Mathematical Functions, Hive Conditional Functions and Hive String Functions. So, this blog contains the whole concept of Hive Built-In Functions. At first, we will understand what are Hive functions actually mean. Afterward, we will learn the whole concept of Hive Built-in Functions. Also, we will cover the description of each function in detail.

Types of Hive Built-in functions

Types of Hive Built-in functions

2. Hive Functions

So, to perform several operations there are some functions built for a specific purpose. Such as Mathematical, arithmetic, logical and relational on the operands of table column names.
Let’s study Apache Hive Architecture & Components in detail

Hadoop Quiz
If these professionals can make a switch to Big Data, so can you:
Rahul Doddamani Story - DataFlair
Rahul Doddamani
Java → Big Data Consultant, JDA
Follow on
Mritunjay Singh Success Story - DataFlair
Mritunjay Singh
PeopleSoft → Big Data Architect, Hexaware
Follow on
Rahul Doddamani Success Story - DataFlair
Rahul Doddamani
Big Data Consultant, JDA
Follow on
I got placed, scored 100% hike, and transformed my career with DataFlair
Enroll now
Deepika Khadri Success Story - DataFlair
Deepika Khadri
SQL → Big Data Engineer, IBM
Follow on
DataFlair Web Services
You could be next!
Enroll now

3. Introduction to Hive Built-In Functions

In Hive, there are some built-in functions available. Basically, to use Hive built-in functions in our applications first we need to check the application requirement. However, it is possible to call these functions directly in our application.
In addition, there are several types of Hive Built-in Functions available.

Now, let’s describe Hive Built-in Functions in detail:

a. Collection Functions

Basically, as par its name we use “Collection Functions” for collections. Here, collections are nothing but defined as a grouping of elements and returning single or array of elements depends on return type mentioned in the function name. Let’s discuss each Collection Functions- Hive Built-in functions in below table:

Return TypeFunction NameDescription
INTsize(Map<K.V>)It will fetch and give the components number in the map type
INTsize(Array<T>)It will fetch and give the elements number in the array type
Array<K>Map_keys(Map<K.V>)It will fetch and gives an array containing the keys of the input map. Here array is in unordered
Array<V>Map_values(Map<K.V>)It will fetch and gives an array containing the values of the input map. Here array is in unordered
Array<t>Sort_array(Array<T>)sorts the input array in ascending order of array and elements and returns it

Let’s learn Impala vs Hive – Difference Between Hive and Impala

b. Hive Date Functions

However, to perform date manipulations and conversion of date types from one type to another type we use Hive date Functions. Let’s discuss each Date Function – Hive Built-in Functions in below table:

Function NameReturn TypeDescription
Unix_Timestamp()Unix_Timestamp()We will get current Unix timestamp in seconds
To_date(string timestamp)stringIt will fetch and give the date part of a timestamp string:
year(string date)INTIt will fetch and give the year part of a date or a timestamp string
quarter(date/timestamp/string)INTIt will fetch and give the quarter of the year for a date, timestamp, or string in the range 1 to 4
month(string date)INTIt will give the month part of a date or a timestamp string
hour(string date)INTIt will fetch and gives the hour of the timestamp
minute(string date)INTIt will fetch and gives the minute of the timestamp
Date_sub(string starting date, int days)stringIt will fetch and gives Subtraction of number of days to starting date
Current_datedateIt will fetch and gives the current date at the start of query evaluation
LAST _day(string date)stringIt will fetch and gives the last day of the month which the date belongs to
trunc(string date, string format)stringIt will fetch and gives date truncated to the unit specified by the format.
Supported formats in this :
MONTH/MON/MM, YEAR/YYYY/YY.

Read about Hive Partitions-Types of Hive Partitioning with Examples

c. Mathematical Functions

For Mathematical Operations in Hive, we use “Mathematical Functions”. Though, we have some inbuilt mathematical functions in Hive despite creating UDFs. Let’s discuss each Mathematical Function- Hive Built-in Functions in below table:

Function NameReturn TypeDescription
round(DOUBLE X)DOUBLEIt will fetch and returns the rounded BIGINT value of X
round(DOUBLE X, INT d)DOUBLEIt will fetch and returns X rounded to d decimal places
bound(DOUBLE X)DOUBLEIt will fetch and returns the rounded BIGINT value of X using HALF_EVEN rounding mode
floor(DOUBLE X)BIGINTIt will fetch and returns the maximum BIGINT value that is equal to or less than X value
ceil(DOUBLE a), ceiling(DOUBLE a)BIGINTIt will fetch and returns the minimum BIGINT value that is equal to or greater than X value
rand(), rand(INT seed)DOUBLEIt will fetch and returns a random number that is distributed uniformly from 0 to 1

Let’s look at HBase vs Hive – Difference between Hive vs HBase in detail

d. Conditional Functions

While it comes to conditional values checks in Hive, we use “Conditional Functions”. Let’s discuss each Conditional Function- Hive Built-In Functions in below table:

Function NameReturn TypeDescription
if(Boolean testCondition, T valueTrue, T valueFalseOrNull)TIt will fetch and gives value True when Test Condition is of true, gives value False Or Null otherwise.
ISNULL( X)BooleanIt will fetch and gives true if X is NULL and false otherwise.
ISNOTNULL(X )BooleanIt will fetch and gives true if X is not NULL and false otherwise.

Let’s learn about what is Hive Metastore in detail

e. Hive String Functions

For String manipulations and string operations in Hive, we call Hive String Functions. Let’s discuss each String Function- Hive Built-In Functions in below table:

Function NameReturn TypeDescription
reverse(string X)stringIt will give the reversed string of X
rpad(string str, int length, string pad)stringIt will fetch and gives str, which is right-padded with the pad to a length of length(integer value)
rtrim(string X)stringIt will fetch and returns the string resulting from trimming spaces from the end (right-hand side) of X For example, rtrim(‘ results ‘) results in ‘ results’
space(INT n)stringIt will fetch and gives a string of n spaces.
split(STRING str, STRING pat)arraySplits str around pat (pat is a regular expression).
Str_to_map(text[, delimiter1, delimiter2])map<String ,String>It will split text into key-value pairs using two delimiters.

4. Conclusion

As a result, in this blog, we have learned all the possible Hive Built-in Functions. Also, we have described types of Hive Built-In Functions, Hive Date Functions, Hive Mathematical Functions, Hive Conditional Functions and Hive String Functions in detail along with their return type. Still, if any doubt occurs regarding Hive Built-in Functions, feel free to ask in the comment section.
See Also- Difference between Pig and Hive
For reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.