Hive行转列实战-CSDN博客

最近更新于 2020年11月20日星期五 18:43:31

hive中行转列

1. 数据准备

person_info.txt

name	constellation	blood_type
孙悟空  白羊座  A
大海    射手座  A
宋宋    白羊座  B
猪八戒  白羊座  A
凤姐    射手座  A

2. 需求

把星座和血型一样的人归类到一起 结果如下：
射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋

3. 创建表

create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "\t";

4. 创建本地person_info.txt后，导入数据

load data local inpath "/opt/modules/input/person_info.txt" into table person_info;

5. 按需求查询数据（分解为一步步来实现）

行转列用到的关键函数：CONCAT

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

select
name,concat(constellation,",",blood_type) constellation_blood_type
from 
person_info;

+-------+--------+----------------+
| name  |  constellation_blood_type|
+-------+--------+----------------+
| 孙悟空   | 白羊座,A  |
| 大海    | 射手座,A  |
| 宋宋    | 白羊座,B  |
| 猪八戒   | 白羊座,A  |
| 凤姐    | 射手座,A  |

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

> select t1.c_b,collect_set(t1.name)
from
(select concat_ws(",",constellation,blood_type) c_b,name
from person_info)t1
group by t1.c_b;

+---------+----------------+--+
| t1.c_b  |      _c1       |
+---------+----------------+--+
| 射手座,A   | ["大海","凤姐"]    |
| 白羊座,A   | ["孙悟空","猪八戒"]  |
| 白羊座,B   | ["宋宋"]         |
+---------+----------------+--+

> select t1.c_b,concat_ws('|',collect_set(t1.name))
from
(select concat_ws(",",constellation,blood_type) c_b,name
from person_info)t1
group by t1.c_b;

+---------+----------+--+
| t1.c_b  |   _c1    |
+---------+----------+--+
| 射手座,A   | 大海|凤姐    |
| 白羊座,A   | 孙悟空|猪八戒  |
| 白羊座,B   | 宋宋       |
+---------+----------+--+