hive怎么处理过滤掉满足多个多个条件的记录_【DATA】Hive优化、数据倾斜处理与UDF使用...

写在文章前

由于最近要分享Hive的优化和UDF的使用，趁着周末大好时光，在家梳理一下。如有纰漏，欢迎留言指正！

---

前言

Hive是基于Hadoop的一个数据仓库，可以将结构化的数据文件映射为一个数据表，并提供类sql的查询功能(hql)。本文不会对Hive的原理和详细语法做介绍。

通过本文，你会了解到如下内容：

1. Hive优化
   1. 合理选择排序
   2. 慎用笛卡尔积
   3. 合理设计Task个数
   4. 少用in/exists
   5. 插入优化
   6. 合理设计分区/分桶
   7. Join优化
   8. Group by优化
2. 数据倾斜处理
   1. Null值问题
   2. 数据类型问题
   3. 大小表关联问题
   4. 大大表关联问题
   5. Group by/Distinct/Count问题
3. UDF使用

1. Hive优化

1.1 合理选择排序

Hive中排序方式包括order by，sort by，distribute by和cluster by四种。在Hive中一定要合理选择排序的方式，下面我对几种排序及使用方法做一个介绍。

order by

order by对查询的结果做一次全局排序，也就是说指定order by的所有数据都会到一个reducer中处理，对数据量特别大的情况，执行时间会特别长。

如果指定了严格模式，此时必须指定limit来限制输出条数，因为所有数据在一个reducer端进行，数据量大时可能不会出结果。

 set hive.mapred.mode=strict

sort by

sort by是局部排序，会在每个reducer端进行排序，实现每个reducer内部是有序的，全部数据不一定有序，除非只有一个reducer。执行sort by可以为order by提高效率(一次归并排序就全局有序了)。

distribute by

distribute by类似mapreduce中的分区，用来控制map端在reducer上如何区分，distribute by会把key相同的数据分发到同一个reducer。可以和sort by结合使用，此时distribute by必须写在sort by之前。

cluster by

cluster by就是distribute by和sort by的结合。ps：cluster by指定的列只能是降序，不能指定asc和desc

相关用法和结合方式

我们有一张描述商户相关表store，uid：商店所属商户，cash：商店的盈利，name：商店名

distribute by和sort by一起使用相当于cluster by

select uid, cash, name from store distribute by uid sort by uid 

等价于

select uid, cash, name from store cluster by uid 

故，针对排序场景需要明确是否需要全局排序，大部分业务场景是不需要全局排序的，通常情况下，distribute by和sort by结合能够解决大部分业务问题。

1.2 慎用笛卡尔积

Hive中在实现笛卡尔积时，由于没有关联的key，所以在MR时，所有数据都到一个reducer中去处理，性能非常低。

那么如果非要使用笛卡尔积，我们该怎么办呢？

很显然，笛卡尔积的本质问题时没有join key，所以我们对需要join的两张表各加入一列join key就可以了。针对小表，我们将小表的数据条目复制N份，N为需要使用的reducer个数，然后join key中存储不同的key值。针对大表，join key是小表key取值的随机。

举个例子：

针对上面商户的例子，如果我们需要和商户用户群体表ctable做笛卡尔积。cid：用户群体id

1.3 合理设计Task个数

1.3.1 合理设计Map Task数量

Map Task的问题包括过多和过少两部分。Map Task过多时小文件过多，Container启动和销毁的时间开销远大于计算时间开销。Map Task过少时Map的并行度不高，资源没有合理利用。

小文件过多的处理方案

• 通过合并Map和Reduce的结果文件来消除影响。

set hive.merge.mapfiles = true ##在 map only 的任务结束时合并小文件
set hive.merge.mapredfiles = false ## true 时在 MapReduce 的任务结束时合并小文件
set hive.merge.size.per.task = 256*1000*1000 ##合并文件的大小
set mapred.max.split.size=256000000; ##每个 Map 最大分割大小
set mapred.min.split.size.per.node=1; ##一个节点上 split 的最少值
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; ##执行 Map 前进行小文件合并

• MR存在串联时，控制上一个MR输出的reduce Task个数。

JVM重用

默认一个Container运行一个Map Task，一个Task会启动一个JVM进程，一个Task执行完毕后，JVM进程会退出。如果任务花费时间很多，又多次重启JVM，JVM的启动时间会非常耗时。而Container的大小在128M，而我们一个map Task的处理数据量不足128M，例如50M。此时，我们可以设置一个Container运行的Map Task任务个数，提高程序整体运行效率。

 set mapred.job.reuse.jvm.num.tasks=5 

增大map Task数量

当输入文件很大，任务逻辑复杂，map执行非常慢时，可以考虑增大map task数量。

1.3.2 合理设置reduce Task数量

reduce Task个数的设置极大的影响执行的效率，这使得如何判断reducer的个数成为了关键。

一般存在一个经验值，也就是Hadoop中datanode个数*0.95 作为reduce Task的上限个数。如果不需要用到这么多个reducer时，我们需要根据Map端输出的数据量来进行判断。

1.4 少用in/exists

由于没有关联条件，所有数据会最终到一个reduce task中进行计算。我们可以用left semi join来进行替代。

select a.id, a.name from a where a.id in (select b.id from b);
select a.id, a.name from a where exists (select id from b where a.id = b.id);

替换为

select a.id, a.name from a left semi join b on a.id = b.id;

1.5 使用多重插入替代单重插入

单重插入每一条数据执行一次，执行效率慢。使用多重插入，数据只执行一次。减少表的扫描次数，提升数据插入效率。例如

insert into table a select * from b where age=18
insert into table a select * from b where age = 19

替换为

from b
insert into table a select * where age =18
insert into table a select * where age = 19

1.6 合理设置分区和分桶

首先先介绍下分区和分桶的含义。

• 分区是表的部分列对表进行重新组织，一般将频繁使用的数据上建立分区，这样当数据量很大时，可以避免全表扫描。形式上可以理解为文件夹。
• 分桶是通过对指定列进行hash来实现的，通过hash值将一个列名下的数据切分为一组桶，每个桶对应该列名下的一个存储文件。

合理设计分区

当数据量非常大时，为了避免全表扫描，我们需要使用分区来减少数据的扫描范围。一般选择频繁使用的字段。

create table aa(
id int,
name string,
cardNum string)
 partitioned by (enter_date string,country string); 

分区字段不能包含在表定义字段中，因为在向表中load数据的时候，需要手动指定该字段的值。

load data inpath '/hadoop/test/data/aa_test' into table aa partition (enter_date='20200726',country='china'); 

合理设计分桶

分桶的最终结果存储在不同文件中，当数据有Join需求或抽样需求时，考虑使用。

针对join需求，两个在相同列上划分了桶的表，那么我们只需要将保存了相同列值的桶进行join就可以了，可以大大减少join的数据量。

create table aa(
id int, 
name string, 
cardNum string) 
partitioned by (enter_date string,country string)
clustered by('name') into 100 buckets; 

分桶表和分区表的区别是，分桶的字段是原始数据中存在的，分区的字段是原始数据中不存在的。

1.7 Join优化

Join优化需要遵循的几点优化技巧：

• 先过滤再join，减少join的数据量
• 小表join大表，最好启动Map Join

set hive.auto.convert.join = true;
set hive.mapjoin.smalltable.filesize=25000000;

具体join优化细节在数据倾斜处理中进行介绍。

2. 数据倾斜处理

数据倾斜是hive处理业务问题中非常常见的情况。数据倾斜一般发生在reduce端，如何保证数据均匀的分配到各个reduce中，是解决数据倾斜的关键。

发生数据倾斜的原因主要是下面几方面：

• key分布不均
• 业务数据本身特性
• 建表时考虑不周
• 某些sql语句本身就有数据倾斜

下面对数据倾斜常见的情况和处理方式进行介绍。

2.1 Null值问题

在很多业务场景中，会存在大量的null值。例如在淘宝搜索商品，会存在大量没有登陆的用户，此时userid会记录为空值。当我们在统计pv时，需要将这部分流量统计进取，在使用hive统计时，占比很高的空值会进入到一个reducer中，造成整体mr任务执行效率下降。

针对Null值问题，有两种解决方案：

• null值不参与关联。在非null值关联后，将null值union到最后。例如：

select * from log a
 join users b
 on a.user_id is not null
 and a.user_id = b.user_id
union all
select * from log a where a.user_id is null;

• 给null值增加一个随机数。例如：

select *
  from log a
  left outer join users b
  on case when a.user_id is null then concat(‘hive’,rand() ) else a.user_id end = b.user_id;

2.2 数据类型问题

建表时关联字段的数据类型不统一。例如table a中userid的类型为Int，table b中userid的类型为String。在按照userid进行join时，会以小类型Int作为分区依据，所有的string会分配到一个reducetask中进行处理。

2.3 大小表关联

如果小表数据量不大，此时使用Map端join来代替reduce端join。将小表分发到所有的datanode上。此时设置命令（默认小表大小上限25M，理论上不超过内存大小）：

set hive.auto.convert.join = true;
set hive.mapjoin.smalltable.filesize=25000000;

2.4 大小表join，小表超过25M

如果小表大小超过了hive进行map join的上限。此时，如果小表超过大小上限不多，可以强制走Map端join。例如：

select /*+mapjoin(x)*/* from log a
  left outer join (
    select  /*+mapjoin(c)*/d.*
      from ( select distinct user_id from log ) c
      join users d
      on c.user_id = d.user_id
    ) x
  on a.user_id = b.user_id;

2.5 大表 join 大表

此时切分一个大表成多个能放入到内存中的小表切片。然后使用Mapjoin进行处理。

2.6 Group by和Distinct/Count

这些命令本身就会产生数据倾斜，一般与业务有关，不可避免。

3. UDF使用

Hive中自带了很多函数，方便进行数据分析。但有时候内部函数无法提供我们想要的功能，此时需要自定义函数(UDF)来实现想要的功能。

UDF可以使用Java和Python进行编写。下面分别进行介绍：

3.1 Java开发UDF

使用Java开发Hive的UDF需要两个步骤：

• 继承org.apache.hadoop.hive.ql.UDF
• 实现evaluate函数，这个函数必须要有返回值，不能设置为void。

首先在Maven项目的pom.xml中添加依赖：

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <hadoop.version>2.5.0</hadoop.version>
  </properties>

  <dependencies>
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-client</artifactId>
        <version>2.5.0</version>
    </dependency>
  
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>0.13.1</version>
    </dependency>

    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>0.13.1</version>
    </dependency>
    
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

编写UDF，例如去除数据字符串中的双引号。

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class testUDF extends UDF {
    public Text evaluate(Text string) {
        if (null == string) {
            return null;
        }
        Text result;
        String s = string.toString().replaceAll(""", "");
        result = new Text(s);
        return result;
    }
}

关联jar包

 add jar /opt/test/testUDF.jar;

单次添加，退出hive shell后失效

create temporary function my_udf as "hiveUDF.hiveUDF.testUDF";

永久添加方式

配置hive-site.xml文件中的hive.aux.jars.path属性

使用方法

select myudf(uid) from table_a;

3.2 python开发UDF

还是以上面的例子为准。使用python编写UDF，去除数据字符串中的双引号。

# -*- coding: utf-8 -*-
import sys

for line in sys.stdin:
    detail = line.strip().split("t")
    if len(detail) != 2:
        continue
    else:
        uid = detail[0]
        name = detail[1]
        uid.replace('"','')
        print("t".join([uid,name]))

UDF使用

add file /xxx/test.py

然后使用transform函数执行

select transform(uid,name) USING'python test.py' AS (uid,name) from table_a;

---

总结

本文介绍了Hive常用优化策略，以及数据倾斜的处理方案。同时对Hive中UDF的使用进行了梳理介绍。时间比较仓促，如有错误敬请指正，后续也会进一步完善。

欢迎大家关注我的微信公众号：机器鼓励猴