Checking the Condition Against Column Values – Hive

Similar to relational database systems like SQL Server, Oracle, MySQL, the CASE statement in Apache Hive verifies the conditions against the values of the columns. This is equal to the IF-THEN-ELSE statement where, if one of the conditions is true, it avoids checking the other conditions and returns the value defined in the THEN clause. If none of the conditions are met, the value of the ELSE clause will be returned.

Syntax:
CASE
WHEN first_expression THEN first_result
WHEN second_expression THEN second_result
ELSE alternative_result
END

Refer to the following examples:

Method 1 – Simple Case Function

— Create the table and insert the data using Impala.
CREATE TABLE Customers(
CustomerID INT,
CustomerName STRING,
ActiveStatus BOOLEAN
);

INSERT INTO Customers VALUES(100, ‘John’, true);
INSERT INTO Customers VALUES(200, ‘Kate’, false);
INSERT INTO Customers VALUES(300, ‘Julia’, true);
INSERT INTO Customers VALUES(400, ‘Maddy’, false);

Use Hive the execute the below query.

SELECT CustomerID,
CustomerName,
ActiveStatus = CASE ActiveStatus
WHEN true THEN ‘Active’
ELSE ‘Inactive’
END
FROM Customers;

Method 2 – Searched Case Function

–Case Function With Expressions
— Use Impla-shell to create the table and insert the data.

CREATE TABLE StudentMarks(
StudentID INT,
StudentName STRING,
Subject1 INT,
Subject2 INT,
Subject3 INT
);

INSERT INTO StudentMarks VALUES(10, ‘John’, 90, 86, 79);
INSERT INTO StudentMarks VALUES(20, ‘Kate’, 69, 58, 43);
INSERT INTO StudentMarks VALUES(30, ‘Julia’, 77, 76, 87);
INSERT INTO StudentMarks VALUES(40, ‘Maddy’, 92, 91, 90);

Use Hive to execute the following query.

SELECT StudentID,
StudentName,
Subject1,
Subject2,
Subject3,
SUM(Subject1+Subject2+Subject3) TotalMarks,
CASE
WHEN SUM(Subject1+Subject2+Subject3)>250 THEN ‘A’
WHEN SUM(Subject1+Subject2+Subject3) BETWEEN 200 AND 250 THEN ‘B’
ELSE ‘C’
END as Grade
FROM StudentMarks
GROUP BY StudentID, StudentName, Subject1, Subject2, Subject3;