rcongiu / Hive-JSON-Serde

Read - Write JSON SerDe for Apache Hive.
Other
733 stars 391 forks source link

org.apache.hadoop.hive.serde2.SerDeException: Row is not a valid JSON Object #235

Open rpatid10 opened 2 years ago

rpatid10 commented 2 years ago

I have one Json file and I have validated it, Its a valid json. now creating Hive table on this file and then selecting data but its showing

Error: java.io.IOException: 
org.apache.hadoop.hive.serde2.SerDeException: Row is not a valid JSON Object 
- JSONException: A JSONObject text must end with '}' at 2 [character 3 line 1] 
(state=,code=0)

when I tried to set below property.

ALTER TABLE test_rahul  SET SERDEPROPERTIES ( "ignore.malformed.json" = "true");

Then It showing Null values for all the fields. Can someone Kindly Help me.

json File:

{ 
"buyer": { 
"legalBusinessName": "test1 Company","organisationIdentifications": [{ "type": "abcd",
"identification": "test.bb@tesr"
}, 
{ 
"type": "TXID","identification": "12345678"
}
]
},
"supplier": {
"legalBusinessName": "test Company",
"organisationIdentifications": [
{
"type":"abcd","identification": "test28@test"
}
]
},
"paymentRecommendationId": "1234-5678-9876-2212-123456",
"excludedRemittanceInformation": [],
"recommendedPaymentInstructions": [{
"executionDate": "2022-06-12",
"paymentMethod": "aaaa",
"remittanceInformation": {
"structured": [{
"referredDocumentInformation": [{
"type": "xxx",
"number": "12341234",
"relatedDate": "2022-06-12",
"paymentDueDate": "2022-06-12",
"referredDocumentAmount": {
"remittedAmount": 2600.5,
"duePayableAmount": 3000
}
}]
}]
}
}]
}

Jar Added:

ADD JAR json-serde-1.3.7-SNAPSHOT-jar-with-dependencies.jar;

Create Table:

CREATE EXTERNAL TABLE IF NOT EXISTS `test`.`testerde11` 
(`buyer` STRUCT< `legalBusinessName`:STRING, `organisationIdentifications`:STRUCT< `type`:STRING, `identification`:STRING>>, 
`supplier` STRUCT< `legalBusinessName`:STRING, `organisationIdentifications`:STRUCT< `type`:STRING, `identification`:STRING>>,
`paymentRecommendationId` STRING, `recommendedPaymentInstructions` ARRAY< STRUCT< `executionDate`:STRING, `paymentMethod`:STRING, 
`remittanceInformation`:STRUCT< `structured`:STRUCT< `referredDocumentInformation`:STRUCT< `type`:STRING, 
`number`:STRING, `relatedDate`:STRING, `paymentDueDate`:STRING, `referredDocumentAmount`:STRUCT< `remittedAmount`:DOUBLE, 
`duePayableAmount`:INT>>>>>>) 
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'  
WITH SERDEPROPERTIES ( "field.delim"=",","mapping.ts" = "number") 
STORED AS textFILE LOCATION '/user/hdfs/Jsontest/';

Error Message : 

select * from test.testerde11;
Error: java.io.IOException: org.apache.hadoop.hive.serde2.SerDeException: 
Row is not a valid JSON Object - JSONException: A JSONObject text must end with '}' at 2 [character 3 line 1] (state=,code=0)

I tried multiple option but didn't worked .Can someone suggest me what is the issue here/or do I need to change the delimiter/add new properties.