...
The operators and functions listed below are not necessarily up - to - date. (Hive Operators and UDFs has more current information.) In Beeline or the Hive CLI, use these commands to show the latest documentation:
...
- Relational Operators—The following operators compare the passed operands and generate a TRUE or FALSE value, depending on whether the comparison between the operands holds or not.
Relational Operator | Operand types | Description |
|---|---|---|
A = B | all primitive types | TRUE if expression A is equivalent to expression B; otherwise FALSE |
A != B | all primitive types | TRUE if expression A is not equivalent to expression B; otherwise FALSE |
A < B | all primitive types | TRUE if expression A is less than expression B; otherwise FALSE |
A <= B | all primitive types | TRUE if expression A is less than or equal to expression B; otherwise FALSE |
A > B | all primitive types | TRUE if expression A is greater than expression B] otherwise FALSE |
A >= B | all primitive types | TRUE if expression A is greater than or equal to expression B otherwise FALSE |
A IS NULL | all types | TRUE if expression A evaluates to NULL otherwise FALSE |
A IS NOT NULL | all types | FALSE if expression A evaluates to NULL otherwise TRUE |
A LIKE B | strings | TRUE if string A matches the SQL simple regular expression B, otherwise FALSE. The comparison is done character by character. The _ character in B matches any character in A (similar to . in posix regular expressions), and the % character in B matches an arbitrary number of characters in A (similar to .* in posix regular expressions). For example, |
A RLIKE B | strings | NULL if A or B is NULL, TRUE if any (possibly empty) substring of A matches the Java regular expression B (see Java regular expressions syntax), otherwise FALSE. For example, 'foobar' rlike 'foo' evaluates to TRUE and so does 'foobar' rlike '^f.*r$'. |
A REGEXP B | strings | Same as RLIKE |
...
Return Type | Aggregation Function Name (Signature) | Description |
|---|---|---|
BIGINT | count(*), count(expr), count(DISTINCT expr[, expr_.]) | count(*)—Returns the total number of retrieved rows, including rows containing NULL values; count(expr) - —Returns the number of rows for which the supplied expression is non-NULL; count(DISTINCT expr[, expr]) - —Returns the number of rows for which the supplied expression(s) are unique and non-NULL. |
DOUBLE | sum(col), sum(DISTINCT col) | returns the sum of the elements in the group or the sum of the distinct values of the column in the group |
DOUBLE | avg(col), avg(DISTINCT col) | returns the average of the elements in the group or the average of the distinct values of the column in the group |
DOUBLE | min(col) | returns the minimum value of the column in the group |
DOUBLE | max(col) | returns the maximum value of the column in the group |
...