Like 函数优化

优化前

SQL

SELECT
    A.DT,
    A.sdcuser_id as user_id,
    A.visitorid as app_device_id,
    A.visitid as sd_sid,
    b.activity_id,
    a.sdcSource      
FROM
    base.UDS_B_I_OMM_WEBTRENDS_T A,  -- 4426985
    tmp.LCBI_HDBG_HDMT_LIST B      -- nums : 1330
WHERE
    A.DT = 20171112       
    AND B.DT = 20171112       
    AND A.referer LIKE concat(b.url, '%');

优化手段

未优化

执行耗时

• TEZ ： 5625.915 seconds
• MR ：5784.321 seconds

LIKE 函数优化

SQL

SELECT
    A.DT,
    A.sdcuser_id as user_id,
    A.visitorid as app_device_id,
    A.visitid as sd_sid,
    b.activity_id,
    a.sdcSource      
FROM
    base.UDS_B_I_OMM_WEBTRENDS_T A,  -- 4426985
    tmp.LCBI_HDBG_HDMT_LIST B      -- nums : 1330
WHERE
    A.DT = 20171112       
    AND B.DT = 20171112       
    AND A.referer LIKE concat(b.url, '%');

优化手段

目前通过在JVM内存中缓存目标对象的方式，实现加速like匹配。

执行耗时

• TEZ ： 1600 seconds
• MR ： 1549 seconds

SQL语句调整(在函数优化后)

SQL

SELECT
    A.DT,
    A.sdcuser_id as user_id,
    A.visitorid as app_device_id,
    A.visitid as sd_sid,
    b.activity_id,
    a.sdcSource      
FROM
    base.UDS_B_I_OMM_WEBTRENDS_T A,  -- 4426985
    (select b.activity_id,concat(b.url, '%') as url from tmp.LCBI_HDBG_HDMT_LIST B where B.DT = 20171112  ) b      -- nums : 1330
WHERE
    A.DT = 20171112       
    AND A.referer LIKE b.url;

优化手段

把concat 函数提前计算好，这样在join及数据过滤的时候，执行效率至少有50%效率提升

执行耗时

• TEZ ： 796 seconds
• MR ：569.829 seconds