Ingesting JSON data into Hive using JSON-SERDE

0
votes

I am trying to ingest the below JSON data into Hive using JSON-SERDE, but get the an error. First - is this JSON data valid? If yes, how can I create this one column table of agentId with the value 123456-123456.

{"agentId":{"string":"123456-123456"}}

CREATE EXTERNAL TABLE testingjson(
    agentId map <string>
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE
LOCATION '/.../json_table';

Error while compiling statement: FAILED: ParseException line 4:20 mismatched input '>' expecting , near 'string' in map type

1
jsonhive

1 Answers

1
votes

Yes, the json is valid.

Try with below ddl and

if you want the value 123456-123456 then use select agentid['string'] from testingjson;

hive>CREATE EXTERNAL TABLE testingjson(
agentId map <string,string>
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE 
LOCATION '/.../json_table';

hive> select * from testingjson;
+-----------------------------+--+
|     testingjson.agentid     |
+-----------------------------+--+
| {"string":"123456-123456"}  |
+-----------------------------+--+
hiv> select agentid['string'] from testingjson;
+----------------+--+
|      _c0       |
+----------------+--+
| 123456-123456  |
+----------------+--+