开发经验介绍

去重指标的增量计算优化方案

1 背景

统计最近30天的uv（去重指标）

SELECT  visitor_id
FROM    (
            SELECT  item_id
                    ,count(DISTINCT visitor_id) AS ipv_uv_1d_001
            FROM    tbcdm.dwd_tb_log_obj_vst_di
            WHERE   ds <= '{bizdate}'
            AND     ds >= to_char(dateadd(to_date('${bizdate}','yyyymmdd'), - 29,'dd'),'yyyymmdd')
            GROUP BY item_id
        ) a

2 问题

上面代码存在的问题：需要的map task实例数很多，超过了99999的个数限制。

原因：每天日志的数据量很大，30天的更大，所以会有大量的map instance。

3 解决方案

解决方案1：

构建中间表，按天建立分区，先对1d数据轻度汇总，再对30天数据进行汇总

step1:构建item_id+visitior_id粒度的日汇总表，记作A

INSERT OVERWRITE TABLE mds_itm_vsr_xx(ds='${bizdate}')
SELECT  item_id
        ,visitor_id
FROM    (
            SELECT  item_id
                    ,visitor_id
            FROM    tbcdm.dwd_tb_log_obj_vst_di
            WHERE   ds = '{bizdate}'
            GROUP BY item_id
                     ,visitor_id
        ) a

setp2:对A进行30天汇总 

SELECT  visitor_id
FROM    (
            SELECT  item_id
                    ,count(DISTINCT visitor_id) AS ipv_uv_1d_001
            FROM    mds_itm_vsr_xx
            WHERE   ds <= '{bizdate}'
            AND     ds >= to_char(dateadd(to_date('${bizdate}','yyyymmdd'), - 29,'dd'),'yyyymmdd')
            GROUP BY item_id
        ) a

该方案优缺点：

提高了性能，但是每次计算都需要30个分区的数据，因此引出解决方案2，只需要1个分区的数据

解决方案2：

通过增量计算，建一张表，存储最近30d的数据

例子：求最近1天店铺的老访客数，老访客数的算法定义为：最近30天有访问，就算是老访客。

思路：建一张表A，保存最近30d的数据，和最近1天的数据做Join，如果在集合中，就是老访客，否则是新访客。表A：昨天记作0，前天记作-1，往前推30天，记作-30，那么就是说-30到-1的这些天的数据都在A中记录了。

有了这张A表，如何实现A表的动态更新？也就是保证A表存储的永远是最近30天的数据：

假设现在A表存储的是-30到-1天的数据，需要更新为-29到0天的数据。

首先将A表和第0天的数据取并集，然后再将第-30天的数据再删掉，此时A表的数据就是-29到0天的数据了。

总结更新公式为：An=An-1+Ao-A ，即今天的nd数据=历史全量数据+今天的增量数据-最老那天的增量数据

1 数据倾斜

1.1产生的原因和常见场景

产生的原因：有少数的实例数据量远大于其他实例，导致少数实例运行时间非常长，会产生数据倾斜，造成任务延迟。

常见场景：双11场景下，数据量是平时的好几倍，容易产生数据倾斜。此外，淘宝上会有一些店家销量很高，产生一些“爆款”商品，有的店家销量很低，也会产生数据倾斜。

1.2如何查看是否有数据倾斜

在任务的运行日志里面，可以看到每个task运行的时长，input数据量，如果说有一个task运行时间明显慢于其他task，或者input数据量明显大于其他task，说明产生了数据倾斜。

1.3常见的数据倾斜类型和处理方式

(1)groupby倾斜

原因：groupby的key分布不均匀

解决方案：设置参数set odps.sql.groupby.skewindata=true

真实案例：在一次双11中，小米单品的pv达到4kw+，店铺pv达到8kw+，导致根据商品和店铺算pv的时候，发生了数据倾斜

（2）join倾斜

原因：join on的key分布不均匀

解决方案1：如果两个表中有一个是大表，一个是小表，可以用mapjoin

原理：将小表加载进内存，和大表匹配，在map端进行聚合，避免了shuflle和reduce阶段

原：
SELECT  f.a
        ,f.b
FROM    A t
JOIN    B f
on      ( f.a = t.a AND f.ftime = 20110802)  

现：
SELECT /*+ mapjoin(A)*/
        f.a
        ,f.b
FROM    A t
JOIN    B f
on      ( f.a = t.a AND f.ftime = 20110802)

解决方案2： join的key里面有大量的null，也会发生倾斜。可以在join前先过滤掉null的数据再做join，或者把null转成一个随机数再做join

真实案例：在一个日志表里面，有大量的未登录用户的访问记录(userid is null)，如果直接和用户表进行关联，会产生数据倾斜

具体代码：

SELECT  *
FROM    log a
LEFT OUTER JOIN bmw_users b
ON      CASE WHEN a.user_id IS NULL THEN concat('dp_hive',rand() ) ELSE a.user_id END = b.user_id
;

（3）count distinct倾斜

原因：特殊值过多，常见于固定的特殊值比较多的场景，和join的场景2比较类似 

解决方案：可以先过滤特殊值，count完了的结果在加上特殊值的个数。或者根据具体场景进行具体分析。

（4）大表Join大表: a表加随机数    b表扩容

未经优化的大表与大表的Join的情况：

优化后的大表与大表的Join的情况：

select

    t1.id,

    t1.name,

    t2.id,

    t2.name

from

(    select

        id,

        concat(id,'_',cast(floor(rand() * 2) as int)) id_s,

        name

    from a
)t1

join

(    select 
        id,
        concat(id,'_','0') id_s,
        name 
    from b

union all

    select 
        id,
        concat(id,'_','1') id_s,
        name 
    from b
)t2;

这种情况完全没必要用动态分区，用了就会起reduce task，纯浪费资源，而且很有可能发生数据倾斜，正确的写法应该是：

INSERT OVERWRITE TABLE alimama_cloudatlas.tanx_pv_swifturl_internal_table partition(dt='20150502')
SELECT  split_part(content,'\t',1) AS nurl
        ,ca_j_get_host(split_part(content,'\t',1)) AS host
        ,split_part(content,'\t',2) AS stat
FROM    alimama_cloudatlas.tanx_pv_swifturl
WHERE   dt = '20150502'
;

3 如何处理暴力扫描

3.1什么是暴力扫描：

同时满足以下两个条件:

(1)扫描的分区数 > 一个月（31天）

(2)avg(天分区大小) > 500M or sum(总分区大小) > 100G

月任务不计入暴力扫描

3.2如何消除暴力扫描：

加分区过滤条件，或者减少扫描分区数，或者拆出中间小表再扫描小表的历史分区

案例1：

SELECT  a.id
        ,a.status
        ,b.url
        ,b.data_owner
        ,c.keyword
FROM    secods.odl_rcp_aliyun_case_main a
LEFT OUTER JOIN secods.odl_rcp_aliyun_url b
ON      a.id = b.main_id
LEFT OUTER join (
                    SELECT  main_id
                            ,wm_concat(', ', keyword) AS keyword
                    FROM    secods.odl_rcp_aliyun_case_keyword
                    WHERE   ds = '20150525'
                    GROUP BY main_id
                ) c
ON      a.id = c.main_id
WHERE   a.ds = '20150525'
AND     b.ds = '20150525'
;

这样的写法会造成 b 表暴力扫描，建议改成这样的写法即可避免暴力扫描（先从b表中select出相关的字段，然后再进行join）:

SELECT
    a.id,  
    a.status,
    b.url,
    b.data_owner,
    c.keyword
FROM
    (
    SELECT
    id,  
    status
    FROM
    secods.odl_rcp_aliyun_case_main
    WHERE  ds='20150525'
    ) a
LEFT OUTER join
    (SELECT
     main_id
     url,
     data_owner
     FROM 
     secods.odl_rcp_aliyun_url 
     WHERE ds='20150525';
    )b
ON a.id=b.main_id
LEFT OUTER join(
    SELECT
        main_id,
        wm_concat(', ', keyword) AS keyword
    FROM
        secods.odl_rcp_aliyun_case_keyword
    WHERE
        ds='20150525'
    GROUP BY
        main_id
)c
ON a.id=c.main_id

案例2：

SELECT  buyer_id AS userid
        ,shop_id
        ,ds AS bizdate
        ,count(order_id) AS alipay_num
        ,sum(confirm_paid_amt) AS alipay_amt
        ,sum(item_quantity) AS alipay_qty
FROM    tbcdm.dwd_tb_trd_ord_ent_di
WHERE   ds >= '20140526'
AND     ds <= '20150526'
AND     is_rga = 'Y'
AND     is_succ = 'Y'
GROUP BY buyer_id
         ,shop_id
         ,ds

tbcdm.dwd_tb_trd_ord_ent_di 这是一张交易大表，每天分区数据量在400G左右，如果每天都去扫一年的分区，计算消耗是非常大的，建议拆出一张中间表，每天做一次汇总，然后再去扫描这张中间表的一年分区，扫描数据量会减少很多：

INSERT OVERWRITE temp_table partition(ds='${bizdate}') 
SELECT 
  buyer_id AS userid,
  shop_id,
  count(order_id) AS alipay_num,
  sum(confirm_paid_amt) AS alipay_amt,
  sum(item_quantity) AS alipay_qty
  FROM 
  tbcdm.dwd_tb_trd_ord_ent_di
WHERE 
     ds ='${bizdate}'
     AND is_rga='Y' 
     AND is_succ='Y'
GROUP BY 
     buyer_id, 
     shop_id

测试发现，这样下来一天的分区量只有170M左右，一年的分区量还不到70G.可以避免暴力扫描。 

4 如何解决相似计算？

何为相似计算

相似计算 ：从相同的源表按照相似的逻辑进行加工。

解决方案

将两个相似计算的任务合并，合并规则： (1) 其他BU和公共层相似，以公共层为准 (2) 相似表是不同BU的话，以源表所在BU为准 (3) BU内部相似，以谁的下游多为准 (4) 下游数差不多，以先建表的为准 (5) 如果两个owner能主动达成一致，可以无视任何规则

动态分区性能优化

Join长尾优化

1 背景

join阶段会将相同的key分发到同一个instance上进行处理，如果某个key数据量比较大，会导致该instance执行时间比其他instance执行时间长。表现为：大部分instance已经执行完成，但是还剩个别instance一直处于执行中

一般来说，由于数据量倾斜导致的长尾比较常见，尤其是在双11期间，有些商家销量非常高，有些商家销量比较低，将这个订单数据和卖家维表做关联，会按照卖家id做分发，导致某个Instace数据量远超其他

2 方案

2.1 map join 方案

当某个表比较小的时候，可以用map join避免倾斜

2.2 join 因为热点值导致长尾

2.2.1 手动切分热点值

如果因为热点值导致了长尾，比较表比较大，无法用map join，可以先将热点key取出，此时根据热点key，主表就切分为热点数据以及非热点数据两部分，然后分别处理，最后合并

1. 取热点Key：将PV大于50000的商品ID取出到临时表

INSERT OVERWRITE TABLE topk_item
SELECT  item_id
FROM    (
            SELECT  item_id
                    ,count(1) AS cnt
            FROM    dwd_tb_log_pv_di
            WHERE   ds = '${bizdate}'
            AND     url_type = 'ipv'
            AND     item_id IS NOT NULL
            GROUP BY item_id
        ) a
WHERE   cnt >= 50000

2. 取出非热点数据

将主表（dwd_tb_log_pv_di）和热点key表（topk_item）外关联后通过条件b1.item_id is null，取关联不到的数据即非热点商品的日志数据，此时需要用Mapjoin。再用非热点数据关联商品维表，因为已经排除了热点数据，不会存在长尾。

select   ...
from
        (SELECT   *
         FROM     dim_tb_itm
         WHERE    ds = '${bizdate}'
         ) a
RIGHT OUTER join
        (SELECT   /*+mapjoin(b1)*/
                  b2.*
         FROM
                 (SELECT   item_id
                  FROM     topk_item
                  WHERE    ds = '${bizdate}'
                  ) b1
         RIGHT OUTER join
                 (SELECT   *
                  FROM     dwd_tb_log_pv_di
                  WHERE    ds = '${bizdate}'
                  AND      url_type = 'ipv'
                  ) b2
         ON       b1.item_id = coalesce(b2.item_id,concat("tbcdm",rand())
         WHERE    b1.item_id IS NULL
         ) l
ON       a.item_id = coalesce(l.item_id,concat("tbcdm",rand())

3. 取出热点数据

将主表（dwd_tb_log_pv_di）和热点Key表（topk_item）内关联，此时需要用Mapjoin，取到热点商品的日志数据。同时，需要将商品维表（dim_tb_itm）和热点Key表（topk_item）内关联，取到热点商品的维表数据，然后将第一部分数据外关联第二部分数据，因为第二部分只有热点商品的维表，数据量比较小，可以用Mapjoin避免长尾。

SELECT /*+mapjoin(a)*/
        ...
from    (
            SELECT /*+mapjoin(b1)*/
                    b2.*
            FROM    (
                        SELECT  item_id
                        FROM    topk_item
                        WHERE   ds = '${bizdate}'
                    ) b1
            join    (
                        SELECT  *
                        FROM    dwd_tb_log_pv_di
                        WHERE   ds = '${bizdate}'
                        AND     url_type = 'ipv'
                        AND     item_id IS NOT NULL
                    ) b2
            on      (b1.item_id = b2.item_id)
        ) l
LEFT OUTER join (
                    SELECT /*+mapjoin(a1)*/
                            a2.*
                    FROM    (
                                SELECT  item_id
                                FROM    topk_item
                                WHERE   ds = '${bizdate}'
                            ) a1
                    join    (
                                SELECT  *
                                FROM    dim_tb_itm
                                WHERE   ds = '${bizdate}'
                            ) a2
                    on      (a1.item_id = a2.item_id)
                ) a
ON      a.item_id = l.item_id

4. 将上面2、3的数据通过union all合并后即得到完整的日志数据，并且关联了商品的信息。

2.3 Skew Join Hint避免热值倾斜

3 影响及思考

当大表和大表Join时因为热点值发生倾斜，虽然可以通过修改代码解决，但是修改起来很麻烦，代码改动也很大，且影响阅读。而ODPS现有的参数设置使用不够灵活，倾斜值多的时候不可能将所有值都列在参数中，且倾斜值可能经常变动。因此，需要寻求更智能的解决方法，如生成统计信息，ODPS内部根据统计信息来自动生成解决倾斜的代码。

Map端的一读多写优化

Map端长尾优化

1 背景

一般情况下，map端很少发生长尾，如果发生了长尾，可能是下面两种情况：

（1）上游文件大小不均匀，小文件很多，由于会给每个小文件开启一个maptask，会引起长尾

（2）在map端做聚合时由于某些map instance读取文件的某个值特别多而引起长尾，主要是指Count Distinct操作。

2 方案

（1）对于小文件过多的情况，可以通过合并小文件

 设置参数：

set odps.sql.mapper.merge.limit.size=64
set odps.sql.mapper.split.size=256

（2）数据量分布不均匀，可以通过

distribute by rand()

 来打乱数据分布，使数据尽可能的分布均匀。

3 影响及思考

很多长尾问题都是与数据分布直接相关的，但是在日常开发过程中的确没有对数据分布规律提高重视，更多的是负责数据的组装。只在遇到耗时超长的任务时才被迫去查询数据的分布情况，才去做优化！因此最好是在开发过程中关注数据本身的问题，做到事前扼杀长尾，而非事后补救。当然，有些数据本身存在的不稳定性无法在短期内暴露，需要我们来一步步熟悉数据和总结优化经验，这样在面对突发数据暴增时，不至于慌乱无措。

UDF性能问题优化

Reduce端长尾优化

1 背景

reduce端负责的是对map端处理后的key_value数据进行聚合，即进行count、sum、avg等聚合操作，得到最终聚合的结果。
由于distinct的存在，数据无法在map端的shuffle阶段根据groupby先做一次聚合操作，减少数据的传输量，而是将所有的数据都传到reduce端。当key的数据分发不均匀时，会造成reduce长尾，特别是当多个distinct同时出现在一段sql代码中时，数据会被分发多次，会造成数据膨胀n倍，长尾方法n倍。

2 主要场景

造成reduce端长尾的主要原因：

（1）join时有大量的null值，导致所有的null值被分发到同一个reduce任务上，造成reduce长尾

（2）代码中有大量的count distinct，会导致数据膨胀n倍，也会将长尾现象放大n倍

（3）map端聚合后的key分布不均匀，造成reduce端长尾

（4）动态分区数过多造成小文件数过多，造成reduce长尾

3 方案

（1）Join阶段Null值随机化

（2）Map端聚合时某些key值过大，造成Reduce端倾斜

解决方式参考数据倾斜，对热点key进行单独计算

（3）动态分区数过多导致的长尾

参考动态分区优化，开启参数

set odps.sql.reshuffle.dynamicpt=true;

（4） Count Distinct造成的数据膨胀产生的长尾

避免使用distinct，将需要去重的字段进行groupby，把相同的Key的记录都合并成一条，然后计算不同统计口径出现的记录条数，当出现的记录条数大于0时，对应统计口径的指标累计加1

4 案例

下面案例主要是因Multi Distinct导致的计算性能问题：

其中支付买家数，支付商品数指标需要去重，由于分PC、无线、所有终端以及7天、30天等统计口径，所以就出现了12个Distinct，因此计算过程会出现根据12个组合Key去分发数据来统计支付买家数和支付商品数。这样使得节点运行效率变低，节点运行时长为1h14min。数据膨胀如下图：

 

针对上面的数据需求，需要计算支付买家数和支付商品数。因此可以先分别起子查询，先Group By 原表粒度＋Buyer_id，分别Count出PC、无线、所有终端以及7天、30天等统计口径的Buyer_id（这里可以理解为买家的支付次数），然后在子查询外Group By 原表粒度，当上一步的Count值大于0时，说明这一买家在这个统计口径有过支付，就计入支付买家数，否则不计。支付商品数同样的处理方式。然后再把支付商品数和支付买家数Join在一起。 

 按上述方案修改后的代码如下：

SELECT  t2.seller_id
       ,t2.price_seg_id
       ,SUM(case when pay_ord_byr_cnt_1w_001>0 then 1 else 0 end)    AS pay_ord_byr_cnt_1w_001 --最近7天支付买家数
       ,SUM(case when pay_ord_byr_cnt_1w_002>0 then 1 else 0 end)    AS pay_ord_byr_cnt_1w_002 --最近7天PC端支付买家数
       ,SUM(case when pay_ord_byr_cnt_1w_003>0 then 1 else 0 end)    AS pay_ord_byr_cnt_1w_003 --最近7天无线端支付买家数
       ,SUM(case when pay_ord_byr_cnt_1m_002>0 then 1 else 0 end)    AS pay_ord_byr_cnt_1m_002 --最近30天支付买家数
       ,SUM(case when pay_ord_byr_cnt_1m_003>0 then 1 else 0 end)    AS pay_ord_byr_cnt_1m_003 --最近30天PC端支付买家数
       ,SUM(case when pay_ord_byr_cnt_1m_004>0 then 1 else 0 end)    AS pay_ord_byr_cnt_1m_004 --最近30天无线端支付买家数
FROM
 (
    SELECT   a1.seller_id
             ,a2.price_seg_id
             ,buyer_id
             ,COUNT(buyer_id) AS pay_ord_byr_cnt_1m_002 --最近30天支付买家数
             ,COUNT(CASE WHEN is_wireless = 'N' THEN buyer_id  ELSE NULL END) AS pay_ord_byr_cnt_1m_003 --最近30天PC端支付买家数
             ,COUNT(CASE WHEN is_wireless = 'Y' THEN buyer_id  ELSE NULL END) AS pay_ord_byr_cnt_1m_004 --最近30天无线端支付买家数
             ,COUNT(case
                      when a1.ds>=TO_CHAR(DATEADD(TO_DATE('${bizdate}', 'yyyymmdd'), -6, 'dd'), 'yyyymmdd') then buyer_id
                      else null
                    end) AS pay_ord_byr_cnt_1w_001 --最近7天支付买家数
             ,COUNT(CASE WHEN a1.ds>=TO_CHAR(DATEADD(TO_DATE('${bizdate}', 'yyyymmdd'), -6, 'dd'), 'yyyymmdd') and is_wireless = 'N' THEN buyer_id
                         ELSE NULL
                    END) AS pay_ord_byr_cnt_1w_002 --最近7天PC端支付买家数
             ,COUNT(CASE WHEN a1.ds>=TO_CHAR(DATEADD(TO_DATE('${bizdate}', 'yyyymmdd'), -6, 'dd'), 'yyyymmdd') and is_wireless = 'Y' THEN buyer_id
                         ELSE NULL
                    END) AS pay_ord_byr_cnt_1w_003 --最近7天无线端支付买家数
    FROM
            (
               select *
               from  tbcdm.dwd_tb_trd_ord_ent_di
               WHERE  ds >= TO_CHAR(DATEADD(TO_DATE('${bizdate}', 'yyyymmdd'), -29, 'dd'), 'yyyymmdd')
               AND    ds <= '${bizdate}'
               AND    is_rga = 'Y'
               AND    is_pay = 'Y'
               AND    bc_type <> 'bbc'
            )a1
       JOIN ( SELECT item_id
                     ,price_seg_id
              FROM   mds_tb_sycm_tag_itm_1d
              WHERE  ds = '${bizdate}'
            ) a2
       ON  (  a1.item_id = a2.item_id  )
       GROUP  BY a1.seller_id      --原表粒度
                 ,a2.price_seg_id  --原表粒度
                 ,buyer_id
 )t2
 GROUP  BY t2.seller_id       --原表粒度
           ,t2.price_seg_id； --原表粒度