What is Hive Query Language? HiveQL Operators

What is Hive Query Language (HiveQL)?

Hive Query Language (HiveQL) is a query language in Apache Hive for processing and analyzing structured data. It separates users from the complexity of MapReduce programming. It reuses common concepts from relational databases, such as tables, rows, columns, and schema, to ease learning. Hive provides a CLI for Hive query writing using Hive Query Language (HiveQL).

Most interactions tend to take place over a command line interface (CLI). Generally, HiveQL syntax is similar to the SQL syntax that most data analysts are familiar with. The four file formats named in the original Hive documentation are TEXTFILE, SEQUENCEFILE, ORC and RCFILE (Record Columnar File); current releases also read and write Parquet and Avro, and ORC is the format most tuning guidance assumes.

Hive uses an embedded Derby database for single-user metadata storage. For multi-user or shared metastore deployments, Hive uses MySQL or another external relational database instead, because Derby allows only one session at a time.

HiveQL Built-in Operators

Hive provides built-in operators for the data operations that run against the tables held in the Hive warehouse.

These operators act on operands inside an expression and return a value according to the logic applied, whether that logic is a comparison, a calculation or a lookup into a nested column.

Below are the main types of built-in operators in HiveQL:

• Relational operators
• Arithmetic operators
• Logical operators
• Operators on complex types
• Complex type constructors

Each family is covered in its own section below, with the full operator table for that family.

Relational Operators in HiveQL

We use relational operators for relationship comparisons between two operands.

• Operators such as equals, not equals, less than and greater than.
• The operand types are all primitive types in these operators, and the pattern-matching operators accept strings only.

Every relational operator returns a BOOLEAN, which is why these operators are what a WHERE clause, a JOIN condition and a CASE WHEN branch are built from. The following table gives details about relational operators and their usage in HiveQL:

Note that a comparison against NULL never returns TRUE or FALSE. That is the reason the table lists IS NULL and IS NOT NULL as separate operators rather than expecting X = NULL to work.

HiveQL Arithmetic Operators

We use arithmetic operators for performing arithmetic operations on operands.

• Arithmetic operations such as addition, subtraction, multiplication and division between operands use these operators.
• The operand types are all number types in these operators.

Sample example:

2 + 3 gives the result 5.

In this example, ‘+’ is the operator, 2 and 3 are the operands, and the return value is 5.

The following table gives details about arithmetic operators in Hive Query Language:

The last four rows are bitwise rather than ordinary arithmetic: they operate on the binary representation of integer operands, so &, | and ^ are not the same as the logical AND, OR and NOT covered in the next section.

HiveQL Logical Operators

We use logical operators for performing logical operations on operands.

• Logical operations such as AND, OR and NOT between operands use these operators.
• The operand types are all BOOLEAN type in these operators.

The following table gives details about logical operators in HiveQL:

Because relational operators return BOOLEAN values, logical operators are what combine them: a predicate such as salary > 50000 AND dept = ‘Sales’ chains one family into the other.

Operators on Complex Types

Relational, arithmetic and logical operators all work on single scalar values. Hive also stores array, map and struct columns, and those need a different mechanism to reach the elements inside them. The following table gives details about complex type operators, which provide that mechanism:

Both forms are ordinary expressions, so they can appear in a SELECT list, a WHERE clause or a GROUP BY, and a struct field is reached with dot notation such as S.field instead of a bracket.

Complex Type Constructors

Where the operators above read an existing complex column, constructors build one. The following table gives details about complex type constructors, which create instances of the complex data types — Array, Map and Struct — in Hive.

In this section, we are going to see the operations performed on complex type constructors.

Constructors are most often used in an INSERT that populates a complex column, or in a SELECT that packs several scalar columns into one struct before writing the result to another table.

HiveQL Built-in Functions

Operators cover comparison, calculation and element access, but they do not round a number, trim a string or subtract two dates. That work belongs to Hive’s built-in functions, which are called by name inside the same expressions the operators appear in. The table below groups the families a beginner meets first.

The function list depends on the Hive release, so it is worth reading it from the session itself rather than from a static page. Two statements do that:

SHOW FUNCTIONS;

DESCRIBE FUNCTION round;

DESCRIBE FUNCTION EXTENDED round;

SHOW FUNCTIONS lists every registered name, DESCRIBE FUNCTION prints the one-line signature, and the EXTENDED form adds usage examples where the implementing class supplies them. When no built-in function fits, Hive accepts a user defined function written in Java.

HiveQL vs SQL: Key Differences

HiveQL borrows SQL syntax deliberately, and the operator tables above will look familiar to anyone who has written SQL. The execution model underneath is not the same, and the differences show up as soon as a query moves beyond a simple SELECT.

The practical consequence for expression writing is small but real. Operators behave as expected, yet a predicate that would be answered from an index in a relational database is answered in Hive by scanning files, so filtering on a partition column is usually worth far more than tuning the operator itself.

FAQs