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.数据准备

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.数据准备

原数据表如下图

 3.需求

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

 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)