执行含多子查询的HIVE任务报Vertex Failed错误求助

问题场景 #

具备Hive与SQL基础，编写的查询包含大量子查询：每个UNION ALL分支对应一组3个独立计数子查询，单独运行5-6个分支正常，但运行包含99个分支的完整语句时，触发Vertex Failed错误。

查询结构示例：

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_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

当前已配置：

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_NAME33  10   20  15

原因分析 #

99个分支对应297个独立子查询，会导致Tez引擎生成的执行计划过于复杂，并行任务数超出集群资源承载上限，或者触发Hive默认的查询复杂度限制，最终引发Vertex失败。

解决方案 #

1. 重构SQL，大幅降低子查询数量 #

将原SQL的“分支+子查询”模式，改为“批量统计+行转列”模式，把所有表的计数操作合并成一个子查询，再通过分组聚合得到目标格式：

SELECT 
  TBL_NAME,
  MAX(CASE WHEN src_type = 'a' THEN cnt END) AS a,
  MAX(CASE WHEN src_type = 'b' THEN cnt END) AS b,
  MAX(CASE WHEN src_type = 'c' THEN cnt END) AS c
FROM (
  -- 统计所有a系列表
  SELECT 'TABLE_NAME_2' AS TBL_NAME, 'a' AS src_type, COUNT(*) AS cnt FROM a_t2 WHERE BDATE='2023-05-31'
  UNION ALL
  SELECT 'TABLE_NAME_3' AS TBL_NAME, 'a' AS src_type, COUNT(*) AS cnt FROM a_t3 WHERE BDATE='2023-05-31'
  UNION ALL
  ...
  SELECT 'TABLE_NAME_33' AS TBL_NAME, 'a' AS src_type, COUNT(*) AS cnt FROM a_t33 WHERE BDATE='2023-05-31'
  
  -- 统计所有b系列表
  UNION ALL
  SELECT 'TABLE_NAME_2' AS TBL_NAME, 'b' AS src_type, COUNT(*) AS cnt FROM b_t2 WHERE BDATE='2023-05-31'
  UNION ALL
  ...
  SELECT 'TABLE_NAME_33' AS TBL_NAME, 'b' AS src_type, COUNT(*) AS cnt FROM b_t33 WHERE BDATE='2023-05-31'
  
  -- 统计所有c系列表
  UNION ALL
  SELECT 'TABLE_NAME_2' AS TBL_NAME, 'c' AS src_type, COUNT(*) AS cnt FROM c_t2 WHERE BDATE='2023-05-31'
  UNION ALL
  ...
  SELECT 'TABLE_NAME_33' AS TBL_NAME, 'c' AS src_type, COUNT(*) AS cnt FROM c_t33 WHERE BDATE='2023-05-31'
) stats
GROUP BY TBL_NAME
ORDER BY TBL_NAME;

这种方式把297个子查询简化为99个单表统计，执行计划复杂度大幅降低，资源占用也会显著减少。

2. 调整Tez资源参数 #

如果暂时无法重构SQL，可通过调整Tez资源配置，提升任务的资源承载能力：

-- 调整容器内存（根据集群资源调整，示例为4G）
set tez.container.size=4096;
-- 调整单个任务内存
set tez.task.resource.memory.mb=4096;
-- 降低任务并行度，减少同时运行的任务数
set tez.max.partition.factor=10;
-- 增加应用管理器（AM）的内存
set tez.am.resource.memory.mb=8192;

注意参数值需匹配集群实际资源，避免过度申请导致资源抢占。

3. 拆分查询分批执行 #

把99个UNION ALL分支拆分成多个小批次，分别执行后合并结果：

-- 第一批次执行，保存到临时表
CREATE TEMPORARY TABLE temp_res1 AS
Select "TABLE_NAME_2" as TBL_NAME, ... UNION ALL ... Select "TABLE_NAME_11" as TBL_NAME, ...;

-- 第二批次执行，保存到临时表
CREATE TEMPORARY TABLE temp_res2 AS
Select "TABLE_NAME_12" as TBL_NAME, ... UNION ALL ... Select "TABLE_NAME_22" as TBL_NAME, ...;

-- 第三批次执行，保存到临时表
CREATE TEMPORARY TABLE temp_res3 AS
Select "TABLE_NAME_23" as TBL_NAME, ... UNION ALL ... Select "TABLE_NAME_33" as TBL_NAME, ...;

-- 合并所有临时表结果
SELECT * FROM temp_res1 UNION ALL SELECT * FROM temp_res2 UNION ALL SELECT * FROM temp_res3;

这种方式将大任务拆分为多个小任务，每个任务的资源消耗在集群承载范围内。

4. 调整Hive查询限制参数 #

如果是Hive默认的查询复杂度限制导致的问题，可尝试调整以下参数：

-- 增加子查询嵌套层数限制
set hive.subquery.max.levels=100;
-- 增加动态分区数量限制
set hive.exec.max.dynamic.partitions.pernode=1000;

不过这类参数默认值通常能满足需求，优先尝试前三种方案。

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