Производственный запрос Hadoop, который занимает много времени
Текущий статус
У нас есть запрос, который выполняется более 2 часов. При проверке выполнения запрос тратит много времени на присоединиться с таблицей T5 и на заключительном этапе запроса. Можно ли как-то упростить выполнение этого запроса? Мне не удалось использовать агрегатные функции вместо rank(), так как используемый Сортировать по немного сложен.
Что мы уже пробовали
Мы уже преобразовали операторы подзапросы в кейс в предложении Выбрать и помогли сократить время выполнения, но это не имело существенного значения. Мы упростили связанный запрос для 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
Ответы 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)
Это, вероятно, для объединения NULLs, а также нормальных значений. Тогда это не работает, потому что
Во-первых, условие соединения: T5 ON T1.trxn_id = T5.acct_trxn_id это означает, что значения NULL не объединяются, затем WHERE работает как фильтр после соединения. ЕСЛИ T5 не соединен, то T5.acct_trxn_id преобразуется в строку «NULL» в WHERE и сравнивается со значением NOT NULL T1.trxn_id и, скорее всего, отфильтровывается, в этом случае работает как INNER JOIN. Если это происходит, T1.trxn_id равен NULL (управляющая таблица), он преобразуется в строку «NULL» и сравнивается с всегда строкой «NULL» (потому что все равно не соединяется в соответствии с предложением ON), и такая строка передается (хотя я не проверял это ). Логика выглядит странно, и я думаю, что она не работает должным образом или преобразована во ВНУТРЕННЮЮ. Если вы хотите присоединиться ко всем, включая NULL, переместите WHERE в предложение JOIN ON.
Если имеется много строк со значениями NULL, то объединение по значениям NULL с использованием подстановки строкой 'NULL' приведет к умножению строк и приведет к дублированию.
На самом деле, исследуя низкую производительность JOIN, проверьте две вещи:
- Ключи соединения не дублируются или ожидается дублирование
- Ключи соединения (а также разделение по столбцам в 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 имеет значение null, ваш оператор case вернет значение null, и метод great() также вернет значение null.
Оператор CASE в row_number упрощен:
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')
Конечно, это только предложения, я их не проверял.