Hive实践5之【分割函数（split)、行转列函数（explode)、lateral view】

1、split 函数—分割

• 作用： split()函数是用于切分数据，也就是将一串字符串切割成了一个数组，

• 语法: split(string str, string pat)
  string str ：待分割字符串
  string pat：分割符

• 返回值: array

• 说明: 按照pat字符串分割str，会返回分割后的字符串数组

举例：

hive> select split ('wo shi xiao ming',' ');
OK
_c0
["wo","shi","xiao","ming"]
Time taken: 0.096 seconds, Fetched: 1 row(s)
hive> select split('abcdef', 'c');
OK
_c0
["ab","def"]
Time taken: 0.054 seconds, Fetched: 1 row(s)

2、explode函数—行转列

• 作用： explode()函数是用于打散行的函数，将一行的数据拆分成一列
• 语法: explode(array/map类型)
  参数必须为map或array

2.1 用于array类型的数据

select explode(array_col) as new_col from table_name

• table_name 表名
• array_col 为数组类型的字段
• new_col array_col被explode之后对应的列

hive> select explode(array("wo","shi","xiao","ming")) as word;
OK
word
wo
shi
xiao
ming

2.2 用于map类型的数据

• 由于map是kay-value结构的，所以它在转换的时候会转换成两列，一列是kay转换而成的，一列是value转换而成的。

select explode(map_col) as (may_key_col, may_value_col) from table_name

• table_name 表名
• map_col 为map类型的字段
• may_key_col, may_value_col 分别对应map_col被拆分之后的map映射里的key 和 value

hive> select explode(map("ABC","2016-05","EFG","2016-09")) as (m_name,m_time);
OK
m_name	m_time
ABC	2016-05
EFG	2016-09

2.3 如何将其用入string类型的数据

• string形式的字段其实也可以转换，只需要用split函数把字段分割成一个数组的形式即可。

select explode(split(string_col,'分割符')) as new_col from table_name

• table_name 表名
• string_col 为string类型的字段
• new_col string_col被explode之后对应的列

hive> select explode(split("wo shi xiao ming",' ')) as word;
OK
word
wo
shi
xiao
ming

2.4 explode函数的局限性

• 不能关联原有的表中的其他字段, 例如:

select other_col, explode(array_col) as new_col from table_name 

• 不能与group by、cluster by、distribute by、sort by联用
• 不能进行UDTF嵌套,
• 参数只能是两种类型
• 一个select后面只能获得一个explode产生的视图，如果要显示多个列，则需要将多个视图合并。lateral view就是做这样的事的。

3、lateral view

• Lateral View 是为了优化 UDTF
• Lateral View用于和UDTF函数（explode、split）结合来使用。
• 首先通过UDTF函数拆分成多行，再将多行结果组合成一个支持别名的虚拟表。虚拟表相当于再和主表关联, 从而达到添加“UDTF生成的字段“以外字段的目的, 即主表里的字段或者主表运算后的字段。
• 主要解决在select使用UDTF做查询过程中，查询只能包含单个UDTF，不能包含其他字段、以及多个UDTF的问题

语法

lateral view UDTF(expression) table_view as  new_column;

• UDTF(expression)：复合逻辑规则的UDTF函数，最常用的explode
• table_view : 对应的虚拟表的表名
• new_col: 虚拟表里存放的有效字段

a.lateral view explode

select source_column,new_column 
from source_table 
lateral view explode(source_column) new_table as new_column;

• source_table：表示需要行转列的表
• source_column：表示 原表中的一字段
• new_table：表示 lateral view explode 生成的新表名
• new_column：表示 source_column 行转列后生成的新列名

4、小实践

4.1 单词统计

1、建立文章表

create table article(
sentence string
)
row format delimited fields terminated by '';

2、加载本地数据到表article中

load data local inpath '/mr/mr_wc/The_Man_of_Property.txt' into table article;

• 查看前五条数据

select * from article limit 5;

3、基于上述表，进行单词统计

• 首先将句子字符串形式切割，形成数组形式

select
split(sentence,' ') word
from article
limit 10;

• 行转列

select
explode(split(sentence,' ')) word
from article
limit 10;

• 分组，统计

select 
word, count(*) AS cnt
from 
(select
explode(split(sentence,' ')) word
from article
) t
group by word
limit 10;

• 可以看出，已经统计出来了，但瑕疵的是有一些特殊符号，这就需要正则匹配来处理了，下次再梳理

4.2 学生成绩相关操作

1、建立学生成绩表

create table student_score(
stu_id string,
stu_name string,
courses string,
scores string 
) 
row format delimited fields terminated by ',';

2、添加数据

3、查询每个学生所有科目的成绩并且以列的形式呈现