这就是你需要的
方法一:将name添加到数组中
Data
{"data":[{"identifier" : "id#1","dataA" : "dataA#1"},{"identifier" : "id#2","dataA" : "dataA#2"}]}
SQL
SET hive.support.sql11.reserved.keywords=false;
CREATE EXTERNAL TABLE IF NOT EXISTS ramesh_test (
data array<
struct<
identifier:STRING,
dataA:STRING
>
>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 'my_location';
SELECT rows.identifier,
rows.dataA
FROM ramesh_test d
LATERAL VIEW EXPLODE(d.data) d1 AS rows ;
Output
方法 2 - 不更改数据
Data
[{"identifier":"id#1","dataA":"dataA#1"},{"identifier":"id#2","dataA":"dataA#2"}]
SQL
CREATE EXTERNAL TABLE IF NOT EXISTS ramesh_raw_json (
json STRING
)
LOCATION 'my_location';
SELECT get_json_object (exp.json_object, '$.identifier') AS Identifier,
get_json_object (exp.json_object, '$.dataA') AS Identifier
FROM ( SELECT json_object
FROM ramesh_raw_json a
LATERAL VIEW EXPLODE (split(regexp_replace(regexp_replace(a.json,'\\}\\,\\{','\\}\\;\\{'),'\\[|\\]',''), '\\;')) json_exploded AS json_object ) exp;
Output