Case Statement in Hive with examples

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

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.

Select query in Hive
Student_Marks table in Hive

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

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

Case statement example in Hive
Case statement example in Hive