EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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 Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW