配置单元-是否可以从Map创建列

q9rjltbz  于 2021-06-25  发布在  Hive
关注(0)|答案(1)|浏览(376)

我正在寻求一些关于理解将配置单元中的Map转换为列(转置)的可能性的建议。下面给出了一些示例记录以及我喜欢如何表示它们。

{"AR":["Fasdfadsfasd","Hasdfasdf"]}
{"DR":["Aasdfads","Baasdfasdf","Iasdfadsf","Zasdfasdf"]}
{"PR":["Easdfadsf","Gasdfadsfads"]}
{"SR":["Casdfasdf","Dasdfadsf","Jasdfasdfa"]}

AR              DR              PR             SR
Fasdfadsfasd    Aasdfads        Easdfadsf      Casdfasdf
Hasdfasdf       Baasdfasdf      Gasdfadsfads   Dasdfadsf
NULL            Iasdfadsf       NULL           Jasdfasdfa
NULL            Zasdfasdf       NULL           NULL

谢谢你的帮助!

jjhzyzn0

jjhzyzn01#

请参见代码中的注解。并非有意将转换步骤组合在一起以使其更易于理解:

with your_table as ( --use your_table instead of this 
select stack(4,
             '{"AR":["Fasdfadsfasd","Hasdfasdf"]}',
             '{"DR":["Aasdfads","Baasdfasdf","Iasdfadsf","Zasdfasdf"]}',
             '{"PR":["Easdfadsf","Gasdfadsfads"]}',
             '{"SR":["Casdfasdf","Dasdfadsf","Jasdfasdfa"]}'
             ) as src
)

select max(AR) AR, max(DR) DR, max(PR) PR, max(SR) SR --group by position
from
(
select case when key='AR' then value end AR, --transpose
       case when key='DR' then value end DR,
       case when key='PR' then value end PR,
       case when key='SR' then value end SR,
       pos
from
(
select s.key, regexp_replace(v.value,'(^\\")|(\\"$)','') value, v.pos --remove quotes from value
from
(
select regexp_replace(m[0],'(^\\")|(\\"$)','') key, regexp_replace(m[1],'\\[|\\]','') value --remove [] from value and unquote key
from
(
select split(
       regexp_replace(src,'\\{|\\}',''), --remove curly braces
       '[:]') as m --split map key and value
  from your_table t
)s
)s lateral view outer posexplode(split(value,'\\",\\"')) v as pos, value
)s
)s 
group by pos;

结果:

ar           dr           pr           sr
Fasdfadsfasd Aasdfads     Easdfadsf    Casdfasdf
Hasdfasdf    Baasdfasdf   Gasdfadsfads Dasdfadsf
NULL         Iasdfadsf    NULL         Jasdfasdfa
NULL         Zasdfasdf    NULL         NULL

相关问题