question Re: Hive CAST functions return NULL values: in Archives of Support Questions (Read Only)

Hive CAST functions return NULL values:

lsundar19 — Fri, 21 Oct 2016 20:20:30 GMT

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

Re: Hive CAST functions return NULL values:

gkeys — Fri, 21 Oct 2016 20:43:28 GMT

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.

Re: Hive CAST functions return NULL values:

lsundar19 — Fri, 21 Oct 2016 20:58:22 GMT

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;

Re: Hive CAST functions return NULL values:

gkeys — Fri, 21 Oct 2016 21:06:57 GMT

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