Hive JOIN性能调优：从WHERE条件到子查询与分区策略的全方位探索

文章目录

前言
场景说明
场景一：JOIN前使用WHERE条件
场景二：JOIN后使用WHERE条件
结论
案例
总结

前言

在Hive中，当你执行一个包含JOIN操作的查询时，WHERE条件的使用时机和它对查询性能的影响是一个重要的考虑因素。WHERE条件可以在JOIN操作之前或之后应用，但具体的应用方式会对查询的执行计划和性能产生不同的影响。

场景说明

假设我们有两个表：employees（员工表）和departments（部门表）。employees表包含员工ID、员工姓名和部门ID；departments表包含部门ID和部门名称。

employees 表

employee_id	name	department_id
1	Alice	101
2	Bob	102
3	Charlie	101

departments 表

employee_id	name
101	Sales
102	Engineering

场景一：JOIN前使用WHERE条件

如果你只对某个特定部门的员工感兴趣，比如Sales部门（部门ID为101），你可能会在JOIN之前先过滤employees表。然而，直接在JOIN前过滤通常不是Hive SQL的直接写法，因为Hive通常会在JOIN操作之后再应用WHERE条件（除非你使用了子查询或视图等方式）。但这里我们讨论的是逻辑上的操作顺序。
逻辑上的SQL（非直接Hive SQL写法）

SELECT e.name, d.department_name  
FROM (  SELECT *  FROM employees  WHERE department_id = 101  
) e  
JOIN departments d ON e.department_id = d.department_id;

在这个例子中，我们实际上是通过子查询来模拟在JOIN之前应用WHERE条件的效果。这样做的好处是可以减少参与JOIN操作的数据量，从而提高查询性能。

场景二：JOIN后使用WHERE条件

如果你直接在JOIN之后应用WHERE条件，那么Hive会先执行JOIN操作，然后再过滤结果。

直接的Hive SQL写法

SELECT e.name, d.department_name  
FROM employees e  
JOIN departments d ON e.department_id = d.department_id  
WHERE e.department_id = 101;

虽然这个查询在逻辑上也是正确的，但如果employees表和departments表非常大，那么JOIN操作可能会非常耗时，并且会产生大量不必要的数据，这些数据最终会被WHERE条件过滤掉。

结论

在Hive中，为了优化查询性能，尤其是在处理大数据集时，尽量在JOIN之前通过子查询、视图或分区等方式来减少参与JOIN的数据量。这样做可以减少数据的处理量，从而缩短查询时间。不过，也需要注意查询的复杂度和可读性，避免过度优化导致查询难以理解和维护。

案例

SET mapreduce.job.queuename=root.spark;  
SET mapreduce.reduce.memory.mb=4096;
SET mapreduce.map.memory.mb=4096;
--  先查询出集卡车辆
WITH jk AS (SELECT vehicle_no, plate_color_codeFROM ods.ods_collect_dl_02_dw_baseinfo_trans_cert_dfWHERE `date` = ${dt1}AND BUSINESS_SCOPE_CODE LIKE '%02201%'), owner_id AS (-- 车辆和企业关联查询SELECT veh.vehicle_no, veh.plate_color_code, lic.owner_id, lic.owner_nameFROM ods.ods_collect_dl_02_dw_baseinfo_vehicle_df vehJOIN ods.ods_collect_dl_02_dw_info_business_license_df licON veh.`date` = ${dt1}AND lic.`date` = ${dt1}AND veh.owner_id = lic.owner_id), -- 筛选出集卡车辆信息和企业信息jk_veh AS (SELECT jk.vehicle_no, jk.plate_color_code, owner_id.owner_nameFROM jkJOIN owner_idON jk.vehicle_no = owner_id.vehicle_noAND jk.plate_color_code = owner_id.plate_color_code)
-- 集卡车的进出围栏,和车辆停靠点信息
INSERT overwrite directory "/user/cttic/lifeng/ajkj/dt=${dt}" ROW format delimited fields terminated BY "," 
SELECT   
fence.dt, fence.fence_code, fence.veh_no, substr(md5(concat('aj', fence.veh_no, fence.veh_color, 'kj')), 9, 16) AS vehicleno_md5, final_jk.owner_name, from_unixtime(cast(fence.enter_time as int), 'yyyy-MM-dd HH:mm:ss') as enter_time,from_unixtime(cast(fence.out_time as int), 'yyyy-MM-dd HH:mm:ss') as out_time, from_unixtime(cast(stop.start_time as int), 'yyyy-MM-dd HH:mm:ss') as start_time, from_unixtime(cast(stop.end_time as int), 'yyyy-MM-dd HH:mm:ss') as end_time, stop.start_lon, stop.start_lat
FROM (SELECT dt, fence_code, veh_no, veh_color, enter_time, out_time -- substr((md5(concat('aj', veh_no, veh_color, 'kj'))), 9, 16) as vehicleno_md5FROM mid.ct_fence_into_out_dt fenceWHERE fence.dt = ${dt}AND job_id = 'admin_110'AND out_time - enter_time >= 1800AND fence_code IN ('001_lyg', '002_rz', '003_qd')
) fenceJOIN (SELECT vehicle_no, plate_color_code, owner_nameFROM jk_veh) final_jkON final_jk.vehicle_no = fence.veh_noAND final_jk.plate_color_code = fence.veh_colorJOIN (SELECT veh_no, p_color, start_time, end_time, start_lon, start_latFROM dwd.dwd_vehicle_stops_dfWHERE `date` >= ${dt2}AND `date` <= ${dt}AND end_time - start_time > 3600) stopON fence.veh_no = stop.veh_noAND fence.veh_color = stop.p_color;