1

I have JSON files to be loaded to hive table, but it contains duplicate key that make all the data null or unable to be select queried on Hive.

Those JSON file had something like this :

{"timeSeries":"17051233123","id":"123","timeseries":"17051233123","name":"sample"}

I try to create hive table 

CREATE EXTERNAL TABLE table_hive (`id` 
STRING, `name` STRING, `timeseries` STRING,`timeseries2` STRING)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( "mapping.timeseries2" = "timeSeries") 
LOCATION 'app/jsonfile.json';

how to make it become queryable hive table ?

dtolnay
  • 9,621
  • 5
  • 41
  • 62
code-addict
  • 137
  • 2
  • 12

1 Answers1

0

Works fine with the JSON SerDe that comes with the Hive distribution

create external table table_hive 
(
    id          string
   ,name        string   
   ,timeseries  string
)
row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
stored as textfile
;

select * from table_hive
;

+-----+--------+-------------+
| id  |  name  | timeseries  |
+-----+--------+-------------+
| 123 | sample | 17051233123 |
+-----+--------+-------------+
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
  • which version did you use ? i already try using this `hcatalog-core-0.12.0.2.0.11.0-1.jar` (reading somewhere that it's still use `jackson 1.x`) still no good, it store the json as table maybe but when i try do select it return error telling me the json is a malformed one by giving notification about timeseries being duplicate key detected in the json ? while if i set ignore malformed into true, all data become null. P.S: my real json file are not like this, this is sample that similar to my json file. Let me try your JSONSerDe first? – code-addict May 26 '17 at 18:09
  • `java.io.IOException:Row is not a valid JSON Object - JSONException: Duplicate key "timeseries"` when i try SELECT it. it return me something like that – code-addict May 26 '17 at 18:15
  • 1
    Tested on Hive `1.1.0-cdh5.10.0` using `hive-hcatalog-core-1.1.0-cdh5.10.0.jar` (as well as Hive `2.1.1`) – David דודו Markovitz May 26 '17 at 18:34
  • love you <3 lol scratchin my head for 3 days :))))), download it and test it with that version, works like charm lol – code-addict May 26 '17 at 19:02
  • Did you know what this kind of error is ? When ever i try to query by limit, it's working fine, but when i remove it, giving me ; `org.apache.hadoop.hive.serde2.SerDeException: java.io.IOException: Field name expected` similar to this case : https://stackoverflow.com/questions/39015237/caused-by-java-io-ioexception-field-name-expected-in-hive – code-addict May 29 '17 at 05:04
  • Open a new question for that – David דודו Markovitz May 29 '17 at 05:26
  • Okay that custom serde a bit screwed. i open new here https://stackoverflow.com/questions/44240611/hive-from-json-error – code-addict May 29 '17 at 11:19