If condition in Hive with examples
Contents
If condition/statement in Hive
Hive supports many conditional functions such as If, isnull, isnotnull, nvl, nullif, COALESCE and CASE. The If condition is used to validate the condition on the column values. In the if condition, we need to mention the true/false values to be returned. It returns valueTrue when Condition is true. It returns valueFalseOrNull when condition is false.
Syntax of If condition in Hive
|
1 |
if(boolean testCondition, T valueTrue, T valueFalseOrNull) |
The if condition accepts exactly 3 arguments in Hive. Here we specify the condition as a first argument, Then we need to mention the true and false values as a second and third argument. It allows NULL as a value in the place of valueTrue/valueFalseorNull.
Example 1: If statement in Hive
For this example, we will take the patient_details table in Hive. It conditions the patient id, name and their oxygen level as below
From this table, we want to find the patients who needs Ventilator support. As we know that, if the oxygen level goes below the 85 %, the patient needs ventilator support to breathe well.
So we need to validate the value of oxygen_level column. Here the if condition is oxygen_level < 85. If the condition is true, we will return the value as ‘Yes‘. Otherwise we will return ‘False‘. Lets implement the same in hive query with if condition.
|
1 2 3 4 |
select patient_id,name,oxygen_level, if(oxygen_level<85,'Yes','No') as ventilator_support from patient_details; |
We have added the required if condition in the above select query. It will return the values of if condition as ventilator_support.
As mentioned above, the query returned the ventilator_support details for the patients. It has the value as ‘Yes’ (True condition )for the patients with lesser than 85 % of oxygen_level. Similarly it has the value as ‘No’ (False condition) for the patients with greater than 85 % of oxygen_level.
Example 2: Sum function with if statement
Hive supports aggregate functions such as Sum to add the return values of if statement. Lets consider the same patient_details table for this example.
Now we are going to find the Number of patients who needs ventilator_support. We use the same if condition as previous query. But the return values will be different. Also we will add sum function on top of if condition.
Since we aggregate the return values of if condition, we will change the true value to 1. Then we will change the false value 0.
|
1 2 3 4 |
select sum(if(oxygen_level<85,1,0)) as num_of_ventilators from patient_details; |
As per the patients_details table, Only 2 patients who has the lesser the 85 % of oxygen_level. So the query is returned the count as 2.
Recommended Articles
