Hive Built-in Operators

When doing typical arithmetic operations or logically comparing two operands in SQL, operators are always useful; the same is true in Hive. They are divided into four types: relational, arithmetic, logical, and complex operators.

Relational operators are “=” (Equal to), “<” (Less than to), “>” (Greater than to), “>=” (Greater than or Equal to), “<=” (Less than or Equal to), “!=” (Not equal to), “LIKE”, “IS NULL”, “IS NOT NULL”, “RLIKE” and “REGEXP.

Examples:
SELECT * FROM TestTable WHERE Col1  =  2000;
SELECT * FROM TestTable WHERE Col1  >  2000;
SELECT * FROM TestTable WHERE Col1  <  2000;
SELECT * FROM TestTable WHERE Col1  <=  2000;
SELECT * FROM TestTable WHERE Col1  >=  2000;
SELECT * FROM TestTable WHERE Col1  !=  2000;
SELECT * FROM TestTable WHERE Col1 IS NULL;
SELECT * FROM TestTable WHERE Col1 IS NOT NULL;
SELECT * FROM TestTable WHERE Col2 LIKE ‘%Smith%’;
SELECT * FROM TestTable WHERE Col2 LIKE ‘%Smith%’;
SELECT * FROM TestTable WHERE Col3 RLIKE ‘%[0-9]+%’;
This will return all the rows that has numeric values.
SELECT * FROM TestTable WHERE REGEXP (Col2, ‘Smith’);
This will return TRUE/FALSE in output based on the string match.

Arithmetic Operators are “+” (Addition), “-” (Subtraction), “*” (Multiplication), “/” (Division) and “%” (Reminder after division).

Examples:
SELECT Col1+1000 FROM TestTable;
SELECT Col1–1000 FROM TestTable;
SELECT Col1*2 FROM TestTable;
SELECT Col1/10 FROM TestTable;
SELECT Col1%10 FROM TestTable;

Logical Operators are “AND”, “OR”, etc.

Examples:
SELECT * FROM TestTable WHERE Col1 = 2000 AND Col2 = ‘Smith’; 
SELECT * FROM TestTable WHERE Col1 = 3000 OR Col2 = ‘Smith’; 

Complex Operators helps in retrieving ‘n’ the element from an Array, value of the specified key from Map, specific field from the Struct.

Examples:
SELECT Students[0] FROM Table1;
This will return first value from the array “Students”.
SELECT Team[“A”] FROM Table2;
This will return the value of the key “A” from the Struct.
SELECT Address.City FROM Table3;
This will return the city from the address, a struct column.

Hope you find this article helpful.