Hive学习笔记-HiveSQL(4)-Hive函数重要应用案例

Hive多字节分隔符

前面我们提到，在Hive加载表的时候可以手动指定不同种类的分隔符，但是这有一个限制，就是只能使用单字节分隔符，即char类型的分隔符。如果我们的真实数据中分隔符超过单字节，而我们不做任何处理就直接套用之前的语法，得到的结果将不会符合我们的预期。还有一种情况是在数据的字段内容中包含了分隔符，原本的语法也无法解决这类问题。因此针对多字节分隔符以及分隔符出现在字段中的情况，我们应该进行特殊的处理

第一种处理方法就是进行分隔符的替换。我们利用程序提前对原始数据进行处理，将其中的多字节分隔符替换为单字节分隔符，之后再利用之前的语法

第二种处理方法是使用RegexSerDe进行正则加载

前面我们也提到，默认情况下Hive载入数据的过程中使用的SerDe类是LazySimpleSerDe。实际上Hive还提供了其他多种SerDe用于解析和加载不同类型的数据文件，常用的有ORCSerDe、RegexSerDe、JsonSerDe等，这里我们需要使用到的是RegexSerDe。

RegexSerDe可以处理加载特殊数据的问题，它使用正则匹配来加载数据。我们通过书写正则表达式，指定列分隔的模式，之后Hive根据这个正则表达式来进行数据的匹配。

举例来说，我们现在的字段使用||双竖线进行分隔，一共有3个字段，即类似于：

1
xxx||xxx||xxx

则我们可以用如下的正则表达式来定义这一列：

1
(.*)\\|\\|(.*)\\|\\|(.*)

RegexSerDe的使用是在表加载的时候指定。这种情况下，对我们的要求就是要写出符合对应模式的正则表达式。之后再使用load进行数据加载，就能够得到符合预期的结果了

1
2
3
4
5
6
7
create table table_name(
    col1 string,
    col2 string,
    col3 string
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' --指定RegexSerDe类
with serdeproperties('input.regex'='(.*)\\|\\|(.*)\\|\\|(.*)') --指定正则表达式

第三种处理方法是自定义InputFormat。我们知道Hive在读取底层HDFS文件的时候，还是通过InputFormat来读入的。而我们可以通过自定义InputFormat来修改读入的逻辑，在读入的时候将其中的多字节分隔符和特殊情况进行处理，处理之后的结果能够被Hive的默认行为处理即可

使用我们自定义的InputFormat需要首先将程序打包成jar包，然后添加到Hive的classpath中，使用add jar命令进行添加，之后在创建表的时候指定InputFormat为自定义的InputFormat，再进行数据加载即可

1
2
3
4
5
6
7
8
9
10
create table table_name(
    col1 string,
    col2 string,
    col3 string
)
row format delimited fields terminated by '|'
--指定使用自定义的类来解析
stored as
inputformat 'xxx.MyInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

虽然以上三种方式都可以解决问题，但是角度各不同，开发成本也各有高低。整体上推荐使用正则加载的方式来实现对于特殊数据的处理

URL解析函数

Hive中的URL解析函数主要是parse_url和parse_url_tuple

parse_url函数是Hive中提供的最基本的URL解析函数，可以根据指定的参数从URL中解析出对应的参数进行返回。这是普通的一进一出的UDF函数，因此如果要查询多个字段的话，则需要写多个parse_url函数

1
2
3
--语法
parse_url(url, key)
--可选的key有:host、path、query、ref、protocol、authority等

parse_url_tuple函数则可以通过一次性指定多个参数，从URL中解析出多个参数的值，然后返回多列。在效果表现上，parse_url_tuple接受一行URL，同样会返回一行，但是存在多列，列字段分别为指定查询的参数。

1
2
--语法
parse_url_tuple(url, key1, key2, ...)

虽然从表现上看，parse_url_tuple是一进一出，但是这是一个UDTF表生产函数。因此在使用的时候，可以配合侧视图进行使用，或者说将其当成简单的UDF函数使用是不行的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--单个lateral view使用
select
	a.url as url,
    b.host as host,
    b.path as path,
    b.query as query
from table_url a 
lateral view parse_url_tuple(url,"HOST","PATH","QUERY") b as host,path,query;

--多个lateral view
select
	a.url as url,
    b.host as host,
    b.path as path,
    c.protocol as protocol,
    c.query as query
from table_url a
         lateral view parse_url_tuple(url,"HOST","PATH") b as host,path
         lateral view parse_url_tuple(url,"PROTOCOL","QUERY") c as protocol,query;

注意侧视图的底层类似于inner join，因此如果UDTF不产生数据时，侧视图与原表关联的结果将为空，但是如果加上outer关键字后，就会保留原表数据，类似于outer join

1
2
3
select ...
from ...
lateral view outer UDTF(...) as col

行列转换

行列转化主要涉及到表形式的问题。在一些场景下，表形式的转换会更适合业务的要求。以下的情况中将表形式抽象为比较简单的模式，而不涉及具体的业务含义

多行转多列

多行转多列，情况如下：

这种类型我们可以使用case-when函数和group by来实现。首先对数据进行分组，然后通过查询构造不同的列，针对不同情况进行不同的查询（case-when函数也可以使用if函数代替）。注意由于这里使用了group by，因此在select之后需要使用聚合函数。

1
2
3
4
5
6
7
select
	col1,
	max(case col2 when 'c' then col3 else 0 end) as c,
	max(case col2 when 'd' then col3 else 0 end) as d,
	max(case col2 when 'e' then col3 else 0 end) as e,
from table_name
group by col1;

多行转单列

多行转单列，情况如下：

这种类型我们同样使用group by进行分组，之后使用聚合函数中的数据收集函数。当然这里可能涉及到数据类型的细节问题，具体分析即可

这里先列出可能会使用到的函数以及对应的简要说明：

  • concat函数：实现字符串拼接，但是不可以指定分隔符（如果任意一个函数为null，结果为null）
  • concat_ws函数：实现字符串拼接，可以指定分隔符，接受单个字符串或者字符串数组（如果任意一个元素不为null，则结果不为null）
  • collect_list：用于将一列中的多行合并为一行，不进行去重
  • collect_set：用于将一列中的多行合并为一行，进行去重

则应用上面的函数，我们可以写出如下的查询代码：

1
2
3
4
5
select
	col1, col2,
	concat_ws(',',collect_list(cast(col3 as string))) as col3
from table_name
group by col1, col2;

多列转多行

多列转多行，情况如下：

这种类型我们需要使用union关键字，查询不同的情况之后进行拼接即可，实现如下：

1
2
3
4
5
select col1， 'c' as col2, col2 as col3 from table_name
union all
select col1， 'd' as col2, col3 as col3 from table_name
union all
select col1， 'e' as col2, col3 as col3 from table_name

单列转多行

单列转多行，情况如下：

这种类型主要就是需要使用到explode函数，它可以将一个复杂类型的字段进行元素展开。实现如下：

1
2
3
4
5
select
	col1, col2, 
	sone_name.col3 as col3
from table_name
lateral view explode(split(col3, ',')) some_name as col3;

json数据处理

Hive中为了实现json格式的数据解析，提供了两种解析数据的方式

  1. 使用get_json_object和json_tuple函数，这两个函数都可以实现将json数据中的每个字段独立解析出来，构建成表
  2. 使用JsonSerde类，在建表的时候指定Serde，加载json文件到表中。这会自动将文件解析为对应的表格式

get_json_object函数可以用于解析json，从json字符串中返回指定某个对象列的值。函数一共有两个参数，第一个参数是要解析的json字符串，第二个参数是指定要返回的字段，可以通过$.columnName的方式来指定path。不过这种方式每次只能返回json对象中一列的值，如果要获取多个字段只能重复使用函数

1
2
3
4
5
6
7
--语法
get_json_object(json_txt, path)

--举例
select
	get_json_object(json, '$.key') as key
from table_name;

json_tuple函数也可以用于json的解析，并且支持指定多个参数从而返回多个列的值。返回的每一列都是字符串类型。这也是一个UDTF表生成函数，一般也是搭配lateral view使用，类似于上面说到的URL解析函数中的parse_url_tuple。

1
json_tuple(jsonStr, path1, path2, ...)

上述解析json的过程是将数据作为一个字符串加载到表中，要使用的时候再通过json解析函数对字符串进行解析，灵活性较高。还可以在数据加载的时候就将json文件直接解析加载，使用的是专门用于解析json文件的Serde解析器。在创建表的时候，只要指定使用JSONSerde，之后加载数据的时候就会自动对Json文件中的每一列进行解析

1
2
3
4
5
6
create table(
    key1 string,
    key2 string
)
row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
stored as textfile;

两种解析json的方式不同，如果数据中每一行只有个别字段是json格式字符串，就可以使用json函数来实现处理，但是如果数据加载的文件整体就是json文件，每一行数据就是一个json数据，那么建议直接使用JsonSerde来实现处理最为方便。

拉链表的设计与实现

Hive在实际工作中主要用于构建离线数仓，定期从各种数据源中同步采集数据到Hive中，然后经过分层转换提供数据应用。由于数据定期会更新，那么就存在新数据和老数据如何设计安排的问题。例如在用户状态场景下，可能一天后有些用户的状态发生改变，即出现了新数据，应该如何将新数据进行加载写入，又应该如何处理老数据。

第一种方案就是在Hive中直接使用新数据覆盖老数据。这种方式实现起来最简单，但是没有历史状态。

第二种方案是每次数据改变都进行记录，根据日期构建一份全量的快照表，每天一张表。这种方式虽然记录了所有的状态，但是会造成很严重的数据冗余问题，导致数据存储量过大。

第三种方案是构建拉链表，通过时间来标记每行数据的有效期，记录每个状态的时间周期。一般来说，我们会在表中额外记录两个字段，starttime和endtime，分别记录当前行的有效时间范围。如果当前有效，可以将endtime设置为9999-12-31这样的格式

拉链表专门用于解决在数据仓库中，数据发生变化之后如何实现数据存储的问题。它的设计是将更新的数据进行状态记录，通过时间进行标记每个状态的生命周期。查询的时候，可以根据需求获取指定时间范围内的数据，同时可以使用9999-12-31等最大值来表示最新状态

拉链表的实现过程如下：

假设我们现在需要持续更新的表具有字段为id、value、starttime、endtime，那么拉链表的构建如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
--创建DW层拉链表
create table dw_zipper(
    id int,
    value string,
    starttime string,
    endtime string
)

--创建ODS层增量表
create table ods_update(
    id int,
    value string,
    starttime string,
    endtime string
)

--创建临时表
create table temp(
    id int,
    value string,
    starttime string,
    endtime string
)


--合并拉链表和增量表
insert overwrite table temp
select
	id, value, starttime, endtime
from ods_update --查询增量表中的所有数据
union all
select
	id, value, starttime, 
	if(b.id is null or a.endtime < '9999-12-31',
       a.endtime, date_sub(b.starttime, 1)
      ) as entdime
    --如果这条id没有更新，或者id更新但是该行不是最新状态，则保留原来的值，否则改为新数据开始时间-1
from dw_zipper a 
left join ods_update b
on a.id = b.id 
--利用join进行查询，完全保留左表的同时可以根据字段判断当前行是否需要更新状态
大数据 > Hive
#笔记 #Hive
Hive学习笔记-HiveSQL(4)-Hive函数重要应用案例
http://example.com/2022/07/05/Hive学习笔记-HiveSQL-4-Hive函数重要应用案例/
作者
EverNorif
发布于
2022年7月5日
许可协议