Nullpointer Exception Hive with Union All

Question

I have a query which uses union all to combine data from two tables. The first query before union all returns records but the second query returns no records ( zero records only for this run. We may have records for the next load). The issue is hive throws a Nullpointer Exception when i do not have any records from the second query.

select col1,col2,col3 from A
union all
select col1,col2,col3 from B

I have records in A table. But, number of records in B table may vary depending on each load. Query works perfectly when there are records in B table.

I'm aware of the fact that this is a bug in hive until the version 0.12. But i'm using version 0.14. Any ideas what could be the root cause.

Note: My B table is partitioned. When i remove the partition, I do not face any issue. But partition is required.

Answer 1

This may be late but did you try the other way around?

select col1,col2,col3 from B
union all
select col1,col2,col3 from A

I had the exact same issue. Union with empty set gives an NPE. But empty set union some non empty set seems to work fine for me. This may not be the best approach to this problem but a workaround until this is fixed in the next versions of hive.

Answer 2

It appears that changing the Hive engine to MapReduce will get around this problem. Simply use set hive.execution.engine=mr; prior to your query. This worked for me on Hive 1.1.

Also, since there's several posts on the interwebs regarding this problem, here are some things that did NOT solve it for me:

Surrounding the UNION ALL statements with one large SELECT (also didn't matter whether you aliased the table 'foo' or 'AS foo'): SELECT * FROM (select col1,col2,col3 from A union all select col1,col2,col3 from B ) foo

Answer 3

I was facing the same issue having hive version hive-2.3.7. It worked just using the MR engine

set hive.execution.engine=mr;