Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hive CAST functions return NULL values:

avatar
Rising Star

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!!

1 ACCEPTED SOLUTION

avatar
Guru

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.

View solution in original post

3 REPLIES 3

avatar
Guru

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.

avatar
Rising Star

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;

avatar
Guru

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