Hive-JSON-Serde
Hive-JSON-Serde copied to clipboard
org.apache.hadoop.hive.serde2.SerDeException: Row is not a valid JSON Object
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.