Skip to content
Advertisement

Casting the Bigint number Returns NULL

I need o convert a integer value to the highest data type in hive as my value is of 25 digits

select cast(18446744073709551614 as bigint); 

NULL value will be returned for the above select stmnt;

I am very well aware that the supplied number is greater than the largest number of Bigint. But we are getting such values upon which i have to calculate the max,min,sum,avg

So how can i cast this type of values so that i will not get the NULLs.

Advertisement

Answer

Use decimal(38, 0) for storing numbers bigger than BIGINT, it can store 38 digits. BIGINT can store 19 digits. Read also manual about decimal type.

For literals postfix BD is required. Example:

hive> select CAST(18446744073709551614BD AS DECIMAL(38,0))+CAST(18446744073709551614BD AS DECIMAL(38,0));
OK
36893488147419103228
Time taken: 0.334 seconds, Fetched: 1 row(s)
hive> select CAST(18446744073709551614BD AS DECIMAL(38,0))*2;
OK
36893488147419103228
Time taken: 0.129 seconds, Fetched: 1 row(s)
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement