0

I have json like the following in hadfs.

{"result": [{"sys_tags": {"display_value": "d1", "value": "v1"}, "user_input": {"display_value": "d2", "value": "v2"}}, {"sys_tags": {"display_value": "d1", "value": "v1"}, "user_input": {"display_value": "d2", "value": "v2"}}]}

I want to create an external table in hive to analyse the data.

I downloaded json-serde-1.3.7-jar-with-dependencies.jar and added in in hive shell. here is the query i ran 

CREATE EXTERNAL TABLE t2(result array<STRUCT<sys_tags STRUCT<display_value :STRING, value:STRING>>, STRUCT<user_input STRUCT<display_value :STRING, value:STRING>>>) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'  location 'hdfs://localhost:9000/t2';

But it' not working. Can anybody help to figure out the issue?

dtolnay
  • 9,621
  • 5
  • 41
  • 62
Manish Kumar
  • 245
  • 1
  • 3
  • 7

2 Answers2

1

There are a few mismatched < and missing : in the CREATE statement.

Try,

CREATE EXTERNAL TABLE t2(
       result array<STRUCT<sys_tags:STRUCT<display_value:STRING, value:STRING>,user_input:STRUCT<display_value:STRING, value:STRING>>>) 
       ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'  
       location 'hdfs://localhost:9000/t2';
franklinsijo
  • 17,784
  • 4
  • 45
  • 63
0

It's failing because the json attributes are not mapped properly, try this out:

CREATE EXTERNAL TABLE t2(result MAP<STRING, ARRAY<MAP<STRING,STRUCT<display_value :STRING, value:STRING>>>>) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'  location 'hdfs://localhost:9000/t2';
Rahul Sharma
  • 5,614
  • 10
  • 57
  • 91
  • Thank for prompt response Rahul. I tried using above query. table is being created without any error but it is throwing error while selecting the data from table. error is like: OK Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassCastException: org.openx.data.jsonserde.json.JSONArray cannot be cast to org.openx.data.jsonserde.json.JSONObject Time taken: 0.101 seconds – Manish Kumar Feb 09 '17 at 18:16