arrays - 将 json 数组导入配置单元

Question

我正在尝试在配置单元中导入以下 json

[{"time":1521115600,"latitude":44.3959,"longitude":26.1025,"altitude":53,"pm1":21.70905,"pm25":16.5,"pm10":14.60085,"gas1":0,"gas2":0.12,"gas3":0,"gas4":0,"temperature":null,"pressure":0,"humidity":0,"noise":0},{"time":1521115659,"latitude":44.3959,"longitude":26.1025,"altitude":53,"pm1":24.34045,"pm25":18.5,"pm10":16.37065,"gas1":0,"gas2":0.08,"gas3":0,"gas4":0,"temperature":null,"pressure":0,"humidity":0,"noise":0},{"time":1521115720,"latitude":44.3959,"longitude":26.1025,"altitude":53,"pm1":23.6826,"pm25":18,"pm10":15.9282,"gas1":0,"gas2":0,"gas3":0,"gas4":0,"temperature":null,"pressure":0,"humidity":0,"noise":0},{"time":1521115779,"latitude":44.3959,"longitude":26.1025,"altitude":53,"pm1":25.65615,"pm25":19.5,"pm10":17.25555,"gas1":0,"gas2":0.04,"gas3":0,"gas4":0,"temperature":null,"pressure":0,"humidity":0,"noise":0}]

CREATE TABLE json_serde (
 s array<struct<time: timestamp, latitude: string, longitude: string, pm1: string>>)
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
 WITH SERDEPROPERTIES (
     'mapping.value' = 'value'
 )
 STORED AS TEXTFILE
location '/user/hduser';

导入有效但如果我尝试

Select * from json_serde;

它将从 hadoop/user/hduser 上的每个文档仅返回每个文件的第一个元素。

关于使用 json 数组有很好的文档吗？？

Answer 1

如果我可以建议您另一种方法，将整个 JSON 字符串作为 String 数据类型加载到外部表中的列中。唯一的限制是正确定义 LINES TERMINATED BY。例如如果你可能在一行中有每个 json，那么你可以创建如下表:

例如

CREATE EXTERNAL TABLE json_data_table (
    json_data String
    )   
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\u0001' LINES TERMINATED BY '\n' STORED AS TEXTFILE 
LOCATION '/path/to/json';

使用Hive get_json_object提取单个列。此命令支持基本的 xPath，例如对 json 字符串的查询

如果 json_data 列有下面的 JSON 字符串

{"store":
  {"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
   "bicycle":{"price":19.95,"color":"red"}
  },
 "email":"amy@only_for_json_udf_test.net",
 "owner":"amy"
}

下面的查询获取

SELECT get_json_object(json_data, '$.owner') FROM json_data_table;

返回 amy

通过这种方式，您可以从表中提取每个 json 元素作为列。