What is HiveQL(Hive Query Language)?
Hive provides a CLI to write Hive queries using Hive Query Language (HiveQL). Generally HQL syntax is similar to the SQL syntax that most data analysts are familiar with.
Hive's SQL-inspired language separates the user from the complexity of Map Reduce programming. It reuses familiar concepts from the relational database world, such as tables, rows, columns and schema, to ease learning.
Most interactions tend to take place over a command line interface (CLI). Hive provides a CLI to write Hive queries using Hive Query Language (Hive-QL).
Generally, HiveQL syntax is similar to the SQL syntax that most data analysts are familiar with. Hive supports four file formats those are TEXTFILE, SEQUENCEFILE, ORC and RCFILE (Record Columnar File).
- For single user metadata storage Hive uses derby database and
- For multiple user Metadata or shared Metadata case Hive uses MYSQL
Built-in operators
Hive provides Built-in operators for Data operations to be implemented on the tables present inside Hive warehouse.
These operators are used for mathematical operations on operands, and it will return specific value as per the logic applied.
Types of Built-in Operators in HIVE are:
- Relational Operators
- Arithmetic Operators
- Logical Operators
- Operators on Complex types
- Complex type Constructors
Relational Operators:
We use Relational operators for relationship comparisons between two operands.
- Operators such as equals, Not equals, less than, greater than …etc
- The operand types are all number types in these Operators.
The following Table will give us details about Relational operators and its usage.
| Built-in Operator | Description | Operand |
| X = Y | TRUE
if expression X is equivalent to expression Y
Otherwise FALSE.
| It takes all primitive types |
| X != Y | TRUE
if expression X is not equivalent to expression Y
Otherwise FALSE.
| It takes all primitive types |
| X < Y | TRUE
if expression X is less than expression Y
Otherwise FALSE.
| It takes all primitive types |
| X <= Y | TRUE
if expression X is less than or equal to expression Y
Otherwise FALSE.
| It takes all primitive types |
| X>Y | TRUE
if expression X is greater than expression Y
Otherwise FALSE.
| It takes all primitive types |
| X>= Y | TRUE
if expression X is greater than or equal to expression Y
Otherwise FALSE.
| It takes all primitive types |
| X IS NULL | TRUE if expression X evaluates to NULL otherwise FALSE. | It takes all types |
| X IS NOT NULL | FALSE
If expression X evaluates to NULL otherwise TRUE.
| It takes all types |
| X LIKE Y | TRUE
If string pattern X matches to Y otherwise FALSE.
| Takes only Strings |
| X RLIKE Y | NULL if X or Y is NULL, TRUE if any substring of X matches the Java regular expression Y, otherwise FALSE. | Takes only Strings |
| X REGEXP Y | Same as RLIKE. | Takes only Strings |
Arithmetic Operators:
We use Arithmetic operators for performing arithmetic operations on operands
- Arithmetic operations such as addition, subtraction, multiplication and division between operands we use these Operators.
- The operand types all are number types in these Operators
Sample Example:
2 + 3 gives result 5.
In this example, '+' is theoperator and 2 and 3 are operands. The return value is 5
The following Table will give us details about Arithmetic operators
| Built-in Operator | Description | Operand |
| X + Y | It will return the output of adding X and Y value. | It takes all number types |
| X - Y | It will return the output of subtracting Y from X value. | It takes all number types |
| X * Y | It will return the output of multiplying X and Y values. | It takes all number types |
| X / Y | It will return the output of dividing Y from X. | It takes all number types |
| X % Y | It will return the remainder resulting from dividing X by Y. | It takes all number types |
| X & Y | It will return the output of bitwise AND of X and Y. | It takes all number types |
| X | Y | It will return the output of bitwise OR of X and Y. | It takes all number types |
| X ^ Y | It will return the output of bitwise XOR of X and Y. | It takes all number types |
| ~X | It will return the output of bitwise NOT of X. | It takes all number types |
Logical Operators:
We use Logical operators for performing Logical operations on operands
- Logical operations such as AND, OR, NOT between operands we use these Operators.
- The operand types all are BOOLEAN type in these Operators
The following Table will give us details about Logical operators
| Operators | Description | Operands |
| X AND Y | TRUE if both X and Y are TRUE, otherwise FALSE. | Boolean types only |
| X && Y | Same as X AND Y but here we using && symbol | Boolean types only |
| X OR Y | TRUE if either X or Y or both are TRUE, otherwise FALSE. | Boolean types only |
| X || Y | Same as X OR Y but here we using || symbol | Boolean types only |
| NOT X | TRUE if X is FALSE, otherwise FALSE. | Boolean types only |
| !X | Same as NOT X but here we using! symbol | Boolean types only |
Operators on Complex types:
The following Table will give us details about Complex Type Operators . These are operators which will provide a different mechanism to access elements in complex types.
| Operators | Operands | Description |
| A[n] | A is an Array and n is an integer type | It will return nth element in the array A. The first element has index of 0 |
| M[key] | M is a Map<K, V> and key has type K | It will return the values belongs to the key in the map |
Complex type Constructors:
The following Table will give us details about Complex type Constructors. It will construct instances on complex data types. These are of complex data types such as Array, Map and Struct types in Hive.
In this section, we are going to see the operations performed on Complex type Constructors.
| Operators | Operands | Description |
| array | (val1, val2, ...) | It will create an array with the given elements as mentioned like val1, val2 |
| Create_ union | (tag, val1, val2, ...) | It will create a union type with the values that is being mentioned to by the tag parameter |
| map | (key1, value1, key2, value2, ...) | It will create a map with the given key/value pairs mentioned in operands |
| Named_struct | (name1, val1, name2, val2, ...) | It will create a Struct with the given field names and values mentioned in operands |
| STRUCT | (val1, val2, val3, ...) | Creates a Struct with the given field values. Struct field names will be col1, col2, . |
Summary:
Hive provides some inbuilt functions and operators to manipulate the data stored in Hive warehouse. Hive is similar to SQL language, which supports all type of data operations and querying on tables and databases.
