HIVE Sql 笛卡尔积关联导致查询过慢问题优化_hive笛卡尔积关联怎么优化

大数据开发过程中可能会遇到关键词或敏感词匹配这种场景，具体来说会有两张表：

a表：包含content字段，数据量在百万级

b表：包含word字段，数据量为数万条，都是要匹配的敏感词

目标需求是把含有敏感词content都匹配出来，查询sql：

select a.content, b.word
from a
left join b
on 1=1
where instr(a.content, b.word) > 0

这种笛卡尔积的查询方式会关联出数百亿条数据，并且通过运行日志发现只有1个map和1个reduce，且超过90%时间耗费在reduce上，最后用了一个小时才跑完这个简单的sql。

那么如何优化来减少运行时间呢？

首先想到的就是通过增加reduce数，相关参数如下
hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1000^3=1G） 
hive.exec.reducers.max（每个任务最大的reduce数，默认为999）
mapred.reduce.tasks （直接设置reduce个数）

设置完参数后实际运行发现reduce个数并没有改变，因为reduce个数在下面几种场景不受这些配置的影响

1. 数据量小于hive.exec.reducers.bytes.per.reduce
2. 用了Order by
3. 有笛卡尔积
4. 无group by的count

为了解决笛卡尔积无法增加reduce个数的问题，需要设置join key。在我的查询场景下，针对content有语种属性，将查询sql改造如下：

set mapred.reduce.tasks = 20;
select a.content, b.word
from a
left join b
on a.lang=b.lang
where instr(a.content, b.word) > 0

再次运行，运行时间缩短到6分钟，性能提升了超过10倍！

如果a和b表没有合适的字段做join，可在两表分别扩充一列字段，进行join。此方法也可以成功增加reducer个数，但性能提升要低于上面的方法：

set mapred.reduce.tasks = 20;
select a.content, b.word
from 
(select content, 1 join_col from a) m
left join 
(select word, 1 join_col from b) n
on m.join_col=n.join_col
where instr(m.content, n.word) > 0