Hive 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_code
		FROM ods.ods_collect_dl_02_dw_baseinfo_trans_cert_df
		WHERE `date` = ${dt1}
			AND BUSINESS_SCOPE_CODE LIKE '%02201%'
	), 
	owner_id AS (
		-- 车辆和企业关联查询
		SELECT veh.vehicle_no, veh.plate_color_code, lic.owner_id, lic.owner_name
		FROM ods.ods_collect_dl_02_dw_baseinfo_vehicle_df veh
			JOIN ods.ods_collect_dl_02_dw_info_business_license_df lic
			ON 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_name
		FROM jk
			JOIN owner_id
			ON jk.vehicle_no = owner_id.vehicle_no
				AND 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_md5
	FROM mid.ct_fence_into_out_dt fence
	WHERE fence.dt = ${dt}
		AND job_id = 'admin_110'
		AND out_time - enter_time >= 1800
		AND fence_code IN ('001_lyg', '002_rz', '003_qd')
) fence
	JOIN (
		SELECT vehicle_no, plate_color_code, owner_name
		FROM jk_veh
	) final_jk
	ON final_jk.vehicle_no = fence.veh_no
		AND final_jk.plate_color_code = fence.veh_color
	JOIN (
		SELECT veh_no, p_color, start_time, end_time, start_lon
			, start_lat
		FROM dwd.dwd_vehicle_stops_df
		WHERE `date` >= ${dt2}
			AND `date` <= ${dt}
			AND end_time - start_time > 3600
	) stop
	ON fence.veh_no = stop.veh_no
		AND fence.veh_color = stop.p_color;

---

总结

如果此篇文章有帮助到您, 希望打大佬们能关注、点赞、收藏、评论支持一波，非常感谢大家！
如果有不对的地方请指正!!!