Hive 从多列创建 map

Question

我有一个包含 id 和 12 列的表，我想从所有 12 列中创建 map :

基表:

CREATE TABLE test_transposed(
id string,
jan double,
feb double,
mar double,
apr double,
may double,
jun double,
jul double,
aug double,
sep double,
oct double,
nov double,
dec double)
ROW FORMAT SERDE
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

决赛 table :

CREATE TABLE test_map(
id string,
trans map<String,Double>)
ROW FORMAT SERDE
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

实现此目标的最佳方法是什么？我发现了类似的问题 here ，但这没有帮助。我不想将 test_transposed 数据写入文件，然后在其上创建外部表 test_map。

Answer 1

create table test_map
stored as textfile
as
select  id
       ,map
        (
             'jan',jan
            ,'feb',feb
            ,'mar',mar
            ,'apr',apr
            ,'may',may
            ,'jun',jun
            ,'jul',jul
            ,'aug',aug
            ,'sep',sep
            ,'oct',oct
            ,'nov',nov
            ,'dec',dec
        )               as trans

from    test_transposed