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