CODEWITHSUNDEEP
hivenullhiveql
2strange
2strange

Reputation: 33

seeing null's even after using coalesce in hive

I am getting null values in the target table even after using 

coalesce(x.coulmn, 0)

while loading the data. column is a smallint data type. Please let me know if anyone has faced this type of issue. 

coalesce(cntdim.epsd_ind, 0) as epsd_ind

this should return 0 or 1 but i am getting null in the target.

Upvotes: 1

Views: 902

Answers (1)

leftjoin
leftjoin

Reputation: 38325

Have faced the same issue when input data cannot be converted to the target data type. When it cannot be converted, Hive silently converts it to NULL during insert. To avoid this apply explicit conversion using cast():

coalesce(cast(x.coulmn as smallint), 0)

In this case if value cannot be converted to smallint, cast will return NULL, coalesce will return 0. Or use other means to make sure you are inserting compatible or the same data type exactly.

Without cast() if x.column is not null and cannot be converted to smallint, coalesce will return value, and value will be inserted as NULL:

coalesce(x.coulmn, 0)

Similar issue can be observed when you inserting strings into DATE column. If string is in a wrong format, like '2017-01-010000' it will be converted to NULL without exception. And interestingly, bigint being inserted into int in Hive 1.2.x is truncated to fit int, not converted to null.

Upvotes: 1

Related Questions