hive union

hive中执行如下语句报错

hive> insert overwrite table test6 

    > SELECT t1.user_id, t2.sale_developer_id develop_staff_id, t2.eparchy_code FROM yudh_test3 t1 LEFT JOIN 

    > (SELECT DISTINCT a.DEV_CODE, a.SALE_DEVELOPER_ID, a.EPARCHY_CODE FROM ts_u_develop_rel a) t2 

    > ON (t1.develop_staff_id = t2.dev_code AND t1.EPARCHY_CODE = t2.eparchy_code) 

    > UNION ALL 

    > SELECT t1.user_id, t2.sale_developer_id develop_staff_id, t2.eparchy_code FROM yudh_test3 t1 LEFT JOIN ts_u_develop_rel t2 

    > ON (t1.develop_staff_id = t2.bss_developer_id AND t1.EPARCHY_CODE = t2.eparchy_code);

FAILED: SemanticException 2:79 Top level UNION is not supported currently; use a subquery for the UNION. Error encountered near token 'eparchy_code'

hive不支持顶层union，只能讲union放到子查询中。

正确做法如下：把所有union all放到子查询中，并给起别名：

INSERT overwrite TABLE test6 

SELECT t3.user_id, t3.develop_staff_id, t3.eparchy_code FROM (

SELECT t1.user_id, t2.sale_developer_id develop_staff_id, t2.eparchy_code FROM yudh_test3 t1 LEFT JOIN 

(SELECT DISTINCT a.DEV_CODE, a.SALE_DEVELOPER_ID, a.EPARCHY_CODE FROM ts_u_develop_rel a) t2 

ON (t1.develop_staff_id = t2.dev_code AND t1.EPARCHY_CODE = t2.eparchy_code) 

UNION ALL 

SELECT t1.user_id, t2.sale_developer_id develop_staff_id, t2.eparchy_code FROM yudh_test3 t1 LEFT JOIN ts_u_develop_rel t2 

ON (t1.develop_staff_id = t2.bss_developer_id AND t1.EPARCHY_CODE = t2.eparchy_code)) t3