使用HIVE解析JSON数组

Question

我有许多存储在一个名为jt的表格中的JSON数组，它看起来像这样：

[{"ts":1403781896,"id":14,"log":"show"},{"ts":1403781896,"id":14,"log":"start"}]
[{"ts":1403781911,"id":14,"log":"press"},{"ts":1403781911,"id":14,"log":"press"}]

每个数组都是一条记录。

我想解析这个表格，以便获取一个具有3个字段（ts、id、log）的新表（logs）。我尝试使用get_json_object方法，但该方法似乎与json数组不兼容，因为我只得到null值。

这是我测试过的代码：

CREATE TABLE logs AS 
SELECT get_json_object(jt.value, '$.ts') AS ts, 
get_json_object(jt.value, '$.id') AS id,
get_json_object(jt.value, '$.log') AS log
FROM jt;

我尝试使用其他函数，但它们似乎非常复杂。 谢谢！:)

更新！ 我通过执行正则表达式解决了我的问题：

CREATE TABLE jt_reg AS
select regexp_replace(regexp_replace(value,'\\}\\,\\{','\\}\\\n\\{'),'\\[|\\]','') as valuereg  from jt;


CREATE TABLE logs AS 
SELECT get_json_object(jt_reg.valuereg, '$.ts') AS ts, 
get_json_object(jt_reg.valuereg, '$.id') AS id,
get_json_object(jt_reg.valuereg, '$.log') AS log
FROM ams_json_reg;

- marlieg

Answer 1

我刚遇到了这个问题，在hive表中，JSON数组被存储为字符串。

解决方案有点hacky和丑陋，但它有效并且不需要使用serdes或外部UDF。

SELECT 
       get_json_object(single_json_table.single_json, '$.ts') AS ts,
       get_json_object(single_json_table.single_json, '$.id') AS id,
       get_json_object(single_json_table.single_json, '$.log') AS log
FROM ( SELECT explode (
  split(regexp_replace(substr(json_array_col, 2, length(json_array_col)-2),
            '"}","', '"}",,,,"'), ',,,,')
      ) FROM src_table) single_json_table;

我将把文本分行以方便阅读。使用substr()函数剪切字符串的首尾字符，即去掉中括号[和]。然后使用regex_replace()函数匹配JSON数组中记录之间的分隔符，并添加或更改为唯一且易于用split()函数转换成Hive JSON对象数组的分隔符。这样就可以像之前的解决方案一样使用explode()函数了。

注意，此处使用的分隔符正则表达式("}")在原始数据集中将无法工作。必须使用正则表达式("},\{")并将其替换为"},,,,{"}。

  split(regexp_replace(substr(json_array_col, 2, length(json_array_col)-2),
            '"},\\{"', '"},,,,{"'), ',,,,')

- ryan

---

这很好！还有其他做法吗？你能解释一下第一个正则表达式的用法吗？ - user 923227

丑陋但似乎是我见过的唯一可行的方法 - Diansheng

Answer 2

使用explode() 函数

 hive (default)> CREATE TABLE logs AS
                  >   SELECT get_json_object(single_json_table.single_json, '$.ts') AS ts,
                  >   get_json_object(single_json_table.single_json, '$.id') AS id,
                  >   get_json_object(single_json_table.single_json, '$.log') AS log
                  >   FROM
                  >     (SELECT explode(json_array_col) as single_json FROM jt) single_json_table ;

Automatically selecting local only mode for query
Total MapReduce jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator

hive (default)> select * from logs;
OK
ts      id      log
1403781896      14      show
1403781896      14      start
1403781911      14      press
1403781911      14      press
Time taken: 0.118 seconds, Fetched: 4 row(s)
hive (default)>

其中json_array_col是jt表中存储JSON数组的列。

hive (default)> select json_array_col from jt;
json_array_col
["{"ts":1403781896,"id":14,"log":"show"}","{"ts":1403781896,"id":14,"log":"start"}"]
["{"ts":1403781911,"id":14,"log":"press"}","{"ts":1403781911,"id":14,"log":"press"}"]

- vijay kumar

---

谢谢您的回复！对我来说不起作用。我收到了以下错误信息：编译语句时出错：FAILED: UDFArgumentException explode()需要一个数组或映射作为参数。我将“json_array_col”替换为“value”。 - marlieg

value 的数据类型是什么？您能粘贴以下内容吗：desc jt; - vijay kumar

列名: 值, "数据类型": "字符串" - marlieg

正如您在问题中提到的，您正在存储JSON数组，我使用explode()函数给出了解决方案。explode()函数需要数组/映射作为参数，这就是为什么它会出现错误的原因。 - vijay kumar

如果您的值类型是字符串，则必须编写UDF将逗号分隔的JSON字符串转换为数组并使用explode。最简单的方法是在将数据存储到Hive表时，将值数据类型设置为Array<String>。 - vijay kumar

Answer 3

因为get_json_object不支持json数组字符串，所以你可以将其连接到一个json对象中，像这样：

SELECT 
    get_json_object(concat(concat('{"root":', jt.value), '}'), '$.root')
FROM jt;

- lingjue8848

---

结果的类型是String，至少在Hive 1.1上（这已经有点老了...）。 - Potatoswatter

我的结果也是字符串类型 - Raja