6

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.
  • 17,766
  • 32
  • 107
  • 160
  • 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

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,614
  • 1
  • 15
  • 35
12

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
  • 944
  • 6
  • 8