Overview of Impala Functions

Functions let you apply arithmetic, string, or other computations and transformations to Impala data. You typically use them in SELECT lists and WHERE clauses to filter and format query results so that the result set is exactly what you want, with no further processing needed on the application side.

Scalar functions return a single result for each input row. See Impala Built-In Functions.

  1. [localhost:21000] > select name, population from country where continent = 'North America' order by population desc limit 4;
  2. [localhost:21000] > select upper(name), population from country where continent = 'North America' order by population desc limit 4;
  3. +-------------+------------+
  4. | upper(name) | population |
  5. +-------------+------------+
  6. | USA         | 320000000  |
  7. | MEXICO      | 122000000  |
  8. | CANADA      | 25000000   |
  9. | GUATEMALA   | 16000000   |
  10. +-------------+------------+

Aggregate functions combine the results from multiple rows: either a single result for the entire table, or a separate result for each group of rows. Aggregate functions are frequently used in combination with GROUP BY and HAVING clauses in the SELECT statement. See Impala Aggregate Functions.

  1. [localhost:21000] > select continent, sum(population) as howmany from country group by continent order by howmany desc;
  2. +---------------+------------+
  3. | continent     | howmany    |
  4. +---------------+------------+
  5. | Asia          | 4298723000 |
  6. | Africa        | 1110635000 |
  7. | Europe        | 742452000  |
  8. | North America | 565265000  |
  9. | South America | 406740000  |
  10. | Oceania       | 38304000   |
  11. +---------------+------------+

User-defined functions (UDFs) let you code your own logic. They can be either scalar or aggregate functions. UDFs let you implement important business or scientific logic using high-performance code for Impala to automatically parallelize. You can also use UDFs to implement convenience functions to simplify reporting or porting SQL from other database systems. See User-Defined Functions (UDFs).

  1. [localhost:21000] > select rot13('Hello world!') as 'Weak obfuscation';
  2. +------------------+
  3. | weak obfuscation |
  4. +------------------+
  5. | Uryyb jbeyq!     |
  6. +------------------+
  7. [localhost:21000] > select likelihood_of_new_subatomic_particle(sensor1, sensor2, sensor3) as probability
  8.                   > from experimental_results group by experiment;

Each function is associated with a specific database. For example, if you issue a USE somedb statement followed by CREATE FUNCTION somefunc, the new function is created in the somedb database, and you could refer to it through the fully qualified name somedb.somefunc. You could then issue another USE statement and create a function with the same name in a different database.

Impala built-in functions are associated with a special database named _impala_builtins, which lets you refer to them from any database without qualifying the name.

  1. [localhost:21000] > show databases;
  2. +-------------------------+
  3. | name                    |
  4. +-------------------------+
  5. | _impala_builtins        |
  6. | analytic_functions      |
  7. | avro_testing            |
  8. | data_file_size          |
  9. ...
  10. [localhost:21000] > show functions in _impala_builtins like '*subs*';
  11. +-------------+-----------------------------------+
  12. | return type | signature                         |
  13. +-------------+-----------------------------------+
  14. | STRING      | substr(STRING, BIGINT)            |
  15. | STRING      | substr(STRING, BIGINT, BIGINT)    |
  16. | STRING      | substring(STRING, BIGINT)         |
  17. | STRING      | substring(STRING, BIGINT, BIGINT) |
  18. +-------------+-----------------------------------+

Related statements: CREATE FUNCTION Statement, DROP FUNCTION Statement

Parent topic: Impala Schema Objects and Object Names