时间戳

首页 > 解决方案 > 需要大量时间的生产 Hadoop 查询

sql - 需要大量时间的生产 Hadoop 查询

问题描述

当前状态

我们有一个运行 2 多个小时的查询。在检查进度时，查询在与表 T5连接期间和查询的最后阶段花费了大量时间。有什么方法可以简化对这个查询的处理吗？我无法使用聚合函数代替 rank() 因为使用的orderby有点复杂。

我们已经尝试过的

我们已经将子查询转换为select子句中的case语句，并帮助减少了执行时间，但这并不重要。我们简化了 T3、T4 和 T6 的相关查询。

SELECT * FROM 
        (SELECT T2.f1, T2.f2 .... T5.f19, T5.f20, 
                   case when T1.trxn_id is null then T2.crt_ts
                        when T1.trxn_id is not null and T5.acct_trxn_id is not null and T2.crt_ts >= T5.crt_ts then T2.crt_ts
                        when T1.trxn_id is not null and T5.acct_trxn_id is not null and T2.crt_ts < T5.crt_ts then T5.crt_ts
                    end as crt_ts , 
                    row_number() over ( partition by T2.w_trxn_id,
                                            if(T1.trxn_id is null, 'NULL', T1.trxn_id)
                                            order by T2.business_effective_ts desc,
                                            case when T1.trxn_id is null then T2.crt_ts
                                            when T1.trxn_id is not null and T5.acct_trxn_id is not null and T2.crt_ts >= T5.crt_ts then T2.crt_ts
                                            when T1.trxn_id is not null and T5.acct_trxn_id is not null and T2.crt_ts < T5.crt_ts then T5.crt_ts
                                            when T1.trxn_id is not null and T5.acct_trxn_id is null then T2.crt_ts end desc
                                        ) as rnk
                FROM(SELECT * FROM T3 WHERE title_name = 'CAPTURE' and tr_dt IN (SELECT tr_dt FROM DT_LKP))
                T2
                LEFT JOIN (SELECT * FROM T6 WHERE tr_dt IN (SELECT tr_dt FROM DT_LKP)) 
                T1 ON T2.w_trxn_id = T1.w_trxn_id AND T2.business_effective_ts = T1.business_effective_ts
                LEFT JOIN (SELECT f1, f3. ... f20 FROM T4 WHERE tr_dt IN (SELECT tr_dt FROM DT_LKP)) 
                T5 ON T1.trxn_id = T5.acct_trxn_id
                WHERE if(T1.trxn_id is null, 'NULL', T1.trxn_id) = if(T5.acct_trxn_id is null, 'NULL', T5.acct_trxn_id)
        ) FNL WHERE rnk = 1

标签： sqlhadoophivequery-optimization

解决方案

不确定这是否会对您有很大帮助。有一些相当奇怪的 WHERE 子句：

WHERE if(T1.trxn_id is null, 'NULL', T1.trxn_id) = if(T5.acct_trxn_id is null, 'NULL', T5.acct_trxn_id)

这可能是为了加入NULLs 以及正常值。然后它不起作用，因为首先连接条件是T5 ON T1.trxn_id = T5.acct_trxn_id这意味着 NULL 未连接，然后WHERE在连接后用作过滤器。如果T5未加入，则 T5.acct_trxn_id 在 WHERE 中转换为“NULL”字符串，并与 NOT NULL T1.trxn_id 值进行比较，很可能会被过滤掉，在这种情况下类似于 INNER JOIN。如果它发生 T1.trxn_id 为 NULL （驱动表），它转换为字符串 'NULL' 并与总是字符串 'NULL' 进行比较（因为根据 ON 子句无论如何都没有加入）并且这样的行通过了（我没有测试它虽然）。逻辑看起来很奇怪，我认为它不能按预期工作或转换为 INNER。如果要连接所有包括 NULL 的内容，请将此 WHERE 移至 JOIN ON 子句。

如果有许多带有 NULL 的行，则使用字符串 'NULL' 替换的 NULL 连接将增加行并导致重复。

实际上，在调查 JOIN 性能不佳时，请检查两件事：

  1. 连接键不重复或预期重复
  2. 连接键（以及按 row_number 中的列分区）没有倾斜，请参见：https ://stackoverflow.com/a/53333652/2700344和此：https ://stackoverflow.com/a/51061613/2700344

如果一切正常，则调整适当的减速器并行度，减少 hive.exec.reducers.bytes.per.reducer以使更多减速器运行

DT_LKP即使您知道它包含一些绝对不是/不应该是事实表的日期，也要尽可能减少，如果可能的话，使用 CTE 过滤它。

还要稍微简化逻辑（这不会提高性能，但会简化代码）。选择案例：

when T1.trxn_id is not null and T5.acct_trxn_id is not null and T2.crt_ts >= T5.crt_ts then T2.crt_ts
when T1.trxn_id is not null and T5.acct_trxn_id is not null and T2.crt_ts < T5.crt_ts then T5.crt_ts

<=>

else greatest(T2.trxn_id,T5.crt_ts)

如果 T5.crt_ts 为空，您的 case 语句将返回空，最大（）也将返回空

row_number 中的 CASE 语句简化：

case when case when (T1.trxn_id is null) or (T5.acct_trxn_id is null) then T2.crt_ts
     else greatest(T2.trxn_id,T5.crt_ts)
 end

还有这个：if(T1.trxn_id is null, 'NULL', T1.trxn_id)<=>NVL(T1.trxn_id,'NULL')

当然这些只是建议，我没有测试它们

推荐阅读