HiveQL 行列转换样例

列转行

数据文件内容 player.txt

詹姆斯|小皇帝|2003|骑士,热火,湖人|3
保罗|三炮|2005|黄蜂,快船,火箭|0
韦德|闪电侠|2003|热火,骑士,公牛|3
安东尼|甜瓜|2003|掘金,尼克斯,雷霆,火箭|0

需求内容

将选秀年份相同且冠军数量相同的球员姓名用+拼接展示

2003,0|安东尼
2003,3|詹姆斯+韦德
2005,0|保罗

涉及函数解析

• CONCAT_WS

CONCAT(string A/col, string B/col…)：
返回输入字符串连接后的结果，支持任意个输入字符串;  

CONCAT_WS 是一个特殊形式的 CONCAT()()。  
CONCAT_WS(separator, str1, str2,...)
第一个参数剩余参数间的分隔符。
分隔符可以是与剩余参数一样的字符串。
如果分隔符是 NULL，返回值也将为 NULL。
这个函数会跳过分隔符参数后的任何 NULL 和空字符串。
分隔符将被加到被连接的字符串之间;

• COLLECT_SET

COLLECT_SET(col)：
函数只接受基本数据类型，
它的主要作用是将某字段的值进行去重汇总，
产生array类型字段。

开始实现

创建表接收数据

CREATE TABLE NBA_PLAYER(
  player_name     string  comment "球员姓名", 
  nickname        string  comment "外号",
  begin_year      varchar(4)  comment "选秀年份",
  join_team       string  comment "效力过的球队",
  champion_count  int  comment "总冠军次数"
 )ROW FORMAT DELIMITED FIELDS TERMINATED BY "|";

把数据从本地加载进数据表中

LOAD DATA LOCAL INPATH '/home/hadoop/player.txt' 
OVERWRITE INTO TABLE NBA_PLAYER ;

拼接字段函数 CONCAT_WS

SELECT CONCAT_WS(",",begin_year,CAST(champion_count AS STRING)) group_info,player_name 
 FROM NBA_PLAYER;

-- 查询结果
2003,3  詹姆斯
2005,0  保罗
2003,3  韦德
2003,0  安东尼

完成列转行 COLLECT_SET

SELECT  A.group_info,
        CONCAT_WS("+",COLLECT_SET(A.player_name))
FROM (
       SELECT CONCAT_WS(",",begin_year,CAST(champion_count AS STRING)) group_info,
              player_name
        FROM NBA_PLAYER
      ) A
GROUP BY A.group_info;

-- 查询结果
2003,0  安东尼
2003,3  詹姆斯+韦德
2005,0  保罗

简单例子：把选秀年份相同球员找到并放到一起+分割展示

SELECT  begin_year,
        CONCAT_WS("+",COLLECT_SET(player_name))
FROM  NBA_PLAYER
GROUP BY begin_year;

--  查询结果
2003    詹姆斯+韦德+安东尼
2005    保罗 

行转列

需求内容

统计每个球队效力过球员人数

涉及函数解析

• explode（array<string>）

explode函数以array类型数据输入，然后对数组中的数据进行迭代，返回多行结果，一行一个数组元素值
ARRAY函数是将一列输入转换成一个数组输出。

Hive的表生成函数只是生成了一种数据的展示方式，
而无法在表中产生一个其他的列。   
因此这块需要使用LATERAL  VIEW 功能来进行处理。
LATERAL VIEW将explode生成的结果当做一个视图来处理。        

• LATERAL VIEW

# 使用方法
LATERAL VIEW udtf(expression) tableAlias AS columnAlias

# 用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

LATERAL VIEW 会将UDTF生成的结果放到一个虚拟表中  
然后这个虚拟表会和输入行进行join  
来达到连接UDTF外的select字段的目的

• split

# 使用方法: 
split(string str, string pat)
返回值: array

按照pat字符串分割str，会返回分割后的字符串数，  
正则表达式中的特殊符号作为分隔符时，需做转义 (前缀加上\)

实现 字符串转数组 -> 行转列 -> 分组统计

SELECT player_name,
       join_team_name
FROM (
     SELECT player_name,
            split(join_team,",")  AS array_join_team
     FROM NBA_PLAYER
     ) A  
LATERAL VIEW EXPLODE(array_join_team) tmpTable AS join_team_name;

--- 查询结果
詹姆斯 骑士
詹姆斯 热火
詹姆斯 湖人
保罗  黄蜂
保罗  快船
保罗  火箭
韦德  热火
韦德  骑士
韦德  公牛
安东尼 掘金
安东尼 尼克斯
安东尼 雷霆
安东尼 火箭

SELECT  join_team_name , 
        COUNT(*) 
FROM (
      SELECT player_name,
             join_team_name
      FROM (
           SELECT player_name,
                  split(join_team,",")  AS array_join_team
           FROM NBA_PLAYER
           ) A  
      LATERAL VIEW EXPLODE(array_join_team) tmpTable AS join_team_name
      ) B
GROUP BY join_team_name;

--- 查询结果
公牛  1
尼克斯 1
快船  1
掘金  1
湖人  1
火箭  2
热火  2
雷霆  1
骑士  2
黄蜂  1