文章目录
- 前言
- 场景说明
- 场景一：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;
总结
如果此篇文章有帮助到您, 希望打大佬们能
关注、点赞、收藏、评论支持一波，非常感谢大家！
如果有不对的地方请指正!!!
