CODEWITHSUNDEEP
hivehiveql
Rahul Sharma
Rahul Sharma

Reputation: 5824

Hive create map from multiple columns

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.

Upvotes: 2

Views: 9924

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

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

Upvotes: 7

Related Questions