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;

Simple_Case_Function

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;

Search_Case_Function

Query Result:

Search_Case_Function_Result

Hope you like this article. Please click on the follow button to receive notifications on latest posts.

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s