Case Statement in Hive

Hive supports Case statements to check the conditions against the column values. If any of the condition is true, it will stop to check the other conditions and returns the value that specified in THEN clause. In case none of the condition is met, it will return the value in the ELSE clause.

Case statement Syntax

CASE
	WHEN condition_1 THEN result_1
	WHEN condition_2 THEN result_2
	ELSE result_n
END

Example 1 : Simple CASE statement in Hive

Lets see the Case statement in Hive with examples. Consider that we have a Student marks table with the columns such as Student_Id, Name and Mathematics marks in percentage.

We need to find whether the student passed in Mathematics or not based on their marks. Here are the conditions to decide pass/fail in Mathematics.

• Marks greater than or equal to 65 is Pass
• Marks below 65 is Fail.

Lets write the Case statement on Student_Marks table in Hive and get required results. The Mathematics marks present in the third column of the below table.

The Case statement should be write with the following conditions as below in Hive select query.

CASE 
    WHEN Marks >= 65 THEN 'Pass'
    WHEN Marks <65 THEN 'Fail'
    ELSE 'No_Marks'
END as Result

The case conditions in the select query is validated the marks successfully and returned the result as ‘Pass’ or ‘Fail’.

Recommended Articles

• If condition in Hive with examples
• Sum function with Case expression in Hive

Your Suggestions

Suggest Article