Search code examples
hadoophivecaseapache-hive

Insert data in a case statement in Hive

Is it possible to insert a new row of data in a case statement in Hive.

I have a basic 'team' table, with the following fields (team_id,fname,lname).

This is what I am trying to run,

SELECT team_id,fname,lname,
CASE WHEN team_id = 2 THEN insert into team values (20, 'billy', 'bob'); ELSE "" END team_id
FROM team order by team_id;

Error ParseException line 2:29 Failed to recognize predicate 'insert'. Failed rule: 'identifier' in table or column identifier

If anyone could provide and info or solution, it would be great

Frostie

Solution

  • Afaik we can't put any ddl or dml operation in case statement in hive. But workaround can be applied to solve above problem, if really needs to be solved.

    insert into table team select 20, 'billy', 'bob' from team where team_id = 2;

    Explanation:- it will insert a new record in team table if team_id=2 else nothing to insert.