关于Hive使用的一些实例

行转列聚合

一、开始之前

在进行操作之前，你需要知道以下的几个函数：

1、CONCAT(string A/col, string B/col…)：

返回输入字符串连接后的结果，支持任意个输入字符串。

2、CONCAT_WS(separator, str1, str2,...)：

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

3、COLLECT_SET(col)：

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

二、准备数据

假设你拥有如下的用户数据

name	sex	age
Titan	男	18
Goodman	男	18
Cooper	男	10
Missy	女	10
Penny	女	27

三、需求分析

把相同性别的同龄人归类到一起，输出如下的结果

info	name
女-10	Missy
女-27	Penny
男-10	Cooper
男-18	Titan,Goodman

四、创建Hive表并导入数据

1、建立user_info表，将数据导入到表中

CREATE table user_info(
	name string,
    sex string,
    age string
) 
row format delimited fields terminated by "\t";

load data local inpath './users.txt' into table user_info;

2、根据需求，查询数据，使用CONCAT拼接字段，用COLLECT_SET将多行转为去重列表

SELECT
	t1.info,
	CONCAT_WS(',', COLLECT_SET(t1.name)) name
FROM
	(
	SELECT
		name, CONCAT_WS('-', sex, age) info
	FROM
		user_info ) t1
GROUP BY
	t1.info;

最后，查看输出结果

info	name
女-10	Missy
女-27	Penny
男-10	Cooper
男-18	Titan,Goodman

列转行拆分

一、开始之前

在进行操作之前，你需要知道以下的几个函数：

1、EXPLODE(col)：

将 hive 一列中复杂的 array 或者 map 结构拆分成多行。

2、LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

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

二、准备数据

假设你拥有这样的电影数据

movie	category
《疑犯追踪》	["悬疑","动作","科幻","剧情"]
《Lie to me》	["悬疑","警匪","动作","心理","剧情"]
《战狼 2》	["战争","动作","灾难"]

三、需求分析

将每个电影的分类拆分出来，展开数据

movie	category_name
《疑犯追踪》	悬疑
《疑犯追踪》	动作
《疑犯追踪》	科幻
《疑犯追踪》	剧情
《Lie to me》	悬疑
《Lie to me》	警匪
《Lie to me》	动作
《Lie to me》	心理
《Lie to me》	剧情
《战狼 2》	战争
《战狼 2》	动作
《战狼 2》	灾难

四、创建 movie_info 表并导入数据

create table movie_info(
	movie string,
	category array<string>
)
row format delimited fields terminated by "\t"
collection items terminated by ",";

hive (db_titan)> load data local inpath 'movies.txt' into table movie_info;
Loading data to table db_titan.movie_info
Table db_titan.movie_info stats: [numFiles=1, totalSize=135]
OK
Time taken: 0.556 seconds

五、根据需求，将分类列表拆分，实现列转行

select
	movie,
	category_name
from
	movie_info lateral view explode(category) table_tmp as category_name;

movie	category_name
《疑犯追踪》	悬疑
《疑犯追踪》	动作
《疑犯追踪》	科幻
《疑犯追踪》	剧情
《Lie to me》	悬疑
《Lie to me》	警匪
《Lie to me》	动作
《Lie to me》	心理
《Lie to me》	剧情
《战狼 2》	战争
《战狼 2》	动作
《战狼 2》	灾难