代码之家 › 专栏 › 技术社区 › Victor Mayrink

如何将字符串解析为配置单元中的映射数组

hiveql hive

0

Victor Mayrink · 技术社区 · 4 年前

我有一个从系统日志中摄取的配置单元表。数据以一种奇怪的格式(映射数组)编码,其中数组的每个元素都包含 field_name 而且是 value . 列类型为STRING。如下例所示:

select 1 as user_id, '[{"field":"name", "value":"Bob"}, {"field":"gender", "value":"M"}]' as user_info
union all
select 2 as user_id, '[{"field":"gender", "value":"F"}, {"field":"age", "value":22}, {"field":"name", "value":"Ana"}]' as user_info;

	用户信息
1
2	[{“字段”:“性别”、“价值”:“F”},{“字段”:“年龄”、“价值”:22},{“字段”:“姓名”、“价值”:“Ana”}]

请注意,数组大小并不总是相同的。我正在尝试将贴图数组转换为一个简单的贴图。那么,这就是我所期望的结果:


1	{“姓名”:“鲍勃”,“性别”:“M”}
2	{“姓名”:“安娜”,“性别”:“F”,“年龄”:22}

我计划通过3个步骤实现这一点:(1)解析字符串列以创建一个映射数组,(2)分解数组(使用横向视图),(3)收集字段列表并按 user_id

我正在努力完成第一步:解析字符串列以创建一个映射数组。任何帮助都将不胜感激

0 回复 | 直到 4 年前

1

leftjoin 4 年前

请参见代码中的注释。要转换为映射的字符串数组是由 split(user_info, '(?<=\\}) *, *(?=\\{)')

with mydata as
(select 1 as user_id, '[{"field":"name", "value":"Bob"}, {"field":"gender", "value":"M"}]' as user_info
union all
select 2 as user_id, '[{"field":"gender", "value":"F"}, {"field":"age", "value":22}, {"field":"name", "value":"Ana"}]' as user_info
)

select user_id,
       --build new map
       str_to_map(concat('name:', name, nvl(concat(',','gender:', gender),''),  nvl(concat(',','age:', age),'') )) as user_info
from 
(
select user_id, 
      --get name, gender, age, aggregate by user_id
      max(case when user_info['field'] = 'name' then user_info['value'] end) name,
      max(case when user_info['field'] = 'gender' then user_info['value'] end) gender,
      max(case when user_info['field'] = 'age' then user_info['value'] end) age
      
from      
(
select s.user_id, 
       --remove {} and ", convert to map
       str_to_map(regexp_replace(e.element,'^\\{| *"|\\}$','')) as user_info 
from
(
select user_id, regexp_replace(user_info, '^\\[|\\]$','') as user_info -- remove []
 from mydata
)s lateral view outer explode(split(user_info, '(?<=\\}) *, *(?=\\{)'))e as element --split by comma between }{ with optional spaces in between
) s
group by user_id
)s

结果:

user_id   user_info 
1        {"name":"Bob","gender":"M"}
2        {"name":"Ana","gender":"F","age":"22"}