This article explains the syntax requirement for UNION ALL clause in Hive in order for it to work. If you have a table with the following definition:
CREATE TABLE test (a int, b int);
And the following query:
SELECT * FROM (
    SELECT * FROM test
    UNION ALL
    SELECT a, 'b' FROM test
) a
will give you the error:
Error: Error while compiling statement: FAILED: SemanticException 4:23 Schema of both sides of union should match. 
a-subquery2 does not have the field b. 
Error encountered near token 'test' (state=42000,code=40000)
The reason is that UNION ALL clause requires that both sides to have exact same number of columns with matching column names. The query we had in the above example did not have an alias for ‘b’ in the right side of the UNION. So simply update the query to be the following:
SELECT * FROM (
    SELECT * FROM test
    UNION ALL
    SELECT a, 'b' AS b FROM test
) a;
will fix the issue.

3 Comments

Leave a Reply to Mohit Nihalani Cancel reply

Your email address will not be published. Required fields are marked *