Home Forums Hive / HCatalog Hive json Data

This topic contains 0 replies, has 1 voice, and was last updated by  Anupam Gupta 10 months ago.

  • Creator
    Topic
  • #44405

    Anupam Gupta
    Participant

    HI All,
    I loaded json data into hive table using json-serde-1.1.4 of rcongiu/Hive-JSON-Serde.
    data is in following format
    {
    “DocId”: “ABC”,
    “User”: {
    “Id”: 1234,
    “Username”: “sam1234″,
    “Name”: “Sam”,
    “ShippingAddress”: {
    “Address1″: “123 Main St.”,
    “Address2″: null,
    “City”: “Durham”,
    “State”: “NC”
    },
    “Orders”: [
    {
    "ItemId": 6789,
    "OrderDate": "11/11/2012"
    },
    {
    "ItemId": 4352,
    "OrderDate": "12/12/2012"
    }
    ]
    }
    }

    when I run following query
    SELECT DocId, User.Id, User.ShippingAddress.City as city,
    > User.Orders[0].ItemId as order0id,
    > User.Orders[1].ItemId as order1id
    > FROM complex_json;

    I am getting exception
    java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing writable {
    at org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:162)
    at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:50)
    at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:429)
    at org.apache.hadoop.mapred.MapTask.run(MapTask.java:365)
    at org.apache.hadoop.mapred.Child$4.run(Child.java:255)
    at java.security.AccessController.doPrivileged(Native Method)
    at javax.security.auth.Subject.doAs(Subject.java:396)
    at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1232)
    at org.apache.hadoop.mapred.Child.main(Child.java:249)
    Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing writable {
    at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:6 43)
    at org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:144)
    … 8 more
    Caused by: 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]
    at org.openx.data.jsonserde.JsonSerDe.onMalformedJson(JsonSerDe.java:382 )
    at org.openx.data.jsonserde.JsonSerDe.deserialize(JsonSerDe.java:167)
    at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:6
    … 9 more

    But when I used data format(Single line data) like
    {“DocId”:”ABC”,”User”:{“Id”:1234,”Username”:”sam1234″,”Name”:”Sam”,”ShippingAddress”:{“Address1″:”123 Main St.”,”Address2″:””,”City”:”Durham”,”State”:”NC”},”Orders”:[{"ItemId":6789,"OrderDate":"11/11/2012"},{"ItemId":4352,"OrderDate":"12/12/2012"}]}}

    and then run the above select query
    it will give me following result…

    Total MapReduce CPU Time Spent: 1 seconds 30 msec
    OK
    ABC NULL NULL NULL NULL
    Time taken: 32.255 seconds, Fetched: 1 row(s)

    I followed fowling link for this example..

    http://thornydev.blogspot.in/2013/07/querying-json-records-via-hive.html

    Kindly Help…

    Thanks,
    Sandeep

You must be logged in to reply to this topic.