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

Question

当前状态

我们有一个运行了 2 小时以上的查询。在检查进度时，查询在与表 T5 的join 期间以及查询的最后阶段花费了大量时间。有什么方法可以简化或对此查询做些什么吗？我无法使用聚合函数代替 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

Answer 1

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

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

这可能用于连接 NULL 以及正常值。然后它不起作用，因为首先，连接条件是 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

如果一切正常，则调整适当的 reducer 并行度，减少 hive.exec.reducers.bytes.per.reducer 以使更多的 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为null，你的case语句会返回null，greatest()也会返回null

简化了 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')

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