{"id":11753,"date":"2018-03-27T08:37:17","date_gmt":"2018-03-27T08:37:17","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=11753"},"modified":"2021-05-09T13:11:20","modified_gmt":"2021-05-09T07:41:20","slug":"impala-built-in-function","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/","title":{"rendered":"Impala Built in Function- 6 types of Build in Functions"},"content":{"rendered":"<p><span style=\"font-weight: 400\">There are several categories of Impala built in function. Basically, those Impala functions help to perform various operations in<strong> Impala<\/strong>. So, in this blog, we will learn the whole concept of Impala Built in Functions. Moreover, we will discuss types of Built in Functions in Impala.<\/span><\/p>\n<p>So, let&#8217;s start Impala Built in Functions.<\/p>\n<h2><span style=\"font-weight: 400\">What is Impala Built in Functions<\/span><\/h2>\n<p><span style=\"font-weight: 400\">In order to perform several functions like mathematical calculations, string manipulation, date calculations, and other kinds of data transformations directly in SELECT statements we can use Impala Built in Functions. <\/span><\/p>\n<p><span style=\"font-weight: 400\">We can get results with all formatting, calculating, and type conversions applied, with the built-in functions SQL query in Impala.<\/span> Despite performing time-consuming postprocessing in another application we can use the Impala Built in Functions.<\/p>\n<p><span style=\"font-weight: 400\">Impala support following categories of built in functions in Impala, s<\/span>uch as:<\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Mathematical Functions<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Type Conversion Functions<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Date and Time Functions<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Conditional Functions<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">String Functions<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Aggregation functions<\/span><\/li>\n<\/ul>\n<h2><span style=\"font-weight: 400\">How to call Impala Built in Functions<\/span><\/h2>\n<p><span style=\"font-weight: 400\">We can call any of these Impala functions by using the SELECT statement. Basically, for any required arguments we can omit the FROM clause and supply literal values, for the most function:<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><br \/>\n<strong>select abs(-1);<br \/>\nselect concat(&#8216;The rain &#8216;, &#8216;in Spain&#8217;);<br \/>\nselect power(2,5);<\/strong><\/p>\n<p><span style=\"font-weight: 400\">However, \u00a0the function is applied for each item in the result set, whenever we use a FROM clause and specify a column name as a function argument:<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><strong>select concat(&#8216;Country = &#8216;,country_code) from all_countries where population &gt; 100000000;<br \/>\nselect round(price) as dollar_value from product_catalog where price between 0.0 and 100.0;<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Also, make sure that if any argument to a built-in function is NULL, that means \u00a0the result value is also NULL:<\/span><\/p>\n<p><strong>select cos(null);<br \/>\nselect power(2,null);<br \/>\nselect concat(&#8216;a&#8217;,null,&#8217;b&#8217;);<\/strong><\/p>\n<h2><span style=\"font-weight: 400\">Types of Built-in Functions in Impala<\/span><\/h2>\n<p><span style=\"font-weight: 400\">Now, let\u2019s discuss each category in Impala Built in Functions one by one, in detail;<\/span><\/p>\n<div id=\"attachment_18967\" style=\"width: 1210px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Types-of-Built-in-Functions-in-Impala-01-1.jpg\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-18967\" class=\"wp-image-18967 size-full\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Types-of-Built-in-Functions-in-Impala-01-1.jpg\" alt=\"\" width=\"1200\" height=\"628\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Types-of-Built-in-Functions-in-Impala-01-1.jpg 1200w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Types-of-Built-in-Functions-in-Impala-01-1-150x79.jpg 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Types-of-Built-in-Functions-in-Impala-01-1-300x157.jpg 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Types-of-Built-in-Functions-in-Impala-01-1-768x402.jpg 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Types-of-Built-in-Functions-in-Impala-01-1-1024x536.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/a><p id=\"caption-attachment-18967\" class=\"wp-caption-text\">Types of Built-in Functions in Impala<\/p><\/div>\n<h3><span style=\"font-weight: 400\">i. Mathematical Functions<\/span><\/h3>\n<p><span style=\"font-weight: 400\">There are several mathematical functions which Impala supports:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>abs(double a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> \u00a0This function returns the absolute value of the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cdouble\u201d.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\">Usage: In order to ensure all return values are positive, we use it.<\/span> However, we can say it is different than the positive() function. Since it returns its argument unchanged even if the argument was negative<\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>acos(double a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Purpose: This Impala function returns the arccosine of the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\">Return type: \u00a0Its return type is \u201cdouble\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>asin(double a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala Built in function returns the arcsine of the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type: \u00a0<\/strong>Its return type is \u201cdouble\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>atan(double a<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function returns the arctangent of the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0Its return type is \u201cdouble\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>bin(bigint a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala Built in function returns the binary representation of an integer value. That is a string of 0 and 1 digits.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0Its return type is \u00a0\u201cstring\u201d<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>ceil(double a), ceiling(double a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function returns the smallest integer. However, that is greater than or equal to the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u00a0\u201cint\u201d<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>conv(bigint num, int from_base, int to_base), conv(string num, int from_base, int to_base)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala Built in function returns the string representation of an integer value in a particular base. Here, the input value can be a string.<\/span><br \/>\n<span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u00a0\u201cstring\u201d<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>cos(double a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function returns the cosine of the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type: \u00a0<\/strong>Its return type is \u201cdouble\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>degrees(double a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function Converts the argument value, from radians to degrees.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0Its return type is \u201cdouble\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>e()<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the mathematical constant e.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type: \u00a0<\/strong>Its return type is \u201cdouble\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>exp(double a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function returns the mathematical constant e raised to the power of the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0Its return type is \u201cdouble\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>floor(double a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the largest integer that is less than or equal to the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u00a0\u201cint\u201d<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>fmod(double a, double b), fmod(float a, float b)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala Built in function returns the modulus of a number.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return ty<\/strong>pe: \u00a0Its return type depends on type of arguments. Hence, it can be float or double.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>greatest(bigint a[, bigint b &#8230;]), greatest(double a[, double b &#8230;]), greatest(string a[, string b &#8230;]), greatest(timestamp a[, timestamp b &#8230;])<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Purpose: This Impala function returns the largest value from a list of expressions.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>hex(bigint a), hex(string a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the hexadecimal representation of an integer value, or of the characters in a string.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0Its return type is \u00a0\u201cstring\u201d<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>ln(double a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Built-in function returns the natural logarithm of the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0Its return type is \u201cdouble\u201d.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>sqrt(double a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the square root of the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cdouble\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>tan(double a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the tangent of the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cdouble\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>unhex(string a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the string of characters with ASCII values. Basically, those corresponding to pairs of hexadecimal digits in the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c string\u201d.<\/span><\/p>\n<h3><span style=\"font-weight: 400\">ii. Type Conversion Impala Functions <\/span><\/h3>\n<p><span style=\"font-weight: 400\">There are several conversion functions which Impala supports:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>cast(expr as type)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Basically, to explicitly pass the expected data types we use Conversion functions in combination with other functions. Also, it attains some strict rules regarding data types for function parameters. Let\u2019s understand it with the example. <\/span><\/p>\n<p><span style=\"font-weight: 400\">As we know, Impala does not automatically convert. Like a DOUBLE value to FLOAT, a BIGINT value to INT, or another conversion where precision could be lost or overflow could occur. Hence, we use CAST when passing a column value or literal to a function that expects a parameter with a different type.<\/span><\/p>\n<h3><span style=\"font-weight: 400\">iii. Date and Time Impala Functions<\/span><\/h3>\n<p><span style=\"font-weight: 400\">There are several data and time functions which Impala supports, such as:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>adddate(timestamp startdate, int days, adddate(timestamp startdate, bigint days,<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> Basically, this function Adds a specified number of days to a TIMESTAMP value. It is same as date_add(). <\/span><\/p>\n<p><span style=\"font-weight: 400\">However, despite of a string that is converted to a TIMESTAMP, it starts with an actual TIMESTAMP value.<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> timestamp<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>current_timestamp()<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function is an alias for the now() function.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201ctimestamp\u201d<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>date_add(timestamp startdate, int days), date_add(timestamp startdate, interval_expression)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> While it comes to adds specified number of days to a TIMESTAMP value, we use it. Here, the first argument can be a string. <\/span><\/p>\n<p><span style=\"font-weight: 400\">Although, if it uses the recognized format, as described in TIMESTAMP Data Type, it automatically cast to TIMESTAMP. Hence, we can calculate a delta value using other units such as weeks, years, hours, seconds, and so on, with an INTERVAL expression as the second argument.<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201ctimestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>date_sub(timestamp startdate, int days), date_sub(timestamp startdate, interval_expression)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> when we need to subtracts a specified number of days from a TIMESTAMP value, we use it. Here, the first argument can be a string. Although, if it uses the recognized format, as described in TIMESTAMP Data Type it automatically cast to TIMESTAMP. <\/span><\/p>\n<p><span style=\"font-weight: 400\">Hence, we can calculate a delta value using other units such as weeks, years, hours, seconds, and so on, with an INTERVAL expression as the second argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type: <\/strong>Its return type is \u201ctimestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>datediff(string enddate, string startdate)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala Built in function returns the number of days between two dates represented as strings.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\">Return type: Its return type is \u00a0\u201cint\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">day(string date), dayofmonth(string date)<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the day field from a date represented as a string.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u00a0\u201cint\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong> dayname(string date)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the day field from a date represented as a string, converted to the string corresponding to that day name. Here, the range of return values is &#8216;Sunday&#8217; to &#8216;Saturday&#8217;.<\/span><\/p>\n<p><span style=\"font-weight: 400\"> However, we use it as an alternative to calling dayofweek() and turning that numeric return value into a string using a CASE expression in report-generating queries.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c string\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>dayofweek(string date)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function returns the day field from a date represented as a string. Basically, those correspond to the day of the week. Here, the range of return values is 1 (Sunday) to 7 (Saturday).<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u00a0\u201cint\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>dayofyear(timestamp date)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function returns the day field from a TIMESTAMP value. Basically, those correspond to the day of the year. Here, the of range of return values is 1 (January 1) to 366 (December 31 of a leap year).<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u00a0\u201cint\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>days_add(timestamp startdate, int days), days_add(timestamp startdate, bigint days)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function Adds a specified number of days to a TIMESTAMP value.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201ctimestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>days_sub(timestamp startdate, int days), days_sub(timestamp startdate, bigint days)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function Subtracts a specified number of days from a TIMESTAMP value. <\/span><br \/>\n<span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201ctimestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>from_unixtime(bigint unixtime[, string format])<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala Built in function Converts the number of seconds from the Unix epoch to the specified time into a string.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>from_utc_timestamp(timestamp, string timezone)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function Converts a specified UTC timestamp value into the appropriate value for a specified time zone.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201ctimestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>hour(string date)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala Built in function returns the hour field from a date represented as a string.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u00a0\u201cint\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>hours_add(timestamp date, int hours), hours_add(timestamp date, bigint hours)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose: <\/strong>This function returns the specified date and time plus some number of hours.<\/span><br \/>\n<span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u00a0\u201cint\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>hours_sub(timestamp date, int hours), hours_sub(timestamp date, bigint hours)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose: \u00a0<\/strong>This Built-in function in Impala returns the specified date and time minus some number of hours.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u00a0\u201cint\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>microseconds_add(timestamp date, int microseconds), microseconds_add(timestamp date, bigint microseconds)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function returns the specified date and time plus some number of microseconds.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c timestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>microseconds_sub(timestamp date, int microseconds), microseconds_sub(timestamp date, bigint microseconds)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function returns the specified date and time minus some number of microseconds.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c timestamp\u201d.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>milliseconds_add(timestamp date, int milliseconds), milliseconds_add(timestamp date, bigint milliseconds)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Built-in function in Impala function returns the specified date and time plus some number of milliseconds.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c timestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>milliseconds_sub(timestamp date, int milliseconds), milliseconds_sub(timestamp date, bigint milliseconds)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the specified date and time minus some number of milliseconds.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c timestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>minute(string date)<\/strong><\/li>\n<\/ul>\n<p><strong>Purpose:<\/strong> This Impala built in function returns the minute field from a date represented as a string.<br \/>\n<span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c int\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>minutes_add(timestamp date, int minutes), minutes_add(timestamp date, bigint minutes)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala Built in function returns the specified date and time plus some number of minutes.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c timestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>minutes_sub(timestamp date, int minutes), minutes_sub(timestamp date, bigint minutes)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function returns the specified date and time minus some number of minutes.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c timestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>month(string date)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the month field from a date represented as a string.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c int\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>now()<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the current date and time (in the UTC time zone) as a timestamp value.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0Its return type is \u201c timestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>second(string date)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Built-in function returns the second field from a date represented as a string.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong>\u00a0Its return type is \u201c int\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>seconds_add(timestamp date, int seconds), seconds_add(timestamp date, bigint seconds)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function returns the specified date and time plus some number of seconds.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0Its return type is \u201c timestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>seconds_sub(timestamp date, int seconds), seconds_sub(timestamp date, bigint seconds)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the specified date and time minus some number of seconds.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c timestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>year(string date)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Built-in function in Impala returns the year field from a date represented as a string.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c int\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>years_add(timestamp date, int years), years_add(timestamp date, bigint years)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function returns the specified date and time plus some number of years.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201c timestamp\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>years_sub(timestamp date, int years), years_sub(timestamp date, bigint years)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> \u00a0This Impala built in function returns the specified date and time minus some number of years.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type: <\/strong>Its return type is \u201c timestamp\u201d.<\/span><\/p>\n<h3><span style=\"font-weight: 400\">iv. Conditional Functions<br \/>\n<\/span><\/h3>\n<p><span style=\"font-weight: 400\">For the purpose of testing equality, comparison operators, and nullity, Impala supports the following conditional functions.<\/span> Such as:<\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>CASE a WHEN b THEN c [WHEN d THEN e]&#8230; [ELSE f] END<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function Compares an expression to one or more possible values. Further returns a corresponding result when a match is found.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> It is similar to the initial argument value, except that integer values are promoted to BIGINT and floating-point values are promoted to DOUBLE. Also, uses CAST() when inserting into a smaller numeric column.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>CASE WHEN a THEN b [WHEN c THEN d]&#8230; [ELSE e] END<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function Tests whether any of a sequence of expressions is true. Further returns a corresponding result for the first true expression.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> It is similar to the initial argument value, except that integer values are promoted to BIGINT and floating-point values are promoted to DOUBLE. Also, uses CAST() when inserting into a smaller numeric column<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>coalesce(type v1, type v2, &#8230;)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function returns the first specified argument that is not NULL. However, it only returns NULL if all arguments are NULL.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0It is similar to the initial argument value, except that integer values are promoted to BIGINT and floating-point values are promoted to DOUBLE. Also, uses CAST() when inserting into a smaller numeric column<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>if(boolean condition, type ifTrue, type ifFalseOrNull)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function Tests an expression. Further, it returns a corresponding result depending on whether the result is true, false, or NULL.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> It is similar to the ifTrue argument value.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>isnull(type a, type ifNotNull)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function Tests if an expression is NULL. Further, it returns the expression result value if not. Moreover, returns the second argument, if the first argument is NULL.<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> It is similar to the first argument value<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>nullifzero(numeric_expr)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function Returns NULL if the numeric expression evaluates to 0. Else it returns the result of the expression.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> It is similar to the initial argument value, except that integer values are promoted to BIGINT and floating-point values are promoted to DOUBLE. Also, uses CAST() when inserting into a smaller numeric column<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>nvl(type a, type ifNotNull)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function is Alias for the isnull() function. It tests if an expression is NULL. Further, returns the expression result value if not. <\/span><\/p>\n<p><span style=\"font-weight: 400\">Moreover, returns the second argument if the first argument is NULL. Equivalent to the nvl() function from Oracle Database or ifnull() from MySQL.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> It is similar to the first argument value<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>zeroifnull(numeric_expr)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function returns 0 if the numeric expression evaluates to NULL. Else, returns the result of the expression.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> It is similar to the initial argument value. Although, that integer values are promoted to BIGINT and floating-point values are promoted to DOUBLE. Also, uses CAST() when inserting into a smaller numeric column<\/span><\/p>\n<h3><span style=\"font-weight: 400\">v. String Functions <\/span><\/h3>\n<p><span style=\"font-weight: 400\">Impala supports the following string functions:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>ascii(string str)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function returns the numeric ASCII code of the first character of the argument.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cint\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>char_length(string a), character_length(string a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function returns the length in characters of the argument string. Also, it is an Alias for the length() function.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0Its return type is \u201cint\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>concat(string a, string b&#8230;)<\/strong><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function returns a single string representing all the argument values joined together.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>concat_ws(string sep, string a, string b&#8230;)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns a single string representing the second and following argument values joined together.<\/span> Also, it is delimited by a specified separator.<span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>initcap(string str)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function returns the input string with the first letter capitalized.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>instr(string str, string substr)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function returns the position (starting from 1) of the first occurrence of a substring. Also, it sustains within a longer string.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><\/p>\n<p><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0Its return type is \u201cint\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>length(string a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Built in function returns the length in characters of the argument string.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0Its return type is \u201cint\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>locate(string substr, string str[, int pos])<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the position (starting from 1) of the first occurrence of a substring within a longer string. Although, optionally after a particular position.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> \u00a0Its return type is \u201cint\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>lower(string a), lcase(string a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Built-in function in Impala returns the argument string converted to all-lowercase.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>ltrim(string a)<\/strong><\/li>\n<\/ul>\n<p><strong>Purpose: <\/strong>This Impala built in function returns the argument string with any leading spaces removed from the left side.<strong><br \/>\nReturn type:<\/strong>Its return type is \u201cstring\u201d.<\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>repeat(string str, int n)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the argument string repeated a specified number of times.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>reverse(string a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala Built in function returns the argument string with characters in reversed order.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>rtrim(string a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function returns the argument string with any trailing spaces removed from the right side.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>space(int n)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the concatenated string of the specified number of space. Although, it is shorthand for repeat (&#8216; &#8216;,n).<\/span><strong><br \/>\n<\/strong><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>strleft(string a, int num_chars)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This built-in function in Impala returns the leftmost characters of the string. Although, it is shorthand for a call to substr() with 2 arguments.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>strright(string a, int num_chars)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala function returns the rightmost characters of the string. Although, it is shorthand for a call to substr() with 2 arguments.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>substr(string a, int start [, int len]), substring(string a, int start [, int len])<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the portion of the string starting at a specified point. Optionally with a specified maximum length. Here, the characters in the string are indexed starting at 1.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> string<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>translate(string input, string from, string to)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function returns the input string with a set of characters replaced by another set of characters.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>trim(string a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This Impala built in function returns the input string with both leading and trailing spaces removed.<\/span><br \/>\n<span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><strong>upper(string a), ucase(string a)<\/strong><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><strong>Purpose:<\/strong> This function returns the argument string converted to all-uppercase.<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><strong>Return type:<\/strong> Its return type is \u201cstring\u201d.<\/span><\/p>\n<h3><span style=\"font-weight: 400\">vi. Aggregate Functions<\/span><\/h3>\n<p><span style=\"font-weight: 400\">While we need to calculate a return value across all the items in a result set, we use Aggregate functions.<\/span> These are the very special category with different rules. Hence, they require a FROM clause in the query:<span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\"><br \/>\n<\/span><strong>select count(product_id) from product_catalog;<br \/>\nselect max(height), avg(height) from census_data where age &gt; 20;<\/strong><br \/>\n<span style=\"font-weight: 400\"><br \/>\n<\/span><span style=\"font-weight: 400\">Despite returning a NULL result, Aggregate functions ignore NULL values.<\/span> Let\u2019s understand it with an example. If some rows have NULL for a particular column, those rows are ignored when computing the AVG() for that column.<\/p>\n<p>Moreover, where col_name contains a non-NULL value it specifies COUNT(col_name) in a query counts only.<br \/>\nThis was all on Impala Built in Functions. Hope you like this article on Impala Functions.<\/p>\n<h2><span style=\"font-weight: 400\">Conclusion<\/span><\/h2>\n<p><span style=\"font-weight: 400\">Hence, we have seen all possible categories of Impala built in functions. Also, we have seen various types of Impala Built in Functions. Still, if any doubt occurs regarding Impala Built in Functions, please ask through the comment section.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are several categories of Impala built in function. Basically, those Impala functions help to perform various operations in Impala. So, in this blog, we will learn the whole concept of Impala Built in&#46;&#46;&#46;<\/p>\n","protected":false},"author":6,"featured_media":42128,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[393,2868,3553,6485,6523,6524,8596,13901,14998,15025,15059,15060,15484],"class_list":["post-11753","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-impala","tag-aggregation-functions","tag-conditional-functions","tag-date-and-time-functions","tag-impala-built-in-functions","tag-impala-function-types","tag-impala-functions","tag-mathematical-functions","tag-string-functions","tag-type-conversion-function","tag-types-of-built-in-functions-in-impala","tag-types-of-impala-built-in-functions","tag-types-of-impala-functions","tag-what-are-impala-built-in-functions"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Impala Built in Function- 6 types of Build in Functions - DataFlair<\/title>\n<meta name=\"description\" content=\"Impala Built-in Functions: Types of Built-in Functions in Impala- impala Mathematical Functions, Type Conversion Function, Date and Time Functions etc\" \/>\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\/impala-built-in-function\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Impala Built in Function- 6 types of Build in Functions - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Impala Built-in Functions: Types of Built-in Functions in Impala- impala Mathematical Functions, Type Conversion Function, Date and Time Functions etc\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/\" \/>\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-27T08:37:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-05-09T07:41:20+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Impala-Built-in-Function_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=\"15 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Impala Built in Function- 6 types of Build in Functions - DataFlair","description":"Impala Built-in Functions: Types of Built-in Functions in Impala- impala Mathematical Functions, Type Conversion Function, Date and Time Functions etc","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\/impala-built-in-function\/","og_locale":"en_US","og_type":"article","og_title":"Impala Built in Function- 6 types of Build in Functions - DataFlair","og_description":"Impala Built-in Functions: Types of Built-in Functions in Impala- impala Mathematical Functions, Type Conversion Function, Date and Time Functions etc","og_url":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-03-27T08:37:17+00:00","article_modified_time":"2021-05-09T07:41:20+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Impala-Built-in-Function_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":"15 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"Impala Built in Function- 6 types of Build in Functions","datePublished":"2018-03-27T08:37:17+00:00","dateModified":"2021-05-09T07:41:20+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/"},"wordCount":3474,"commentCount":2,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Impala-Built-in-Function_1.jpg","keywords":["Aggregation functions","Conditional Functions","Date and Time Functions","Impala Built-in Functions","impala function types","impala functions","Mathematical Functions","String Functions","Type Conversion Function","types of Built-in functions in Impala","types of impala Built-in functions","types of impala functions","what are impala built-in functions"],"articleSection":["Impala Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/impala-built-in-function\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/","url":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/","name":"Impala Built in Function- 6 types of Build in Functions - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Impala-Built-in-Function_1.jpg","datePublished":"2018-03-27T08:37:17+00:00","dateModified":"2021-05-09T07:41:20+00:00","description":"Impala Built-in Functions: Types of Built-in Functions in Impala- impala Mathematical Functions, Type Conversion Function, Date and Time Functions etc","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/impala-built-in-function\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Impala-Built-in-Function_1.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/03\/Impala-Built-in-Function_1.jpg","width":1200,"height":628,"caption":"Impala Built in Function- 6 types of Build in Functions"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/impala-built-in-function\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Impala Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/impala\/"},{"@type":"ListItem","position":3,"name":"Impala Built in Function- 6 types of Build in 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\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/11753","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\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=11753"}],"version-history":[{"count":1,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/11753\/revisions"}],"predecessor-version":[{"id":94058,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/11753\/revisions\/94058"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/42128"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=11753"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=11753"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=11753"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}