工作中常用的hive行列轉換方法

一、列轉行

1. 測試資料準備：

表dev.dev_three_kingdoms中存放三國武将各項屬性案列存放的資料。

drop table dev.dev_three_kingdoms; CREATE TABLE IF NOT EXISTS dev.dev_three_kingdoms ( kingdom           string   comment '國家', general           string   comment '武将', commander_value     int   comment '統帥值', force_value         int   comment '武力值', intelligence        int   comment '智力值', political_value     int   comment '政治值' )     COMMENT '三國武将屬性表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' NULL DEFINED AS "" STORED AS ORC tblproperties ('orc.compress'='SNAPPY');

建表語句：

資料如下：

kingdom	general	commander_value	force_value	intelligence	political_value
蜀國	關羽	95	97	75	62
蜀國	諸葛亮	92	44	100	95
魏國	曹操	96	78	91	94
魏國	張遼	93	92	78	58
吳國	周瑜	97	71	96	86
吳國	太史慈	90	93	66	58

2. 實作的功能為将每個武将的屬性轉換為按行存儲，比如：

蜀國         關羽         統帥         95

蜀國         關羽         武力         97

蜀國         關羽         智力         75

蜀國         關羽         政治         62

3. 實作方法：

         3.1 建構一個編碼表，存放武将屬性字段名和屬性分類描述資訊

表名：dev.dim_attr_code

drop table dev.dim_attr_code; CREATE TABLE IF NOT EXISTS dev.dim_attr_code ( attr_code           string   comment '屬性值編碼', attr_name           string   comment '屬性描述' )   ROW FORMAT DELIMITED   FIELDS TERMINATED BY ','   LINES TERMINATED BY '\n' STORED AS INPUTFORMAT   'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

資料手工整理出來，如下所示：

attr_code	attr_name
commander_value	統帥
force_value	武力
intelligence	智力
political_value	政治

         3.2 列轉行SQL如下

select a.kingdom  國家,                            a.general  武将,                            b.attr_name 屬性,                            a.col_value 屬性值 from ( select kingdom                      ,general                            ,split(col_str,'!')[0]  col_name                            ,split(col_str,'!')[1]  col_value from (select kingdom                                                       ,general                                         ,concat('commander_value!',commander_value,'&',                                                                                          'force_value!',force_value,'&',                                                                                          'intelligence!',intelligence,'&',                                                                                   'political_value!',political_value                                                ) cols_str from dev.dev_three_kingdoms      )t lateral  view explode(split(cols_str,'&'))  b AS col_str ) a join dev.dim_attr_code b on a.col_name=b.attr_code;

執行結果如下：

國家	武将	屬性	屬性值
蜀國	關羽	統帥	95
蜀國	關羽	武力	97
蜀國	關羽	政治	62
蜀國	關羽	智力	75
蜀國	諸葛亮	統帥	92
蜀國	諸葛亮	武力	44
蜀國	諸葛亮	政治	95
蜀國	諸葛亮	智力	100
魏國	曹操	統帥	96
魏國	曹操	武力	78
魏國	曹操	政治	94
魏國	曹操	智力	91
魏國	張遼	統帥	93
魏國	張遼	武力	92
魏國	張遼	政治	58
魏國	張遼	智力	78
吳國	太史慈	統帥	90
吳國	太史慈	武力	93
吳國	太史慈	政治	58
吳國	太史慈	智力	66
吳國	周瑜	統帥	97
吳國	周瑜	武力	71
吳國	周瑜	政治	86
吳國	周瑜	智力	96

二、行轉列

表名：dev.dev_three_kingdoms_zong

建表語句：

drop table dev.dev_three_kingdoms_zong; CREATE TABLE IF NOT EXISTS dev.dev_three_kingdoms_zong ( kingdom           string   comment '國家', general           string   comment '武将', attr_name         string   comment '統帥值', attr_value        string   comment '武力值' )     COMMENT '三國武将屬性縱表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' NULL DEFINED AS "" STORED AS ORC tblproperties ('orc.compress'='SNAPPY');

縱轉行SQL：

select kingdom  國家                      ,general 武将                            ,max(case when attr_name='統帥' then attr_value else '' end) 統帥                            ,max(case when attr_name='武力' then attr_value else '' end) 武力                            ,max(case when attr_name='智力' then attr_value else '' end) 智力                            ,max(case when attr_name='政治' then attr_value else '' end) 政治 from dev.dev_three_kingdoms_zong group by kingdom                      ,general ;