Hive大表join大表如何调优¶

前言¶

在Hive中，优化器会根据统计信息决定是将大表放在前面（Join的左边）还是小表放在前面。通常，优化器会选择数据量较小的表作为驱动表（小表作为左边），因为这样可以减少内存消耗并提高效率。

但是，如果你有特定的需求，比如你知道大部分数据能快速过滤掉，希望减少任务的执行时间，那么你可以强制指定某个表作为小表。在Hive中，可以使用/*+ MAPJOIN(table_name) */ 注释来强制将一个大表作为小表处理。

例如，如果你想要将 small_table 作为小表（将小表加载到内存中）：

SELECT /*+ MAPJOIN(small_table) */
  a.column1, a.column2, b.column1, b.column2
FROM
  small_table a
JOIN
  big_table b
ON
  a.common_column = b.common_column;

一、调优思路¶

1、SQL优化¶

1.1 大小表join¶

mapjoin：小表使用mapjoin，或者强制hint
将大表放后头：原因：Hive假定查询中最后的一个表是大表。它会将其它表缓存起来，然后扫描最后那个表。因此通常需要将小表放前面，或者标记哪张表是大表：/*streamtable(table_name) */
过滤无效值：空值、不使用的字段等
不能过滤的空值：将空值转化为随机数避免数据倾斜

1.2 大大表join¶

1）创建第二张大表

create table bigtable2(
    id bigint,
    t bigint,
    uid string,
    keyword string,
    url_rank int,
    click_num int,
    click_url string)
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/data/bigtable' into table bigtable2;

2）测试大表直接JOIN

insert overwrite table jointable
select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable a
join bigtable2 b
on a.id = b.id;

测试结果：Time taken: 72.289 seconds

3）创建分桶表1

create table bigtable_buck1(
    id bigint,
    t bigint,
    uid string,
    keyword string,
    url_rank int,
    click_num int,
    click_url string)
clustered by(id)
sorted by(id)
into 6 buckets
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/data/bigtable' into table bigtable_buck1;

4）创建分桶表2，分桶数和第一张表的分桶数为倍数关系

create table bigtable_buck2(
    id bigint,
    t bigint,
    uid string,
    keyword string,
    url_rank int,
    click_num int,
    click_url string)
clustered by(id)
sorted by(id)
into 6 buckets
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/data/bigtable' into table bigtable_buck2;

5）设置参数

set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

6）测试

insert overwrite table jointable
select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable_buck1 s
join bigtable_buck2 b
on b.id = s.id;

测试结果：Time taken: 34.685 seconds

2、insert overwrite替换union all¶

使用insert overwrite替代union all可以提高执行效率。

3、排序order by换位sort by¶

在不需要全局排序的情况下，使用sort by替代order by可以减少单个Reduce的任务负担，提高性能。

4、并行执行¶

启用并行执行可以充分利用集群资源，减少任务总执行时间。

set hive.exec.parallel=true;

5、数据倾斜优化¶

数据倾斜是Hive调优中的常见问题，主要解决方法包括：

空值处理：将空值转化为随机数打散
key扩容：对倾斜的key进行扩容处理
分而治之：将倾斜key和非倾斜key分开处理，最后union all

6、小文件优化¶

小文件过多会导致NameNode内存压力增大，同时也会影响MapReduce任务的执行效率。解决方法包括：

合并小文件：使用Hive的合并功能
调整输入格式：使用CombineHiveInputFormat
调整输出：减少输出文件数量

二、实战¶

2.1 场景¶

（场景描述待补充）

2.2 限制所需的字段，间接mapjoin¶

通过限制SELECT中的字段，可以减少数据传输量，间接实现mapjoin的效果。

2.3 解决异常值倾斜，如NULL加随机数打散¶

当JOIN键中存在大量NULL值时，会导致数据倾斜。解决方法是将NULL值转换为随机数：

SELECT /*+ MAPJOIN(a) */
  a.id, a.name, b.score
FROM (
  SELECT 
    CASE WHEN id IS NULL THEN CAST(RAND() * 1000 AS BIGINT) ELSE id END AS id,
    name
  FROM table_a
) a
JOIN table_b b
ON a.id = b.id;

2.4 扩容解决数据倾斜¶

2.4.1 客户表扩大N倍¶

将客户表扩容N倍，减少数据倾斜。

2.4.2 部分倾斜key扩容，大卖家扩容¶

针对特定的大卖家key进行扩容处理。

2.4.3 推荐：分而治之：倾斜和非倾斜再union all¶

将倾斜的key和非倾斜的key分开处理，最后通过union all合并结果。这是推荐的最佳实践。