Hive Built-in Operators

This article lists all built-in operators supported by Hive 0.13.0 (HDP 2.1).

Download Your Hive Guide

Table of Contents

Predicate Operators

Operator Types Description
A = B All primitive types TRUE if expression A is equal to expression B otherwise FALSE
A <=> B All primitive types Returns same result with EQUAL(=) operator for non-null operands, but returns TRUE if both are NULL, FALSE if one of the them is NULL (as of version 0.9.0)
A == B None! Fails because of invalid syntax. SQL uses =, not ==
A <> B All primitive types NULL if A or B is NULL, TRUE if expression A is NOT equal to expression B otherwise FALSE
A != B All primitive types a synonym for the operator
A < B All primitive types NULL if A or B is NULL, TRUE if expression A is less than expression B otherwise FALSE
A <= B All primitive types NULL if A or B is NULL, TRUE if expression A is less than or equal to expression B otherwise FALSE
A > B All primitive types NULL if A or B is NULL, TRUE if expression A is greater than expression B otherwise FALSE
A >= B All primitive types NULL if A or B is NULL, TRUE if expression A is greater than or equal to expression B otherwise FALSE
A [NOT] BETWEEN B AND C All primitive types NULL if A, B or C is NULL, TRUE if A is greater than or equal to B AND A less than or equal to C otherwise FALSE. This can be inverted by using the NOT keyword. (as of version 0.9.0)
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 [NOT] LIKE B strings NULL if A or B is NULL, 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) while the % character in B matches an arbitrary number of characters in A(similar to .* in posix regular expressions) e.g. 'foobar' like 'foo' evaluates to FALSE where as 'foobar' like 'foo_ _ _' evaluates to TRUE and so does 'foobar' like 'foo%'
A [NOT] RLIKE B strings NULL if A or B is NULL, TRUE if any (possibly empty) substring of A matches the Java regular expression B, otherwise FALSE. E.g. 'foobar' RLIKE 'foo' evaluates to FALSE whereas 'foobar' RLIKE '^f.*r$' evaluates to TRUE.
A REGEXP B strings Same as RLIKE

Arithmetic Operators

Operator Types Description
A + B Numbers Gives the result of adding A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands. e.g. since every integer is a float, therefore float is a containing type of integer so the + operator on a float and an int will result in a float.
A - B Numbers Gives the result of subtracting B from A. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.
A * B Numbers Gives the result of multiplying A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands. Note that if the multiplication causing overflow, you will have to cast one of the operators to a type higher in the type hierarchy.
A / B Numbers Gives the result of dividing B from A. The result is a double type.
A % B Numbers Gives the reminder resulting from dividing A by B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.
A & B Numbers Gives the result of bitwise AND of A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.
A | B Numbers Gives the result of bitwise OR of A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.
A ^ B Numbers Gives the result of bitwise XOR of A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.
~A Numbers Gives the result of bitwise NOT of A. The type of the result is the same as the type of A.

Logical Operators

Operator Types Description
A AND B boolean TRUE if both A and B are TRUE, otherwise FALSE. NULL if A or B is NULL
A && B boolean Same as A AND B
A OR B boolean TRUE if either A or B or both are TRUE; FALSE OR NULL is NULL; otherwise FALSE
A || B boolean Same as A OR B
NOT A boolean TRUE if A is FALSE or NULL if A is NULL. Otherwise FALSE.
! A boolean Same as NOT A
A IN (val1, val2, ...) boolean TRUE if A is equal to any of the values
A NOT IN (val1, val2, ...) boolean TRUE if A is not equal to any of the values

Operators for Complex Types

Operator Types Description
A[n] A is an Array and n is an int Returns the nth element in the array A. The first element has index 0 e.g. if A is an array comprising of ['foo', 'bar'] then A[0] returns 'foo' and A[1] returns 'bar'
M[key] M is a Map and key has type K Returns the value corresponding to the key in the map e.g. if M is a map comprising of {'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'} then M['all'] returns 'foobar'
S.x S is a struct Returns the x field of S. e.g for struct foobar {int foo, int bar} foobar.foo returns the integer stored in the foo field of the struct.

Last modified: Dec 24 2015 15:22:08 UTC

If this article is incorrect or outdated, or omits critical information, please let us know. For all other issues, please see our support channels.