hadoop - 配置单元 0.14.0.2.2.4.10-1 : Multi Insert - Empty partition

Question

我正在尝试使用以下查询进行多次插入。

From kiran.employee_part ep
insert overwrite table kiran.employee_ext_part
partition (pdept = 'gbm', pspm = 'ajay')
select ep.id,ep.name,ep.dept,ep.skill,ep.sal,ep.mgr,ep.spm,ep.comment  where ep.pdept = 'gbm' and ep.pspm = 'ajay'
insert overwrite table kiran.employee_ext_part
partition (pdept='rw' , pspm='prashanth')
select ep.id,ep.name,ep.dept,ep.skill,ep.sal,ep.mgr,ep.spm,ep.comment where ep.pdept='rw' and ep.pspm='prashanth'
insert overwrite table kiran.employee_ext_part
partition (pdept='test' , pspm='test')
select ep.id,ep.name,ep.dept,ep.skill,ep.sal,ep.mgr,ep.spm,ep.comment where ep.pdept='test' and ep.pspm='test'
insert overwrite table kiran.employee_ext_part partition (pdept='test1' , pspm='test1')
select ep.id,ep.name,ep.dept,ep.skill,ep.sal,ep.mgr,ep.spm,ep.comment where ep.pdept='test1' and ep.pspm='test1';

选择 ep.id,ep.name,ep.dept,ep.skill,ep.sal,ep.mgr,ep.spm,ep.comment 其中 ep.pdept='test1' 和 ep。 pspm='test1' 查询未按预期返回任何行。其余的选择查询返回几行。执行上述查询后，我的整个 kiran.employee_ext_part 表变为 NULL，如下所示。

hive> select * from employee_ext_part;
OK
employee_ext_part.id    employee_ext_part.name  employee_ext_part.dept  employee_ext_part.skill employee_ext_part.sal   employee_ext_part.mgr   employee_ext_part.spm   employee_ext_part.comment       employee_ext_part.pdept        employee_ext_part.pspm
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    gbm     ajay
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    rw      prashanth
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    test    test
Time taken: 8.116 seconds, Fetched: 3 row(s)

如果我注释掉最后一个查询并执行它，表中会填充相应的值。

From kiran.employee_part ep
insert overwrite table kiran.employee_ext_part
partition (pdept = 'gbm', pspm = 'ajay')
select ep.id,ep.name,ep.dept,ep.skill,ep.sal,ep.mgr,ep.spm,ep.comment  where ep.pdept = 'gbm' and ep.pspm = 'ajay'
insert overwrite table kiran.employee_ext_part
partition (pdept='rw' , pspm='prashanth')
select ep.id,ep.name,ep.dept,ep.skill,ep.sal,ep.mgr,ep.spm,ep.comment where ep.pdept='rw' and ep.pspm='prashanth'
insert overwrite table kiran.employee_ext_part
partition (pdept='test' , pspm='test')
select ep.id,ep.name,ep.dept,ep.skill,ep.sal,ep.mgr,ep.spm,ep.comment where ep.pdept='test' and ep.pspm='test'
--insert overwrite table kiran.employee_ext_part
--partition (pdept='test1' , pspm='test1')
--select ep.id,ep.name,ep.dept,ep.skill,ep.sal,ep.mgr,ep.spm,ep.comment where ep.pdept='test1' and ep.pspm='test1'
;

hive> select * from employee_ext_part;
OK
employee_ext_part.id    employee_ext_part.name  employee_ext_part.dept  employee_ext_part.skill employee_ext_part.sal   employee_ext_part.mgr   employee_ext_part.spm   employee_ext_part.comment       employee_ext_part.pdept        employee_ext_part.pspm
11    devillers gbm     plsql   1000.0  brijesh ajay            NULL    gbm     ajay
12      fafdu   gbm     plsql   5000.0  kiran   ajay            NULL    gbm     ajay
13      steyn   gbm     ba      10000.0 sudeep  ajay            NULL    gbm     ajay
18      duminy  gbm     hr     100001.0 smith   ajay            NULL    gbm     ajay
15      albe    rw      testing 100.0   venu    prashanth       NULL    rw      prashanth
19      miller  rw      testing 1000.0  ram     prashanth       NULL    rw      prashanth
20      pointin rw      testing 8989.0  ram     prashanth       NULL    rw      prashanth
21      rhodes  rw      tesging 9090.0  ram     prashanth       NULL    rw      prashanth
15      albe    rw      testing 100.0   venu    prashanth       NULL    test    test
19      miller  rw      testing 1000.0  ram     prashanth       NULL    test    test
20      pointin rw      testing 8989.0  ram     prashanth       NULL    test    test
21      rhodes  rw      tesging 9090.0  ram     prashanth       NULL    test    test
Time taken: 0.295 seconds, Fetched: 12 row(s)

有人可以告诉我出了什么问题吗？当我们有一个在 Multi Insert 中返回 NULL 的查询或者我遗漏了什么时，它应该如何工作？

P.S - 抱歉标题。无法正确对齐。

Answer 1

免责声明:我不是多表 Insert 的忠实拥护者，尤其是在多表的情况下，它们都是相同的表，但分区不同.

如果您无法修复您的脚本，为什么不尝试更直接的方法，例如...

set hive.exec.dynamic.partition.mode =nonstrict ;

insert overwrite into table KIRAN.EMPLOYEE_EXT_PART
partition (PDEPT, PSPM)
select ID, NAME, DEPT, SKILL, SAL, MGR, SPM, COMMENT,
       PDEPT, PSPM
from KIRAN.EMPLOYEE_PART
--where ....
;