HiveQL Operators: Arithmetic and Relational Comparisons

Learn how to use arithmetic and relational operators in HiveQL to perform calculations and comparisons on your data. This guide provides clear examples and explanations of different operator types and their usage in Hive queries.

HiveQL Operators

Using Arithmetic and Relational Operators in HiveQL

HiveQL provides operators for performing arithmetic and relational comparisons on data within tables. This section illustrates their use with a sample employee table.

Setting up the Example Table

1. Select Database: Specify the database for the table:

HiveQL Command (Selecting Database)

hive> USE hql;

Copy Code
2. Create Table: Create the employee table:

HiveQL Command (Creating Table)

hive> CREATE TABLE employee (Id INT, Name STRING, Salary FLOAT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

Copy Code
3. Load Data: Load data into the table (replace the path with your data file):

HiveQL Command (Loading Data)

hive> LOAD DATA LOCAL INPATH '/home/codegyani/hive/emp_data' INTO TABLE employee;

Copy Code
4. View Data: Check the loaded data:

HiveQL Command (Viewing Data)

hive> SELECT * FROM employee;

Copy Code

Arithmetic Operators

Arithmetic operators work on numeric data types. Here's a summary:

Examples:

hive> SELECT id, name, salary + 50 FROM employee;  -- Increase salary by 50
hive> SELECT id, name, salary - 50 FROM employee;  -- Decrease salary by 50
hive> SELECT id, name, (salary * 10) / 100 FROM employee; -- Calculate 10% of salary

Relational Operators

Relational operators compare values and return boolean results (true/false or NULL if a value is NULL). They're commonly used in WHERE clauses.

Examples:

hive> SELECT * FROM employee WHERE salary >= 25000;
hive> SELECT * FROM employee WHERE salary < 25000;