question TRIM on Hive Decimal Datatype in Archives of Support Questions (Read Only)

TRIM on Hive Decimal Datatype

grajagopal — Tue, 03 Nov 2015 07:08:43 GMT

I'm working on a scenario where a good number of queries uses TRIM(DECIMAL VALUE) in Hive 0.11 (Pivotal 1.0). After migration to HDP 2.2 (Hive 0.14), the queries started failing with error "Trim Takes only STRING/CHAR/VARCHAR types. Found Decimal". If I tried to run the same query as TRIM(STRING(DECIMAL VALUE)), it runs fine in Hive 0.14. Due to the volume of queries that should under go this casting change, is it possible to set this Implicit casting at a global level (say SET). Are there any workaround to trim decimal values in Hive 0.14 (which was supported in 0.11).

Thank you!

Re: TRIM on Hive Decimal Datatype

deepesh1 — Tue, 03 Nov 2015 09:31:23 GMT

No there is no global way to set casting in Hive. What do you want to achieve by trimming decimal, do you want to use trim to remove trailing zeros? Looks like even Hive 0.11 trim was for removing the leading and trailing space characters. Its just that now they do explicit type check for string/var/varchar before trimming spaces which seem like the right behavior.

Re: TRIM on Hive Decimal Datatype

grajagopal — Tue, 03 Nov 2015 11:07:15 GMT

Thanks Deepesh. I'm working with a customer where they have coded the existing Informatica Workflows (BDE) with TRIM on Decimal value in prod. When we are trying to migrate HDP and run them on Hive 0.14, it started erroring out in lower environment. We stated Hive is working as intended to, but wanted to check if there are any workarounds.

Thanks!