7

I have a table with a column double type, and I am trying to convert that from double to string.

However, when I use the cast command, it "smartly" convert that into scientific notation. like below: 

select 
    number, 
    cast(number as string), 
from ...

it looks like 

number     c1_
9999999902  9.999999902E9
9999999902  9.999999902E9
9999999902  9.999999902E9
9999999902  9.999999902E9
9999999909  9.999999909E9

Can anyone show me how to avoid converting that into scientific and keep the raw text?

B.Mr.W.
  • 18,910
  • 35
  • 114
  • 178
  • 1
    `cast(cast(number as BIGINT) as STRING)` maybe? – o-90 Sep 15 '15 at 04:18
  • 1
    DOUBLE is evil. Whatever the database you are using (Hive, Oracle, MySkull, etc) it is evil. With DOUBLEs, 999.99+0.01-1000 is not equal to zero. Use it only as a last resort, when you have absolutely no clue of the scale and precision of your data, and provision for verbose code to handle rounding errors. ===> Starting with Hive 0.13 you can use the DECIMAL(x,y) data type, at long last :-) – Samson Scharfrichter Sep 15 '15 at 22:00

2 Answers2

14

Hive converts double to scientific representation while cast to string because Hive treats double itself in a same way. Therefore, problem is not with cast to string.

See below example:

 select 9999999902.0, cast(9999999902.0 as BIGINT), cast(cast(9999999902.0 as BIGINT)  as string) from ..

Output:

OK
9.999999902E9   9999999902
Sachin Gaikwad
  • 1,014
  • 7
  • 9
13

Hive supports good old printf() function so that you can control the output format explicitly - check Language Manual UDF under "String functions"

Samson Scharfrichter
  • 8,884
  • 1
  • 17
  • 36