在HIVE中将JSON格式字符串转换为数组

Question

我有一个数据库列，存储了JSON格式的字符串。该字符串本身包含像数组一样的多个元素，每个元素包含多个键值对。有些值也可能包含多个键值对，例如下面的“地址”属性。

[{"name":"abc", 
  "address":{"street":"str1", "city":"c1"},
  "phone":"1234567"
 },
 {"name":"def", 
  "address":{"street":"str2", "city":"c1"},
  "phone":"7145895"
 }
]

我的最终目标是获取JSON字符串中每个字段的单个值。我可能会使用explode()函数来实现这一点，但是explode()需要传入数组而不是字符串。因此，我的第一个目标是将JSON字符串转换为数组。请问有人知道如何做吗？非常感谢。

- jlp

Answer 1

你可以从这里开始：

select concat(‘{“name”’,data_json) from your_table q1 --re-construct your json
lateral view explode(split(json_data,’{“name”’)) json_splits as data_json --split json at each {"name" tag into array and then explode

注意：由于我目前无法访问Hive，因此我编写的代码未经测试。这肯定会为您提供一个良好的开端，或者您可以选择使用Hive SerDe来处理JSON com.cloudera.hive.serde.JSONSerDe。

- gkc123

Answer 2

根据@ruben123的建议，尤其是当您的json很复杂时，请选择Hive SerDe用于JSON。有几个可用的JSONSerDe，例如com.cloudera.hive.serde.JSONSerDe，org.openx.data.jsonserde.JsonSerDelink。

确保json格式正确，每个记录的json为一行。因此，您的json应该是：

{"name":"abc", "address":{"street":"str1", "city":"c1"}, "phone":"1234567"}
{"name":"def", "address":{"street":"str2", "city":"c1"}, "phone":"7145895"} 

创建Hive表：

CREATE TABLE sample_json (
   name STRING,
   address STRUCT<
     street: STRING,
     city: STRING>,
   phone INT )
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION '/your/hdfs/directory';

选择访问字段，只需简单地：

select name, address.street, address.city, phone from sample_json;

abc   str1  c1  1234567
def   str2  c1  7145895

注意：如果尚未安装JSONSerDe，则必须运行ADD JAR。

- nurandi

---

非常感谢您的回复。 - jlp

@jlp曾发帖表示他的列中只有一个包含JSON字符串。由于SerDe本质上是一个序列化反序列化框架，JsonSerDe只能应用于整个表，对于用户既有JSON又有非JSON的管道分隔数据怎么办？即使用户使用RegexSerde，JSON部分仍将是一个字符串。在这种情况下，我们该如何处理爆炸的JSON字符串？ - thegreatcoder