Hive - Using UNION - Casting a Column value to Null throwing exception

[#1]

In the below query, I want to force a column value to null by casting it to required datatype. This works with usual sql. But Hive is throwing the exception.

Select * from(select driverid, name, ssn from drivers where driverid<15
UNION ALL
Select driverid,name, cast(null as bigint) from drivers where driverid BETWEEN 18 AND 21) T
;

SemanticException 3:48 Schema of both sides of union should match. T-subquery2 does not have the field ssn. Error encountered near token 'drivers'.

[#2 · Accepted solution]

The error message clearly explains that your subquery does not have the field ssn. Basically this implies that when you are doing a UNION ALL operation, the interpreter expects the same schema from all the queries (meaning the same number, type, name of columns). Thus even though you have casted null to match ssn's datatype bigint, you must give it an alias as shown below.

Select*from(select driverid, name, ssn from drivers where driverid<15 

UNION ALL 

Select driverid,name, cast(nullas bigint) as ssn from drivers where driverid BETWEEN 18 AND 21) T

[#3]

Thank you @Dinesh Chitlangia !! That helped.