Presto转Hive SQL后查询耗时剧增，是否终止或优化？

是否终止查询？ #

建议直接终止。原Presto执行仅需10分钟且结果仅100行，当前Hive查询运行75分钟仍无结果，说明查询逻辑或执行配置存在明显性能瓶颈，继续执行只会浪费集群资源。

核心优化方向 #

结合你提供的SQL，以下是针对性的优化点：

1. 消除冗余UNION，合并日期过滤条件 #

原SQL中对theme_activate_event_history分4段日期做UNION，完全可以合并为一个WHERE条件，避免多次重复扫描同一张大表：

-- 原冗余UNION写法（可删除）
select * from theme_activate_event_history where date_key between '2019-01-01' and '2020-01-01' ...
union
select * from theme_activate_event_history where date_key between '2020-01-01' and '2021-01-01' ...
-- 优化后合并写法
select * from theme_activate_event_history
where date_key between '2019-01-01' and last_day(add_months(current_date, -1))
  and activate = 'true' 
  and themetype in ('ThemeBundle','ScreenSaver','Skin','Audio')

2. 避免重复扫描大表，合并数据聚合逻辑 #

原SQL两次扫描agg_device_streaming_metrics_daily表（分别用于计算theme_status和streaming_hours），可以合并为一次扫描，同时聚合所需字段，减少IO开销：

-- 合并后的streaming数据查询
WITH streaming_data AS (
    SELECT
        substring(date_key, 1, 7) as year_month,
        last_day(add_months(date_key, -1)) as year_month_ed,
        upper(account_id) as account_id,
        play_seconds,
        sum(play_seconds) over(partition by substring(date_key, 1, 7), upper(account_id)) as total_play_seconds
    FROM agg_device_streaming_metrics_daily
    WHERE date_key between date_add(last_day(add_months(current_date, -2)),1) and last_day(add_months(current_date, -1)) 
      and play_seconds > 0
)

3. 移除冗余DISTINCT #

最外层的distinct完全多余：内层tbl已按year_month和account_id分组，streaming子查询也按相同维度分组，INNER JOIN后不会产生重复行，保留DISTINCT会额外增加计算开销。

4. 确保分区裁剪生效 #

检查agg_device_streaming_metrics_daily和theme_activate_event_history是否按date_key分区：

• 如果是分区表，确保WHERE条件中的date_key过滤是直接针对分区键的（避免函数包裹分区键，比如date(date_key)会导致分区裁剪失效）
• 可添加参数SET hive.exec.dynamic.partition=true; SET hive.exec.dynamic.partition.mode=nonstrict;（如果使用动态分区插入）

5. 调整Hive执行参数 #

在查询开头添加以下参数优化执行效率：

SET hive.exec.parallel=true; -- 开启并行执行多个MapReduce任务
SET hive.auto.convert.join=true; -- 自动将小表转为MapJoin，减少Shuffle
SET hive.mapreduce.job.reduces=8; -- 根据集群资源调整Reducer数量（可按需调整）
SET hive.exec.compress.intermediate=true; -- 开启中间数据压缩

优化后的完整SQL示例 #

SET hive.limit.query.max.table.partition = 1000000;
SET hive.exec.parallel=true;
SET hive.auto.convert.join=true;
SET hive.mapreduce.job.reduces=8;
SET hive.exec.compress.intermediate=true;

INSERT INTO TABLE schema.temp_tbl partition(year_month_key)
WITH streaming_data AS (
    SELECT
        substring(date_key, 1, 7) as year_month,
        last_day(add_months(date_key, -1)) as year_month_ed,
        upper(account_id) as account_id,
        play_seconds,
        sum(play_seconds) over(partition by substring(date_key, 1, 7), upper(account_id)) as total_play_seconds
    FROM agg_device_streaming_metrics_daily
    WHERE date_key between date_add(last_day(add_months(current_date, -2)),1) and last_day(add_months(current_date, -1)) 
      and play_seconds > 0
),
theme_events AS (
    SELECT
        upper(virtualuserid) as account_id,
        min(date_key) as date_key
    FROM theme_activate_event_history
    WHERE date_key between '2019-01-01' and last_day(add_months(current_date, -1))
      and activate = 'true' 
      and themetype in ('ThemeBundle','ScreenSaver','Skin','Audio')
    GROUP BY virtualuserid
),
tbl_0 AS (
    SELECT
        sd.year_month,
        sd.account_id,
        CASE WHEN te.account_id IS NOT NULL THEN 1 ELSE 0 END as theme_status
    FROM streaming_data sd
    LEFT JOIN theme_events te 
        ON sd.account_id = te.account_id
        AND te.date_key <= sd.year_month_ed
),
tbl AS (
    SELECT
        year_month,
        account_id,
        CASE WHEN max(theme_status) = 1 THEN 'With Theme' ELSE 'No Theme' END as theme_status
    FROM tbl_0
    GROUP BY year_month, account_id
),
streaming_hours AS (
    SELECT
        year_month,
        account_id,
        cast(total_play_seconds / 3600 as double) as streaming_hours
    FROM streaming_data
    GROUP BY year_month, account_id, total_play_seconds
)
SELECT
    tbl.account_id,
    tbl.theme_status,
    streaming_hours.streaming_hours,
    tbl.year_month as year_month_key
FROM tbl
INNER JOIN streaming_hours 
    ON tbl.account_id = streaming_hours.account_id 
    AND tbl.year_month = streaming_hours.year_month;

内容的提问来源于stack exchange，提问作者Crubal Chenxi Li