{"id":10076,"date":"2018-03-07T08:54:27","date_gmt":"2018-03-07T08:54:27","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=10076"},"modified":"2018-03-07T08:54:27","modified_gmt":"2018-03-07T08:54:27","slug":"hive-built-in-functions","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/","title":{"rendered":"Apache Hive Built-In Functions | Hive Functions"},"content":{"rendered":"

Basically, to perform several operations there are some functions available. Similarly, in Hive<\/strong> 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. <\/span><\/p>\n

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.<\/span><\/p>\n

Hive Functions<\/span><\/h2>\n

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.<\/span><\/p>\n

Introduction to Hive Built-In Functions<\/span><\/h2>\n

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.<\/span><\/p>\n

In addition, there are several types of Hive Built-in Functions available.<\/span><\/p>\n

Now, let\u2019s describe Hive Built-in Functions in detail:<\/span><\/p>\n

a. Collection Functions<\/span><\/h3>\n

Basically, as par its name we use \u201cCollection Functions\u201d for collections. <\/span><\/p>\n

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\u2019s discuss each Collection Functions- Hive Built-in functions in below table:<\/span><\/p>\n\n\n\n\n\n\n\n\n
Return Type<\/strong><\/td>\nFunction Name<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
INT<\/span><\/td>\nsize(Map<K.V>)<\/span><\/td>\nIt will fetch and give the components number in the map type<\/span><\/td>\n<\/tr>\n
INT<\/span><\/td>\nsize(Array<T>)<\/span><\/td>\nIt will fetch and give the elements number in the array type<\/span><\/td>\n<\/tr>\n
Array<K><\/span><\/td>\nMap_keys(Map<K.V>)<\/span><\/td>\nIt will fetch and gives an array containing the keys of the input map. Here array is in unordered<\/span><\/td>\n<\/tr>\n
Array<V><\/span><\/td>\nMap_values(Map<K.V>)<\/span><\/td>\nIt will fetch and gives an array containing the values of the input map. Here array is in unordered<\/span><\/td>\n<\/tr>\n
Array<t><\/span><\/td>\nSort_array(Array<T>)<\/span><\/td>\nsorts the input array in ascending order of array and elements and returns it<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

b. Hive Date Functions<\/h3>\n

However, to perform date manipulations and conversion of date types from one type to another type we use Hive date Functions. Let\u2019s discuss each Date Function – Hive Built-in Functions in below table:<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Function Name<\/strong><\/td>\nReturn Type<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
Unix_Timestamp()<\/span><\/td>\nUnix_Timestamp()<\/span><\/td>\nWe will get current Unix timestamp in seconds<\/span><\/td>\n<\/tr>\n
To_date(string timestamp)<\/span><\/td>\nstring<\/span><\/td>\nIt will fetch and give the date part of a timestamp string:<\/span><\/td>\n<\/tr>\n
year(string date)<\/span><\/td>\nINT<\/span><\/td>\nIt will fetch and give the year part of a date or a timestamp string<\/span><\/td>\n<\/tr>\n
quarter(date\/timestamp\/string)<\/span><\/td>\nINT<\/span><\/td>\nIt will fetch and give the quarter of the year for a date, timestamp, or string in the range 1 to 4<\/span><\/td>\n<\/tr>\n
month(string date)<\/span><\/td>\nINT<\/span><\/td>\nIt will give the month part of a date or a timestamp string<\/span><\/td>\n<\/tr>\n
hour(string date)<\/span><\/td>\nINT<\/span><\/td>\nIt will fetch and gives the hour of the timestamp<\/span><\/td>\n<\/tr>\n
minute(string date)<\/span><\/td>\nINT<\/span><\/td>\nIt will fetch and gives the minute of the timestamp<\/span><\/td>\n<\/tr>\n
Date_sub(string starting date, int days)<\/span><\/td>\nstring<\/span><\/td>\nIt will fetch and gives Subtraction of number of days to starting date<\/span><\/td>\n<\/tr>\n
Current_date<\/span><\/td>\ndate<\/span><\/td>\nIt will fetch and gives the current date at the start of query evaluation<\/span><\/td>\n<\/tr>\n
LAST _day(string date)<\/span><\/td>\nstring<\/span><\/td>\nIt will fetch and gives the last day of the month which the date belongs to<\/span><\/td>\n<\/tr>\n
trunc(string date, string format)<\/span><\/td>\nstring<\/span><\/td>\nIt will fetch and gives date truncated to the unit specified by the format.<\/span>
\nSupported formats in this :<\/b>
\nMONTH\/MON\/MM, YEAR\/YYYY\/YY.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

c. Mathematical Functions<\/h3>\n

For Mathematical Operations in Hive, we use \u201cMathematical Functions\u201d. Though, we have some inbuilt mathematical functions in Hive despite creating UDFs. Let\u2019s discuss each Mathematical Function- Hive Built-in Functions in below table:<\/span><\/p>\n\n\n\n\n\n\n\n\n\n
Function Name<\/strong><\/td>\nReturn Type<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
round(DOUBLE X)<\/span><\/td>\nDOUBLE<\/span><\/td>\nIt will fetch and returns the rounded BIGINT value of X<\/span><\/td>\n<\/tr>\n
round(DOUBLE X, INT d)<\/span><\/td>\nDOUBLE<\/span><\/td>\nIt will fetch and returns X rounded to d decimal places<\/span><\/td>\n<\/tr>\n
bound(DOUBLE X)<\/span><\/td>\nDOUBLE<\/span><\/td>\nIt will fetch and returns the rounded BIGINT value of X using HALF_EVEN rounding mode<\/span><\/td>\n<\/tr>\n
floor(DOUBLE X)<\/span><\/td>\nBIGINT<\/span><\/td>\nIt will fetch and returns the maximum BIGINT value that is equal to or less than X value<\/span><\/td>\n<\/tr>\n
ceil(DOUBLE a), ceiling(DOUBLE a)<\/span><\/td>\nBIGINT<\/span><\/td>\nIt will fetch and returns the minimum BIGINT value that is equal to or greater than X value<\/span><\/td>\n<\/tr>\n
rand(), rand(INT seed)<\/span><\/td>\nDOUBLE<\/span><\/td>\nIt will fetch and returns a random number that is distributed uniformly from 0 to 1<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

d. Conditional Functions<\/span><\/h3>\n

While it comes to conditional values checks in Hive, we use \u201cConditional Functions\u201d. Let\u2019s discuss each Conditional Function-\u00a0Hive Built-In Functions in below table:<\/span><\/p>\n\n\n\n\n\n\n
Function Name<\/strong><\/td>\nReturn Type<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
if(Boolean testCondition, T valueTrue, T valueFalseOrNull)<\/span><\/td>\nT<\/span><\/td>\nIt will fetch and gives value True when Test Condition is of true, gives value False Or Null otherwise.<\/span><\/td>\n<\/tr>\n
ISNULL( X)<\/span><\/td>\nBoolean<\/span><\/td>\nIt will fetch and gives true if X is NULL and false otherwise.<\/span><\/td>\n<\/tr>\n
ISNOTNULL(X )<\/span><\/td>\nBoolean<\/span><\/td>\nIt will fetch and gives true if X is not NULL and false otherwise.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

e. Hive String Functions<\/h3>\n

For String manipulations and string operations in Hive, we call Hive String Functions. Let\u2019s discuss each String Function-\u00a0Hive Built-In Functions in below table:<\/span><\/p>\n\n\n\n\n\n\n\n\n\n
Function Name<\/strong><\/td>\nReturn Type<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
reverse(string X)<\/span><\/td>\nstring<\/span><\/td>\nIt will give the reversed string of X<\/span><\/td>\n<\/tr>\n
rpad(string str, int length, string pad)<\/span><\/td>\nstring<\/span><\/td>\nIt will fetch and gives str, which is right-padded with the pad to a length of length(integer value)<\/span><\/td>\n<\/tr>\n
rtrim(string X)<\/span><\/td>\nstring<\/span><\/td>\nIt 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’<\/span><\/td>\n<\/tr>\n
space(INT n)<\/span><\/td>\nstring<\/span><\/td>\nIt will fetch and gives a string of n spaces.<\/span><\/td>\n<\/tr>\n
split(STRING str, STRING pat)<\/span><\/td>\narray<\/span><\/td>\nSplits str around pat (pat is a regular expression).<\/span><\/td>\n<\/tr>\n
Str_to_map(text[, delimiter1, delimiter2])<\/span><\/td>\nmap<String ,String><\/span><\/td>\nIt will split text into key-value pairs using two delimiters.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Conclusion<\/h2>\n

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. <\/span><\/p>\n

Still, if any doubt occurs regarding Hive Built-in Functions, feel free to ask in the comment section.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

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...<\/p>\n","protected":false},"author":7,"featured_media":42075,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[801,5687,5691,5702,5751,7039,15024],"class_list":["post-10076","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hive","tag-apache-hive-built-in-functions","tag-hive-built-in-functions","tag-hive-collection-functions","tag-hive-date-functions","tag-hive-mathematical-functions","tag-introduction-to-hive-built-in-functions","tag-types-of-built-in-functions"],"yoast_head":"\nApache Hive Built-In Functions | Hive Functions - DataFlair<\/title>\n<meta name=\"description\" content=\"Hive Built-In Functions-types of Built in functions,Collection Function,Hive Date Function,Mathematical Function,Conditional Function & Hive String Function\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Apache Hive Built-In Functions | Hive Functions - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Hive Built-In Functions-types of Built in functions,Collection Function,Hive Date Function,Mathematical Function,Conditional Function & Hive String Function\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-07T08:54:27+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Built-In-Functions-01-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Apache Hive Built-In Functions | Hive Functions - DataFlair","description":"Hive Built-In Functions-types of Built in functions,Collection Function,Hive Date Function,Mathematical Function,Conditional Function & Hive String Function","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/","og_locale":"en_US","og_type":"article","og_title":"Apache Hive Built-In Functions | Hive Functions - DataFlair","og_description":"Hive Built-In Functions-types of Built in functions,Collection Function,Hive Date Function,Mathematical Function,Conditional Function & Hive String Function","og_url":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-03-07T08:54:27+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Built-In-Functions-01-1.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd"},"headline":"Apache Hive Built-In Functions | Hive Functions","datePublished":"2018-03-07T08:54:27+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/"},"wordCount":1075,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Built-In-Functions-01-1.jpg","keywords":["Apache Hive Built-In Functions","Hive Built In Functions","Hive Collection Functions","Hive Date Functions","Hive Mathematical Functions","Introduction to Hive Built In Functions","types of Built in functions"],"articleSection":["Hive Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/","url":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/","name":"Apache Hive Built-In Functions | Hive Functions - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Built-In-Functions-01-1.jpg","datePublished":"2018-03-07T08:54:27+00:00","description":"Hive Built-In Functions-types of Built in functions,Collection Function,Hive Date Function,Mathematical Function,Conditional Function & Hive String Function","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Built-In-Functions-01-1.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Hive-Built-In-Functions-01-1.jpg","width":1200,"height":628,"caption":"Types of Hive Built in Functions"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/hive-built-in-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Hive Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/hive\/"},{"@type":"ListItem","position":3,"name":"Apache Hive Built-In Functions | Hive Functions"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/beb0cab24b7aa54423a3b50e669a9dcd","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322416204232f4dd97ef3901b0a499a5d34d7ba7fe333f4bfe53a907873d293?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"DataFlair Team specializes in creating clear, actionable content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Backed by industry expertise, we make learning easy and career-oriented for beginners and pros alike.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam3\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/10076","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=10076"}],"version-history":[{"count":0,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/10076\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/42075"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=10076"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=10076"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=10076"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}