sql - Hive爆炸并从字符串中提取值

Question

伙计们，我试图从 hive 下面的字符串(列名:people)下面提取“状态”的值。问题是，该列既不是完整的JSON，也不是存储为数组。

我试图通过将'='替换为':'使它看起来像JSON，但没有帮助。

[{name=abc, org=true, self=true, status=accepted, email=abc@gmail.com}, {name=cab abc, org=false, self=false, status=needsAction, email=cab@google.com}]

以下是我使用的查询:

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

Note: There is a table already, the datatype is string, and I can't change the table schema.

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=abc@gmail.com}, {name=cab abc, org=false, self=false, status=needsAction, email=cab@google.com}]" 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        abc@gmail.com
false   cab abc false   needsAction     cab@google.com
Time taken: 1.001 seconds, Fetched: 2 row(s)