配置单元分解并从字符串中提取值

Question

伙计们，我正在尝试从蜂巢中的以下字符串(列名：人(中提取"状态"的值。问题是，该列既不是完整的 JSON，也不是存储为数组。

我试图通过将"="替换为"："来使其看起来像 JSON，这没有帮助。

[{name=abc, org=true, self=true, status=accepted, email=[e-mail]}, {name=cab abc, org=false, self=false, status=needsAction, email=[e-mail]}]

以下是我使用的查询：

SELECT 
str.name,
str.org, 
str.status 
FROM table 
LATERAL VIEW EXPLODE (TRANSLATE(people,'=',':')) exploded as str;

但我得到以下错误：

FAILED: UDFArgumentException explode() takes an array or a map as a parameter

需要输出这样的东西：

name    | org   | status
-------- ------- ------------
abc     | true  | accepted
cab abc | false | needsAction

注意：已经有一个表，数据类型是字符串，我 无法更改表架构。

Answer 1

Hive 的解决方案。它可能会被优化。阅读代码中的注释：

with your_table as ( --your data example, you select from your table instead
select "[{name=abc, org=true, self=true, status=accepted, email=[e-mail]}, {name=cab abc, org=false, self=false, status=needsAction, email=[e-mail]}]" str
)
select --get map values
m['org']    as org    , 
m['name']   as name   , 
m['self']   as self   , 
m['status'] as status , 
m['email']  as email 
from
(--remove spaces after commas, convert to map     
select str_to_map(regexp_replace(a.s,', +',','),',','=') m --map
from your_table t --replace w your table
lateral view explode(split(regexp_replace(str,'\[|\{|]',''),'}, *')) a as s --remove extra characters: '[' or '{' or ']', split and explode
)s;

结果：

OK
true    abc     true    accepted        [e-mail]
false   cab abc false   needsAction     [e-mail]
Time taken: 1.001 seconds, Fetched: 2 row(s)