Impala Built-In Functions

Impala supports several categories of built-in functions. These functions let you perform mathematical calculations, string manipulation, date calculations, and other kinds of data transformations directly in SELECT statements. The built-in functions let a SQL query return results with all formatting, calculating, and type conversions applied, rather than performing time-consuming postprocessing in another application. By applying function calls where practical, you can make a SQL query that is as convenient as an expression in a procedural programming language or a formula in a spreadsheet.

The categories of functions supported by Impala are:

You call any of these functions through the SELECT statement. For most functions, you can omit the FROM clause and supply literal values for any required arguments:

  1. select abs(-1);
  2. +---------+
  3. | abs(-1) |
  4. +---------+
  5. | 1       |
  6. +---------+
  8. select concat('The rain ', 'in Spain');
  9. +---------------------------------+
  10. | concat('the rain ', 'in spain') |
  11. +---------------------------------+
  12. | The rain in Spain               |
  13. +---------------------------------+
  15. select power(2,5);
  16. +-------------+
  17. | power(2, 5) |
  18. +-------------+
  19. | 32          |
  20. +-------------+

When you use a FROM clause and specify a column name as a function argument, the function is applied for each item in the result set:

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

Typically, if any argument to a built-in function is NULL, the result value is also NULL:

  1. select cos(null);
  2. +-----------+
  3. | cos(null) |
  4. +-----------+
  5. | NULL      |
  6. +-----------+
  8. select power(2,null);
  9. +----------------+
  10. | power(2, null) |
  11. +----------------+
  12. | NULL           |
  13. +----------------+
  15. select concat('a',null,'b');
  16. +------------------------+
  17. | concat('a', null, 'b') |
  18. +------------------------+
  19. | NULL                   |
  20. +------------------------+

Aggregate functions are a special category with different rules. These functions calculate a return value across all the items in a result set, so they require a FROM clause in the query:

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

Aggregate functions also ignore NULL values rather than returning a NULL result. For example, if some rows have NULL for a particular column, those rows are ignored when computing the AVG() for that column. Likewise, specifying COUNT(col_name) in a query counts only those rows where col_name contains a non-NULL value.

Aggregate functions are a special category with different rules. These functions calculate a return value across all the items in a result set, so they do require a FROM clause in the query:

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

Aggregate functions also ignore NULL values rather than returning a NULL result. For example, if some rows have NULL for a particular column, those rows are ignored when computing the AVG() for that column. Likewise, specifying COUNT(col_name) in a query counts only those rows where col_name contains a non-NULL value.

Analytic functions are a variation on aggregate functions. Instead of returning a single value, or an identical value for each group of rows, they can compute values that vary based on a “window” consisting of other rows around them in the result set.

Parent topic: Impala SQL Language Reference