Impala Built in Function- 6 types of Build in Functions

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 Functions. Moreover, we will discuss types of Built in Functions in Impala.

So, let’s start Impala Built in Functions.

What is Impala Built in Functions

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.

We can get results with all formatting, calculating, and type conversions applied, with the built-in functions SQL query in Impala. Despite performing time-consuming postprocessing in another application we can use the Impala Built in Functions.

Impala support following categories of built in functions in Impala, such as:

• Mathematical Functions
• Type Conversion Functions
• Date and Time Functions
• Conditional Functions
• String Functions
• Aggregation functions

How to call Impala Built in Functions

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:

select abs(-1);
select concat(‘The rain ‘, ‘in Spain’);
select power(2,5);

However,  the 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:

select concat(‘Country = ‘,country_code) from all_countries where population > 100000000;
select round(price) as dollar_value from product_catalog where price between 0.0 and 100.0;

Also, make sure that if any argument to a built-in function is NULL, that means  the result value is also NULL:

select cos(null);
select power(2,null);
select concat(‘a’,null,’b’);

Types of Built-in Functions in Impala

Now, let’s discuss each category in Impala Built in Functions one by one, in detail;

Types of Built-in Functions in Impala

i. Mathematical Functions

There are several mathematical functions which Impala supports:

• abs(double a)

Purpose:  This function returns the absolute value of the argument.
Return type: Its return type is “double”.

Usage: In order to ensure all return values are positive, we use it. However, we can say it is different than the positive() function. Since it returns its argument unchanged even if the argument was negative

• acos(double a)

Purpose: This Impala function returns the arccosine of the argument.
Return type:  Its return type is “double”.

• asin(double a)

Purpose: This Impala Built in function returns the arcsine of the argument.
Return type:  Its return type is “double”.

• atan(double a

Purpose: This Impala function returns the arctangent of the argument.
Return type:  Its return type is “double”.

• bin(bigint a)

Purpose: This Impala Built in function returns the binary representation of an integer value. That is a string of 0 and 1 digits.
Return type:  Its return type is  “string”

• ceil(double a), ceiling(double a)

Purpose: This Impala function returns the smallest integer. However, that is greater than or equal to the argument.
Return type: Its return type is  “int”

• conv(bigint num, int from_base, int to_base), conv(string num, int from_base, int to_base)

Purpose: 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.
Return type: Its return type is  “string”

• cos(double a)

Purpose: This Impala function returns the cosine of the argument.
Return type:  Its return type is “double”.

• degrees(double a)

Purpose: This function Converts the argument value, from radians to degrees.
Return type:  Its return type is “double”.

• e()

Purpose: This function returns the mathematical constant e.
Return type:  Its return type is “double”.

• exp(double a)

Purpose: This Impala function returns the mathematical constant e raised to the power of the argument.
Return type:  Its return type is “double”.

• floor(double a)

Purpose: This function returns the largest integer that is less than or equal to the argument.
Return type: Its return type is  “int”

• fmod(double a, double b), fmod(float a, float b)

Purpose: This Impala Built in function returns the modulus of a number.
Return type:  Its return type depends on type of arguments. Hence, it can be float or double.

• greatest(bigint a[, bigint b …]), greatest(double a[, double b …]), greatest(string a[, string b …]), greatest(timestamp a[, timestamp b …])

Purpose: This Impala function returns the largest value from a list of expressions.

• hex(bigint a), hex(string a)

Purpose: This function returns the hexadecimal representation of an integer value, or of the characters in a string.
Return type:  Its return type is  “string”

• ln(double a)

Purpose: This Built-in function returns the natural logarithm of the argument.
Return type:  Its return type is “double”.

• sqrt(double a)

Purpose: This function returns the square root of the argument.
Return type: Its return type is “double”.

• tan(double a)

Purpose: This function returns the tangent of the argument.
Return type: Its return type is “double”.

• unhex(string a)

Purpose: This function returns the string of characters with ASCII values. Basically, those corresponding to pairs of hexadecimal digits in the argument.
Return type: Its return type is “ string”.

ii. Type Conversion Impala Functions

There are several conversion functions which Impala supports:

• cast(expr as type)

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’s understand it with the example.

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.

iii. Date and Time Impala Functions

There are several data and time functions which Impala supports, such as:

• adddate(timestamp startdate, int days, adddate(timestamp startdate, bigint days,

Purpose: Basically, this function Adds a specified number of days to a TIMESTAMP value. It is same as date_add().

However, despite of a string that is converted to a TIMESTAMP, it starts with an actual TIMESTAMP value.

Return type: timestamp

• current_timestamp()

Purpose: This Impala function is an alias for the now() function.
Return type: Its return type is “timestamp”

• date_add(timestamp startdate, int days), date_add(timestamp startdate, interval_expression)

Purpose: While it comes to adds specified number of days to 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. 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.

Return type: Its return type is “timestamp”.

• date_sub(timestamp startdate, int days), date_sub(timestamp startdate, interval_expression)

Purpose: 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.

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.

Return type: Its return type is “timestamp”.

• datediff(string enddate, string startdate)

Purpose: This Impala Built in function returns the number of days between two dates represented as strings.
Return type: Its return type is  “int”.

• day(string date), dayofmonth(string date)

Purpose: This function returns the day field from a date represented as a string.
Return type: Its return type is  “int”.

• dayname(string date)

Purpose: 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 ‘Sunday’ to ‘Saturday’.

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.
Return type: Its return type is “ string”.

• dayofweek(string date)

Purpose: 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).
Return type: Its return type is  “int”.

• dayofyear(timestamp date)

Purpose: 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).
Return type: Its return type is  “int”.

• days_add(timestamp startdate, int days), days_add(timestamp startdate, bigint days)

Purpose: This Impala built in function Adds a specified number of days to a TIMESTAMP value.
Return type: Its return type is “timestamp”.

• days_sub(timestamp startdate, int days), days_sub(timestamp startdate, bigint days)

Purpose: This function Subtracts a specified number of days from a TIMESTAMP value.
Return type: Its return type is “timestamp”.

• from_unixtime(bigint unixtime[, string format])

Purpose: This Impala Built in function Converts the number of seconds from the Unix epoch to the specified time into a string.
Return type: Its return type is “string”

• from_utc_timestamp(timestamp, string timezone)

Purpose: This function Converts a specified UTC timestamp value into the appropriate value for a specified time zone.
Return type: Its return type is “timestamp”.

• hour(string date)

Purpose: This Impala Built in function returns the hour field from a date represented as a string.
Return type: Its return type is  “int”.

• hours_add(timestamp date, int hours), hours_add(timestamp date, bigint hours)

Purpose: This function returns the specified date and time plus some number of hours.
Return type: Its return type is  “int”.

• hours_sub(timestamp date, int hours), hours_sub(timestamp date, bigint hours)

Purpose:  This Built-in function in Impala returns the specified date and time minus some number of hours.
Return type: Its return type is  “int”.

• microseconds_add(timestamp date, int microseconds), microseconds_add(timestamp date, bigint microseconds)

Purpose: This Impala built in function returns the specified date and time plus some number of microseconds.
Return type: Its return type is “ timestamp”.

• microseconds_sub(timestamp date, int microseconds), microseconds_sub(timestamp date, bigint microseconds)

Purpose: This Impala function returns the specified date and time minus some number of microseconds.
Return type: Its return type is “ timestamp”.

• milliseconds_add(timestamp date, int milliseconds), milliseconds_add(timestamp date, bigint milliseconds)

Purpose: This Built-in function in Impala function returns the specified date and time plus some number of milliseconds.
Return type: Its return type is “ timestamp”.

• milliseconds_sub(timestamp date, int milliseconds), milliseconds_sub(timestamp date, bigint milliseconds)

Purpose: This function returns the specified date and time minus some number of milliseconds.
Return type: Its return type is “ timestamp”.

• minute(string date)

Purpose: This Impala built in function returns the minute field from a date represented as a string.
Return type: Its return type is “ int”.

• minutes_add(timestamp date, int minutes), minutes_add(timestamp date, bigint minutes)

Purpose: This Impala Built in function returns the specified date and time plus some number of minutes.
Return type: Its return type is “ timestamp”.

• minutes_sub(timestamp date, int minutes), minutes_sub(timestamp date, bigint minutes)

Purpose: This Impala built in function returns the specified date and time minus some number of minutes.
Return type: Its return type is “ timestamp”.

• month(string date)

Purpose: This function returns the month field from a date represented as a string.
Return type: Its return type is “ int”.

• now()

Purpose: This function returns the current date and time (in the UTC time zone) as a timestamp value.
Return type:  Its return type is “ timestamp”.

• second(string date)

Purpose: This Built-in function returns the second field from a date represented as a string.
Return type: Its return type is “ int”.

• seconds_add(timestamp date, int seconds), seconds_add(timestamp date, bigint seconds)

Purpose: This Impala built in function returns the specified date and time plus some number of seconds.
Return type:  Its return type is “ timestamp”.

• seconds_sub(timestamp date, int seconds), seconds_sub(timestamp date, bigint seconds)

Purpose: This function returns the specified date and time minus some number of seconds.
Return type: Its return type is “ timestamp”.

• year(string date)

Purpose: This Built-in function in Impala returns the year field from a date represented as a string.
Return type: Its return type is “ int”.

• years_add(timestamp date, int years), years_add(timestamp date, bigint years)

Purpose: This Impala built in function returns the specified date and time plus some number of years.
Return type: Its return type is “ timestamp”.

• years_sub(timestamp date, int years), years_sub(timestamp date, bigint years)

Purpose:  This Impala built in function returns the specified date and time minus some number of years.
Return type: Its return type is “ timestamp”.

iv. Conditional Functions

For the purpose of testing equality, comparison operators, and nullity, Impala supports the following conditional functions. Such as:

• CASE a WHEN b THEN c [WHEN d THEN e]… [ELSE f] END

Purpose: This Impala function Compares an expression to one or more possible values. Further returns a corresponding result when a match is found.

Return type: 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.

• CASE WHEN a THEN b [WHEN c THEN d]… [ELSE e] END

Purpose: This Impala function Tests whether any of a sequence of expressions is true. Further returns a corresponding result for the first true expression.

Return type: 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

• coalesce(type v1, type v2, …)

Purpose: This Impala built in function returns the first specified argument that is not NULL. However, it only returns NULL if all arguments are NULL.

Return type:  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

• if(boolean condition, type ifTrue, type ifFalseOrNull)

Purpose: This function Tests an expression. Further, it returns a corresponding result depending on whether the result is true, false, or NULL.

Return type: It is similar to the ifTrue argument value.

• isnull(type a, type ifNotNull)

Purpose: 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.

Return type: It is similar to the first argument value

• nullifzero(numeric_expr)

Purpose: This Impala built in function Returns NULL if the numeric expression evaluates to 0. Else it returns the result of the expression.

Return type: 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

• nvl(type a, type ifNotNull)

Purpose: This function is Alias for the isnull() function. It tests if an expression is NULL. Further, returns the expression result value if not.

Moreover, returns the second argument if the first argument is NULL. Equivalent to the nvl() function from Oracle Database or ifnull() from MySQL.

Return type: It is similar to the first argument value

• zeroifnull(numeric_expr)

Purpose: This Impala built in function returns 0 if the numeric expression evaluates to NULL. Else, returns the result of the expression.

Return type: 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

v. String Functions

Impala supports the following string functions:

• ascii(string str)

Purpose: This Impala function returns the numeric ASCII code of the first character of the argument.
Return type: Its return type is “int”.

• char_length(string a), character_length(string a)

Purpose: This Impala built in function returns the length in characters of the argument string. Also, it is an Alias for the length() function.

Return type:  Its return type is “int”.

• concat(string a, string b…)

Purpose: This Impala function returns a single string representing all the argument values joined together.
Return type: Its return type is “string”.

• concat_ws(string sep, string a, string b…)

Purpose: This function returns a single string representing the second and following argument values joined together. Also, it is delimited by a specified separator.

Return type: Its return type is “string”.

• initcap(string str)

Purpose: This Impala built in function returns the input string with the first letter capitalized.

Return type: Its return type is “string”.

• instr(string str, string substr)

Purpose: This Impala function returns the position (starting from 1) of the first occurrence of a substring. Also, it sustains within a longer string.

Return type:  Its return type is “int”.

• length(string a)

Purpose: This Built in function returns the length in characters of the argument string.
Return type:  Its return type is “int”.

• locate(string substr, string str[, int pos])

Purpose: 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.
Return type:  Its return type is “int”.

• lower(string a), lcase(string a)

Purpose: This Built-in function in Impala returns the argument string converted to all-lowercase.
Return type: Its return type is “string”.

• ltrim(string a)

Purpose: This Impala built in function returns the argument string with any leading spaces removed from the left side.
Return type:Its return type is “string”.

• repeat(string str, int n)

Purpose: This function returns the argument string repeated a specified number of times.
Return type: Its return type is “string”.

• reverse(string a)

Purpose: This Impala Built in function returns the argument string with characters in reversed order.
Return type: Its return type is “string”.

• rtrim(string a)

Purpose: This Impala built in function returns the argument string with any trailing spaces removed from the right side.
Return type: Its return type is “string”.

• space(int n)

Purpose: This function returns the concatenated string of the specified number of space. Although, it is shorthand for repeat (‘ ‘,n).
Return type: Its return type is “string”.

• strleft(string a, int num_chars)

Purpose: 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.
Return type: Its return type is “string”.

• strright(string a, int num_chars)

Purpose: This Impala function returns the rightmost characters of the string. Although, it is shorthand for a call to substr() with 2 arguments.
Return type: Its return type is “string”.

• substr(string a, int start [, int len]), substring(string a, int start [, int len])

Purpose: 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.
Return type: string

• translate(string input, string from, string to)

Purpose: This Impala built in function returns the input string with a set of characters replaced by another set of characters.
Return type: Its return type is “string”

• trim(string a)

Purpose: This Impala built in function returns the input string with both leading and trailing spaces removed.
Return type: Its return type is “string”.

• upper(string a), ucase(string a)

Purpose: This function returns the argument string converted to all-uppercase.
Return type: Its return type is “string”.

vi. Aggregate Functions

While we need to calculate a return value across all the items in a result set, we use Aggregate functions. These are the very special category with different rules. Hence, they require a FROM clause in the query:

select count(product_id) from product_catalog;
select max(height), avg(height) from census_data where age > 20;

Despite returning a NULL result, Aggregate functions ignore NULL values. Let’s 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.

Moreover, where col_name contains a non-NULL value it specifies COUNT(col_name) in a query counts only.
This was all on Impala Built in Functions. Hope you like this article on Impala Functions.

Conclusion

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.