Hive SQL中UNION ALL查询结果后执行JOIN关联的语法问题

你可以通过以下两种常用方式实现关联，均兼容Hive语法：

方案1：CTE写法（推荐，可读性高，Hive 0.13+支持） #

source  /s/mysdrive/6298.hql;
set hivevar:selected_month= "2021-01";
set hivevar:selected_day=TO_DATE("2021-01-01");

WITH travel_data AS (
    -- 第一段三地行驶数据UNION ALL查询
    SELECT CANADA.car_id,
    "CANADA" as Source,
    CANADA.tire_type,               
    CANADA.km_traveled,         
    CANADA.outside_temp,    
    CANADA.cabin_temp,                      
    CANADA.lights_on
    FROM ctbls.can_data_na CANADA
    WHERE CANADA.car_id in (${ID_LIST}) and CANADA.cvdc62_partition_month_x >= ${selected_month}

    UNION ALL

    SELECT UAE.car_id,
    "UAE" as Source,
    UAE.tire_type,              
    UAE.km_traveled,            
    UAE.outside_temp,       
    UAE.cabin_temp,                     
    UAE.lights_on
    FROM ctbls.uae_data_me UAE
    WHERE UAE.car_id in(${ID_LIST}) and UAE.cvdc62_partition_month_x >= ${selected_month}

    UNION ALL

    SELECT USA.car_id,
    "USA" as Source,
    USA.tire_type,              
    USA.km_traveled,            
    USA.outside_temp,       
    USA.cabin_temp,                     
    USA.lights_on
    FROM ctbls.usa_data_na USA
    WHERE USA.car_id in(${ID_LIST}) and USA.cvdc62_partition_date_x >= ${selected_day}
),
car_dim AS (
    -- 第二段车辆维度表查询
    select CARDim.ID,
    CARDim.edition,
    CARDim.sale_date,
    CARDim.sale_country,
    CARDim.model_year,
    CARDim.test_date,
    CARDim.test_type, 
    CARDim.executed_by,
    CARDim.pass_comm,
    CARDim.techni_comm,
    CARDim.customer,
    CARDim.`return` -- return是Hive关键字，用反引号包裹避免语法错误
    from test_db.car_dimension CARDim
    where ID IN (${ID_LIST})
)
-- 关联两个结果集，可根据需求替换为INNER JOIN/LEFT JOIN/RIGHT JOIN
SELECT * 
FROM travel_data t1
LEFT JOIN car_dim t2
ON t1.car_id = t2.ID;

方案2：子查询嵌套写法（兼容所有Hive版本） #

source  /s/mysdrive/6298.hql;
set hivevar:selected_month= "2021-01";
set hivevar:selected_day=TO_DATE("2021-01-01");

SELECT * 
FROM (
    -- 第一段三地行驶数据UNION ALL查询
    SELECT CANADA.car_id,
    "CANADA" as Source,
    CANADA.tire_type,               
    CANADA.km_traveled,         
    CANADA.outside_temp,    
    CANADA.cabin_temp,                      
    CANADA.lights_on
    FROM ctbls.can_data_na CANADA
    WHERE CANADA.car_id in (${ID_LIST}) and CANADA.cvdc62_partition_month_x >= ${selected_month}

    UNION ALL

    SELECT UAE.car_id,
    "UAE" as Source,
    UAE.tire_type,              
    UAE.km_traveled,            
    UAE.outside_temp,       
    UAE.cabin_temp,                     
    UAE.lights_on
    FROM ctbls.uae_data_me UAE
    WHERE UAE.car_id in(${ID_LIST}) and UAE.cvdc62_partition_month_x >= ${selected_month}

    UNION ALL

    SELECT USA.car_id,
    "USA" as Source,
    USA.tire_type,              
    USA.km_traveled,            
    USA.outside_temp,       
    USA.cabin_temp,                     
    USA.lights_on
    FROM ctbls.usa_data_na USA
    WHERE USA.car_id in(${ID_LIST}) and USA.cvdc62_partition_date_x >= ${selected_day}
) t1
-- 关联维度表，可根据需求替换为INNER JOIN/LEFT JOIN/RIGHT JOIN
LEFT JOIN (
    select CARDim.ID,
    CARDim.edition,
    CARDim.sale_date,
    CARDim.sale_country,
    CARDim.model_year,
    CARDim.test_date,
    CARDim.test_type, 
    CARDim.executed_by,
    CARDim.pass_comm,
    CARDim.techni_comm,
    CARDim.customer,
    CARDim.`return`
    from test_db.car_dimension CARDim
    where ID IN (${ID_LIST})
) t2
ON t1.car_id = t2.ID;

注意事项 #

• 关联类型可按需调整：仅保留两边都匹配的数据用INNER JOIN，保留所有行驶数据即使无对应维度信息用LEFT JOIN
• 变量配置语句必须放在SQL最开头才能正常生效
• 若不需要返回全字段，可以替换SELECT *为指定的字段列表，避免car_id和ID重复返回

内容的提问来源于stack exchange，提问作者Jupiter22