Hive 案例 行转列

相关函数说明

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

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

注意: CONCAT_WS must be "string or array< string >

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

concat用例：

hive (lh)> select concat('huanhuan','-','haoge');
OK
_c0
huanhuan-haoge
Time taken: 0.417 seconds, Fetched: 1 row(s)

或者

hive (lh)> select concat(name,'-',sex) from emp_sex;
OK
_c0
悟空-男
大海-男
宋宋-男
凤姐-女
婷姐-女
婷婷-女
Time taken: 0.179 seconds, Fetched: 6 row(s)

concat_ws用例

hive (lh)> select concat_ws('==','huan','hao');
OK
_c0
huan==hao
Time taken: 0.163 seconds, Fetched: 1 row(s)

或者

hive (lh)> select concat_ws('--','name',sex) from emp_sex;
OK
_c0
name--男
name--男
name--男
name--女
name--女
name--女
Time taken: 0.374 seconds, Fetched: 6 row(s)

collect_set用例：

hive (lh)> select collect_set(dept_id) from emp_sex;
Query ID = root_20210802220456_9e44be3a-2624-4e81-963f-949bc53d2269
Total jobs = 1
Launching Job 1 out of 1
Tez session was closed. Reopening...
Session re-established.

Status: Running (Executing on YARN cluster with App id application_1627885857089_0007)

--------------------------------------------------------------------------------
        VERTICES      STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
--------------------------------------------------------------------------------
Map 1 ..........   SUCCEEDED      1          1        0        0       0       0
Reducer 2 ......   SUCCEEDED      1          1        0        0       0       0
--------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 13.28 s    
--------------------------------------------------------------------------------
OK
_c0
["A","B"]

数据准备

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

需求

把星座和血型一样的人归类到一起。结果如下：

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

创建本地 person.txt，导入数据

[root@huan01 datas]$ vi person.txt
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A

创建 hive 表并导入数据

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

load data local inpath "/opt/hive/data/person.txt" into 
table person;

按需求查询数据

第一步，将星座和血型之间，加上逗号

hive (lh)> select name,concat(constellation,',',blood_type) base from person;
OK
name	base
孙悟空	白羊座,A
大海	射手座,A
宋宋	白羊座,B
猪八戒	白羊座,A
凤姐	射手座,A
Time taken: 0.215 seconds, Fetched: 5 row(s)

结果如下

在第一步的基础上，将名字之间加上 | 并展示出来

select
 t1.base,
 concat_ws('|', collect_set(t1.name)) name
from
 (select
 name,
 concat(constellation, ",", blood_type) base
 from
 person) t1
 group by t1.base;

结果如下