数仓项目总结

项目分点：

1. 集群规模：
   （12台物理机：128G内存，8T机械硬盘，2T固态硬盘，20核40线程，戴尔4万多一台）
2. 框架结构，画出来
   （日志部分：日志服务器，落盘日志，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。
   ⑦　我们数据仓库是基于维度建模，主要使用星型模型。
   ⑧　把表分为四类，实体表，主要是一些对象表比如用户，商家，商品等。
   ⑨　维度表，是指指一些业务状态，编号的解释，又叫码表，像地区表，订单状态，支付方式，审批状态。状态分类。
   ⑩　事实表分为周期型事实表和事务型事实表。如果需要后面状态还会改变的就是周期型事实表，一旦确定了，就是事务性事实表。
   11　对于不同的表我们使用不同的同步策略，

同步策略包括全量表，增量表，新增及变化，拉链表
日志表：（商品点击，商品详情，商品详情页表，广告表，错误日志表，消息通知表等）
(1) 商品点击：用户的基本信息字段，动作，商品id，种类等。
(2) 商品详情页：入口，上一页面来源，商品id，加载时间，种类。
(3) 广告表：入口，内容，行为，展示风格等。
(4) 错误日志：错误详情
(5) 消息通知表：通知类型，展示时间，通知内容等
这些记录性质的，都使用每日增量

业务表：（购物车，评分，评论，订单表，订单详情表，退货表，用户表，商家表，商品分类表（一级，二级，三级），支付流水，物流信息等）
(1) 购物车详情：用户id ，商品id，商品价格，商家id ，商品型号，商品分类等
同步策略：这属于周期型事实表因为他可能会随时改变，所以得用每日新增及变化。
(2) 评分表：评分时间，评分用户，评分商品 ，分数等，
同步策略：这是事务性事实表，一般可以用每日增量就可以了。但是如果电商用户对某件商品用过之后，又想改评论，所以用每日新增及变化
(3) 评论表：评论时间，评论用户，评论商品，评论内容，
同步策略：这个跟评分差不多，用每日新增及变化
(4) 订单表：订单状态，订单编号，订单金额，支付方式，支付流水，创建时间等
同步策略：因为订单的状态会随时发生改变，比如下单，支付，商家发货，用户收到货，确认收货，等这一系列的状态会比较长，然后订单也比较多。所以，要做历史快照信息的话，最好使用拉链表。
(5) 订单详情表：订单编号，订单号，用户id，商品名称，商品价格，商品数量，创建时间等。
同步策略：这属于记录信息的，直接用每日新增。
(6) 退货流程表：用户id，订单编号，退货商品id，退货状态，时间等
同步策略：这种需要分阶段操作的，需要用户申请退货，平台审核，通过后，用户寄货，上传订单号，跟踪物流信息，商家收货，平台退款。这一系列的流程，可能需要很长时间。为了查看每个历史时间点的切片信息，我们需要做拉链表。
(7) 用户表：用户id，性别，等级，vip，注册时间等等。
同步策略：因为表不是很大，每次做全量表。
(8) 商家表：商家id，商家地址，商家规模等级，商家注册时间，商家分类信息。
同步策略：每次做每日全量
(9) 商品分类表：一级，二级，三级，
同步策略：表很小，或者不怎么改变的直接默认值。使用每日全量
(10) 商品表：商品id，商品各级分类id，商品的型号，商品的价格等
同步策略：有些可能做每日新增。但是他的数据量基本固定，并且没那么大，但是可能随着商品的上架，下架，更新起来很麻烦，所以我们直接做每日全量，
(11) 支付流水：支付方式，支付订单，支付用户id，支付商家，支付时间等。
同步策略：每日新增。
(12) 物流信息表：快递公司名称，快递单号，状态，订单编号，等
同步策略：他是属于事务性事实表，像流水信息，直接用每日增量

总结：
①　实体表，不大，就可以做每日全量
②　对于维度表，比如说商品分类，这种不是很大，也可以做每日全量
有一些不太会发生改变的维度，就可以固定保存一份值，比如说：地区，种族，等
③　像事务型事实表，比如说交易流水，操作日志，出库信息，这种每日比较大，且需要历史数据的，就根据时间做每日新增，可以利用分区表，每日做分区存储。
④　像这种周期型事实表的同步策略，比如订单表，有周期性变化，需要反应不同时间点的状态的，就需要做拉链表。记录每条信息的生命周期，一旦一条记录的生命周期结束，就开始下一条新的记录。并把当前的日期放生效开始日期。

离线指标：
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的（）
(1) 日活/周活/月活统计：（每日的根据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')要求年月时间符合要求就可以了

(2) 用户新增：每日新增（每日活跃设备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;

(3)用户留存率,(一周留存)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层建表
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 '留存率'
#向表中导入数据
insert into table ads_user_retention_day_rate
select 
    '2019-02-11', 
    ur.create_date,
    ur.retention_day, 
    ur.retention_count, 
    nc.new_mid_count,
    ur.retention_count/nc.new_mid_count*100
from 
(
    select
        create_date,
        retention_day,
        count(*) retention_count
    from dws_user_retention_day
    where dt='2019-02-11' 
    group by create_date,retention_day
) ur join ads_new_mid_count nc on nc.create_date=ur.create_date;

(4) 沉默用户数：不是新用户，只在注册当天启动过，且启动时间在一周前
求沉默用户数（根据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;

(5) 流失用户数:最近一个月没有登录的用户个数
需求:如果最近15天没有登录认为是流失用户

from 
     dws_uv_detail_day
group by
     mid_id
having max(dt)<=date_sub('2019-02-03',15))t1;

(6) 本周回流用户：本周回流=本周活跃-本周新增-上周活跃
本周活跃mid left join 本周新增， left join 上周活跃 然后join的新字段都是null的
(7) 最近连续三周活跃用户
最近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;

(8)本周内连续三天的登录 用户
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的数据取出,即为连续三天及以上的活跃用户数

select
     mid_id
from t2
group by mid_id,date_diff
having count(*)>=3;t3

4.统计最近7天连续3天活跃的用户数

select count(*) from t3;

(9) GMV每日/每周/每月成交总额：根据用户行为宽表，sum 订单总额
(10) 业务指标：当日新增占日活的比率

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;

(11) 用户行为之漏斗分析：

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'

(12) 品牌复购率（一个季度之内，同一个品牌买两次的人数，买三次的人数。/此品牌购买的总人数）

(13) 用户在线时长统计（进入后台时间戳-启动时间戳，根据用户sum，然后求平均值）
(14) 一周销量前十品类：用户行为宽表，过滤一周的数据，根据group by 品类 Count数
(15) 各区域漏斗分析，与漏斗分析类似，根据区域分组