EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials Hive Tutorial Hive Built-in Functions

Hive Built-in Functions

Priya Pedamkar
Article byPriya Pedamkar

Updated March 21, 2023

Hive built in function

Introduction to Hive Built-in Functions

Functions are built to perform different analytical requirements and operations like mathematical, logical, arithmetic and relational, on huge datasets and tables. Functions are used when we have to re-use similar operations multiple times. In this advanced BigData world, to handle and process huge datasets, writing MapReduce programs becomes difficult. So, Hive Query Language  (HQL) was introduced on top of Apache Hadoop in October 2010, using which we can easily run queries, similar to SQLs. HQLs are further internally converted into Mapreduce programs to yield the outputs. In this topic, we are going to discuss Hive Built-in Functions.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Hive functions are broadly categorized into two :

  1. Built-in Functions
  2. User-Defined Functions

Built-In Functions of Hive

Built-in Functions are predefined and readily available to use in Hive. They are categorized as below:

1. String Functions

Used for string manipulations and transformation.

Function Name Return Type Description Example
substr(string, int<pos>, int<len>) String It returns a string from the specified start position to specified length substr(‘Hive query ‘,5,5) results in ‘query’
rtrim(string X) String It returns a string without right leading spaces rtrim(‘Hello     ‘) results in ‘Hello’
ltrim(string X) String It returns a string without left leading spaces ltrim(‘      Hello’) results in ‘Hello’
reverse(string X) String It returns a reversed string of X reverse(‘hello’) results in ‘olleh’
rpad(string X, int <len>, string <‘pad’>) String It returns a right padded string with ‘pad’ to a length of <len> rpad(‘Hello ‘,3,’hi ‘) results in ‘Hello hi hi hi ‘
lpad(string X, int <len>, string <‘pad’>) String It returns a left padded string with ‘pad’ to a length of <len> lpad(‘Hello’,3,’ Hi ‘) results in ‘ Hi Hi Hi Hello’
split(string X,string pat) Array It returns an array of strings after splitting the string around the specified pattern split(“A,B,C,D”,”,”) results in [‘A’,B’,’C’,’D’]
length(string X) Integer It returns length of string X length(‘Bigdata’) results in 7
concat(string X,String Y) String It returns a concatenated string of both X and Y concat(‘Hello’,’World’) results in ‘HelloWorld’

2. Mathematical Functions

Used for applying mathematical operations like rounding, ceiling, flooring, etc. instead of creating UDFs.

Function Name Return Type Description Example
round(double X) Double It returns the rounded off value of X round(29.5) results in 30
round(double X,Int d) Double It returns the rounded off value of X to d decimal places round(29.3456,2) results in 29.34
ceil(double X) Double It returns a minimum BIGINT value that is equal to or greater than X ceil(20.5555) results in 21
floor(double X) Double It returns a maximum BIGINT value that is equal to or greater than X ceil(20.5555) results in 20
rand() Double It returns random numbers between 0 to 9 rand() results in rand(0-9)
abs(double X) Double It returns the absolute value of the number X abs(-25) results in 25
pow(double X,double Y) Double It returns X value raised to the Y power pow(2,3) results in 8
exp(double X) Double It returns the exponent value of X exp(2)results in 7.389

 3. Conditional Functions

Used to check an expression for True or False and returns corresponding results.

Function Name Return Type Description Example
isnull(X) Boolean It returns TRUE if X is NULL else false isnull(‘NULL’) returns in TRUE
isnotnull(X) Boolean It returns TRUE if X is not NULL else false isnotnull(‘NULL’) returns in FALSE
nvl(arg X,arg Y) String It returns arg Y if arg X is NULL else return arg X nvl(‘NULL’,’Value is Null’) results in ‘Value is Null’
if(Boolean testCondition, T valueTrue, T valueFalseOrNull) T It returns True when test condition is true and False or Null otherwise if (2=2,’True’,’False’)
coalesce(X,Y) (Any) It returns the first non-null values from the list{I have a doubt on this—will it return the very first value or all non null values?} coalesce(null,null,null,1,5,null,null,6) results in 1

4. Date Functions

Used for performing date manipulations and date type conversions.

Function Name Return Type Description Example
current_date Date It returns current date of query execution current_date() returns today’s date
unix_timestamp() BigInt It returns current unix timestamp of query execution in seconds unix_timestamp() returns current unix timestamp
year(string date) Int It returns year part of a date year(‘1994-11-24’) returns 1994
quarter(string date) Int It returns the quarter of the year of a date quarter(‘1994-11-24’) returns 4
month(string date) Int It returns the month part of a date month(‘1994-11-24’) returns 11
hour(string date) Int It returns the hour part of the timestamp hour(‘1994-11-24 12:45:23’) returns 12
minute(string date) Int It returns the minute part of the timestamp year(‘1994-11-24 12:45:23’) returns 45
to_date() String It returns date part of timestamp string to_date() returns date part
date_sub(string date, int days) String It returns subtraction of number of days to the date date_sub(‘1994-11-24’,20) returns ‘1994-11-04’
months_between(date1,date2) Double It returns the number of months between date1 and date2{months or no of days?} months_between(‘1994-11-24′,’1994-11-04’) returns 20

5. Collection Function

Used for transforming and retrieving parts of collection types like map, array, etc.

Function Name Return Type Description Example
size(map<k,v>) Int It returns the total number of elements in the map size([‘a’:2,’b’:6]) returns 2
size(array<A>) Int It returns the total number of elements in the array size([1,3,4,7]) returns 4
array_contains(array<A>,value) Boolean It returns true if the array contains the value array_contains([1,3,9,8,5,4,7],5) returns TRUE
map_keys(map<k,v>) Array<k> It returns an unordered array of keys of the map map_keys([‘a’:2,’b’:6,’c’:1]) returns [‘b’,’c’,’a’]
map_valuess(map<k,v>) Array<v> It returns an unordered array of values of the map map_keys([‘a’:2,’b’:6,’c’:1]) returns [1,6,2]
sort_array(array<A>) Array<A> It returns a sorted array of the given array sort_array([1,3,9,8,5,4,7]) returns [1,3,4,5,7,8,9]

Conclusion

So far we have discussed different built-in functions in Hive. Compared to MapReduce, Hive is more convenient and time-saving. Anyone who has basic SQL knowledge can easily write HQLs, rather than complicated MapReduce programs for data processing.

Recommended Articles

This is a guide to Hive Built-in Functions. Here we discuss the Built-in Functions which are predefined and readily available to use in Hive. You may also look at the following article to learn more –

  1. Hive String Functions
  2. Hive Commands
  3. Hive Architecture
  4. Hive Order By
SPSS Course Bundle - 14 Courses in 1 | 5 Mock Tests
34+ Hours of HD Videos
14 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
62+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
HADOOP Course Bundle - 32 Courses in 1 | 4 Mock Tests
125+ Hour of HD Videos
32 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
INFORMATICA Course Bundle - 7 Courses in 1
47+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
HIVE Course Bundle - 7 Courses in 1
 23+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more