hive 从两个数组创建映射或键/值对

我有两个具有相同数量值的数组，它们映射为 1:1。我需要从这两个数组创建一个键/值对或映射（键，值）。任何想法或提示都会有帮助。

当前表结构：

USA WEST [NUMBER,Street,City] [135,Pacific,Irvine] 
USA WEST [NUMBER,Street,City] [1672,Madison,Denver]

预期的表结构：

USA WEST [NUMBER:135,Street:Pacific,City:Irvine] 
USA WEST [NUMBER:1672,Street:Madison,City:Denver]

谢谢

基本思想是从两个数组创建一个分隔字符串，例如，NUMBER%Street%City%135%Pacific%Irvine%然后使用正则表达式替换每个标记（例如Street），及其自身及其右侧的第三个标记（Pacific)

Demo

（WITH子句仅用于演示）

假设人物% and &没有出现在正文中

with    t as 
        (
            select  stack
                    (
                        2
                       ,'USA WEST',array('NUMBER','Street','City'),array('135','Pacific','Irvine')
                       ,'USA WEST',array('NUMBER','Street','City'),array('1672','Madison','Denver')
                    ) as (c1,a1,a2)
        )
        
select  c1
       ,str_to_map
        (
            substring_index
            (
                regexp_replace
                (
                    concat_ws('%',a1,a2,'')
                   ,'(?<e1>.*?)%(?=((?<e2>.*?)%){3})'
                   ,'${e1}%${e2}&'
                )
               ,'&'
               ,size(a1)
            )
           ,'&'
           ,'%'
        )   as `map`

from    t
;

+----------+------------------------------------------------------+
|    c1    |                         map                          |
+----------+------------------------------------------------------+
| USA WEST | {"NUMBER":"135","Street":"Pacific","City":"Irvine"}  |
| USA WEST | {"NUMBER":"1672","Street":"Madison","City":"Denver"} |
+----------+------------------------------------------------------+

使用 ascii 值 1 和 2 的字符也是如此。

with    t as 
        (
            select  stack
                    (
                        2
                       ,'USA WEST',array('NUMBER','Street','City'),array('135','Pacific','Irvine')
                       ,'USA WEST',array('NUMBER','Street','City'),array('1672','Madison','Denver')
                    ) as (c1,a1,a2)
        )
        
select  c1
       ,str_to_map
        (
            substring_index
            (
                regexp_replace
                (
                    concat_ws(string(unhex(1)),a1,a2,'')
                   ,concat('(?<e1>.*?)',string(unhex(1)),'(?=((?<e2>.*?)',string(unhex(1)),'){3})')
                   ,concat('${e1}',string(unhex(1)),'${e2}',string(unhex(2)))
                )
               ,string(unhex(2))
               ,size(a1)
            )
           ,string(unhex(2))
           ,string(unhex(1))
        )   as `map`

from    t
;

+----------+------------------------------------------------------+
|    c1    |                         map                          |
+----------+------------------------------------------------------+
| USA WEST | {"NUMBER":"135","Street":"Pacific","City":"Irvine"}  |
| USA WEST | {"NUMBER":"1672","Street":"Madison","City":"Denver"} |
+----------+------------------------------------------------------+