Hive CAST functions return NULL values:

[#1]

Hive CAST functions return NULL values:For example one of the staging area table column have the data like

-21.475,00,

-26.609,00,

-21.932,47,

-17.300,00(String), My expected output would be like in landing area is

-21475,00,

-26609,00,

-21932,47,

-17300.00(decimal(12,2).

Staging area column's datatype : String

Landing area table column's datatype: decimal(12,2).

During data movement from staging to Landing area. i have used insert query with select statement like

SELECT CAST(regexp_replace(regexp_replace(column1,'\\.',''),',','.') as decimal(12,2)) FROM table_name;

the above query return null values.

Kindly do the needful.

Thanks in advance!!

[#2 · Accepted solution]

If your values have any text characters in them, Hive will return null if you try to cast them to decimal. For example, if there is a trailing whitespace character it will look like a decimal but hive will see the text character (whitespace).

It looks like your values have commas after them. If so, Hive will convert to null for reasons explained above.

[#3]

Thanks for your input @Greg Keys !!!

Yes the values has whitespace. So i used to trim the values before CAST. It's working as expected.

SELECT CAST(regexp_replace(regexp_replace(TRIM(column1),'\\.',''),',','.') as decimal(12,2)) FROM table_name;

[#4]

@Sundar Lakshmanan Glad we found the problem. If you are satisfied, please accept the original answer. (That's how HCC works 🙂