工作中常用的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 ;