时间戳

首页 > 解决方案 > 尽管添加了会话设置，但查询仍需要时间

hadoop - 尽管添加了会话设置，但查询仍需要时间

问题描述

以下是 ETL 生成的查询

询问 -

SELECT infaHiveSysTimestamp('SS') as a0, 7991 as a1, single_use_subq30725.a1 as a2, SUBSTR(SUBSTR(single_use_subq30725.a2, 0, 5), 0, 5) as a3, CAST(1 AS SMALLINT) as a4, single_use_subq30725.a3 as a5, single_use_subq30725.a4 as a6, SUBSTR(SUBSTR(SUBSTR(single_use_subq30725.a8, (CASE WHEN 12 < (- LENGTH(single_use_subq30725.a8)) THEN 0 ELSE 12 END), 104857600), 0, 20), 0, 20) as a7, infaNativeUDFCallString('TO_CHAR', single_use_subq30725.a5) as a8, infaHiveSysTimestamp('SS') as a9, CAST(infaNativeUDFCallDate('TRUNC', single_use_subq30725.a6, 'DD') AS DATE) as a10 FROM (SELECT (CASE WHEN 1 = t1.a1 THEN t1.a0 ELSE CAST(NULL AS TIMESTAMP) END) as a0, infaNativeUDFCallDate('TRUNC', (CASE WHEN 1 = t1.a1 THEN t1.a0 ELSE CAST(NULL AS TIMESTAMP) END), 'DD') as a1 
FROM 
    (
        SELECT MAX(t1.a0) as a0, MAX(t1.a1) as a1 
        FROM (
            SELECT mstr_load_audit.last_run_ts as a0, 1 as a1 FROM mstr_etl.mstr_load_audit WHERE interface_name='m_CTM_RAWTLogData_target_tbl'
            ) t1
        )t1
    ) single_use_subq39991
JOIN (
    SELECT w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.CREATE_TS as a0, CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.txACTION_ID AS STRING) as a1, SUBSTR(SUBSTR(infaNativeUDFCallString('TO_CHAR', CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.STORE_NUM AS DECIMAL(18, 0))), (CASE WHEN 0 < (- LENGTH(infaNativeUDFCallString('TO_CHAR', CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.STORE_NUM AS DECIMAL(18, 0))))) THEN 0 ELSE 0 END), 10), (CASE WHEN 0 < (- LENGTH(SUBSTR(infaNativeUDFCallString('TO_CHAR', CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.STORE_NUM AS DECIMAL(18, 0))), (CASE WHEN 0 < (- LENGTH(infaNativeUDFCallString('TO_CHAR', CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.STORE_NUM AS DECIMAL(18, 0))))) THEN 0 ELSE 0 END), 10))) THEN 0 ELSE 0 END), 10) as a2, SUBSTR(SUBSTR(infaNativeUDFCallString('TO_CHAR', CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.LANE_NUM AS DECIMAL(18, 0))), (CASE WHEN 0 < (- LENGTH(infaNativeUDFCallString('TO_CHAR', CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.LANE_NUM AS DECIMAL(18, 0))))) THEN 0 ELSE 0 END), 10), (CASE WHEN 0 < (- LENGTH(SUBSTR(infaNativeUDFCallString('TO_CHAR', CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.LANE_NUM AS DECIMAL(18, 0))), (CASE WHEN 0 < (- LENGTH(infaNativeUDFCallString('TO_CHAR', CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.LANE_NUM AS DECIMAL(18, 0))))) THEN 0 ELSE 0 END), 10))) THEN 0 ELSE 0 END), 10) as a3, SUBSTR(SUBSTR(infaNativeUDFCallString('TO_CHAR', CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.tx_NUM AS DECIMAL(18, 0))), (CASE WHEN 0 < (- LENGTH(infaNativeUDFCallString('TO_CHAR', CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.tx_NUM AS DECIMAL(18, 0))))) THEN 0 ELSE 0 END), 20), (CASE WHEN 0 < (- LENGTH(SUBSTR(infaNativeUDFCallString('TO_CHAR', CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.tx_NUM AS DECIMAL(18, 0))), (CASE WHEN 0 < (- LENGTH(infaNativeUDFCallString('TO_CHAR', CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.tx_NUM AS DECIMAL(18, 0))))) THEN 0 ELSE 0 END), 20))) THEN 0 ELSE 0 END), 20) as a4, CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.LOYALTY_DEV_NUM AS DECIMAL(28, 0)) as a5, CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.tx_DT AS TIMESTAMP) as a6, CAST(w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.ETL_LOAD_DT AS TIMESTAMP) as a7, w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.tx_TS as a8 
FROM 
sourcedb.W5883634877684653839_Read_lcl_tlog_raw_2_VIEW__m_CTM_RAWTLogData_target_tbl
) 
single_use_subq30725 
WHERE (single_use_subq39991.a0 < single_use_subq30725.a0) AND (single_use_subq39991.a1 <= single_use_subq30725.a7)]

由于此查询是在 hive 下推模式下生成的，因此我们在环境 sql 中添加了以下设置

SET hive.vectorized.execution.enabled=true;
SET hive.vectorized.execution.reduce.enabled=true;
SET hive.cbo.enable=true;
SET hive.compute.query.using.stats=true;
set hive.exec.orc.split.strategy=BI;
set hive.merge.tezfiles=true;

但我们没有看到任何显着的收益。我们确实可以选择以传统的批处理模式移动此作业 - 我们通过 shell 脚本运行此作业。是否有通过减少执行时间对查询进行任何更改的范围。我确信我们可以摆脱所有类型转换并减少那里的执行时间。有没有什么额外的东西，我们可以试试。

标签： hadoophivehadoop-yarnhiveqlapache-tez

解决方案

这加入您的查询：

JOIN (
    SELECT 
 ... SKIPPED ...
) 
single_use_subq30725 
WHERE (single_use_subq39991.a0 < single_use_subq30725.a0) AND (single_use_subq39991.a1 <= single_use_subq30725.a7)]

由于未指定 ON 条件，因此可用作 CROSS JOIN。在此 CROSS JOIN 之后，正在使用此过滤数据集 WHERE (single_use_subq39991.a0 < single_use_subq30725.a0) AND (single_use_subq39991.a1 <= single_use_subq30725.a7)

实际上它不会乘以行并且应该作为 MAP-JOIN 工作，因为第一个子查询最多返回一行：

SELECT MAX(t1.a0) as a0, MAX(t1.a1) as a1

添加此设置以启用 map-join：set hive.auto.convert.join=true; 检查 map-join 是否在 EXPLAIN 输出中。

但最大的问题不是这个 CROSS (MAP?) join 本身。在第二个查询中读取表时，它可以防止谓词下推在加入之前工作。

我建议完全删除连接并计算第一个查询一次并提供a0和a1作为where子句中的参数。通过这种方式，您将消除不必要的连接，并且谓词下推可以直接工作。

例如，PPD 可以应用于此列：w5883634877684653839_read_lcl_tlog_raw_2_view__m_ctm_rawtlogdata_target_tbl.CREATE_TS as a0

检查 PPD 和其他性能设置：https ://stackoverflow.com/a/48296562/2700344

推荐阅读