Alternate option for using subquery in case when clause in hive

Question

I tried executing this query but apparently hive does not support subqueries anywhere apart from from clause. Is there any workaround that I can use?

SELECT id1,
       id2,
           CASE
               WHEN EXISTS
                       (SELECT id3
                        FROM BlacklistedIds
                        WHERE id3 = "abcde") THEN 'ignore'
               ELSE 'null'
            END AS id3
 FROM BIG_TABLE

Answer 1

You can rewrite it into a left join:

SELECT id1, id2,
       if(tt.id3 is not null, 'ignore', 'null') AS id3
 FROM BIG_TABLE t
 left join BlacklistedIds tt on t.id3 = tt.id3