[大数据]数据仓库-IP段关联计算优化-从优化原理到优化手段一一道来

?

为什么要优化？

营销活动指标体系需要用日志数据进行多维度分析，其中一个维度就是通过IP段获取到用户的所属地域进行分析，但IP段与其它字段的映射方式不同，IP段是一个范围，关联时是一对多关系，在资源集中使用时段，1万左右的数据量的耗时可达一小时以上，严重影响指标的及时性，所以要进行优化，来解决任务耗时高的问题

优化手段

1 减少参与计算的数据量

当前IP表中记录总数为 772845 条，但中国所使用的IP段仅有 91374 条，因此在统计时计，限制 country_name = '中国'，能够有效的减少任务执行时间；

2 任务层面优化

数据仓库的计算引擎是MR，下面简单介绍下MapReduce计算模型

MapReduce计算模型

MapReduce是作为计算引擎出现的，在处理超大规模的数据集上，MapReduce性能可观，通过分布式计算，将大规模数据计算任务分解，MapReduce计算模型是由Map任务和Reduce任务组成，各自承担不同的功能

优化原理

思路：通过增加Map任务的数量，提高任务计算速度

通常来讲，表的一个分区就对应物理存储（HDFS）的一个文件（上图中的block块），也就对应一个Map任务；

这次优化就是通过构建表的二级、三级分区来增加物理存储文件的数量，从而间接的增加Map任务数量，让Map并行计算少量的数据，达到优化目的

二级分区：ip_category，将start_ip（起始IP）和end_ip（结束IP）按照固定范围划分，分区值A、B、C、D、E

三级分区：ip_seg1，取start_ip（起始IP）的第一段作为分区，分区值 198、199、202 ...

优化如下所示：

• 优化后的表结构

CREATE TABLE IF NOT EXISTS ip_location_mapping_dispose_mf
(
    dw_gmt_create DATETIME COMMENT '数据仓库创建时间',
    gmt_modified  DATETIME COMMENT '记录修改时间',
    gmt_create    DATETIME COMMENT '记录创建时间',
    id            BIGINT   COMMENT '自增id，主键',
    start_ip      STRING   COMMENT 'IP段起始IP地址',
    end_ip        STRING   COMMENT 'IP段结束IP地址',
    start_ip_long BIGINT   COMMENT 'IP段起始IP十进制数',
    end_ip_long   BIGINT   COMMENT 'IP段结束IP十进制数',
    start_ip_seg  BIGINT   COMMENT 'start_ip片段',
    country_name  STRING   COMMENT 'IP所属国家',
    province_name STRING   COMMENT 'IP所属省份',
    city_name     STRING   COMMENT 'IP所属城市',
    isp_name      STRING   COMMENT '网络服务提供商',
    ext0          STRING   COMMENT '扩展字段',
    ext1          STRING   COMMENT '扩展字段',
    ext2          STRING   COMMENT '扩展字段',
    ext3          STRING   COMMENT '扩展字段',
    ext4          STRING   COMMENT '扩展字段'
) 
COMMENT 'IP所属地映射表-月表'
PARTITIONED BY
(
    ms            STRING   COMMENT '日期分区, 格式:yyyymm',
    ip_category   STRING   COMMENT 'ip分类：A B C D E',
    ip_seg1       BIGINT   COMMENT 'ip第1段分区'
);

• 构建分区的sql

WITH t_clear_ip_location_mapping AS (
    SELECT  gmt_modified
            ,gmt_create
            ,id
            ,start_ip
            ,end_ip
            ,start_ip_long
            ,end_ip_long
            ,start_ip_seg
            ,country_name
            ,province_name
            ,city_name
            ,isp_name
            ,ext0
            ,ext1
            ,ext2
            ,ext3
            ,ext4
            ,CASE   WHEN start_ip_long >= 16777216 AND end_ip_long <= 2147483647 THEN 'A'
                    WHEN start_ip_long >= 2147483648 AND end_ip_long <= 3221225471 THEN 'B'
                    WHEN start_ip_long >= 3221225472 AND end_ip_long <= 3758096383 THEN 'C'
                    WHEN start_ip_long >= 3758096384 AND end_ip_long <= 4026531839 THEN 'D'
                    WHEN start_ip_long >= 4026531840 AND end_ip_long <= 4160749567 THEN 'E'
            END AS ip_category
            ,IF(SPLIT_PART(start_ip,'.',1) <> CAST(SPLIT_PART(end_ip,'.',1) AS BIGINT),'N','Y') AS is_equal_ipseg1
    FROM    ip_location_mapping_mf
    WHERE   ms = '${before_month_str}'
    AND     record_status = 0
    AND     country_name = '中国'
)

INSERT OVERWRITE TABLE ip_location_mapping_dispose_mf PARTITION (ms = '${before_month_str}',ip_category,ip_seg1)
SELECT  GETDATE() AS dw_gmt_create
        ,gmt_modified
        ,gmt_create
        ,id
        ,start_ip
        ,end_ip
        ,start_ip_long
        ,end_ip_long
        ,start_ip_seg
        ,country_name
        ,province_name
        ,city_name
        ,isp_name
        ,ext0
        ,ext1
        ,ext2
        ,ext3
        ,ext4
        ,ip_category
        ,CAST(SPLIT_PART(start_ip,'.',1) AS INT) AS ip_seg1
FROM    t_clear_ip_location_mapping
WHERE   is_equal_ipseg1 = 'N'
UNION ALL
SELECT  GETDATE() AS dw_gmt_create
        ,gmt_modified
        ,gmt_create
        ,id
        ,start_ip
        ,end_ip
        ,start_ip_long
        ,end_ip_long
        ,start_ip_seg
        ,country_name
        ,province_name
        ,city_name
        ,isp_name
        ,ext0
        ,ext1
        ,ext2
        ,ext3
        ,ext4
        ,ip_category
        ,CAST(SPLIT_PART(end_ip,'.',1) AS INT) AS ip_seg1
FROM    t_clear_ip_location_mapping
WHERE   is_equal_ipseg1 = 'N'
UNION ALL
SELECT  GETDATE() AS dw_gmt_create
        ,gmt_modified
        ,gmt_create
        ,id
        ,start_ip
        ,end_ip
        ,start_ip_long
        ,end_ip_long
        ,start_ip_seg
        ,country_name
        ,province_name
        ,city_name
        ,isp_name
        ,ext0
        ,ext1
        ,ext2
        ,ext3
        ,ext4
        ,ip_category
        ,CAST(SPLIT_PART(start_ip,'.',1) AS INT) AS ip_seg1
FROM    t_clear_ip_location_mapping
WHERE   is_equal_ipseg1 = 'Y'
;

• 优化后的关联方式

select *
from(
  SELECT  MAX(buss_line) AS buss_line
          ,app_id
          ,MAX(product_name) AS app_name
          ,cookie_id
          ,uid
          ,MAX(channel) AS channel
          ,MAX(os) AS os
          ,MAX(device_brand) AS device_brand
          ,SPLIT_PART(MAX(ip),',',1,1) AS ip
          ,MAX(ip_long) AS ip_long
          ,MIN(spm_time) AS first_spm_time
          ,MAX(spm_time) AS last_spm_time
          ,COUNT(CASE WHEN action = '2' THEN 1 ELSE NULL END) AS visit_cnt
          ,COUNT(CASE WHEN action = '1' THEN 1 ELSE NULL END) AS click_cnt
          ,DATEDIFF(MAX(spm_time),MIN(spm_time),'ss') AS during_time
          ,SUM(CASE WHEN action = '4' AND events_id LIKE '%activity_during_visit%' AND events_params["duration_time"] >= '3' THEN 1 ELSE 0 END) AS is_exsit_3s_spm
          ,MAX(CASE WHEN ip_long >= 16777216 AND ip_long <= 2147483647 THEN 'A' WHEN ip_long >= 2147483648 AND ip_long <= 3221225471 THEN 'B' WHEN ip_long >= 3221225472 AND ip_long <= 3758096383 THEN 'C' WHEN ip_long >= 3758096384 AND ip_long <= 4026531839 THEN 'D' WHEN ip_long >= 4026531840 AND ip_long <= 4160749567 THEN 'E' END) AS ip_category
          ,MAX(CAST(SPLIT_PART(ip,'.',1) AS INT)) AS ip_seg1
  FROM    (
                          SELECT  buss_line
                                  ,app_id
                                  ,product_name
                                  ,cookie_id
                                  ,uid
                                  ,channel
                                  ,os
                                  ,device_brand
                                  ,ip
                                  ,spm_time
                                  ,action
                                  ,events_id
                                  ,events_params
                                  ,CAST(GETIPFROMSTRING(SPLIT_PART(ip,',',1,1)) AS INT) AS ip_long
                          FROM    dw_d2v_spm_markactivity_detail_hi
                          WHERE   ds = '${before_hour_date_str}'
                          AND     hs = '${before_hour}'
                      ) 
              GROUP BY app_id
                       ,uid
                       ,cookie_id
          ) s
  LEFT JOIN   (
                  SELECT  *
                  FROM    ip_location_mapping_dispose_mf
                  WHERE   ms = SUBSTR('${before_32day_date_str}',1,6)
              ) s_ip
  ON      s.ip_category = s_ip.ip_category
  AND     s.ip_seg1 = s_ip.ip_seg1
  WHERE   s.ip_long >= s_ip.start_ip_long
  AND     s.ip_long <= s_ip.end_ip_long

优化效果

分别对3万、30万、50万、80万的数据量进行了测试，对比优化前，综合效率提升35 ~ 40倍，优化效果明显