Creating a HIVE table with structs inside structs with org.apache.hcatalog.data.JsonSerDe

mbarrientos — Tue, 08 Aug 2017 00:06:58 GMT

I'm trying to create a hive external table using org.apache.hcatalog.data.JsonSerDe library based on this example https://community.hortonworks.com/questions/28684/creating-a-hive-table-with-orgapachehcatalogdatajs.html. The thing is that when I try with a very simple json structure it works fine but when I try with a more complex one (such as having a struct inside a struct attribute) I get the org.apache.hive.service.cli.HiveSQLException: java.io.IOException: org.apache.hadoop.hive.serde2.SerDeException: java.io.IOException: Start of Object expected error when I do a simple query.

Data Example:

{ "user": { "userlocation": "California, Santa Clara", "id": 222222, "name": "Hortonworks", "screenname": "hortonworks", "geoenabled": true, "location":[{"x":-88.083131,"y":99.984982},{"x":-88.083131,"y":99.984982}] }, "tweetmessage": "Learn more about #Spark in #HDP 2.3 with @Hortonworks founder @acmurthy in this video overview http://bit.ly/1gOyr9w #hadoop", "createddate": "2015-07-24T16:30:33"}

Query:

CREATE EXTERNAL TABLE tweets ( createddate string, geolocation string, tweetmessage string, `user` struct<geoenabled:boolean, id:int, name:string, screenname:string, userlocation:string, location:struct<x: double, y: double>>) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' LOCATION '/user/root/';

Re: Creating a HIVE table with structs inside structs with org.apache.hcatalog.data.JsonSerDe

mgaido1 — Tue, 08 Aug 2017 16:20:36 GMT

I guess there are some errors in your DDL. The first one I can see is that location should be:

array<struct<x: double, y: double>>

Please try with this change and see whether it works or there are other problems.

Re: Creating a HIVE table with structs inside structs with org.apache.hcatalog.data.JsonSerDe

mbarrientos — Tue, 08 Aug 2017 21:19:19 GMT

Thanks a lot. That worked.

question Re: Creating a HIVE table with structs inside structs with org.apache.hcatalog.data.JsonSerDe in Archives of Support Questions (Read Only)

Creating a HIVE table with structs inside structs with org.apache.hcatalog.data.JsonSerDe

Re: Creating a HIVE table with structs inside structs with org.apache.hcatalog.data.JsonSerDe

Re: Creating a HIVE table with structs inside structs with org.apache.hcatalog.data.JsonSerDe