hive 中列转行 hive列转行函数介绍

目录

一、行转列

1.函数说明

concat

concat_ws

collect_set

2.数据准备

3.需求

4.数据准备

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

创建hive表

向表中导入数据

验证表数据

5.按需求查询数据

第一步

第二步

二.列转行

1.函数说明

split

explode

lateral view

2.数据准备

 3.需求

 4.数据准备

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

创建hive表

向表中导入数据

验证表数据

5.按需求查询数据

第一步

第二步

6.情况简单下，多用如下方式

---

一、行转列

1.函数说明

concat

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

concat_ws

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

collect_set

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

2.数据准备

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

3.需求

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

 

4.数据准备

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

[root@hadoop dool]# vim constellation.txt
孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A

创建hive表

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

向表中导入数据

load data local inpath "/usr/word/dool/constellation.txt"
into table person_info;

验证表数据

select * from person_info;

导入成功，如下：

0: jdbc:hive2://192.168.171.151:10000> select * from person_info;
+-------------------+----------------------------+-------------------------+--+
| person_info.name  | person_info.constellation  | person_info.blood_type  |
+-------------------+----------------------------+-------------------------+--+
| 孙悟空               | 白羊座                        | A                       |
| 大海                | 射手座                        | A                       |
| 宋宋                | 白羊座                        | B                       |
| 猪八戒               | 白羊座                        | A                       |
| 凤姐                | 射手座                        | A                       |
|                   | NULL                       | NULL                    |
+-------------------+----------------------------+-------------------------+--+
6 rows selected (1.343 seconds)

5.按需求查询数据

第一步

将星座（constellation）和血型（blood_type）用逗号连接

0: jdbc:hive2://192.168.171.151:10000> select
. . . . . . . . . . . . . . . . . . > name,
. . . . . . . . . . . . . . . . . . > concat(constellation,",",blood_type) base
. . . . . . . . . . . . . . . . . . > from
. . . . . . . . . . . . . . . . . . > person_info;
+-------+--------+--+
| name  |  base  |
+-------+--------+--+
| 孙悟空   | 白羊座,A  |
| 大海    | 射手座,A  |
| 宋宋    | 白羊座,B  |
| 猪八戒   | 白羊座,A  |
| 凤姐    | 射手座,A  |
|       | NULL   |
+-------+--------+--+
6 rows selected (1.072 seconds)

第二步

在第一步的基础上根据星座,血型(base)进行分组，通过collect_set将同组多行数据根据name字段聚合成一个数组，再通过concat_ws对数组进行拆分拼接

0: jdbc:hive2://192.168.171.151:10000> select t1.base,
. . . . . . . . . . . . . . . . . . > concat_ws("|",collect_set(t1.name)) name
. . . . . . . . . . . . . . . . . . > from
. . . . . . . . . . . . . . . . . . > (select
. . . . . . . . . . . . . . . . . . > name,
. . . . . . . . . . . . . . . . . . > concat(constellation,",",blood_type) base
. . . . . . . . . . . . . . . . . . > from
. . . . . . . . . . . . . . . . . . > person_info) t1
. . . . . . . . . . . . . . . . . . > group by t1.base;
+----------+----------+--+
| t1.base  |   name   |
+----------+----------+--+
| NULL     |          |
| 射手座,A    | 大海|凤姐    |
| 白羊座,A    | 孙悟空|猪八戒  |
| 白羊座,B    | 宋宋       |
+----------+----------+--+
4 rows selected (40.1 seconds)

二.列转行

1.函数说明

split

Split(str, separator)：将字符串按照后面的分隔符切割，转换成字符array。

explode

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

lateral view

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

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

lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

2.数据准备

原数据表如下图

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

 3.需求

将电影分类中的数组数据展开。结果如下：

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

 4.数据准备

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

[root@hadoop dool]# vim movie.txt
《疑犯追踪》    悬疑,动作,科幻,剧情
《Lie to me》    悬疑,警匪,动作,心理,剧情
《战狼2》    战争,动作,灾难

创建hive表

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

向表中导入数据

load data local inpath "/usr/word/dool/movie.txt"
into table movie_info;

验证表数据

0: jdbc:hive2://192.168.171.151:10000> select * from movie_info;
+-------------------+-----------------------------+--+
| movie_info.movie  |     movie_info.category     |
+-------------------+-----------------------------+--+
| 《疑犯追踪》            | ["悬疑","动作","科幻","剧情"]       |
| 《Lie to me》       | ["悬疑","警匪","动作","心理","剧情"]  |
| 《战狼2》             | ["战争","动作","灾难"]            |
|                   | NULL                        |
+-------------------+-----------------------------+--+
4 rows selected (0.204 seconds)

5.按需求查询数据

第一步

用split将category切分成数组，用explode函数将category数组炸开

0: jdbc:hive2://192.168.171.151:10000> select 
. . . . . . . . . . . . . . . . . . > explode(split(category,','))
. . . . . . . . . . . . . . . . . . > from movie_info;
+------+--+
| col  |
+------+--+
| 悬疑   |
| 动作   |
| 科幻   |
| 剧情   |
| 悬疑   |
| 警匪   |
| 动作   |
| 心理   |
| 剧情   |
| 战争   |
| 动作   |
| 灾难   |
+------+--+
12 rows selected (0.158 seconds)

第二步

用lateral view函数对原表的movie字段对炸开表进行侧写

0: jdbc:hive2://192.168.171.151:10000> select
. . . . . . . . . . . . . . . . . . > movie,
. . . . . . . . . . . . . . . . . . > category_name
. . . . . . . . . . . . . . . . . . > from movie_info
. . . . . . . . . . . . . . . . . . > lateral view 
. . . . . . . . . . . . . . . . . . > explode(split(category,',')) movie_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》        | 灾难             |
+--------------+----------------+--+
12 rows selected (0.166 seconds)

6.情况简单下，多用如下方式

• 创建hive表

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

• 查询数据

0: jdbc:hive2://192.168.171.151:10000> select
. . . . . . . . . . . . . . . . . . > movie,
. . . . . . . . . . . . . . . . . . > category_name
. . . . . . . . . . . . . . . . . . > from movie_info
. . . . . . . . . . . . . . . . . . > lateral view 
. . . . . . . . . . . . . . . . . . > explode (category) movie_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》        | 灾难             |
+--------------+----------------+--+
12 rows selected (0.13 seconds)