Hive union all 与 multi insert执行测试
A insert overwrite table temp_test select accountbin,accounttype from s_tbl_cardbininfo where accounttype = 'C' union all select accountbin,accounttype from s_tbl_cardbininfo where accounttype = 'D';
执行计划:
B from s_tbl_cardbininfo insert overwrite table test_lxw partition (flag = 'C') select accountbin,accounttype where accounttype = 'C' insert overwrite table test_lxw partition (flag = 'D') select accountbin,accounttype where accounttype = 'D'
执行计划:
Hive union all 和 multi insert 对于同表都会只加载一次,hive对union all做了很多优化性能要优于multi insert,multi insert用到分区操作复杂了些。测试结果也是union all 要优于multi insert。
C insert overwrite table temp_test select 'countaccountbin', accounttype from s_tbl_cardbininfo where accounttype = 'C' group by accounttype union all select 'countaccountbin',accounttype from s_tbl_cardbininfo where accounttype = 'D' group by accounttype;
执行计划:
D explain from s_tbl_cardbininfo insert overwrite table test_lxw partition (flag = 'C') select 'accountbin',accounttype where accounttype = 'C' group by accounttype insert overwrite table test_lxw partition (flag = 'D') select 'accountbin',accounttype where accounttype = 'D' group by accounttype
执行计划:
Hive count,distinct,group by会导致union all 同表关联扫描多次表,multi insert 不会,可以通过调整count,distinct,group by 位置,先执行union all后进行count,distinct,group by来避免这种情况发生。