Hi Snadeep, Did you find solution for this,if so can you please share the query here ... View more

Hi @Rajkumar Singh Are you to to fix the problem,even i have same requirement , for me getting null values can you share the ddl and select query for this ... View more

Hi @Shu Thanks for the help. i have json file as below format , i am creating ddl which works well,but when i do a select query it throwing error. json file::: {"purchaseid": {"ticid": "1496","ticlocation": "vizag","custnum": "222","Travleinfo": {"Trav": {"fname": "ramu","mname": "g","freq": {"fre": {"frequencynumber": "9","frequnecystatus": "na"}},"food": {"foodpref": [{"foodcode": "9","foodcodeSegment": "chic"},{"foodcode": "22","foodcodeSegment": "veg"},{"foodcode": "36","foodcodeSegment": "idl"}] },"Seats": { "Seat": [{"seatberth": "upper","loc": "s15"},{"seatberth": "lower","loc": "s215"},{"seatberth": "upper","loc": "s3"}] },"stations": { "station": [{"sationname": "vizag","trainnum": "c197"},{"sationname": "hyd","trainnum": "hyd187"},{"sationname": "wrgl","trainnum": "wr1822"}]}}},"Comments": {"comment": [{"commentno": "1","desc": "journey","passengerseat": { "intele": "09" },"passengerloc": { "intele": "s15" }},{"commentno": "5","desc": " food","passengerseat": { "intele": "09" },"passengerloc": { "intele": "s15" }},{"commentno": "12","desc": " service","passengerseat": { "intele": "09" },"passengerloc": { "intele": "s15" } }]}}} DDL::: Create EXTERNAL TABLE SRGMSBI1417.json14_07_05_01( purchaseid struct<ticid:string,ticlocation:string,custnum :string, Travleinfo:struct< trav:struct<fname:string,lname:string,mname:string, freq :struct<fre:array<struct<frequencynumber:string,frequnecystatus:string>>>, food :struct<foodpref:array<struct<foodcode:string,foodcodeSegment:string>>>, Seats :struct<Seat:array<struct<seatberth:string,loc:string>>>, stations :struct<station:array<struct<sationname:string,trainnum:string>>> >>, Comments :struct<Comment:array<struct<commentno:string,desc:string,passengerseat :struct<intele :string>,passengerloc :struct<intele :string> >>> > ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' location '/user/srgmsbi1417/json14_07_05_01'; error::: Failed with exception java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException: java.io.IOException: Start of Array ... View more

@Shu Thanks for the help. i have json file as below format , i am creating ddl which works well,but when i do a select query it throwing error. json file::: {"purchaseid": {"ticid": "1496","ticlocation": "vizag","custnum": "222","Travleinfo": {"Trav": {"fname": "ramu","mname": "g","freq": {"fre": {"frequencynumber": "9","frequnecystatus": "na"}},"food": {"foodpref": [{"foodcode": "9","foodcodeSegment": "chic"},{"foodcode": "22","foodcodeSegment": "veg"},{"foodcode": "36","foodcodeSegment": "idl"}] },"Seats": { "Seat": [{"seatberth": "upper","loc": "s15"},{"seatberth": "lower","loc": "s215"},{"seatberth": "upper","loc": "s3"}] },"stations": { "station": [{"sationname": "vizag","trainnum": "c197"},{"sationname": "hyd","trainnum": "hyd187"},{"sationname": "wrgl","trainnum": "wr1822"}]}}},"Comments": {"comment": [{"commentno": "1","desc": "journey","passengerseat": { "intele": "09" },"passengerloc": { "intele": "s15" }},{"commentno": "5","desc": " food","passengerseat": { "intele": "09" },"passengerloc": { "intele": "s15" }},{"commentno": "12","desc": " service","passengerseat": { "intele": "09" },"passengerloc": { "intele": "s15" } }]}}} DDL::: Create EXTERNAL TABLE SRGMSBI1417.json14_07_05_01( purchaseid struct<ticid:string,ticlocation:string,custnum :string, Travleinfo:struct< trav:struct<fname:string,lname:string,mname:string, freq :struct<fre:array<struct<frequencynumber:string,frequnecystatus:string>>>, food :struct<foodpref:array<struct<foodcode:string,foodcodeSegment:string>>>, Seats :struct<Seat:array<struct<seatberth:string,loc:string>>>, stations :struct<station:array<struct<sationname:string,trainnum:string>>> >>, Comments :struct<Comment:array<struct<commentno:string,desc:string,passengerseat :struct<intele :string>,passengerloc :struct<intele :string> >>> > ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' location '/user/srgmsbi1417/json14_07_05_01'; error::: Failed with exception java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException: java.io.IOException: Start of Array ... View more

hi @Shu still i am getting the same error,please find the attachment of errorerror.png ... View more

Hi i have a json as below.. { "purchaseid": { "ticketnumber": "23546852222", "location": "vizag", "Travelerhistory": { "trav": { "fname": "ramu", "lname": "gogi", "travelingarea": { "destination": { "stationid": "KAJKL", "stationname": "hyd" } }, "food": { "foodpref": [{ "foodcode": "CK567", "foodcodeSegment": "NOVEG" }, { "foodcode": "MM98", "foodcodeSegment": "VEG" } ] } } } } } i am creating a hive table structure as below... add jar /home/**********/json-serde-1.3.7-jar-with-dependencies.jar; CREATE external TABLE ds1414( ticketnumber string, location string, Travelerhistory ARRAY<struct<trav :struct<fname:string,lname:string, travelingarea :ARRAY<struct<destination :struct<stationid:string,stationname:string>>>, food :ARRAY<struct<foodpref :struct<foodcode:string,foodcodeSegment:string>>> >>> ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' location '/user/***********/json2/' ; when i do a select statement it is throwing below error...how i need to handle this Failed with exception java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException: org.codehaus.jackson.JsonParseException: Unexpected end-of-input: expected close marker for OBJECT (from [Source: java.io.ByteArrayInputStream@205df5dc; line: 1, column: 0]) at [Source: java.io.ByteArrayInputStream@205df5dc; line: 1, column: 3] Time taken: 0.212 seconds ... View more