The Impala Query Language and Built-in Functions - Learning Cloudera Impala - page 69

Functions
SQL has lots of built-in functions to perform calculations on the data stored in various
columns in tables. There are two different kinds of functions and Impala uses both
kinds:
• Scalar functions : These functions return a single value, which is based
on the input value. Scalar functions are mostly used with the STRING and
TIMESTAMP columns. The most common scalar functions are UCASE , LCASE ,
MID , LEN , ROUND , NOW , and FORMAT . Short descriptions of these SQL Scalar
functions follow:
• FORMAT() : This function formats the column value to the display per
user preference
• NOW() : This function returns the current system time and date as a
value
• LEN() : This function returns the length of the text value in the column
• ROUND() : This function rounds the numeric column value to the spe-
cified decimal number
• MID() : This function extracts specific characters from text values in a
column
• UCASE() : This function converts the column text value to all upper
case
• LCASE() : This function converts the column text value to all lower
case
• Aggregation Functions : These functions also return a single value after the
calculation is done on column values. Aggregation functions are operated
mostly on numeric column values. The most-used aggregation functions are
AVG , COUNT , MAX , MIN , FIRST , LAST , and SUM . We will learn a few of them in
Chapter 4 , Impala Walkthrough with an Example .
Let's go through the details of a few aggregation functions as follows:
• AVG : AVG is the aggregation function that returns the average value from a
group of numbers in the specified column, as supplied in the SQL statement.
The AVG function uses a single argument, which can be numeric or a numeric
result of other functions or expressions applied to the column value. If there is