What's the best way to write if/else if/else if/else in HIVE?

hive hql
Popcorn picture Popcorn · Sep 9, 2015 · Viewed 57k times · Source

Hive uses IF(condition, expression, expression), so when I want to do if / else if / else if / else, I have to do:

IF(a, 1, IF(b, 2, IF(c, 3, 4)))

Is there a better way to do this that's more readable?

Looking for something similar to the standard

if (a) {
  1
} else if (b) {
  2
} else if (c) {
  3
} else {
  4
}

Answer

YoungHobbit picture YoungHobbit · Sep 9, 2015

You can use Hive Conditional CASE WHEN function for if-else scenario. The CASE Statement will provide you better readability with the same functionality. 

CASE
  WHEN (condition1) THEN result1
  WHEN (condition2) THEN result2
  WHEN (condition3) THEN result3 
  WHEN (condition4) THEN result4
  ELSE result_default 
END AS attribute_name

Related questions

Difference between INNER JOIN and LEFT SEMI JOIN

What is the difference between an INNER JOIN and LEFT SEMI JOIN? In the scenario below, why am I getting two different results? The INNER JOIN result set is a lot larger. Can someone explain? I am trying to get …

Read More
Hive cluster by vs order by vs sort by

As far as I understand; sort by only sorts with in the reducer order by orders things globally but shoves everything into one reducers cluster by intelligently distributes stuff into reducers by the key hash and make a sort by …

Read More
Why can't hive recognize alias named in select part?

Here's the scenario: When I invoke hql as follows, it tells me that it cannot find alias for u1. hive> select user as u1, url as u2 from rank_test where u1 != ""; FAILED: SemanticException [Error 10004]: Line 1:50 Invalid table alias …

Read More