在HIVE中执行查询(多个/子查询)导致顶点失败

qltillow 于 12个月前发布在 Hive

关注(0)|答案(1)|浏览(142)

我对HIVE和SQL Query有基本的了解。所以我有一个查询有多个单独的查询或者你可以说是子查询。然而，如果我一起运行5-6个查询，它工作正常，但如果我运行整个查询（99个单独查询），它会给我顶点失败错误
查询：

Select "TABLE_NAME_2" as TBL_NAME,  (Select count(*)  from a_t2 where BDATE='2023-05-31') as  a,    (Select count(*)  from b_t2 where BDATE='2023-05-31') as  b,    (Select count(*)  from c_t2 where BDATE='2023-05-31') as  c     UNION ALL
Select "TABLE_NAME_3" as TBL_NAME,  (Select count(*)  from a_t3 where BDATE='2023-05-31') as  a,    (Select count(*)  from b_t3 where BDATE='2023-05-31') as  b,    (Select count(*)  from c_t3 where BDATE='2023-05-31') as  c     UNION ALL
Select "TABLE_NAME_4" as TBL_NAME,  (Select count(*)  from a_t4 where BDATE='2023-05-31') as  a,    (Select count(*)  from b_t4 where BDATE='2023-05-31') as  b,    (Select count(*)  from c_t4 where BDATE='2023-05-31') as  c     UNION ALL
Select "TABLE_NAME_5" as TBL_NAME,  (Select count(*)  from a_t5 where BDATE='2023-05-31') as  a,    (Select count(*)  from b_t5 where BDATE='2023-05-31') as  b,    (Select count(*)  from c_t5 where BDATE='2023-05-31') as  c     UNION ALL
Select "TABLE_NAME_6" as TBL_NAME,  (Select count(*)  from a_t6 where BDATE='2023-05-31') as  a,    (Select count(*)  from b_t6 where BDATE='2023-05-31') as  b,    (Select count(*)  from c_t6 where BDATE='2023-05-31') as  c     UNION ALL
.
.
.
.
.
Select "TABLE_NAME_33" as TBL_NAME, (Select count(*)  from a_t33 where BDATE='2023-05-31') as  a,   (Select count(*)  from b_t33 where BDATE='2023-05-31') as  b,   (Select count(*)  from c_t33 where BDATE='2023-05-31') as  c

不确定这是HiveDB的限制还是我遗漏了什么。
注：我也用过

set tez.queue.name = APPNAME; 
set hive.execution.engine = tez;

编辑我想要的输出是：

TBL_NAME     a    b   c
TABLE_NAME1  10   20  15
TABLE_NAME2  10   20  15
TABLE_NAME3  10   20  15
.
.
.
TABLE_NAME33  10   20  15

谢谢

Hive

来源：https://stackoverflow.com/questions/76433681/executing-query-multiple-subquery-in-hive-is-giving-vertex-failed

1条答案

按热度按时间

ippsafx71#

我可以给予你一些解决办法。就像@nbk说的语法是错误的，这种内联SQL在hive中不管用。但这将是一个昂贵的解决方案-在这个表中寻找一些分区，看看是否可以计算已分区的行。
你可以试试-

WITH 
TABLE_NAME_2_a as Select "TABLE_NAME_2" as TBL_NAME,  SUM(case when BDATE='2023-05-31' then 1 else 0 end) as  a from a_t2 ,
TABLE_NAME_2_b as Select "TABLE_NAME_2" as TBL_NAME,  SUM(case when BDATE='2023-05-31' then 1 else 0 end) as  a from b_t2 ,
TABLE_NAME_2_c as Select "TABLE_NAME_2" as TBL_NAME,  SUM(case when BDATE='2023-05-31' then 1 else 0 end) as  a from c_t2 ,
TABLE_NAME_3_a as Select "TABLE_NAME_3" as TBL_NAME,  SUM(case when BDATE='2023-05-31' then 1 else 0 end) as  a from a_t3 ,
TABLE_NAME_3_b as Select "TABLE_NAME_3" as TBL_NAME,  SUM(case when BDATE='2023-05-31' then 1 else 0 end) as  a from b_t3 ,
TABLE_NAME_3_c as Select "TABLE_NAME_3" as TBL_NAME,  SUM(case when BDATE='2023-05-31' then 1 else 0 end) as  a from c_t3 

--UNION for Table 2
select 
TABLE_NAME_2_a.TBL_NAME, TABLE_NAME_2_a.a, TABLE_NAME_2_b.b,TABLE_NAME_2_c.c
from 
TABLE_NAME_2_a,TABLE_NAME_2_b,TABLE_NAME_2_c 
where 
TABLE_NAME_2_a.TBL_NAME=TABLE_NAME_2_b.TBL_NAME and TABLE_NAME_2_a.TBL_NAME=TABLE_NAME_2_c.TBL_NAME 
union all 
--UNION for Table 3
select 
TABLE_NAME_3_a.TBL_NAME, TABLE_NAME_3_a.a, TABLE_NAME_3_b.b,TABLE_NAME_3_c.c
from       
TABLE_NAME_3_a,TABLE_NAME_3_b,TABLE_NAME_3_c 
where      
TABLE_NAME_3_a.TBL_NAME=TABLE_NAME_3_b.TBL_NAME and TABLE_NAME_3_a.TBL_NAME=TABLE_NAME_3_c.TBL_NAME

赞(0）回复(0）举报 12个月前

我来回答

在HIVE中执行查询(多个/子查询)导致顶点失败

1条答案

相关问题

热门标签

最新问答