arrays - hive - 是否可以从 Map 创建列
问题描述
我正在寻求一些建议,以了解将 Hive 中的 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
感谢帮助!
解决方案
请参阅代码中的注释。转换步骤并未有意组合以使其更易于理解:
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
推荐阅读
- scala - Scala(Spark)连接数据框中的列
- php - PHP 从配置文件导入和存储数据的最佳方式
- c++ - 查找 boost 库时抛出错误
- react-native - 如何在 React Native 中正确渲染 axios 调用?
- apache - 外面的80端口只对IIS开放,怎么打开给其他服务和应用?
- python-3.x - 如何正确读取 CSV 文件?
- swift - 对具有 @State 的结构进行编码
- reactjs - AmplifySignOut 按钮仅在存在异步功能时才有效
- django - 有什么方法可以使用 url 而无需在视图中写入任何内容?
- wordpress - Gulp Sass 不编译模块化引导文件