Error with exception for integer - JSON data

[srivastavakushagra999]

Hi - we are facing an issue with this serde.We imported JSON format data using this SerDe in Hive table and when we do a select * to fetch only a particular field called balance , we get the below error - "Caused by: java.lang.NumberFormatException: For input string: "-2380.84008789063"." We expected data -2380.84008789063. The balance field declared is an int (we have tried with float as well).Table declaration is as follows - create table single_customer_kush ( SA_CUSTOMER array<struct< BALANCE:int, MNAME:string, STATE:string, SINCE:string, CUSTOMER_ID:int, C_DATA:string, LNAME:string, STREET_1:string, STREET_2:string, PHONE_NUMBER:string, CREDIT:string, ZIP:string, YTD_PAYMENT:int, CREDIT_LIMIT:string, EMAIL:string, ENAME:string, CITY:string, PAYMENT_CNT:string, DELIVERY_CNT:string, DISCOUNT:string>> ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';

and the sample data we are using is-------->

{"SA_CUSTOMER":[{"BALANCE":-2380.84008789063,"MNAME":"OE","STATE":"4 ","SINCE":"2015-11-05 0","CUSTOMER_ID":8,"C_DATA":"iyL0BmN06ggi0iR0yL0BPkR22BL2k6mkLP2R4 kLmky24kkRyk L42gkBB2kigy0g44gL4Lk PiRyLLBBRk6662wy4NNmwLPPNw00i2 0Pk6im R6wP4 wN2kwkB22PNR0NL6NyLPNPRB4iBBmyiL6gRNy6NgPk2ymPB0m6k0kyw40wg6 kLN4gi06mBkL mRgy0mN42m0PB00 PBRi4k6mBBgy k444 NN2iNw4igPkiNLRPiNmi2NgwBm 6NPy LRRL42kwk44Rw64igPRyBLLgP0m0ggw4N2Py6mNy40L 2ki 24k00BN4Rk y0 wP6 BPmwkiRR iy2NN2yw0RwiL6R62gkL0N4 R BwPyL2yiPw wR6k6wPiL4BRy4kP wmLw2imNP ymgiR2gNg 06m2L0gi6y0w","LNAME":"BARATIONCALLY","STREET_1":"wg0RwNwiwwNB","STREET_2":"kikk2gN 2gN","PHONE_NUMBER":"377-991-3480 ","CREDIT":"GC","ZIP":"802811111","YTD_PAYMENT":2380.84008789063,"CREDIT_LIMIT":50000,"EMAIL":"someone@someplace.com","ENAME":"2y44mPBiyB6iRyy","CITY":"BiNRw0B06P","PAYMENT_CNT":2,"DELIVERY_CNT":0,"DISCOUNT":0.1672}]}

Please let me know what is the core issue here.Please let m know where am I going wrong.

any help will be highly appreciated.

[srivastavakushagra999]

query we ran

select sa_customer.balance from single_customer_kush;

[rcongiu]

You are tryint to use int , which is between -2^31 and 2^31 (see https://docs.oracle.com/javase/tutorial/java/nutsandbolts/datatypes.html). You are passing 238084008789063, which is 2^47.75846 and therefore too big for int. You have to use bigint instead of int. Note that the field ytd_payment also has to be switched to bigint.

Also, it looks like the create statement you sent me got corrupted somehow, it's missing the "struct" part and the periods on the serde class. This definition works for me:

create table single_customer
(
SA_CUSTOMER array<struct<BALANCE:bigint,
MNAME:string,
STATE:string,
SINCE:string,
CUSTOMER_ID:int,
C_DATA:string,
LNAME:string,
STREET_1:string,
STREET_2:string,
PHONE_NUMBER:string,
CREDIT:string,
ZIP:string,
YTD_PAYMENT:bigint,
CREDIT_LIMIT:string,
EMAIL:string,
ENAME:string,
CITY:string,
PAYMENT_CNT:string,
DELIVERY_CNT:string,
DISCOUNT:string>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';

Last but not least, SA_CUSTOMER is an array so you need to do one of the following. WHich one depends on your data.

-- get the first one ?
select sa_customer[0].balance from single_customer;
-- explode them
 select s.balance from single_customer lateral view explode(sa_customer) sa as s;

[srivastavakushagra999]

Hi , Thanks for the prompt response .I worked on wat u suggested and here are my findings 1) First I tried with bigint and excuted the below query select SA_CUSTOMER[0].BALANCE from single_customer_bigint; got the following error -: Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row [Error getting row data with exception java.lang.NumberFormatException: For input string: "-2380.84008789063

2)Tried with Double and was able successfully fetch records in the select statement SELECT sa_customer[0].balance FROM single_customer ; this works But when I do join its giving the error SELECT a.sa_customer[0].balance FROM single_customer a JOIN single_trading b ON a.sa_customer[0].customer_id = b.sa_trading[0].customer_id;

"Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Unxpected exception: org.apache.hadoop.io.DoubleWritable cannot be cast to org.apache.hadoop.hive.serde2.io.DoubleWritable"

schema and data of second table which I used to join

create table single_trading ( SA_TRADING array<struct< CLOSE_DATE:string, INSTRUMENT_TYPE:string, UNITS:float, CREATED_DATE:string, EMPID:int, INSTRUMENT:string, ID:int, OPEN_DATE:string, CUSTOMER_ID:int, INDEXNAME:string>> ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'; data {"SA_TRADING":[{"CLOSE_DATE":"2015-08-09 16:59:37.000000000","INSTRUMENT_TYPE":"Options","UNITS":95000,"CREATED_DATE":"2011-05-03 16:59:37.000000000","EMPID":10776,"INS TRUMENT":"Instrument442","ID":442,"OPEN_DATE":null,"CUSTOMER_ID":88,"INDEXNAME":"FTSE"}]}

[srivastavakushagra999]

many Thanks :)

[rcongiu]

Looks like for double Hive uses its own DoubleWritable for backward compatibility...I pushed a change to develop that should fix it, but you have to build the serde from source. R. ------------------------------------------------------- "Good judgment comes from experience.

Experience comes from bad judgment"

On Tuesday, December 15, 2015 10:46 PM, srivastavakushagra999 <notifications@github.com> wrote:

many Thanks :) — Reply to this email directly or view it on GitHub.

[srivastavakushagra999]

Thanks will chk