hadoop学习---基于Hive的教育平台数据仓库分析案例（二）

衔接第一部分，第一部分请点击：基于Hive的教育平台数据仓库分析案例（一）

后接第三部分，第三部分请点击：基于Hive的教育平台数据仓库分析案例   (三) 

意向用户模块（全量分析）：

需求指标：

需求一: 计期内，新增意向客户（包含自己录入的意向客户）总数。

需求二: 统计指定时间段内，新增的意向客户，所在城市区域人数热力图。

需求三: 统计指定时间段内，新增的意向客户中，意向学科人数排行榜。

需求四: 统计指定时间段内，新增的意向客户中，意向校区人数排行榜。

需求五: 统计指定时间段内，新增的意向客户中，不同来源渠道的意向客户占比。

需求六: 统计指定时间段内，新增的意向客户中，各咨询中心产生的意向客户数占比情况。

总体分析：  

指标: 
            意向量

维度: 
            固有维度:每个表都有的
                        时间维度: 年、 月 、 天、 小时
                        线上线下:
                        新老维度:
            产品属性维度: 
                        地区维度:
                        校区维度:
                        学科维度;
                        来源渠道:
                        各咨询中心：

涉及表: 
            customer_relationship(客户意向表)  --------------(事实表)
            employee(员工表) -------------------------------------(维度表)
            scrm_department(部门表) -------------------------- (维度表)
            customer_clue(线索表) ------------------------------ (维度表)
            itcast_school(校区表) -------------------------------- (维度表)
            itcast_subject(学科表) ------------------------------- (维度表)
            customer (客户表)  ------------------------------------(维度表)

表与表关系:  
            客户意向表.creator= 员工表.id
            员工表.tdepart_id = 部门表.id
            线索表.customer_relationship_id  = 客户意向表.id
            客户意向表.itcast_school_id  = 校区表.id
            客户意向表.itcast_subject_id  = 学科表.id
            客户意向表.customer_id  = 客户表.id

涉及字段: 
            时间维度: 客户意向表.create_date_time
            线上线下: 客户意向表.origin_type --> origin_type_stat
            新老维度: 线索表.clue_state --> clue_state_stat
            地区维度: 客户表.area
            校区维度: 客户意向表.itcast_school_id 和 校区表.name
            学科维度: 客户意向表.itcast_subject_id 和 学科表.name
            来源渠道: 客户意向表.origin_type
            各咨询中心: 员工表.tdepart_id 和 部门表.name
            指标字段: 客户意向表.customer_id
            清洗字段: 线索表.deleted 
    
需要清洗的内容:

                将删除标记为true的数据删除
                过滤出: 客户意向表.deleted = false

需要转换的内容:
    1) 日期: 客户意向表.create_date_time  需要转换为: yearinfo  monthinfo dayinfo hourinfo
    2) 新老维度: 线索表.clue_state
        说明: 当字段的值为 'VALID_NEW_CLUES' 为新用户
                    暂定: 其他的值都是老用户
        需要转换为一个新的字段: clue_state_stat 
            此字段只有二个值: 0(老)   1(新)
    3) 线上线下: 客户意向表.origin_type
        说明: 当字段的值为 'NETSERVICE' 或者 'PRESIGNUP' 表示为线上
                暂定: 其他值都为线下
        需要转换为一个新的字段: origin_type_stat
            此字段只有二个值  0(线下)    1(线上)
    4) 校区和学科的id转换 
            需要将客户意向表中, 校区id 和 学科id 如果为 0或者 null 转换为 -1

数据准备： 

将原始数据加载到本地MySQL数据库中 

创建数据库，执行sql文件： 点击下载：sql文件

create database scrm default character set utf8mb4 collate utf8mb4_unicode_ci;

建模分析： 

ODS层: 源数据层

作用: 对接数据源, 一般和数据源保持相同粒度 (直白: 将数据源中拷贝到ODS层中)

处理方案:查看业务库有那些表，对照在oDs层构建有那些表，保证每个表字段保持一致，同时在ODs建表的时候需要额外添加一个特殊字段: starts_time(表示抽取数据的时间)

放置事实表即可:
     customer_relationship(意向表 )  -- 本次主题的事实表
     customer_clue(线索表)  -- 本次主题的维度表, 下次主题的事实表
建表操作:   
    表中字段与数据源中字段保持一致, 只需要多加一个 抽取时间的字段即可
请注意:
    意向表  和 线索表中数据存在数据变更操作,需要采用缓慢渐变维（scd）的方式来解决
 

DIM层: 维度层

作用：存储维度表的数据

放置维度表: 
    5张表
     customer(客户表)                  ---  维度表
     itcast_subject(学科表)            ---  维度表
     itcast_school(校区表)             ---  维度表
     employee(员工表)                  ---  维度表
     scrm_department(部门表)           ---  维度表

建表:
   与数据源保持一致的字段即可, 多加一个当前抽取时间的字段

DWD层: 明细层

DWD层表的构建: 必须字段(只能是事实表中字段) + 清洗的字段 + 转换的字段+ join字段

作用: 1) 清洗转换处理工作

         2) 少量维度退化(此层不需要执行)

需要清洗内容: 
            将标记为删除的数据进行过滤掉 

需要转换内容:
           1) 将create_date_time 转换为 yearinfo  monthinfo dayinfo hourinfo
           2) 将origin_type 转换为 origin_type_state (用于统计线上线下)
               转换逻辑: origin_type的值为: NETSERVICE 或者 PRESIGNUP 认为线上 其余认为线下
           3) 将clue_state 转换为 clue_state_stat (用于统计新老维度)
               转换逻辑:clue_state的值为 VALID_NEW_CLUES 为新客户  其余暂定为老客户
           4) 将校区和学科的 id字段, 如果为 0 或者 null 转换为 -1

customer_relationship(意向表 )    ---  事实表
         时间维度: create_date_time
           线上线下: origin_type --> origin_type_stat
           来源渠道: origin_type
           校区维度:  itcast_school_id
           学科维度:  itcast_subject_id 
           指标字段: customer_id,
           关联条件的字段: creator,id

表字段的组成:
   customer_id, create_date_time,origin_type,itcast_school_id,itcast_subject_id,creator,id
   deleted,origin_type_stat,yearinfo  monthinfo dayinfo hourinfo

DWM层: 中间层 

作用: 1) 提前聚合的操作( 由于有去重,导致无法实施) 2) 维度退化操作

思考1:需要做什么维度退化操作?
          需要做，将所有维度表和事实表关联在一起，将维度表中需要的字段合并到事实表
思考2:需要做什么提前聚合操作?

          不需要,后期做，现在做可能导致数据缺失。

建表字段:DWD层＋各个表维度字段

customer_id,create_date_time, yearinfo  monthinfo dayinfo hourinfo deleted (意义不大)
clue_state_stat(此字段需要转换)，origin_type_stat，area,itcast_subject_id,itcast_subject_name
itcast_school_id,itcast_school_name，origin_type，tdepart_id,tdepart_name

注意：要聚合上面所有字段要进行七表关联的操作。

DWS层: 业务层

作用: 细化统计各个维度的数据

DWS层表字段构成:  统计的字段 + 各个维度的字段 + 三个用于查询的字段

维度: 
    固有维度:
                时间维度: 年  月  天 小时
                新老维度:
                线上线下
    产品属性维度:
                总意向量
                地区(区域)维度
                学科维度
                校区维度
                来源渠道
                各咨询中心

DWS层表字段:
   customerid_total, yearinfo,monthinfo,dayinfo,hourinfo，clue_state_stat,origin_type_stat,
   area，itcast_subject_id,itcast_subject_name， itcast_school_id,itcast_school_name，
   origin_type， tdepart_id,tdepart_name，group_type，time_type，time_str 

DA层:

作用: 对接应用, 应用需要什么数据, 从DWS层获取什么数据即可

此层目前不做任何处理, 已经全部需要都细化统计完成了, 后续具体用什么, 看图表支持了...

建模操作（建表）：

ODS层：将MySQL数据库中数据导入hive中

set hive.exec.orc.compression.strategy=COMPRESSION;

客户意向表：(内部 分区 分桶表, 拉链表)

CREATE TABLE IF NOT EXISTS itcast_ods.`customer_relationship` (
  `id` int COMMENT '客户关系id',
  `create_date_time` STRING COMMENT '创建时间',
  `update_date_time` STRING COMMENT '最后更新时间',
  `deleted` int COMMENT '是否被删除（禁用）',
  `customer_id` int COMMENT '所属客户id',
  `first_id` int COMMENT '第一条客户关系id',
  `belonger` int COMMENT '归属人',
  `belonger_name` STRING COMMENT '归属人姓名',
  `initial_belonger` int COMMENT '初始归属人',
  `distribution_handler` int COMMENT '分配处理人',
  `business_scrm_department_id` int COMMENT '归属部门',
  `last_visit_time` STRING COMMENT '最后回访时间',
  `next_visit_time` STRING COMMENT '下次回访时间',
  `origin_type` STRING COMMENT '数据来源',
  `itcast_school_id` int COMMENT '校区Id',
  `itcast_subject_id` int COMMENT '学科Id',
  `intention_study_type` STRING COMMENT '意向学习方式',
  `anticipat_signup_date` STRING COMMENT '预计报名时间',
  `level` STRING COMMENT '客户级别',
  `creator` i