Search code examples
hivehiveqlhue

How to do Hive SQL IF/ELSE query?

In my Hive SQL script I want to do:

IF {$user_choice = 1} SELECT a,b,c FROM Table1; ELSE SELECT d,e,f FROM Table1;

Where user_choice is a parameter that Hive prompts for when the query is run.

What's the right syntax for this please? Or is there another way to achieve the same thing if I'm thinking about it wrong?

I'm trying to do this via the Hue editor, if that makes a difference. Thanks

Solution

  • If column types are the same, you can use CASE statements or UNION ALL in Hive:

    SELECT 
      case when ${user_choice} = 1 the a else d end col1 
      case when ${user_choice} = 1 the b else e end col2 
      case when ${user_choice} = 1 the c else f end col3 
  FROM table1;

    OR

    SELECT  a, b, c FROM table1 WHERE ${user_choice} = 1
UNION ALL
SELECT  d, e, f FROM table1 WHERE ${user_choice} = 2

    And if the data types of columns are different, or completely different scripts then call hive from shell

    if if [[ ${user_choice}  == "1" ]] ; then 
  hive -e "query one"
else 
  hive -e "query two" 
fi

    Column names also can be parametrized: https://stackoverflow.com/a/55805764/2700344