(大数据开发部)
- 问?
1、数据量 100g
2、预算 50万
3、数据存储多久 1年
4、云主机、物理机 云主机
5、日活100万
6、用户行为数据(文件)、业务数据(mysql)
7、项目周期 1个月
8、团队多少人 0个
9、首批指标 1-10个
10、未来的规划: 离线和实时 是否都要做
- 系统数据流程设计
1.1 集群流程图
1.2 集群框架图
1.3 集群特点
(1)多数据源对接能力
(2)离线批量/在线实时处理
(3)统一的集群管理配置监控平台
(4)实现用户认证和权限管理,满足多租户需求
- 具体版本选型
· Apache框架版本
产品 | 版本 | 特点 |
Hadoop | 2.7.2 | |
Flume | 1.7.0 |
|
Kafka | 0.11.0.2 |
|
Kafka Eagle | 1.3.7 | |
Hive | 1.2.1 | 支持大规模的数据计算 |
Sqoop | 1.4.6 | 采用命令行的方式调用任务 |
MySQl | 5.6.24 | |
Azkaban | 2.5.0 |
|
Java | 1.8 | |
Zookeeper | 3.4.10 | |
Hbase | 1.3.1 | 分布式,可扩展,海量数据存储的NoSQL数据库 |
Phoenix | 4.14.1 |
|
Canal | 1.1.2 | 支持任意格式的数据格式 |
Elasticsearch | 6.3.1 | 对海量数据进行近实时的处理 |
Kibana | 6.3.1 | 为Es设计的开源分析和可视化平台 |
Spark | 2.1.1 |
|
Redis | 3.2.5 | 自带去重功能 |
- 服务器选型
- 机器成本考虑
3.1.1 物理机
内存 | 128G |
CPU | 20核, 40线程 |
硬盘 | 8THDD, 2TSSD |
品牌 | 惠普 |
报价 | 4W |
寿命 | 5年 |
3.1.2 云主机
内存 | 128G |
CPU | 20核, 40线程 |
硬盘 | 8THDD, 2TSSD |
品牌 | 阿里云 |
每年 | 5W |
-
- 运维成本考虑
- 物理机
- 运维成本考虑
需要有专门的运维人员
-
-
- 云主机
-
运维工作由阿里云完成
- 集群规模
- 数据量分析
- 用户行为数据
- 数据量分析
(1)每天日活跃用户50万,每人一天平均100条: 50万*100条 = 5000万条
(2)每条日志约1k左右, 每天1亿条: 50000000/1024/1024 ≈ 50G
(3)数仓ODS层采用LZO存储: 50G压缩为5G左右
(4)数仓DWD层采用LZO+ORC存储: 5G左右
(5)数仓DWS层轻度聚合存储: 25G左右
(6)数仓ADS层数据量: 很小,忽略不计
(7)保存3副本: 35G*3 = 105G
(8)半年内不扩容服务器: 105G*180天 ≈ 19T
(9)预留20%~30%Buf = 19T/0.7 ≈ 28T
4.1.2 Kafka中数据
(1)每天约50G数据*副本(2) = 100G
(2)保存4天*100G = 400G
(3)预留30%BUf = 400G/0.7 ≈ 570G
4.1.3 业务数据
(1)每天业务数据10万条, 每条数据1k左右: 10万*1k ≈ 100M左右
(2)数仓四层存储: 100M*3 = 300M
(3)保存3副本: 300M*3 ≈ 1G
(4)半年内不扩容服务器来算: 1G*180天 ≈ 180G
(5)预留20%~30%Buf = 180G/0.7 ≈ 250G
4.1.4 集群总规模
28T + 570G + 250G ≈ 30T
4.1.5 服务器台数
约8T*5台服务器
-
- 集群规划
1 | 2 | 3 | 4 | 5 |
DataNode | DataNode | DataNode | ||
NodeManager | NodeManager | NodeManager | ||
ResourceManager | ResourceManager | |||
NameNode | NameNode | |||
Zookeeper | Zookeeper | Zookeeper | ||
Kafka | Kafka | Kafka | ||
Flume | Flume | Flume | ||
Hbase | Hbase | Hbase | ||
Hive | Hive | |||
Mysql | ||||
Spark | Spark | |||
ES | ES | |||
Sqoop | ||||
Azkaban | Azkaban | Azkaban |
-
- 离线测试集群服务器规划
服务器名称 | 自服务 | 服务器 Hadoop102 | 服务器 Hadoop03 | 服务器 Hadoop104 |
HDFS | NameNode | √ | ||
DataNode | √ | √ | √ | |
HA | √ | √ | ||
Yarn | NodeManager | √ | √ | √ |
Resourcemanager | √ | |||
Zookeeper | Zookeeper server | √ | √ | √ |
Flume(采集) | Flume | √ | √ | √ |
Kafka | Kafka | √ | √ | √ |
Flume(消费) | Flume | √ | ||
Hive | Hive | √ | ||
MySQL | MySQL | √ | ||
Sqoop | Sqoop | √ | ||
Azkaban | Azkaban WebServer | √ | √ | |
AzkabanExecutorServer | √ | |||
服务器总计 | 11 | 8 | 7 |
- 人员配置
组长 : 1名
组员 : 2名
任务职责 : 无明确分工
熟悉技能 : Hadoop, Hive, Flume, Kafka, Azkaban, Java/Scala, Spark, Sqoop, Zookeeper, Redis, MySQL, Xshell
了解技能 : SparkStreaming, Flink, Hbase, Phoenix, ES
- 集群搭建用时
- 离线测试集群搭建用时
时间 : 不超过3周
-
- 离线正式集群搭建用时
时间 : 不超过4周
-
- 实时测试集群搭建用时
时间 : 不超过2周
-
- 实时正式集群搭建同时
时间 : 不超过3周
- 首批指标查询用时
· 注: 首批指标要优先熟悉业务字段, 导致首批指标查询用时稍长
-
- 常用指标用时
(日活,周活,用户活跃,用户新增,用户留存,漏斗分析,Gmv,流失用户,增长率,成交转化率,询单量,询单转化率,退单率等等)
每个指标时间 : 0.5天 ~ 1.5天
-
- 实时指标用时
(时活,订单量,接货量,交易额,广告点击,区域订单等等)
每个指标时间 : 1天 ~ 2天
-
- 临时指标用时
每个指标时间 : 1.5天 ~ 3天
-
- 活动指标用时(需提前给到)
每个指标时间 : 1天 ~ 1周
-
- 特殊指标用时
(潜在用户,商品季度,复购率等等)
每个指标时间 : 3天 ~ 2周
-
- 风险预警指标用时
(黑名单等等)
每个指标时间 : 3天 ~ 1周
- Bug处理用时
- 框架Bug处理用时
时间 : 1周 ~ 4周
-
- 指标Bug处理用时
时间 : 1天 ~ 3天
项目分点:
- 集群规模:
(12台物理机:128G内存,8T机械硬盘,2T固态硬盘,20核40线程,戴尔4万多一台)
- 框架结构,画出来
(日志部分:日志服务器,落盘日志,flume,kafka,hdfs,hive,mysql
业务数据部分:mysql-sqoop-hdfs-hive)
3.框架:
- Flume(留了问题:flume,take出小文件怎么处理,可以根据时间10min一次,或者128M一次落盘。)
- 我们选了单层1.7版本flume,12台物理机上部署了四个flume节点,
- 对于日志文件,我们保存30天,flume使用的是tailDir Source,这是1.6版本没有的。具有断点续传功能和读取多目录文件的功能。Memory channel 他比较快。Kafkasink 将event采集至kafka。
- 由于flume的put和take事务机制,所以他的数据采集准确性比较好。
- 另外我们还使用了他的拦截器,用来做日志类型区分,和数据轻度过滤,检查时间戳不对的,json数据不完整的,就舍弃。
- 为了sink到kafka的不同主题,使用选择器,将不同类型的event发送至不同主题。
- 我们为了监控flume的性能还是用了监控器,可以查看put和take机制尝试次数和成功次数,
- 如果put的尝试次数低于成功次数,说明flume集群性能有问题,那我们可以考虑优化flume,可以修改配置文件flume-env文件把内存从默认1G调到4G
- Kafka(留问题:kafka挂了怎么办,数据重复的问题)
- 搭建kafka集群,前后以flume分别作为生产者和消费者,我们根据日常的数据量,以及峰值速度,部署了三台kafka,
- Kafka的数据保存七天,副本数是2,大概给kafka集群给了1T的资源。
- 日常每天的数据量大概在60-70G
- 一般设置8-10个分区,不同的主题可能会不一样。
- 我负责8个topic,(有:浏览主题,评论主题,收藏,启动,故障日志,消息通知,广告,内容推送等)
- 针对生产者,我们的ack设置1,leader收到了,就回应生产者offset,还可以设置0,这个很容易丢数据,设置-1的话,也可以,leader和follower都收到数据,才返回消息。
- 针对消费者,我们使用range的分区分配策略,还可以选择roundRobin,只不过roundroubin对消费者要求线程数一样,并且一个消费者组只消费同一个主题。Range没有这些限制,而且分配也均匀。
- Kafka还有个问题就是相关ISR的副本同步队列问题,因为leader负责消费者消费内容。所以leader挂了谁上。就有副本同步队列。会根据leader和follower之间的延迟条数和延迟时间来判断,后面延迟条数被抛弃了。
- Kafka还可以设置多目录,可以优化读写性能。
- 内存我们一般调为4-5G
- HDFS(问题:HDFS读写流程,shuffle机制,Hadoop优化。Yarn调度器,yarn任务提交流程,集群的搭建过程)
- Hadoop作为集群的文件存储系统,在部署的时候要注意配置HA。
- 也要注意namenode和datanode的通信,有两个参数可以提升他们通信的顺畅度。
- 注意把HDFS的namenode 文件edits和fsimage配置在不同目录下。可以提升namenode性能
- Hive(hive的架构,动态分区与静态分区,四个by,窗口函数,时间系统函数,hive的优化)
- Hive作为数据仓库的重要工具,他底层是MR程序,因为MR程序,代码书写很复杂,所以为了方便只熟悉sql语句的程序员利用MR ,就有了Hive。
- Hive的数据存储到HDFS上,元数据信息,存储在mysql上,记得给mysql进行备份,使用主从master和slave结构。以免元数据被破坏了
- Hive的分析框架还是MR,hive的底层将HQL转换成抽象语法树,然后换成查询块,转换成逻辑查询计划,优化或,编程物理MR计划,选择最优的给Mr去执行
- 数仓
- 我们的业务主要分为几个部分,根据物流一部分是相关特快直送的,一部分是在国内保税仓发货的,然后营销层面会有一些限时特卖板块,还有针对vip的专享活动,outlet的特价商品。
- 针对我们的数仓的输入源,我们前面讲过来的日志数据,其实还包括业务数据,业务数据我们从sqoop里面导进来。
- 要注意的是,sqoop是MR任务。耗时较长,有可能失败,所以要做好事务一致性处理,他有两个参数可以利用--staging-table,--clear-staging-table。会讲数据先导入导临时表,如果失败了,就把临时表给删了,重新执行任务。当我们使用sqoop把数据从hive往mysql里面导的时候,hive底层的null是‘\N’,所以sqoop导的时候要额外加参数 --null-string
- kafka主题:
日志: 浏览主题,广告,商品相关的,
业务:购物车, 退货,物流信息(根据产品的来源,有两种,香港特快直送,闪电保税仓。一个从香港发货,一个从内地的保税仓发货),订单,评论主题,评分。 - 我们的数据仓库,搭建了四层,ods,dwd,dws,ads层。
- Ods层基本上就是一些原始数据,dwd层是根据ods层表进行解析,然后清楚脏数据,利用spark做ETL工具进行过滤脏数据,敏感信息处理,比如电话,身份证脱敏,掩码,加密,截取等。Dwd层得到的是比较干净的,能用的数据。Dws层是根据dwd层轻度聚合来的数据,主要是一些宽表,ads就是聚合后的数据,数据量很小,一些重要的指标数据结果,可以导入到mysql。
- 我们数据仓库是基于维度建模,主要使用星型模型。
- 把表分为四类,实体表,主要是一些对象表比如用户,商家,商品等。
- 维度表,是指指一些业务状态,编号的解释,又叫码表,像地区表,订单状态,支付方式,审批状态。状态分类。
- 事实表分为周期型事实表和事务型事实表。如果需要后面状态还会改变的就是周期型事实表,一旦确定了,就是事务性事实表。
- 对于不同的表我们使用不同的同步策略,
同步策略包括全量表,增量表,新增及变化,拉链表
日志表:(商品点击,商品详情,商品详情页表,广告表,错误日志表,消息通知表等)
- 商品点击:用户的基本信息字段,动作,商品id,种类等。
- 商品详情页:入口,上一页面来源,商品id,加载时间,种类。
- 广告表:入口,内容,行为,展示风格等。
- 错误日志:错误详情
- 消息通知表:通知类型,展示时间,通知内容等
这些记录性质的,都使用每日增量
业务表:(购物车,评分,评论,订单表,订单详情表,退货表,用户表,商家表,商品分类表(一级,二级,三级),支付流水,物流信息等)
- 购物车详情:用户id ,商品id,商品价格,商家id ,商品型号,商品分类等
同步策略:这属于周期型事实表因为他可能会随时改变,所以得用每日新增及变化。
- 评分表:评分时间,评分用户,评分商品 ,分数等,
同步策略:这是事务性事实表,一般可以用每日增量就可以了。但是如果电商用户对某件商品用过之后,又想改评论,所以用每日新增及变化
- 评论表:评论时间,评论用户,评论商品,评论内容,
同步策略:这个跟评分差不多,用每日新增及变化
- 订单表:订单状态,订单编号,订单金额,支付方式,支付流水,创建时间等
同步策略:因为订单的状态会随时发生改变,比如下单,支付,商家发货,用户收到货,确认收货,等这一系列的状态会比较长,然后订单也比较多。所以,要做历史快照信息的话,最好使用拉链表。
- 订单详情表:订单编号,订单号,用户id,商品名称,商品价格,商品数量,创建时间等。
同步策略:这属于记录信息的,直接用每日新增。
- 退货流程表:用户id,订单编号,退货商品id,退货状态,时间等
同步策略:这种需要分阶段操作的,需要用户申请退货,平台审核,通过后,用户寄货,上传订单号,跟踪物流信息,商家收货,平台退款。这一系列的流程,可能需要很长时间。为了查看每个历史时间点的切片信息,我们需要做拉链表。
- 用户表:用户id,性别,等级,vip,注册时间等等。
同步策略:因为表不是很大,每次做全量表。
- 商家表:商家id,商家地址,商家规模等级,商家注册时间,商家分类信息。
同步策略:每次做每日全量
- 商品分类表:一级,二级,三级,
同步策略:表很小,或者不怎么改变的直接默认值。使用每日全量
- 商品表:商品id,商品各级分类id,商品的型号,商品的价格等
同步策略:有些可能做每日新增。但是他的数据量基本固定,并且没那么大,但是可能随着商品的上架,下架,更新起来很麻烦,所以我们直接做每日全量,
- 支付流水:支付方式,支付订单,支付用户id,支付商家,支付时间等。
同步策略:每日新增。
- 物流信息表:快递公司名称,快递单号,状态,订单编号,等
同步策略:他是属于事务性事实表,像流水信息,直接用每日增量
总结:
- 实体表,不大,就可以做每日全量
- 对于维度表,比如说商品分类,这种不是很大,也可以做每日全量
有一些不太会发生改变的维度,就可以固定保存一份值,比如说:地区,种族,等
- 像事务型事实表,比如说交易流水,操作日志,出库信息,这种每日比较大,且需要历史数据的,就根据时间做每日新增,可以利用分区表,每日做分区存储。
- 像这种周期型事实表的同步策略,比如订单表,有周期性变化,需要反应不同时间点的状态的,就需要做拉链表。记录每条信息的生命周期,一旦一条记录的生命周期结束,就开始下一条新的记录。并把当前的日期放生效开始日期。
离线指标:
1.日活/周活/月活统计:(每日的根据key聚合,求key的总数)
2.用户新增:每日新增(每日活跃设备left join每日新增表,如果join后,每日新增表的设备id为空,就是新增)
3.用户留存率:(一周留存)10日新增设备明细join 11日活跃设备明细表,就是10日留存的。注意每日留存,一周留存
4.沉默用户占比:只在当天启动过,且启动时间在一周前
5.
6.用户在线时长统计
7.区域用户订单数(根据区域分区,然后求订单数)
8.区域订单总额(根据区域分区,求订单总额。)
9.区域用户订单访问转化率(以区域分组成单数/访问数)
10.区域客单价(订单总额度/下订单总人数)
11.总退货率(退货商品数/购买商品总数)
12.各区域退货率(根据区域分组)
13.GMV(成交总额)
14.物流平均时长(用户收货时间-物流发货时间)求平均
15.每周销量前十品类
16.每周各品类热门商品销量前三
17.各区域热门商品销量前五(有利于后期铺货)
18.各区域漏斗分析
19.商品评价人数占比(该商品的总评价人数/该商品的总购买人数)
20.各品牌商家总销售额。
21.各品类中销量前三的品牌
22.购物车各品类占比(说明大家想买的东西,便于后期铺货。)
23.每周广告点击率。(看到这个广告的人数/点击这个广告商品的人数)
24.vip用户每日,周订单总额
25.每日限时特卖产品占比(限时特卖产品总额/每日交易总额)
26.香港特快直送渠道总交易额占比(香港特快直送渠道总额/每日商品交易总额)
27.香港特快直送渠道总交易单占比
28.国内保税仓渠道总交易额占比(国内保税仓总额/每日商品交易总额)
29.国内保税仓渠道总交易单占比
30.各区域页面平均加载时长(考察各地区网络问题。后台访问是否稳定)
31.页面单跳转化率统计
32.获取点击下单和支付排名前10的品类
33.各类产品季度复购率
使用HIVE的()
- 日活/周活/月活统计:(每日的根据key聚合,求key的总数)
select
mid_id,
concat_ws('|', collect_set(user_id)) user_id,
concat_ws('|', collect_set(version_code)) version_code,
from dwd_start_log
where dt='2019-02-10'
group by mid_id;
date_add(next_day('2019-02-10','MO'),-7),当前这周的周一日期
date_add(next_day('2019-02-10','MO'),-1),当前这周的周日
concat(date_add( next_day('2019-02-10','MO'),-7), '_' , date_add(next_day('2019-02-10','MO'),-1)
)
from dws_uv_detail_day
where dt>=date_add(next_day('2019-02-10','MO'),-7) and dt<=date_add(next_day('2019-02-10','MO'),-1)
group by mid_id;
月活
where date_format(dt,'yyyy-MM') = date_format('2019-02-10','yyyy-MM')要求年月时间符合要求就可以了
- 用户新增:每日新增(每日活跃设备left join每日新增表,如果join后,每日新增表的设备id为空,就是新增)
from dws_uv_detail_day ud left join dws_new_mid_day nm on ud.mid_id=nm.mid_id
where ud.dt='2019-02-10' and nm.mid_id is null;
- 用户留存率:(一周留存)10日新增设备明细join 11日活跃设备明细表,就是10日留存的。注意每日留存,一周留存
`create_date` string comment '设备新增时间',
`retention_day` int comment '截止当前日期留存天数'
nm.create_date,
1 retention_day
from dws_uv_detail_day ud join dws_new_mid_day nm on ud.mid_id =nm.mid_id
where ud.dt='2019-02-11' and nm.create_date=date_add('2019-02-11',-1);
ADS:create external table ads_user_retention_day_count
(
`create_date` string comment '设备新增日期',
`retention_day` int comment '截止当前日期留存天数',
`retention_count` bigint comment '留存数量'
) COMMENT '每日用户留存情况'
select
create_date,
retention_day,
count(*) retention_count
from dws_user_retention_day
留存率:create external table ads_user_retention_day_rate
(
`stat_date` string comment '统计日期',
`create_date` string comment '设备新增日期',
`retention_day` int comment '截止当前日期留存天数',
`retention_count` bigint comment '留存数量',
`new_mid_count` string comment '当日设备新增数量',
`retention_ratio` decimal(10,2) comment '留存率'
- 沉默用户数:不是新用户,只在注册当天启动过,且启动时间在一周前
求沉默用户数(根据dws判断:第一次启动时间min(dt)<一周前,并且根据mid count,结果值只有1,说明,用户的所有记录,只启动过一次)
dws_uv_detail_day
where
dt<='2019-02-03'
group by
mid_id
having
count(*)=1
and
min(dt)<date_add('2019-02-03',-7)t1;
- 流失用户:最近一月未登陆我们称之为流失用户()
根据日活dws表,查看如果最大的启动时间超过14天以前,那就是流失用户
from
dws_uv_detail_day
group by
mid_id
having max(dt)<=date_sub('2019-02-03',7))t1;
- 本周回流用户:本周回流=本周活跃-本周新增-上周活跃
本周活跃mid left join 本周新增, left join 上周活跃 然后join的新字段都是null的
- 最近连续三周活跃用户
最近3周连续活跃的用户,通常是周一对前3周的数据做统计,该数据一周计算一次
dws_uv_detail_wk利用周日活:
from
(select
mid_id
from dws_uv_detail_wk
where wk_dt>=concat(date_sub(next_day('2019-02-03','MO'),7*3),'_',date_sub(next_day('2019-02-03','MO'),7*3-6))
and
wk_dt<=concat(date_sub(next_day('2019-02-03','MO'),7),'_',date_sub(next_day('2019-02-03','MO'),1))
group by mid_id
having count(*)=3)t1;
- 本周内连续登陆三天的用户
1.查询出最近7天的活跃用户,并对用户活跃日期进行排名
select
mid_id,
dt,
rank() over(partition by mid_id order by dt) rank
from dws_uv_detail_day
where dt>=date_sub('2019-02-03',6) and dt<='2019-02-03';t1
2.计算用户活跃日期及排名之间的差值
select
mid_id,
date_sub(dt,rank)
from t1;t2
3.对同一个用户分组,将差值相同个数大于等于3的数据取出,即为连续3天及以上活跃的用户
select
mid_id
from t2
group by mid_id,date_diff
having count(*)>=3;t3
4.统计最近7天连续3天活跃的用户数
select count(*) from t3;
- GMV每日/每周/每月成交总额:根据用户行为宽表,sum 订单总额
- 业务指标:当日新增占日活的比率
select
'2019-02-10',
sum(uc.dc) sum_dc,
sum(uc.nmc) sum_nmc,
cast(sum( uc.nmc)/sum( uc.dc)*100 as decimal(10,2)) new_m_ratio
from
(
select
day_count dc,
0 nmc
from ads_uv_count
where dt='2019-02-10'
union all
select
0 dc,
new_mid_count nmc
from ads_new_mid_count
where create_date='2019-02-10'
)uc;
- 用户行为之漏斗分析:
create external table ads_user_action_convert_day(
`dt` string COMMENT '统计日期',
`total_visitor_m_count` bigint COMMENT '总访问人数',
`order_u_count` bigint COMMENT '下单人数',
`visitor2order_convert_ratio` decimal(10,2) COMMENT '访问到下单转化率',
`payment_u_count` bigint COMMENT '支付人数',
`order2payment_convert_ratio` decimal(10,2) COMMENT '下单到支付的转化率'
) COMMENT '用户行为漏斗分析'
select
'2019-02-10',
uv.day_count,日活
ua.order_count,日订单
cast(ua.order_count/uv.day_count*100 as decimal(10,2)) visitor2order_convert_ratio,
ua.payment_count,
cast(ua.payment_count/ua.order_count*100 as decimal(10,2)) order2payment_convert_ratio
from
(
select
sum(if(order_count>0,1,0)) order_count,下单人数
sum(if(payment_count>0,1,0)) payment_count 支付人数
from dws_user_action
where dt='2019-02-10'
)ua, ads_uv_count uv
where uv.dt='2019-02-10'
- 品牌复购率(一个季度之内,同一个品牌买两次的人数,买三次的人数。/此品牌购买的总人数)
- 用户在线时长统计(进入后台时间戳-启动时间戳,根据用户sum,然后求平均值)
- 一周销量前十品类:用户行为宽表,过滤一周的数据,根据group by 品类 Count数
- 各区域漏斗分析,与漏斗分析类似,根据区域分组
使用Spark的指标:
- 每周各品类热门商品销量前三(取每周各热门品类,然后取用户行为宽表的几个字段,热门品类,用户id,商品id。然后用热门品类过滤。得到属于热门品类的数据,再根据热门品类,商品id,去聚合。去前三。)
- 各区域热门商品销量前五:取用户行为宽表,然后得到里面的数据,可以转化成样例类的rdd。然后根据区域分组,然后求商品销量,前五的。
- 商品评价人数占比(该商品的总评价人数/该商品的总购买人数):根据商品key分类,拿到评价人数,再拿到总购买人数
- 各品牌商家总销售额。 根据商家为key,拿去用户行为宽表的数据,求对应的销售额
- 各品类中销量前三的品牌
- 购物车各品类占比:以品牌为key,数量为value。从购物车宽表中获取数据。然后根据品牌分类,求总数。(说明大家想买的东西,便于后期铺货。)
- 每周广告点击率。(看到这个广告的人数/点击这个广告商品的人数)
- vip用户每日,周订单总额
- 每日限时特卖产品占比(限时特卖产品渠道总额/每日交易总额)
- 香港特快直送渠道总交易额占比(香港特快直送渠道总额/每日商品交易总额)
- 香港特快直送渠道总交易单占比
- 国内保税仓渠道总交易额占比(国内保税仓总额/每日商品交易总额)
- 国内保税仓渠道总交易单占比
- 各区域页面平均加载时长(页面加载成功时间戳-请求时间戳)(考察各地区网络问题。后台访问是否稳定)
- 获取点击下单和支付排名前10的品类
实时指标(spark streaming做):
- 每日日活实时统计
- 每日订单量实时统计
- 一小时内日活实时统计
- 一小时内订单数实时统计
- 一小时内交易额实时统计
- 一小时内广告点击实时统计
- 一小时内区域订单数统计
- 一小时内区域订单额统计
- 一小时内各品类销售top3商品统计
- 用户购买明细灵活分析(根据区域,性别,品类等)
- 准备三个具体的指标。比较难,又有对运营,营销又非常有价值的。帮助他们做了什么事,讲讲怎么做的
寻找潜在VIP:
- 上一周连续3天登录,且上周内下过一单的
先过滤取出上周内下过一单,又是非vip的人。(从订单明细表)
再根据他们每日的最早启动时间,用rank窗口函数进行排序。那么排序的这个字段就应该是以1为公差的等差数列(left join 用户活跃表日)
然后再用date-sub去将启动日期与rank计算,得到了日期差值,根据这个日期差值进行分组,计算这个差有几个。
>3就是我们所需要的用户。
找出来之后,给她短信,后台消息推送vip活动免费一个月体验。减税,免邮,享受会员价等活动。
- 过去一个月内下单商品大于8件,且下单次数大于2
使用用户订单详情表:取出过去一个月的非vip用户购买详情。
计算每个用户的下单商品数,下单次数>2 (group by userID,sum(购买件数),count(distinct 订单号)》2)
推送消息,给免费vip活动体验
这部分的用户在接下来的三个月时间里,真正转换成vip的有35%的人,所以这个指标还挺有意义的
商品季度/半年复购率(购买过这个商品两次以上的用户数/这个季度购买这种商品的总人数):
- 用户购买明细表。
把上个季度的用户购买详情表过滤出来。group by 用户id 商品id分组,求出用户对于某个商品下单的总次数。
然后用sum if(判断订单单数>2),订单单数>1的人数,求比率,
然后对比率根据品类排名,求每个品类中 比率排名前十的。用row_number<11.分区取品类,排序取复购率。
这些商品,是我们的重要维系的商品,要及时补货。然后复购率高说明,受用户喜欢,可以推荐,给用户发送小样,尝试,增大转化率。
4.品牌复购率:
差不多,把具体商品,改成品牌id。各类商品下的品牌复购率(每月来算)
5.每周各品类热门商品销量前三(取每周各热门品类,然后取用户行为宽表的几个字段,热门品类,用户id,商品id。然后用热门品类过滤。得到属于热门品类的数据,再根据热门品类,商品id,去聚合。去前三。)
6.各区域热门商品销量前五:取用户行为宽表,然后得到里面的数据,可以转化成样例类的rdd。然后根据区域分组,然后求商品销量,前五的。
7.各品类中销量前三的品牌
8.购物车各品类占比:以品牌为key,数量为value。从购物车宽表中获取数据。然后根据品牌分类,求总数。(说明大家想买的东西,便于后期铺货。
数据健康问题:
物流信息:有的客户物流信息上显示收到货了,但是快递可能没有送到他手里,然后过程中有丢失的情况。那么我们的物流计算时长,如果单纯按照物流信息来就会出现偏差,所以我们物流到货时间都是以用户,确认收货为准。也不会差很大。
用户的隐私信息,电话号码:我们使用自己的一套脱敏技术,将每个电话号码的4-11位,加1,然后4-7位与8-11位顺序调换。后期我们需要用到他们的隐私信息,电话进行,营销,发送消息是,就把他转换过来。
数据倾斜问题:
- 用时间维度表去join过去一整年的用户购买明细表,查看,用户集中购买的月份和季节。分析用户的行为。之前不是默认的。(默认开启mapJoin嘛)
- 小表join大表的问题。后面这个优化了,但是小表不能超过512M.我们数据量没那么大,应该是可以的。
比如说算品类销售排名的时候,group by 品类,求销售总量是,某一品类像面膜,可能销售量特别大,占60%多,那么有一个任务就会执行特别久。半天出不来。设置推测执行也差不多,就应该是数据倾斜导致的问题
Map端部分聚合
这里需要修改的参数为:
hive.map.aggr=true(用于设定是否在 map 端进行聚合,默认值为真) hive.groupby.mapaggr.checkinterval=100000(用于设定 map 端进行聚合操作的条目数)
- 有数据倾斜时进行负载均衡
此处需要设定 hive.groupby.skewindata,当选项设定为 true 是,生成的查询计划有两 个 MapReduce 任务。在第一个 MapReduce 中,map 的输出结果集合会随机分布到 reduce 中, 每个 reduce 做部分聚合操作,并输出结果。这样处理的结果是,相同的 Group By Key 有可 能分发到不同的 reduce 中,从而达到负载均衡的目的;第二个 MapReduce 任务再根据预处 理的数据结果按照 Group By Key 分布到 reduce 中(这个过程可以保证相同的 Group By Key 分布到同一个 reduce 中),最后完成最终的聚合操作。
大数据面试
个人介绍
项目分点:
- 集群规模:
(12台物理机:128G内存,8T机械硬盘,2T固态硬盘,20核40线程,戴尔4万多一台)
- 框架结构,画出来
日志部分:
app前端埋点 -> 日志服务器 -> 落盘日志 -> flume -> kafka -> flume -> hdfs -> hive -> mysql
业务数据部分
Java后台采集数据 -> 日志服务器 -> mysql -> hdfs -> hive -> mysql
- 框架:
3.1 第一个Flume
组件:source 、 channel 、 sink 、三个器 、碰到的问题
①source
我们使用的是taildirsource,这个是apache 1.7版本才有,选择这个source的原因是taildirsource可以实时监控多个文件且有断点续传功能
②channel
Channel一共有三种:filechannel、memorychannel和kafkachannel
fileChannel是基于磁盘,io多,性能差,但是可靠性高
Memorychannel基于内存,性能高,但是可靠性相对低,存在丢失数据的风险
Kafkachannel是基于磁盘,可靠性高,性能还优于memorychannel + kafkasink
我们是将数据采集到kafka,所以我们使用了kafkachannel
③sink
kafkachannel可以直接将数据发送到kafka,所以我们没有使用sink。
④拦截器
我们使用了etl拦截器,过滤掉不完整的josn数据
同时还使用了分类拦截器,我们的日志分为了5类数据,启动、页面、动作、曝光和错误数据,我通过给event的header加上对应的标签,后面配合多路复用的选择器,指定不同类型的数据去到不同的topic中。
我们定义拦截器的步骤:
①自定义一个类,实现interceptor,实现4个抽象方法:分别是:初始化、关闭资源、单个event和多个event方法,
②创建一个内部类实现builder类,实现两个抽象方法。
③最后打包 -> 上传到flume 的lib包下 -> 在配置文件中添加拦截器,写上全类名$bulid类
⑤选择器:
一共有两种选择器,一种是replicating,默认的选择器,每一个通道发送一份数据,另外一种是multiplexing,多路复用,根据event的header指定数据去到哪个通道,我们选择的多路复用选择器,将数据发送到kafka不同topic中。
⑥监控器
我们还使用到ganglia监控器,对flume的运行状况进行监控,主要是监控flume的put和take事务,当尝试提交的次数远远大于成功提交的次数以后,我们对flume进行优化,通过配置flume-env文件,增大flume的内存,默认是2G,我们调整到4G
同时在双十一、618等大型活动,flume采集通道也是抗不住这么大的数据量,我们通过临时购买阿里云的日志服务器,然后将flume部署到日志服务器上。
碰到的问题
我们遇到过flume挂掉的情况,我们当时的分析是:
Source -> channel有put事务,channel 到sink有take事务,所以这个两个环节是没有问题的,后端是kafka,那么是暂时无法获取数据,也没有关系。
采集数据的前端,使用的是taildirsource,它的特性是先拉取数据,再维护offset,所以不会丢失数据,可能存在重复数据,针对重复数据,我们开会分讨论是:
可以通过增加事务的方式实现不重复数据,但我们评估这样做性能非常低,我们在hive的dwd层,可以通过groupby进行去重,也可以使用sparkSQL或者redis进行去重,所以在这里我们就没有进行处理。
3.2 kafka
Kafka我们从三个方面向您介绍,框架、碰到的问题和优化
框架介绍
①kafka有4个组件:生产者 、消费者 、 brokers 和 zk
Zk保存着消费者的offset和brokers的ids等信息
②数据量:日常每天80-100g,大概平均的数据生产速度1M/s,不过到了峰值时间,晚上7-9点时间段,最高达到了20MB/S,大概有2W人同时在线
③kafka的台数,通过kafak自带的压测工具,测试生产峰值速度,我们当时选择了3台kafka。
④分区的数量
分区数量 = 期望的吞吐量 / min(生产峰值速度,消费最大的速度)
我们当时设置的是5个分区
⑤存储大小
Kafka数据默认保存7天,我们调整到3天,因为正常情况下,我们是可以跑完当天的数据,当出现异常时,第二天可以重跑一次,所以我们保留3天是足够的。我们给kakfa硬盘的大小为:每天的数据量 *副本数 * 保留的天数 / buffer(0.7) ,大概是0.8T,我们预留了1T
碰到的问题:
我们还遇到了kafka挂了,数据重复、数据丢失、数据挤压问题
①对于kafka挂了,kafka收不到消息,后端flume无法获取数据,没有什么问题,前面的flume,数据会快速挤压在channel中,最多就是后面挤压满了,但是往前是日志服务器,保留了30天的数据,所以就没有什么关系,直接重启就可以了
②对于数据丢失,这个重要看ack的配置,ack有0,1,-1三种,0表示leader一收到数据就回复ack,可能丢失数据,企业中已经不使用,1表示leader落盘以后回复ack,可靠性相对可靠,-1表示所有的副本都落盘以后再回复ack,可靠性高,性能相对较慢,我们传输的是日志数据,所以采用了ack=1的方式,
③对于数据重复,可以通过事务 + ack =-1 + 幂等性来解决这个问题,幂等性通过(生产者id,分区号,序列号)对一个分区内的数据进行标识,可以保证分区内的数据不会重复,当生产者重启时,生产者的id号会发生改变,这样同一条数据就可能会被重复发送到kafka,通过事务将pid和事务id进行绑定,解决了这个问题,不过我们通过会议讨论,这样会严重影响性能,所以这里我们就不做处理,等hive的dwd层进行去重。
④同时我们还遇到了数据挤压的问题,我们做了两个优化:
一是:增加分区,同时增加下一级消费者的cpu核数
二是:通过修改batchsize参数,提高消费者每次拉取的数据量,默认是1000条,我们把它调整到2000,极端情况下我们也调整过到3000条/s
优化
我们通过修改参数对kafka进行优化:
①将数据保存时间由默认的7天修改为3天
②副本数由默认1个修改为2个,增加可靠性
③增加副本拷贝过程中leader和follower之间的通信时间,避免因为网络问题,不断的拷贝同一副本
④对producer发送的数据采用是压缩
⑤我们还调整了kafka的内存大小,由1g调整到6g,提高性能
3.3 第二个flume
我从三个方面介绍:
框架 、 遇到的问题、优化
框架
①source:我们是采集kafka的数据,所以使用kafka source
②channel:我们选择了memory channel,传输普通的日志,如果出现宕机,最多是100evnet个数据发生丢失,我们评估能接受
③sink:数据写到hdfs上,使用了hdfs sink
遇到的问题:
①刚开始我们把数据写到hdfs时,遇到了有很多小文件问题,通过flume的官网,发现有三个参数可以减少小文件的情况,分别是文件滚动大小、时间和event的个数,我将文件滚动的大小设置为128M,时间设置为2H,就是每128m生成一个新文件,如果超过2h没有达到128M,也是生成一个文件,event设置为0,就是不启用,因为我们不知道每一个event的数据大小,如果使用的话,会造成hdfs上的数据量大小不一。
②还遇到了头一天的数据发送到了第二天的文件夹中。通过查询GitHub的资料,发现是kafkasource会获取系统时间并添加到event的header中,而我们是依据这个header中时间戳,将数据发送到指定的文件夹中。
我们解决方式是:通过自定义拦截器,获取event的body中时间戳,并把时间戳添加到timestamp属性中,这样就实现了当天的数据能进入当天的文件中。
③同时我们还遇到了hadoop宕机了,通过调小从kafka中拉取数据的batchsize大小来调整往hdfs写数据的速度,解决了这个问题。
3.4 hdfs
遇到的问题
Log数据写到hdfs的时候,发现还有一些小文件,由于在namenode中,一个文件占用的内存大小是固定150字节,那namenode的资源利用率就低了,并且在后面计算任务的时候,每一个小文件会创建一个maptask,对集群资源也是浪费。
对于小文件,我们采用了三种方式
①har归档,将多个小文件压缩在一起,类似Windows下的zip压缩,对于namenode来说就是一个整体,同时采用har协议,可以将里面的文件一个一个取出来。
②后面我们采用combinerInputFormat,将多个小文件捏合在一起进行统一切片,减少maptask的数量
③同时开启了jvm重用,提高处理的效率,因为对于小文件来说,执行的时间比开关jvm的时间还短,这个时候我们就多个任务开关一次,不过开启jvm重启是一个双刃剑,在没有小文件的场景下,不要开启这个功能。
这样我们的log数据就去到了hdfs中。每天一个文件夹,每个文件中存储了当天日志数据。
3.5 业务数据
组成
①配置
同时我们还有业务数据,通过sqoop将业务数据导入到hdfs层,sqoop的配置比较简单,主要配置的参数有mysql连接的url、用户、密码以及数据写到hdfs的路径、如果文件存在则删除。
②同步策略
我们将数据导入到hdfs时,根据数据量和数据的变化情况,使用不同的同步策略,主要是考虑在满足业务需求的情况下减少数据重复。
同步规则是(画图说明)
- 如果表数据比较少,采用全量同步策略,导入时过滤条件是1=1
b、如果表数据量大,但是数据只有新增数没有变化,采用新增同步策略,导入时过滤条件是创建时间等于今天
c、如果表数据量大,数据有新增也有变化,就采用新增及变化同步策略,导入时过滤条件是创建时间或操作时间等于今天
d、还有一种是特殊表,默认数据不会变化,如地区和省份表,只导入一次。
我们是每天的凌晨12点半左右开始导表,大概需要40分钟,遇到数据量比较大的时候,如双十一、618等大型活动,大概需要1个小时左右。
碰到的问题
在导入的时候也碰到了一些问题
①hive中null值存储是\N,但是mysql中是null,数据不一样,我们通过配置--null-string,--null-no-string参数进行数据转换
3.6 hive
最终log数据和业务数据都存储到了hdfs中,之后通过load的方式,将数据加载到hive里面,像hive这个框架也有很多技术点,从三个方面介绍这个框架
①组成
Hive有元数据、4个器、计算引擎和存储
元素据
元数据默认是存储在derby数据库,但由于这个数据库仅支持单客户端访问,所以后面我们就把元数据存储在mysql中。
4个器
这边4个器是解析器、编译器、优化器和执行器
计算引擎
计算引擎有mr、tez和spark。
Mr引擎是基于磁盘,任务内部要落盘,io大,性能差,我们一般用来执行周、月、季度等长周期任务。
Tez引擎,是基于内存,对内存的要求高,计算的数据量如果很大,会出现oom的情况,所以我们一般用来执行一些临时任务
Spark引擎,是基于内存,中间也会有落盘,我们一般用来执行当天的任务。
存储
数据最终是存储在hdfs中。
②使用
内部表和外部表
hive数仓中,我们用到了内部表和外部表,两者的最大区别是:删除内部表元数据和原始数据都会被删除,而删除外部表,只会删除元数据不会删除原始数据,我自己使用的一些临时表采用内部表,其他的表基本是外部表,用来防止因误操作将原始数据删除了。
4个by
当然,还使用了4个by,分别是order by 、 sort by 、distribute by和cluster by。Order by 很少使用,因为是全局排序,很容易出现oom,sort by 和distribute by 一般是配合使用,分区内排序,当分区字段和排序字段相同时,可以使用cluster by 代替,不过用的比较少。
系统函数
在计算指标时,我们使用了各种函数,如系统函数,用next_day处理周指标,用get_json_object对log数据进行解析,还使用了开窗函数,rank 和over函数,计算topN指标
②碰到的问题
在数仓的过程中,也遇到了很多问题。
问题1:大表 和 大表
问题2:小表和大表
问题3:单个key数据倾斜
问题4:多个key数据倾斜
在数仓计算的过程中,遇到了数据倾斜的问题,当时我们发现有一个reducetask卡在99%,而其他的任务都执行完成了,第一反应可能是数据倾斜了,然后对数据进行group by 求count,发现null的数据很多,然后我们采取使用随机数将null值打散,然后对计算结果数据进行转换,去掉随机数,再进行一次聚合。这个问题解决了,
后来我们还开启了负载均衡的功能。
③ 常规操作
在hive使用的过程中,做了一些常规优化
一是参数优化:
①开启mapjoin、开启map端的combiner和使用压缩
遇到小文件时
①开启了merge功能:就是将任务结束时产生的多个小于16m的小文件捏合成一个256M的大文件
②使用combinerhiveinputformat;将多个文件捏合在一起,减少maptask的数量
③开启jvm重用
二是业务优化:
①创建分区表:避免全局扫描
②先过滤再计算
③列式存储:提高数据查询的速度
④合理设置reduce的个数:避免资源的浪费
⑤根据需求更换对应的计算引擎
这就是hive当中的一些事情。
- 数仓
之后我们基于hive,搭建了一个离线数仓,我们的数仓分为5层,ods、dwd、dws、dwt和ads层。
首先,我们通过ezdml工具分析mysql表之间的关系。
ods层
从三个方面聊一聊ods层
①表的字段:
Ods层表的字段:
Log数据,创建一个新表,表只有一个字段,line,log一条日志就是一条数据
业务数据,字段和mysql的字段保持一致。
②表的维护
获取hdfs中当天的数据,直接load进去
③在ods层做的3件事情
- 创建分区表,每天一个分区,每个分区的数据和数据导入hdfs的策略保持一致
- 数据从hdfs原封不动到ods层,对数据进行备份
- 采用lzo压缩,减少磁盘空间,压缩后数据从100g大概压缩到10g左右
Dwd层:
对于日志数据,我是使用get_json_object对日志数据进行解析,将数据解析为:启动、页面、动作、曝光、错误数据
对于业务数据,从4个方面阐述
1.建模理论
Dwd层采用了维度建模。标准的4步:
①选择业务过程
②声明粒度
③确定维度
④确定事实
因为我们是中小型公司,所以我们把后台的50多张表全部拿过来,所有的业务我们都要了,声明粒度,我们是声明最小的粒度,一行信息代表一次下单,一次支付、一次收藏,并没有对它做任何的聚合操作,确定维度,后面通过画矩形图方式确定和事实相关的维度,最后是确定事实,就是确定事实表的度量值,有次数、件数、金额、个数。以订单为例,最关心的就是下单数量和下单金额。
业务数据我们分为维度表和事实表。
- 维度表:
对于维度表来说,根据维度退化理论创建,我们当时定了6个维度,用户、地区、时间、商品、活动和优惠券维度,从这6个维度进行指标统计,我从三个方面介绍维度表:
1、维度表分类
根据数据的特点,将维度表分为全量表、特殊表和拉链表三种。
全量表:数据量不大的维度表,采用全量表,如商品维度,活动维度等
特殊表:数据默认不会变化,如地区和时间维度表
拉链表:数据会发生变化,但是大部分是不变的表,采用拉链表。如用户表维度表。
2、表的字段
维度表的字段:从ods层找到这个维度相关的表,字段全部取过来,如和商品维度相关的有6张表:sku、spu、一、二、三级表、品类表。
3、表的维护
讲一下拉链表构建过程,我们是将用户表做成了拉链表
创建拉链表步骤是【画图说明】:
第一步:初始化用户表:取ods层用户表中数据,增加起始时间和结束时间字段,将起始时间设置为当天导表的时间,结束时间设置为很大的值,我们设置为9999-01-01,我们暂时称为旧表
第二步:处理新数据:后续导表时,取出ods层当天分区的数据,增加起始时间和结束时间,起始时间修改为今天,结束时间修改为9999-01-01,形成一张新表
第三步:修改旧表数据:初始表 left join ods_user_info表第二天的数据,当ods_user_info的id不为null且初始表的end_date为9999-01-01的数据,将end_date改为昨天,其余字段全部使用旧表的数据
第四步:合并数据,旧表和新表进行unoin all
- 事实表
也从3个方面阐述
1、表的分类
事实表都是分区表,根据数据特点,我们将事实表分为三种:
- 事务型事实表:只有新增数据没有变化数据建事务型事实表,每个分区保存当天新增的数据,如支付事实表、退款事实表
- 周期性快照事实表:对于我们只关心当天最后的数据,不关心中间变化过程的表,每个分区保存当天所有的数据,如收藏表、加购物车表
- 累计型快照事实表:对于表中的一行数据一次性写不完的表,每个分区保存当天新增的数据,当天变化的数据,去到原来分区进行数据修改,如优惠券领用表
2、表的字段
表的字段包含三个部分:维度外键、度量值和一些冗余字段,获取字段的步骤:
第一步:画矩阵图,找到和事实表相关的维度,如和订单事实表相关的维度有:时间、地区、用户、活动
第二步:从ods层找到和这个事实相关的表
第三步:取步骤2中表所有的度量值,以及冗余字段和维度外键作为事实表的字段。
- 表的维护
维护表数据的方式也比较简单,简单说一下稍微复杂一点的累积型事实表中订单表的维护思路:
画图说明
相关表:订单状态表、活动订单表、订单表。
第一步:订单状态表:按订单进行分组,然后使用str_map + concat_ws + collect_set,将多行数据转换为一行map数据,然后和订单表join,再和订单活动进行left join。--新表
第二步:从dwd层取出旧表中分区等于变化数据的创建时间的数据--旧表
第三步:旧表与新表进行full join,新表有数据,就使用新表数据,否则使用旧表数据
第四步:最后采用动态分区的方式对原有的分区数据进行覆盖写。
- 还做了其他3件事情
①数据清洗:过滤重复数据和关键字段空值数据,脏数据一般控制在万分之一,如果遇到了脏数据过多,会和前端沟通。
②采用lzo压缩,减少磁盘空间
③采用了列式存储:提高查询的速度
Dws层
之后来到dws层,这里是宽表,是站在维度的角度看事实的度量值,统计当天的一些指标,如会员主题,统计用户当天下单次数、下单金额、支付次数、支付金额等
我们分为5个主题,设备主题、会员主题、商品主题、活动主题和地区主题。
①设备主题:统计设备id活跃次数
②会员主题:
- user_id
- 登入次数
- 加入购物车次数
- 下单次数
- 下单金额
- 支付金额
- 支付次数
③商品主题表
- 商品id
- 被下单次数
- 被下单件数
- 被下单金额
- 被支付次数
- 被支付件数
- 被支付金额
- 被退款次数
- 被退款件数
- 被退款金额
- 被加购物车次数
- 被收藏次数
- 好评数
- 默认评价数
- 差评数
④活动主题表
- 活动id
- 开始时间
- 结束时间
- 创建时间
- 曝光次数
- 下单次数
- 下单金额
- 支付次数
- 支付金额
⑤地区主题表
- 地区id
- 地区名称
- 省份名称
- 省份id
- 活跃设备数
- 下单次数
- 下单金额
- 支付次数
- 支付金额
Dwt层
之后来到dwt层,也是站在维度的角度看事实,这次是看事实的开始时间、结束时间、度量值的累积值和一段时间内的累积值。也是5个主题
①设备主题
- 设备id
- 首次活跃时间
- 末次活跃时间
- 当天活跃次数
- 累积活跃次数
②会员主题
- user_id
- 首次登入时间
- 末次登入时间
- 累积登入天数
- 最近30天登入天数
- 首次下单时间
- 末次下单时间
- 累积下单次数
- 累积下单金额
- 最近30天下单次数
- 最近30天下单金额
- 首次支付时间
- 末次支付时间
- 累积支付金额
- 累积支付次数
- 最近30天支付次数
- 最近30天的支付金额
③ 商品主题
- sku_id
- Spu_id
- 最近30天被下单次数
- 最近30天被下单件数
- 最近30天被下单金额
- 累积下单次数
- 累积下单件数
- 累积下单金额
- 最近30天被支付次数
- 最近30天被支付件数
- 最近30天被支付金额
- 累积支付次数
- 累积支付件数
- 累积支付金额
- 最近30天被退款次数
- 最近30天被退款件数
- 最近30天被退款金额
- 累积退款次数
- 累积退款件数
- 累积退款金额
- 最近30天被加购物车次数
- 累积被加购物的次数
- 最近30天被加收藏的次数
- 累积被加收藏的次数
- 最近30天好评数
- 最近30天中评数
- 最近30天差评数
- 最近30天默认的评价数
- 累积好评数
- 累积中评数
- 累积差评数
- 累积默认评价数
④活动主题
- 活动编号
- 活动开始时间
- 活动结束时间
- 活动创建时间
- 当日被曝光次数
- 累积曝光次数
- 当日下单金额
- 当日下单次数
- 累积下单金额
- 累积下单次数
- 当日支付次数
- 当日支付金额
- 累积支付次数
- 累积支付金额
⑤地区主题
- 省份id
- 省份名称
- 地区id
- 地区名称
- 当天活跃设备
- 最近30天活跃设备
- 当天下单次数
- 当天下单金额
- 最近30天下单次数
- 最近30天下单金额
- 当天支付次数
- 当天支付金额
- 最近30天支付次数
- 最近30天支付金额
Ads层
指标分析,大概100多个指标。
①设备相关指标
- 日活、周活和月活
(从dwt层获取最后的登入时间在今天、这周、这个月)
- 每日新增设备(首次登入时间是今天)
- 统计1日、2日和3日留存率
从dwt层获取数据。
第一步:统计当天所有的活跃用户
第二步:统计昨天的1日留存率,求出昨天的新用户但是今天上线的用户/昨天的新用户
第三步:统计前天的2日留存率,求出前天的新用户但是今天上线的用户/前天的新用户
- 沉默用户:只在安装当天启动过,且启动时间是在7天前
(统计首次活跃时间 = 最后末次活跃时间,且最后活跃时间在7天前的用户)
- 本周回流用户数:上周未活跃,本周活跃的设备,且不是本周新增设备
第一步:获取本周活跃的用户且不是本周新增的用户
第二步:获取上周的活跃的用户
第三步:第一步获取的用户减少第二步获取的用户就是本周回流的用户
- 流失用户:最近7天未活跃的设备(获取最近活跃时间小于7天的用户)
- 最近连续三周活跃的用户数
第一步: 从dws层获取前一周、前两周以及当前周的所有活跃的用户
第二步: 然后进行内连接,能连接上的,则说明这连续的3周都活跃了,最后按照用户进行分组去重后求count。
- 最近7天连续3天活跃
第一步:从dws层获取最近7天的数据,对数据按照用户分组进行开窗,按照活跃时间进行降序排序
第二步:使用活跃时间减去排名,获取一列
第三步:按照用户和第三步的列进行分组,求count(*) >= 3的用户
第四步:分组求和
第五步:求7天内连续3天的活跃用户
②会员主题
- 会员主题分析:
1. 总付费会员数:指付费的会员数量
2. 会员活跃率 = 今天会员活跃数量 / 总的会员数量
3. 会员付费率 = 今天会员付费人数 / 总的会员数量
4. 会员新鲜度 = 今天新增会员数量 / 今天活跃的会员数量
2. 漏斗分析;浏览页面 -> 进入详情的页面 -> 加入购物车 -> 下单 -> 支付
③商品主题
- 商品个数排名
- 商品销量排名
- 商品收藏排名
- 商品加入购物车排名
- 商品近30天退款率排名
- 商品差评率
④营销主题
- 下单数目统计:单日下单笔数、金额和用户数
- 支付信息统计:单日支付笔数、金额、人数、商品数和下单到支付的平均时长
- 品牌复购率
⑤地区主题
- 地区主题信息:当天活跃设备数,下单次数、金额,支付次数和金额。
留转G复活
留存率计算:会算
转换率的计算:
GMV计算:会计算
复购率:会计算
日活:会计算
高消费的用户->统计高消费用户的前10个商品品类,然后推送对应的优惠力度
生日标签->提前一周触发优惠券的发放,进行引流
优惠券偏好->统计优惠券出现的次数的排名,确定哪一类优惠券用户比较喜欢,然后这个类活动可以经常做。
导出
后面我们通过sqoop将计算的结果导入到mysql中,在导出的过程也遇到了数据不准确的问题,因为sqoop底层是4个map,有可能出现一半成功,一般失败,这样在查询结果的时候,和实际有偏差,我们通过增加--stage-table参数,先将数据导入到一张临时表,之后通过事务的方式导入到mysql中,这样就要么成功要么失败。
可视化展示
之后我们的数据直接对接superset,使用superset做可视化,免费开源的,用起来效果非常棒。
即席查询:
同时数仓当中还采用了各种即席查询,像presto,还装了kylin,kylin主要用于多维分析,主要用于dwd层进行分析,presto主要是针对ads层快速出指标,产品经理让我统计ads层的某一个指标,一般我用presto可以直接得出指标,因为它是完全基于内存的,速度比较快。
调度
最后我们使用azkaban作为全流程调度,每天晚上凌晨30分开始统一调度,业务数据使用sqoop将mysql数据导入hdfs,日志数据通过flume-kafka-flume,导入到hdfs,然后将hdfs的数据load到hive中。我们指标有100多个,搞活动的时候能达到200多个,数据量还是比较大的,凌晨开始跑,如果跑挂了,我们还配置了邮件报警,电话报警,我们继承了第三方工具,onealert来打电话。
以上是我做的离线数仓项目。
下面介绍一下我的实时项目。
5.实时项目:
分4个部分讲述我的实时项目:
- 实时项目的框架
- 具体的指标及实现的方式
- 遇到的问题
- 优化
框架部分:
log数据:flume -> kafka -> sparkStreaming,日志数据通过flume采集到kafka两个topic中,start_topic和event_topic,然后SparkStreaming根据需求来读取数据。
业务数据:MySQL -> Kafka -> SparkStreaming。使用cannl实时监控mysql的变化的数据,并对数据进行解析,根据解析结果数据,发送到kafka不同的topic中,然后SparkStreaming来读取数据。
Canal:
简单介绍一下cannl
我使用cannl实时监控mysql中表变化的数据,并发送到kafka中。
1.实现原理的原理是:
①canal模拟mysql slave的交互协议,伪装自己为mysql slave,向mysql master发送dump协议
② mysql master收到dump请求,开始推送 binary log 给 slave(也就是 canal)
③ canal 解析 binary log 对象
2. Mysql Binarylog一共有三级别:
①statement:binlog 会记录每次执行的写操作的语句,数据量少,但是对于一些随机数函数和自定义函数,slave在重新执行语句时,有可能会出现执行返回结果和主不同
②row:binlog 会记录每次操作后每行记录的变化,保证数据的绝对安全,但是数据量大,因为一条sql语句可能改变多条数据。
③mixed:statement的升级版,对于可能导致数据不同的语句,采用row方式,其他的语句采用statement方式。
由于我是使用cannl监控数据的变化,采用的是row级别。
- 实时指标
- 每日日活实时统计
- 每日订单量实时统计
- 一小时内日活实时统计
- 一小时内订单数实时统计
- 一小时内交易额实时统计
- 一小时内广告点击实时统计
- 一小时内区域订单数统计
- 一小时内区域订单额统计
- 一小时内各品类销售top3商品统计
- 用户购买明细灵活分析(根据区域,性别,品类等)
- 实现方式
前提:
- 使用cannel采集mysql中指定数库下所有的表,然后对数据进行解析,不同表的数据写到不同topic中。
- 手动维护offset,创建ssc时,从mysql读取offset,如果offset有数据,则从获取的offset位置开始读取数据,否则从头earlist开始读数据,数据操作完成以后,并通过ssc获取offset,并维护到mysql中。
每日订单量和每小时实时统计
定义:采集周期为3s,使用sparkStreaming采集数据以后发送到hbase中
实现:
第一步:采集数据
第二步:将数据封装成一个样例类,在样例类内部添加日期和小时两个字段,并根据数据创建时间进行转换
第三步:将数据写入hbase
购物券风险预警分析:同一个设备id中,更换3个以上的用户领取优惠券
定义:每间隔6秒计算5分钟内领取优惠券的数量>3,且没有做其他任何操作的用户,同一设备id一分钟内预警一次。处理逻辑:在spark中只是获取数据,并进行初步的处理,然后将数据写入hbase中。
实现逻辑:窗口:5min,步长为6s
第一步,从kafka中获取event_topic数据
第二步:格式转换成:mid,数据,并按照mid进行分组
第三步:转换处理,对value进行判断
- EventList集合,用来同一设备5分钟内所有事件id
- ItemSet集合,存放优惠券与商品对应的数据
- UidSet集合,存放领取过优惠券的用户信息
设置一个flag,一旦用户有点击商品的行为,该条数据就结束
①将数据写到EventList集合中
对事件id进行匹配
如果等于coupon,将领取优惠券的useid加到UidSet,同时将event的item加入到itemSet中
如果等于click,flag=false则break
等于其他,不做任何处理。
对同一个用户来说,如果所有的value所有的数据遍历完成后,
将数据写出 /** 预警机制的条件:
* !isClickItem,没有点击过商品为true
* uidSet.size() >=3,领取优惠券的用户数量 > 3
* 如果两个都满足,返回true
*/
(!isClickItem && uidSet.size() >= 3, AlertInfo(mid, uidSet, itemSet, eventList, System.currentTimeMillis()))
}
②过滤数据:保留没有点击过商品的用户
③转换,只要value
④将数据写入到中,在es中进行查询
用户购买明细分析:
定义:将用户和订单表和订单详情表三个表的数据进行关联起来,一个订单对应多条订单详情表
实现逻辑:订单表和订单详情表使用双流join,借用redis进行缓存,用户表的数据去mysql中查询以后添加进来
具体逻辑:
①手动维护offset:使用cannl监控mysql数据库,不同的表发送到不同的topic中,从mysql中获取两个topic的offset,然后创建两个流。
②双流join。
订单数据和订单详情数据维护到redis的数据格式:
订单数据:
Key:order_info:订单id
Value:一条订单数据
订单详情数据:
Key:order_detail:order_id:sku_id
Value:一条订单详情数据
将两条流的数据进行数据转换成kv形式,key为订单id
- 双流fulljoin
- (order_id,(订单数据,订单详情数据))
- Mappartitions:
第一步:获取redis的连接
第二步:对数据进行模式匹配
(order_id,(some,some))
将订单信息缓存到redis中
将订单数据和订单详情数据进行合并,创建一个集合,用来接收合并后的数据,将合并后的数据加到Arraylist集合中,从通过订单id从redis中获取订单详情数据的数据,可能有多条
遍历获取的数据,
将数据封装成样例类,将获取的订单详情数据从redis中删除,将订单信息和数据进行合并,并添加到集合中
最后返回集合。
(order_id,(none,some))=>订单数据没有,有订单详情数据
第一步:从redis中获取订单缓存的数据,判断是否为空,如果不为空,先将数据进行封装成样例类,则进行合并,然后返回,如果为空,则将订单详情数据进行缓存。
③合并用户数据
读取mysql用户表的数据,并数据进行转换(user_id,user),
然后将第二步获取的数据也转换,(user_id,saledetail)
然后进行内连接,并进行格式转换,连接在一起,最后将数据写到hbase中。
- Sparkstreaming实现精准一次性消费
- 从mysql中获取offset,将数据封装成map(new TopicPartition,offset)
- 根据获取的offset,如果获取的值为空,则从头消费,earlist,如果不为空,则从获取的offset位置开始消费数据
- 数据经过一些处理完成以后,通过kafka流获取offsetRanges,并遍历,最后将数据维护到mysql中。
- 去重
实时统计日活,使用redis进行去重,获取数据以后,将数据写到Redis中,redis中的数据类型是:dau+日期,value是set集合,存储mid,根据添加数据后的返回值,如果返回1,则添加成功,这条数据就要,如果是返回0,则这条数据过滤。将去重以后的数据写到Phoenix中,然后天和小时的日活都有了。
- Oom,数据倾斜问题
Executor内存:
主要是shuffle阶段:read shuffle 和write shuffle,在shuffle算子的地方会这种情况。
情况1:shuffle阶段的reduce端的缓冲区过大,导致生产的大量的对象导致oom,调小一些缓冲区的大小
情况2:数据倾斜,单个key的数据量太多,使用随机数打散,进行二次聚合
情况3:内存就是不够大,增大内存
情况4:join的时候,采用广播变量的方式,避免shuffle
情况5:增加reduce的并行度
Driver内存:
情况1:当数据从executor使用collect拉取到drive端时,driver的内存不够用,可以增加内存
情况2:在driver创建大集合,导致数据内存不足,可以考虑在executor端加载
数据倾斜出现在连个地方,一是shuffle阶段,二是map阶段,数据量太大导致。
- Join两个大表优化
a先过滤,看数据量大小,可以考虑广播
b使用reducebykey、mappartition、增加reduce的数量
- 常规的一些优化手段
Mappartition优化和mysql的连接
foreachRDD优化和redis的连接
使用mappartition代替map....
- sparkstreaming写到hdfs有小文件怎么办
方案1:Kafka -> sparkStreaming -> kafka -> flume -> hdfs
方案2:可以使用结构化流实现向hdfs文件中追加数据
方案3:扩大采集周期 + 使用coalesce
分析完之后,数据我们是灌倒hbase和es中,hebase一般我们存储的是明细数据,es一般是监控数据,异常数据,因为可以直接通过kibana展示在大屏上,hbase存储的是明细数据,可以通过Phoenix,对外暴露接口,让web项目自己进行查询,主要是运营人员通过他读取数据,这个过程中我们还用到redis,用它去重,如果数据量大,用redis去重。
离线指标:
留转G复活,topn、热门商品、退款率、日活、周活、月活、日新增流量、
- 流量类指标相关:
Uv(独立访问客户)和pv(页面访问数),
页面跳转率
新增用户数量(日、周、月)
留存率(统计1/2/3日留存率)
7天内连续登录3天的下单、退款、支付
页面平均访问的时长
- 交易相关:按地区划分GMV和下单量(当天、近30天)
- 活动推广相关:活动曝光次数、当天下单、支付次数金额以及累计的下单次数和支付次数,用来判断一个活动的推广情况。
- 商品类相关:topn、哪个商品买的最好、复购率、退款率、评论
- 购物车相关:加购次数、
- 下单相关:笔数、金额、用户数(当天和累积30天)
- 支付相关:笔数、金额、用户数(当天和累积30天)
- 交易:终极目标
GMV 和订单量(GMV:订单金额)
指标的作用:用来判断交易结果的好坏
统计方式:从dws层获取
select
'2020-06-14',
sum(order_count),//订单数量
sum(order_amount),//订单金额
sum(if(order_count>0,1,0))//下单用户量
from dws_user_action_daycount
where dt='2020-06-14';
2. 转化率(转化率 = 引入订单量 / 流量)
指标的作用:漏斗分析,统计浏览页面 -> 进入详情的页面 -> 加入购物车 -> 下单 -> 支付,
- 步骤
with
tmp_action as (
select
'2020-06-25' dt,
sum(if(cart_count > 0,1,0)) cart_count,--加入购物车的人数
sum(if(order_count > 0,1,0)) order_count , --下单人数
sum(if(payment_count > 0,1,0)) payment_count --支付人数
from dws_user_action_daycount
where dt ='2020-06-25'
),
tmp_page as (
select
'2020-06-25' dt , --统计日期
sum(if(array_contains(pages,'home'),1,0)) home_count, --浏览首页人数
sum(if(array_contains(pages,'good_detail'),1,0)) good_detail_count, --浏览商品详情页人数
sum(if(array_contains(pages,'good_detail'),1,0)) * 100 /sum(if(array_contains(pages,'home'),1,0)) home2good_detail_convert_ratio --首页到商品详情转化率
from (
select
mid_id,
--对用户进行分组,过滤出今天进入首页和详情页的用户,获取当天用户的页面行为,去重后放到一个集合中
-- 那么一行数据有如下2种情况
-- 用户 page_id
-- 243 ["good_detail","home"]
-- 63 ["home"]
collect_set(page_id) pages
from dwd_page_log
where dt = '2020-06-25'
and page_id in ('home','good_detail')
group by mid_id
)tmp
)
insert into ads_user_action_convert_day
select
'2020-06-25' dt , --统计日期
home_count, --浏览首页人数
good_detail_count, --浏览商品详情页人数
home2good_detail_convert_ratio ,--首页到商品详情转化率
cart_count,--加入购物车的人数
cart_count *100/good_detail_count good_detail2cart_convert_ratio,--商品详情页到加入购物车转化率
order_count , --下单人数
order_count *100/cart_count cart2order_convert_ratio ,--加入购物车到下单转化率
payment_count, --支付人数
payment_count * 100 / order_count order2payment_convert_ratio --下单到支付的转化率
from tmp_action
join tmp_page on tmp_action.dt = tmp_page.dt
客单价(客单价 = GMV / 引入订单量)它描述了每个订单的平均成交金额,具有比较强的品类特征,比如奢侈品类的客单价,天然是比消费品的客单价高的。同时,如果进行了拼单满减等运营策略,也能够刺激用户一单购买更多的商品,进而提升客单价。
UV 价值(UV 价值 = GMV / 流量)它描述的是每个 UV 产出的平均金额,也能侧面看出流量的质量、流量与业务的匹配程度。试想一个页面,如果它的 UV 价值高,那么也就代表给它引入更多同类的流量,它就能创造更大的 GMV。因此 UV 价值也是一个很重要的指标,和转化率一起综合看,可以用来评估到底哪个业务 / 页面值得投入更多的流量。
>> 思考:UV 价值和客单价有什么不同?1)影响因素不同:UV 价值更受流量质量的影响;而客单价更受卖的货的影响;2)使用场景不同:UV 价值可以用来评估页面 / 模块的创造价值的潜力;客单价可以用来比较品类和商品特征,但一个页面客单价高,并不代表它创造价值的能力强,只能得出这个页面的品类更趋近于是卖高价格品类的。
2、流量:决定成败
UV & PV(页面浏览人数、页面访问次数)UV 描述了访问的人数,是一个很重要的数据指标,它的多少往往决定了最终GMV的高低。UV 源自各种途径,例如站外广告、站内的资源位分配、用户主动回访流量、社交裂变活动的分享引流等。
PV 描述了访问的次数,例如用户一天访问了这个页面3次,这时候会计算为 3 PV 和 1 UV。也就是说,PV 比 UV 多了某段时间内用户多次访问的信息。若要看页面的流量量级,无论看 UV 还是 PV 都是可以的。
人均浏览次数(人均浏览次数 = 页面访问次数 / 页面浏览人数)这个指标描述了某段时间内,每个用户平均浏览页面的次数。不同的场景会有不同的值,需要根据具体的场景来判断高低。有些情况会出现 PV 高出 UV 很多的场景,如存在需要用户多次回访的玩法、有分时段运营的策略(e.g. 一天三次红包雨)等等,需要具体场景具体分析。
3、行为:寻根溯源
点击率(点击率 = 模块点击人数 / 页面浏览人数)用户对此模块的点击人数,在所有进入页面的流量中的百分比。可以看作用户对于模块的需求强烈程度的评判指标之一。与页面流量和页面 GMV的关系类似,模块的点击率与模块的产出是强相关的(如下图,横轴是各模块)。
(各模块的点击率 & 订单金额占比对比,图表来自数懒分析平台)
>>点击率的影响因素有:1)模块在页面中的位置:若放得越高,则越可能被更多的用户看见,那么点击率高的可能性,就比放置位置低的模块要来得更高。毕竟页面越往下,看到的用户就更少了。2)模块本身的吸引程度:比如模块本身是个优惠券集合楼层,就比没有利益点的普通模块更吸引人、更容易获得更多点击。此外,模块的样式设计、主题表述的清晰与否、主题对用户的吸引力和潜在用户群大小,这些都会影响到模块的吸引力。
曝光点击率(曝光点击率 = 模块点击人数 / 模块曝光人数)用户对此模块的点击人数,在所有看到此模块的流量中的百分比。与点击率的公式对比可发现,点击率的分母是所有进入页面的流量,但用户的浏览行为永远是浏览得越深,流量越少的。这也就导致位置越深的模块算点击率就越吃亏,因为相当一部分流量压根就没有看到这个模块,也被算进分母里了。而曝光点击率,就是一个排除了页面位置对模块的影响后,可以用来相对公平地去比较各模块的吸引力的数据指标。
>> 思考:什么场景用点击率,什么场景用曝光点击率呢?1)当想要单纯评估楼层对用户的吸引力时,可以看曝光点击率;2)当想要综合评估楼层的整体效果与贡献时,看点击率,毕竟它与楼层 GMV 相关性更高;3)曝光需要特殊埋点,且可能会影响页面性能,因此很多时候我们没有办法取到曝光数据,也只能看点击率了。
>> 曝光点击率的使用注意:首屏内的楼层的曝光点击率,数据可能不准确。首屏的曝光UV是最大的,里面包含了各种异常情况,例如一进页面就跳出,也算作曝光。因此导致首屏的曝光点击率往往会偏小(如下图所示),无法与其他楼层比较。若想比较首屏情况,建议与点击率一起综合来看。
(各屏的曝光点击率差异,图表来自大促某页面的数据分析)
曝光率(曝光率 = 模块曝光人数 / 页面浏览人数)这个数据可以看出用户在页面上的浏览深度如何,有百分之多少的用户看到了哪一屏。从这个数据中,我们可以发现一些关键的节点。例如,若我们的业务主推是在第二~三屏的位置,但最终发现曝光率在第二屏便暴跌,这便是存在问题的,说不定我们需要把主推内容再往上提一些,或者需要去排查首屏是否有会令用户立即跳转和跳出的内容……这便是曝光率这个数据指标,可以带来的分析价值。
停留时长这个数据指标很好理解,是描述用户在页面上平均停留多少秒。
>> 思考:曝光率下跌曲线越慢 / 浏览深度越深 / 停留时长越长,就代表我们的页面做得越好吗?
曝光率和停留时长的影响因素比较一致,因此可以合在一起解释。曝光率的下降曲线、停留时长的长与短,影响因素有这些:
1)人的生理极限:人不是机器,根据研究,“人不受干扰地执行单一操作的时长为 6s ~ 30s ”[注1],超过这一常数,用户就会走神。可想而知,用户在单一页面上停留的时间是有上限的,不因页面放置入的内容多少而变化。一个反例,是通过利益点来吸引用户在页面上浏览得更深,这不但与生理极限相悖,也把用户自然的浏览行为和目标,硬生生变成了为了追寻更多利益点而进行类似完成任务的操作。除了用利益点交换一个好看的数据以外,这样的做法似乎没能带来更多的产出。
2)页面定位及内容:在双11主会场中,用户的行为模式趋近找优惠和找目标品类,那么他可能不会在这里浏览太多屏数、也不会停留太久——这个时候影响曝光率和停留时长的,就是他有多快能找到感兴趣的优惠,因此,并不能说浏览深度越深、停留时长越长就越好;在 BI(千人千面)商品瀑布流中,用户的行为是闲逛和挑选,这时候他更可能浏览更多的屏数、停留更长时间——因此浏览的商品越多,可以说是对最终效益最好的。
3)异常情况:例如加载异常、页面崩溃的场景,就会导致停留时长异常低、二屏后曝光异常低。
综上,我们应该根据具体的场景、通过数次历史数据的对比,去设定和校正目标曝光率、目标停留时长。平日看这两个数据,可以当做一个监测异常的数据,在正常范围内的波动不需要过度解读,一旦发现特别异常的情况,再进行具体的分析。
自己提出的指标:对营销、对运行都非常有价值的指标
指标1:寻找潜在的vip用户
- 准备三个具体的指标。比较难,又有对运营,营销又非常有价值的。帮助他们做了什么事,讲讲怎么做的
寻找潜在VIP:
- 上一周连续3天登录,且上周内下过一单的
先过滤取出上周内下过一单,又是非vip的人。(从订单明细表)
再根据他们每日的最早启动时间,用rank窗口函数进行排序。那么排序的这个字段就应该是以1为公差的等差数列(left join 用户活跃表日)
然后再用date-sub去将启动日期与rank计算,得到了日期差值,根据这个日期差值进行分组,计算这个差有几个。
>3就是我们所需要的用户。
找出来之后,给她短信,后台消息推送优惠券。减税,免邮,享受会员价等活动。
- 过去一个月内下单商品大于8件,且下单次数大于2
使用用户订单详情表:取出过去一个月的非vip用户购买详情。
计算每个用户的下单商品数,下单次数>2 (group by userID,sum(购买件数),count(distinct 订单号)》2)
推送消息,给免费vip活动体验
这部分的用户在接下来的三个月时间里,真正转换成vip的有35%的人,所以这个指标还挺有意义的
商品季度/半年复购率(购买过这个商品两次以上的用户数/这个季度购买这种商品的总人数):
- 用户购买明细表。
把上个季度的用户购买详情表过滤出来。group by 用户id 商品id分组,求出用户对于某个商品下单的总次数。
然后用sum if(判断订单单数>2),订单单数>1的人数,求比率,
然后对比率根据品类排名,求每个品类中 比率排名前十的。用row_number<11.分区取品类,排序取复购率。
这些商品,是我们的重要维系的商品,要及时补货。然后复购率高说明,受用户喜欢,可以推荐,给用户发送小样,尝试,增大转化率。
4.品牌复购率:
差不多,把具体商品,改成品牌id。各类商品下的品牌复购率(每月来算)
5.每周各品类热门商品销量前三(取每周各热门品类,然后取用户行为宽表的几个字段,热门品类,用户id,商品id。然后用热门品类过滤。得到属于热门品类的数据,再根据热门品类,商品id,去聚合。去前三。)
6.各区域热门商品销量前五:取用户行为宽表,然后得到里面的数据,可以转化成样例类的rdd。然后根据区域分组,然后求商品销量,前五的。
7.各品类中销量前三的品牌
8.购物车各品类占比:以品牌为key,数量为value。从购物车宽表中获取数据。然后根据品牌分类,求总数。(说明大家想买的东西,便于后期铺货。
数据健康问题:
物流信息:有的客户物流信息上显示收到货了,但是快递可能没有送到他手里,然后过程中有丢失的情况。那么我们的物流计算时长,如果单纯按照物流信息来就会出现偏差,所以我们物流到货时间都是以用户,确认收货为准。也不会差很大。
用户的隐私信息,电话号码:我们使用自己的一套脱敏技术,将每个电话号码的4-11位,加1,然后4-7位与8-11位顺序调换。后期我们需要用到他们的隐私信息,电话进行,营销,发送消息是,就把他转换过来。
数据倾斜问题:
- 用时间维度表去join过去一整年的用户购买明细表,查看,用户集中购买的月份和季节。分析用户的行为。之前不是默认的。(默认开启mapJoin嘛)
- 小表join大表的问题。后面这个优化了,但是小表不能超过512M.我们数据量没那么大,应该是可以的。
比如说算品类销售排名的时候,group by 品类,求销售总量是,某一品类像面膜,可能销售量特别大,占60%多,那么有一个任务就会执行特别久。半天出不来。设置推测执行也差不多,就应该是数据倾斜导致的问题
Map端部分聚合
这里需要修改的参数为:
hive.map.aggr=true(用于设定是否在 map 端进行聚合,默认值为真) hive.groupby.mapaggr.checkinterval=100000(用于设定 map 端进行聚合操作的条目数)
- 有数据倾斜时进行负载均衡
此处需要设定 hive.groupby.skewindata,当选项设定为 true 是,生成的查询计划有两 个 MapReduce 任务。在第一个 MapReduce 中,map 的输出结果集合会随机分布到 reduce 中, 每个 reduce 做部分聚合操作,并输出结果。这样处理的结果是,相同的 Group By Key 有可 能分发到不同的 reduce 中,从而达到负载均衡的目的;第二个 MapReduce 任务再根据预处 理的数据结果按照 Group By Key 分布到 reduce 中(这个过程可以保证相同的 Group By Key 分布到同一个 reduce 中),最后完成最终的聚合操作。