如何解决Haas表分区范围超出限制的ERROR [42000] [Cloudera][Hardy] (80)错误

问题背景 #

通过ODBC连接Haas表执行Hive SQL时触发ERROR [42000] [Cloudera][Hardy] (80)错误，原因是扫描cosmoss_job_notes_ext表的分区数（3011）超过metastore服务配置的3000上限。原查询用于获取近4天数据，且无表操作权限，仅能通过修改查询语句控制扫描分区范围。

核心优化思路 #

利用Hive分区裁剪机制，让查询仅扫描符合时间范围的分区，避免全量扫描所有分区。关键是在过滤条件中明确关联表的分区字段，让metastore只返回符合条件的分区列表，从而将扫描分区数控制在3000以内。

具体修改方案 #

方案一：明确指定分区日期范围（优先推荐） #

假设表按日期字段（如dt，格式yyyy-MM-dd）分区，通过直接过滤分区字段缩小扫描范围，同时保留原业务时间条件：

Select
haas_createdon AS record_created_date,
record_type AS record_type,
circuit_id AS prod_svce_id,
version_number AS version_number,
circuit_status AS circuit_status,
product_code AS product_code,
job_number AS job_number,
job_status AS job_status,
job_start_date AS job_start_date,
job_end_date AS job_end_date,
notes_creation_date AS notes_creation_date,
notes_update_date AS notes_update_date,
notes_sequence_no AS note_sequence_no,
note_text AS note_text,
haas_createdon AS created_date
From haasContainerName.tablename
where 
-- 先通过分区字段限制日期范围，直接排除无关分区
dt >= DATE_SUB(TO_DATE(from_unixtime(UNIX_TIMESTAMP())), 4)
AND dt <= TO_DATE(from_unixtime(UNIX_TIMESTAMP()))
AND
product_code IN ('Product Codes')
AND
substr(note_text, 1, 4) IN ('Note text')
AND 
UNIX_TIMESTAMP(notes_creation_date) >= UNIX_TIMESTAMP(CONCAT(TO_DATE(from_unixtime(UNIX_TIMESTAMP())),' 19:00:00')) - (86400 * 4)

若表的分区键不是dt，需替换为实际分区字段（如haas_createdon_dt或notes_creation_date_dt），可通过SHOW PARTITIONS haasContainerName.tablename;查询确认。

方案二：缩小时间范围（业务允许时使用） #

如果近4天的范围仍会触发分区数上限，可适当缩小时间窗口，比如调整为3天半：

Select
haas_createdon AS record_created_date,
record_type AS record_type,
circuit_id AS prod_svce_id,
version_number AS version_number,
circuit_status AS circuit_status,
product_code AS product_code,
job_number AS job_number,
job_status AS job_status,
job_start_date AS job_start_date,
job_end_date AS job_end_date,
notes_creation_date AS notes_creation_date,
notes_update_date AS notes_update_date,
notes_sequence_no AS note_sequence_no,
note_text AS note_text,
haas_createdon AS created_date
From haasContainerName.tablename
where 
product_code IN ('Product Codes')
AND
substr(note_text, 1, 4) IN ('Note text')
AND 
-- 缩小时间范围至3天半，减少扫描分区数
UNIX_TIMESTAMP(notes_creation_date) >= UNIX_TIMESTAMP(CONCAT(TO_DATE(from_unixtime(UNIX_TIMESTAMP())),' 19:00:00')) - (86400 * 3.5)

方案三：直接过滤分区时间字段（若分区键为时间类型） #

如果表的分区键是完整时间戳或日期字符串（如notes_creation_date），可直接用日期函数过滤：

Select
haas_createdon AS record_created_date,
record_type AS record_type,
circuit_id AS prod_svce_id,
version_number AS version_number,
circuit_status AS circuit_status,
product_code AS product_code,
job_number AS job_number,
job_status AS job_status,
job_start_date AS job_start_date,
job_end_date AS job_end_date,
notes_creation_date AS notes_creation_date,
notes_update_date AS notes_update_date,
notes_sequence_no AS note_sequence_no,
note_text AS note_text,
haas_createdon AS created_date
From haasContainerName.tablename
where 
-- 直接通过分区字段过滤时间范围
notes_creation_date >= DATE_SUB(CONCAT(TO_DATE(from_unixtime(UNIX_TIMESTAMP())),' 19:00:00'), INTERVAL 4 DAY)
AND
product_code IN ('Product Codes')
AND
substr(note_text, 1, 4) IN ('Note text')

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