关联的优化
根据定义,自关联是表自身的关联。自连接通常仅在给定数据中存在父子关系时使用。在本文中,我们将检查如何在Hive中编写自连接查询,它的性能问题以及如何优化它。开始之前我们可以看一下之前关于关联的文章
Hive实战之自关联经典案例
各种join 的执行计划
自关联优化
自关联查询
正如前面提到的,自关联经常用到数据之间有父-子
关系的数据上
考虑一个员工表。员工表包含员工的详细信息,员工也可以是经理。可以使用自连接来识别员工和主管
+------+-------+-------+--+
| eid | Name | mid |
+------+-------+-------+--+
| 100 | AAA | 101 |
| 101 | BBB | NULL |
| 102 | CCC | 101 |
| 103 | DDD | 102 |
| 104 | EEE | 103 |
| 10 | FFF | 101 |
+------+-------+-------+--+
我们的查询大致如下
select EMPL.EID, EMPL.NAME, MANAGER.NAME
from employee_manager EMPL, employee_manager MANAGER
where EMPL.mid = MANAGER.eid;
+------+-------+---------------+--+
| EID | NAME | MANAGER_NAME |
+------+-------+---------------+--+
| 101 | BBB | FFF |
| 101 | BBB | CCC |
| 101 | BBB | AAA |
| 102 | CCC | DDD |
| 103 | DDD | EEE |
+------+-------+---------------+--+
从查询结果进行自关联
有个需求就是我们需要从一个查询结果上进行自关联,这里我使用WITH
语法构造了这个查询,更多关于WITH
的可以看看我们之前的文章
数仓工具—Hive语法之with as和from
数仓工具—Hive语法之with扩展
WITH sub_query AS(
SELECT * FROM employee_manager
)
select EMPL.EID, EMPL.NAME, MANAGER.NAME as MANAGER_NAME
from sub_query EMPL, sub_query MANAGER
where EMPL.eid = MANAGER.mid;
其实我们可以使用临时表来替代WITH
语法,这个时候可能会看到性能有所提示,常见的创建临时表的方式如下
CREATE TEMPORARY TABLE temp1(col1 string);
CREATE TEMPORARY TABLE temp2 AS Select * from table_name;
CREATE TEMPORARY TABLE temp3 LIKE table_name;
自关联的优化
其实关于自关联的优化手段,和我们之前优化join
的差不多,我们简单看一下
- 避免使用自关联在大表上,如果无法避免那么参考之前我们大表的关联优化方案
- 创建临时表避免
WITH
,因为你通过WITH
创建出来的对象在多次使用时会多次执行查询 - 在关联之前删除不必要的数据
- 创建索引,因为是自关联,所以我们就只需要创建一次索引
- 自关联往往是为了去当前数据的前一条记录或者是后一条记录,如果是这种场景下我们可以尝试使用lead 和lag 函数替代
普通关联优化
普通的关联优化
有倾斜
- 空值处理 不参与关联 和 随机值关联不上
- Map join
- 膨胀分开处理
空值处理
空值不参与关联处理
一定是下面这种写法
from
(select * from trackinfo where id is not null)
a
left join
pm_info b
on
a.id= b.id
union all
select * from trackinfo where id is null
而不是
from trackinfo a
left outer join pm_info b
on
a.id= b.id
and a.id is not null
这个写法在于 a 表id 为null 的数据为被分配到一个reduce 中去,倾斜还是在
随机处理
其实case 在join 中的关联有一个非常典型的应用,那就是我们可以用来处理join 的时候的数据倾斜,我们可以使用case when 对特定倾斜的值进行处理,例如NULL值
没倾斜
- 大小表 map join
- 两个大表 SMB
- 两个大表 索引
- 提前数据过滤