Hive create map from multiple columns

hive, hiveql

Solution

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

Problem

I have a table which contains a id and 12 columns and I want to create map out of all 12 columns: Base table: ``` 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' ``` Final 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' ``` what is the best way to achieve this? I found the similar question here, but it didn't help. I don't want to write `test_transposed` data to file then create external table `test_map` on top of it.

Original source

Related problems