Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

Contributor

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'.
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

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

View solution in original post

2 REPLIES 2
Highlighted

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

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

View solution in original post

Highlighted

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

Contributor

Thank you @Dinesh Chitlangia !! That helped.

Don't have an account?
Coming from Hortonworks? Activate your account here