json - 删除/映射 Hive 表上的重复键？

Question

我有 JSON 文件要加载到 Hive 表，但它包含使所有数据为空或无法在 Hive 上选择查询的重复键。

那些 JSON 文件有这样的东西:

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

我尝试创建配置单元表

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';

如何让它变成可查询的hive表？

Answer 1

与 Hive 发行版附带的 JSON SerDe 配合良好

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 |
+-----+--------+-------------+