CODEWITHSUNDEEP
hivehiveqlhue
Alex Kerr
Alex Kerr

Reputation: 1080

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

Upvotes: 1

Views: 1869

Answers (1)

leftjoin
leftjoin

Reputation: 38290

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

Upvotes: 1

Related Questions