为什么要优化？
营销活动指标体系需要用日志数据进行多维度分析，其中一个维度就是通过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倍，优化效果明显
1123
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
