Impala supports lots of built-in functions in various categories, and these functions are used to perform several types of data transformation operations, such as mathematical calculations, string manipulations, and data calculations. You can use built-in functions with SQL queries to avoid post-processing of data; by using these functions you can get fully-formatted, calculated, and type changed data as results. Aggregate functions ignore NULL values rather than returning a NULL result.
These built-in functions can be used directly with a SELECT statement, as shown in the following example:
SELECT ABS(-1);
SELECT CONCAT('NFL ', 'American Football'),
SELECT POWER(3,3);
SELECT CONCAT('State = ',state_name) FROM states WHERE population > 10000000;
SELECT SIN(null);
SELECT POWER(2,null);
SELECT MAX(wheels), AVG(windows) FROM automobiles WHERE year <1950;Impala supports the following categories of built-in functions:
- Mathematical functions
- String functions
- Conditional functions
- Date and time functions
- Type conversation functions
- Aggregate functions (which we have already discussed in previous sections)
Now, we will take a look at a few functions from each category and their usage patterns. Let's starts with mathematical functions, described in the following table:
|
Function name |
Usage |
Return type |
|---|---|---|
|
ABS(DOUBLE a) |
| |
|
COS(DOUBLE a) |
| |
|
BIN(BIGINT a) |
| |
|
FLOOR(DOUBLE a) |
| |
|
PI() |
| |
|
RAND(INT seed) |
|
Besides these, there are several other mathematical functions in Impala such as SIGN, SIN, SQRT, TAN, ROUND, POW, NEGATIVE, HEX, DEGREES, and ASIN. To study most of the functions, please visit the Impala documentation at Cloudera's website. Now, let's study a few of the common string built-in functions in the following table:
Besides these, there are many other string functions such as FIND_IN_SET, INSTR, LOCATE, LOWER, UPPER, LTRIM, REPEAT, RTRIM, SUBSTR, TRANSLATE, TRIM, and UPPER. Now, we can learn a few conditional functions, as described in the following table:
|
Function name |
Usage |
Return type |
|---|---|---|
|
CASE |
Argument | |
|
COALESCE |
Argument | |
|
IF |
Argument | |
|
ISNULL |
Argument |
Besides these, the other conditional functions are NV1 and CASE. Now it is time to learn a few of the date/time-specific functions, as in the following table:
|
Function name |
Usage |
Return type |
|---|---|---|
|
NOW() |
| |
|
TO_DATE(STRING date) |
| |
|
YEAR(STRING date) |
| |
|
DATEDIFF(date1, date2) |
To return the number of days between two dates passed as arguments |
|
Other date and time functions not described previously are DATE_ADD, DATE_SUB, DAY, DAYNAME, DAYOFWEEK, FROM_UNIXTIME, FROM_UTC_TIMESTAMP, HOUR, MINUTE, MONTH, SECOND, TO_UTC_TIMESTAMP, UNIX_TIMESTAMP, and WEEKOFYEAR.
For type conversion, Impala uses the CAST() function within strict rules regarding data types for functional parameters. The CAST() function is mostly used in conjunction with other SQL statements with other functions to explicitly pass the desired data types. The syntax for using the CAST operator is shown in the following example:
CAST (expression as TYPE)
SELECT CONCAT ('Today is ', 28 , 'October.'),The previous SQL statement will generate an error as 28 is used as a numeric value, and the CONCAT function only accepts string values. So we can use the CAST operator to convert numeric to STRING values as follows:
SELECT CONCAT ('Today is ', CAST(28 as STRING) , 'October.'),I have previously suggested that most of the functions are described in the SQL documentation, so you can look at specific SQL documentation to learn more about Impala built-in functions.