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` int COMMENT '创建人',
  `current_creator` int COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
  `creator_name` STRING COMMENT '创建者姓名',
  `origin_channel` STRING COMMENT '来源渠道',
  `comment` STRING COMMENT '备注',
  `first_customer_clue_id` int COMMENT '第一条线索id',
  `last_customer_clue_id` int COMMENT '最后一条线索id',
  `process_state` STRING COMMENT '处理状态',
  `process_time` STRING COMMENT '处理状态变动时间',
  `payment_state` STRING COMMENT '支付状态',
  `payment_time` STRING COMMENT '支付状态变动时间',
  `signup_state` STRING COMMENT '报名状态',
  `signup_time` STRING COMMENT '报名时间',
  `notice_state` STRING COMMENT '通知状态',
  `notice_time` STRING COMMENT '通知状态变动时间',
  `lock_state` STRING COMMENT '锁定状态',
  `lock_time` STRING COMMENT '锁定状态修改时间',
  `itcast_clazz_id` int COMMENT '所属ems班级id',
  `itcast_clazz_time` STRING COMMENT '报班时间',
  `payment_url` STRING COMMENT '付款链接',
  `payment_url_time` STRING COMMENT '支付链接生成时间',
  `ems_student_id` int COMMENT 'ems的学生id',
  `delete_reason` STRING COMMENT '删除原因',
  `deleter` int COMMENT '删除人',
  `deleter_name` STRING COMMENT '删除人姓名',
  `delete_time` STRING COMMENT '删除时间',
  `course_id` int COMMENT '课程ID',
  `course_name` STRING COMMENT '课程名称',
  `delete_comment` STRING COMMENT '删除原因说明',
  `close_state` STRING COMMENT '关闭装填',
  `close_time` STRING COMMENT '关闭状态变动时间',
  `appeal_id` int COMMENT '申诉id',
  `tenant` int COMMENT '租户',
  `total_fee` DECIMAL COMMENT '报名费总金额',
  `belonged` int COMMENT '小周期归属人',
  `belonged_time` STRING COMMENT '归属时间',
  `belonger_time` STRING COMMENT '归属时间',
  `transfer` int COMMENT '转移人',
  `transfer_time` STRING COMMENT '转移时间',
  `follow_type` int COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
  `transfer_bxg_oa_account` STRING COMMENT '转移到博学谷归属人OA账号',
  `transfer_bxg_belonger_name` STRING COMMENT '转移到博学谷归属人OA姓名',
  `end_time` STRING COMMENT '有效截止时间')
comment '客户关系表'
PARTITIONED BY(start_time STRING)
clustered by(id) sorted by(id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

客户线索表：（内部分区分桶表, 拉链表）

CREATE TABLE IF NOT EXISTS itcast_ods.customer_clue (
  id int COMMENT 'customer_clue_id',
  create_date_time STRING COMMENT '创建时间',
  update_date_time STRING COMMENT '最后更新时间',
  deleted STRING COMMENT '是否被删除（禁用）',
  customer_id int COMMENT '客户id',
  customer_relationship_id int COMMENT '客户关系id',
  session_id STRING COMMENT '七陌会话id',
  sid STRING COMMENT '访客id',
  status STRING COMMENT '状态（undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转）',
  users STRING COMMENT '所属坐席',
  create_time STRING COMMENT '七陌创建时间',
  platform STRING COMMENT '平台来源 （pc-网站咨询|wap-wap咨询|sdk-app咨询|weixin-微信咨询）',
  s_name STRING COMMENT '用户名称',
  seo_source STRING COMMENT '搜索来源',
  seo_keywords STRING COMMENT '关键字',
  ip STRING COMMENT 'IP地址',
  referrer STRING COMMENT '上级来源页面',
  from_url STRING COMMENT '会话来源页面',
  landing_page_url STRING COMMENT '访客着陆页面',
  url_title STRING COMMENT '咨询页面title',
  to_peer STRING COMMENT '所属技能组',
  manual_time STRING COMMENT '人工开始时间',
  begin_time STRING COMMENT '坐席领取时间 ',
  reply_msg_count int COMMENT '客服回复消息数',
  total_msg_count int COMMENT '消息总数',
  msg_count int COMMENT '客户发送消息数',
  comment STRING COMMENT '备注',
  finish_reason STRING COMMENT '结束类型',
  finish_user STRING COMMENT '结束坐席',
  end_time STRING COMMENT '会话结束时间',
  platform_description STRING COMMENT '客户平台信息',
  browser_name STRING COMMENT '浏览器名称',
  os_info STRING COMMENT '系统名称',
  area STRING COMMENT '区域',
  country STRING COMMENT '所在国家',
  province STRING COMMENT '省',
  city STRING COMMENT '城市',
  creator int COMMENT '创建人',
  name STRING COMMENT '客户姓名',
  idcard STRING COMMENT '身份证号',
  phone STRING COMMENT '手机号',
  itcast_school_id int COMMENT '校区Id',
  itcast_school STRING COMMENT '校区',
  itcast_subject_id int COMMENT '学科Id',
  itcast_subject STRING COMMENT '学科',
  wechat STRING COMMENT '微信',
  qq STRING COMMENT 'qq号',
  email STRING COMMENT '邮箱',
  gender STRING COMMENT '性别',
  level STRING COMMENT '客户级别',
  origin_type STRING COMMENT '数据来源渠道',
  information_way STRING COMMENT '资讯方式',
  working_years STRING COMMENT '开始工作时间',
  technical_directions STRING COMMENT '技术方向',
  customer_state STRING COMMENT '当前客户状态',
  valid STRING COMMENT '该线索是否是网资有效线索',
  anticipat_signup_date STRING COMMENT '预计报名时间',
  clue_state STRING COMMENT '线索状态',
  scrm_department_id int COMMENT 'SCRM内部部门id',
  superior_url STRING COMMENT '诸葛获取上级页面URL',
  superior_source STRING COMMENT '诸葛获取上级页面URL标题',
  landing_url STRING COMMENT '诸葛获取着陆页面URL',
  landing_source STRING COMMENT '诸葛获取着陆页面URL来源',
  info_url STRING COMMENT '诸葛获取留咨页URL',
  info_source STRING COMMENT '诸葛获取留咨页URL标题',
  origin_channel STRING COMMENT '投放渠道',
  course_id int COMMENT '课程编号',
  course_name STRING COMMENT '课程名称',
  zhuge_session_id STRING COMMENT 'zhuge会话id',
  is_repeat int COMMENT '是否重复线索(手机号维度) 0:正常 1：重复',
  tenant int COMMENT '租户id',
  activity_id STRING COMMENT '活动id',
  activity_name STRING COMMENT '活动名称',
  follow_type int COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
  shunt_mode_id int COMMENT '匹配到的技能组id',
  shunt_employee_group_id int COMMENT '所属分流员工组',
  ends_time STRING COMMENT '有效时间')
comment '客户关系表'
PARTITIONED BY(starts_time STRING)
clustered by(customer_relationship_id) sorted by(customer_relationship_id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

DIM层：

五张维度表：客户表，学科表，校区表，员工表，部门表：

CREATE DATABASE IF NOT EXISTS itcast_dimen;
-- 客户表
CREATE TABLE IF NOT EXISTS itcast_dimen.`customer` (
  `id` int COMMENT 'key id',
  `customer_relationship_id` int COMMENT '当前意向id',
  `create_date_time` STRING COMMENT '创建时间',
  `update_date_time` STRING COMMENT '最后更新时间',
  `deleted` int  COMMENT '是否被删除（禁用）',
  `name` STRING COMMENT '姓名',
  `idcard` STRING  COMMENT '身份证号',
  `birth_year` int COMMENT '出生年份',
  `gender` STRING COMMENT '性别',
  `phone` STRING COMMENT '手机号',
  `wechat` STRING COMMENT '微信',
  `qq` STRING COMMENT 'qq号',
  `email` STRING COMMENT '邮箱',
  `area` STRING COMMENT '所在区域',
  `leave_school_date` date COMMENT '离校时间',
  `graduation_date` date COMMENT '毕业时间',
  `bxg_student_id` STRING COMMENT '博学谷学员ID，可能未关联到，不存在',
  `creator` int COMMENT '创建人ID',
  `origin_type` STRING COMMENT '数据来源',
  `origin_channel` STRING COMMENT '来源渠道',
  `tenant` int,
  `md_id` int COMMENT '中台id')
comment '客户表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 学科表
CREATE TABLE IF NOT EXISTS itcast_dimen.`itcast_subject` (
  `id` int COMMENT '自增主键',
  `create_date_time` timestamp COMMENT '创建时间',
  `update_date_time` timestamp COMMENT '最后更新时间',
  `deleted` STRING COMMENT '是否被删除（禁用）',
  `name` STRING COMMENT '学科名称',
  `code` STRING COMMENT '学科编码',
  `tenant` int COMMENT '租户')
comment '学科字典表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 校区表
CREATE TABLE IF NOT EXISTS itcast_dimen.`itcast_school` (
  `id` int COMMENT '自增主键',
  `create_date_time` timestamp COMMENT '创建时间',
  `update_date_time` timestamp  COMMENT '最后更新时间',
  `deleted` STRING COMMENT '是否被删除（禁用）',
  `name` STRING COMMENT '校区名称',
  `code` STRING COMMENT '校区标识',
  `tenant` int COMMENT '租户')
comment '校区字典表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 员工表
CREATE TABLE IF NOT EXISTS itcast_dimen.employee (
  id int COMMENT '员工id',
  email STRING COMMENT '公司邮箱，OA登录账号',
  real_name STRING COMMENT '员工的真实姓名',
  phone STRING COMMENT '手机号，目前还没有使用；隐私问题OA接口没有提供这个属性，',
  department_id STRING COMMENT 'OA中的部门编号，有负值',
  department_name STRING COMMENT 'OA中的部门名',
  remote_login STRING COMMENT '员工是否可以远程登录',
  job_number STRING COMMENT '员工工号',
  cross_school STRING COMMENT '是否有跨校区权限',
  last_login_date STRING COMMENT '最后登录日期',
  creator int COMMENT '创建人',
  create_date_time STRING COMMENT '创建时间',
  update_date_time STRING COMMENT '最后更新时间',
  deleted STRING COMMENT '是否被删除（禁用）',
  scrm_department_id int COMMENT 'SCRM内部部门id',
  leave_office STRING COMMENT '离职状态',
  leave_office_time STRING COMMENT '离职时间',
  reinstated_time STRING COMMENT '复职时间',
  superior_leaders_id int COMMENT '上级领导ID',
  tdepart_id int COMMENT '直属部门',
  tenant int COMMENT '租户',
  ems_user_name STRING COMMENT 'ems用户名称'
)
comment '员工表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 部门表
CREATE TABLE IF NOT EXISTS itcast_dimen.`scrm_department` (
  `id` int COMMENT '部门id',
  `name` STRING COMMENT '部门名称',
  `parent_id` int COMMENT '父部门id',
  `create_date_time` STRING COMMENT '创建时间',
  `update_date_time` STRING COMMENT '更新时间',
  `deleted` STRING COMMENT '删除标志',
  `id_path` STRING COMMENT '编码全路径',
  `tdepart_code` int COMMENT '直属部门',
  `creator` STRING COMMENT '创建者',
  `depart_level` int COMMENT '部门层级',
  `depart_sign` int COMMENT '部门标志，暂时默认1',
  `depart_line` int COMMENT '业务线，存储业务线编码',
  `depart_sort` int COMMENT '排序字段',
  `disable_flag` int COMMENT '禁用标志',
  `tenant` int COMMENT '租户')
comment 'scrm部门表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');CREATE DATABASE IF NOT EXISTS itcast_dimen;
-- 客户表
CREATE TABLE IF NOT EXISTS itcast_dimen.`customer` (
  `id` int COMMENT 'key id',
  `customer_relationship_id` int COMMENT '当前意向id',
  `create_date_time` STRING COMMENT '创建时间',
  `update_date_time` STRING COMMENT '最后更新时间',
  `deleted` int  COMMENT '是否被删除（禁用）',
  `name` STRING COMMENT '姓名',
  `idcard` STRING  COMMENT '身份证号',
  `birth_year` int COMMENT '出生年份',
  `gender` STRING COMMENT '性别',
  `phone` STRING COMMENT '手机号',
  `wechat` STRING COMMENT '微信',
  `qq` STRING COMMENT 'qq号',
  `email` STRING COMMENT '邮箱',
  `area` STRING COMMENT '所在区域',
  `leave_school_date` date COMMENT '离校时间',
  `graduation_date` date COMMENT '毕业时间',
  `bxg_student_id` STRING COMMENT '博学谷学员ID，可能未关联到，不存在',
  `creator` int COMMENT '创建人ID',
  `origin_type` STRING COMMENT '数据来源',
  `origin_channel` STRING COMMENT '来源渠道',
  `tenant` int,
  `md_id` int COMMENT '中台id')
comment '客户表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 学科表
CREATE TABLE IF NOT EXISTS itcast_dimen.`itcast_subject` (
  `id` int COMMENT '自增主键',
  `create_date_time` timestamp COMMENT '创建时间',
  `update_date_time` timestamp COMMENT '最后更新时间',
  `deleted` STRING COMMENT '是否被删除（禁用）',
  `name` STRING COMMENT '学科名称',
  `code` STRING COMMENT '学科编码',
  `tenant` int COMMENT '租户')
comment '学科字典表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 校区表
CREATE TABLE IF NOT EXISTS itcast_dimen.`itcast_school` (
  `id` int COMMENT '自增主键',
  `create_date_time` timestamp COMMENT '创建时间',
  `update_date_time` timestamp  COMMENT '最后更新时间',
  `deleted` STRING COMMENT '是否被删除（禁用）',
  `name` STRING COMMENT '校区名称',
  `code` STRING COMMENT '校区标识',
  `tenant` int COMMENT '租户')
comment '校区字典表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 员工表
CREATE TABLE IF NOT EXISTS itcast_dimen.employee (
  id int COMMENT '员工id',
  email STRING COMMENT '公司邮箱，OA登录账号',
  real_name STRING COMMENT '员工的真实姓名',
  phone STRING COMMENT '手机号，目前还没有使用；隐私问题OA接口没有提供这个属性，',
  department_id STRING COMMENT 'OA中的部门编号，有负值',
  department_name STRING COMMENT 'OA中的部门名',
  remote_login STRING COMMENT '员工是否可以远程登录',
  job_number STRING COMMENT '员工工号',
  cross_school STRING COMMENT '是否有跨校区权限',
  last_login_date STRING COMMENT '最后登录日期',
  creator int COMMENT '创建人',
  create_date_time STRING COMMENT '创建时间',
  update_date_time STRING COMMENT '最后更新时间',
  deleted STRING COMMENT '是否被删除（禁用）',
  scrm_department_id int COMMENT 'SCRM内部部门id',
  leave_office STRING COMMENT '离职状态',
  leave_office_time STRING COMMENT '离职时间',
  reinstated_time STRING COMMENT '复职时间',
  superior_leaders_id int COMMENT '上级领导ID',
  tdepart_id int COMMENT '直属部门',
  tenant int COMMENT '租户',
  ems_user_name STRING COMMENT 'ems用户名称'
)
comment '员工表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 部门表
CREATE TABLE IF NOT EXISTS itcast_dimen.`scrm_department` (
  `id` int COMMENT '部门id',
  `name` STRING COMMENT '部门名称',
  `parent_id` int COMMENT '父部门id',
  `create_date_time` STRING COMMENT '创建时间',
  `update_date_time` STRING COMMENT '更新时间',
  `deleted` STRING COMMENT '删除标志',
  `id_path` STRING COMMENT '编码全路径',
  `tdepart_code` int COMMENT '直属部门',
  `creator` STRING COMMENT '创建者',
  `depart_level` int COMMENT '部门层级',
  `depart_sign` int COMMENT '部门标志，暂时默认1',
  `depart_line` int COMMENT '业务线，存储业务线编码',
  `depart_sort` int COMMENT '排序字段',
  `disable_flag` int COMMENT '禁用标志',
  `tenant` int COMMENT '租户')
comment 'scrm部门表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

DWD层:

CREATE TABLE IF NOT EXISTS itcast_dwd.`itcast_intention_dwd` (
  `rid` int COMMENT 'id',
  `customer_id` STRING COMMENT '客户id',
  `create_date_time` STRING COMMENT '创建时间',
  `itcast_school_id` STRING COMMENT '校区id',
  `deleted` STRING COMMENT '是否被删除',
  `origin_type` STRING COMMENT '来源渠道',
  `itcast_subject_id` STRING COMMENT '学科id',
  `creator` int COMMENT '创建人',
  `hourinfo` STRING COMMENT '小时信息',
  `origin_type_stat` STRING COMMENT '数据来源:0.线下；1.线上'
)
comment '客户意向dwd表'
PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
clustered by(rid) sorted by(rid) into 10 buckets
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

DWM层：

create database itcast_dwm;

CREATE TABLE IF NOT EXISTS itcast_dwm.`itcast_intention_dwm` (
  `customer_id` STRING COMMENT 'id信息',
  `create_date_time` STRING COMMENT '创建时间',
  `area` STRING COMMENT '区域信息',
  `itcast_school_id` STRING COMMENT '校区id',
  `itcast_school_name` STRING COMMENT '校区名称',
  `deleted` STRING COMMENT '是否被删除',
  `origin_type` STRING COMMENT '来源渠道',
  `itcast_subject_id` STRING COMMENT '学科id',
  `itcast_subject_name` STRING COMMENT '学科名称',
  `hourinfo` STRING COMMENT '小时信息',
  `origin_type_stat` STRING COMMENT '数据来源:0.线下；1.线上',
  `clue_state_stat` STRING COMMENT '新老客户：0.老客户；1.新客户',
  `tdepart_id` STRING COMMENT '创建者部门id',
  `tdepart_name` STRING COMMENT '咨询中心名称'
)
comment '客户意向dwm表'
PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
clustered by(customer_id) sorted by(customer_id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

DWS层

CREATE TABLE IF NOT EXISTS itcast_dws.itcast_intention_dws (
   `customer_total` INT COMMENT '聚合意向客户数',
   `area` STRING COMMENT '区域信息',
   `itcast_school_id` STRING COMMENT '校区id',
   `itcast_school_name` STRING COMMENT '校区名称',
   `origin_type` STRING COMMENT '来源渠道',
   `itcast_subject_id` STRING COMMENT '学科id',
   `itcast_subject_name` STRING COMMENT '学科名称',
   `hourinfo` STRING COMMENT '小时信息',
   `origin_type_stat` STRING COMMENT '数据来源:0.线下；1.线上',
   `clue_state_stat` STRING COMMENT '客户属性：0.老客户；1.新客户',
   `tdepart_id` STRING COMMENT '创建者部门id',
   `tdepart_name` STRING COMMENT '咨询中心名称',
   `time_str` STRING COMMENT '时间明细',
   `groupType` STRING COMMENT '产品属性类别：1.总意向量；2.区域信息；3.校区、学科组合分组；4.来源渠道；5.咨询中心;',
   `time_type` STRING COMMENT '时间维度：1、按小时聚合；2、按天聚合；3、按周聚合；4、按月聚合；5、按年聚合；'
)
comment '客户意向dws表'
PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY');

数据采集：

目的: 用sqoop将业务端（MySQL）的数据导入到ODS层对应表（hive）中。

注意：字段名字要一一对应，不对的要改名再导入。

DIM层（维度表）：

# 客户表
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/scrm \
--username root \
--password 123456 \
--query 'SELECT 
  *, "2021-09-27" AS start_time
FROM customer where 1=1 and $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table customer \
-m 1 

# 学科表:
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/scrm \
--username root \
--password 123456 \
--query 'SELECT 
  *, "2021-09-27" AS start_time
FROM itcast_subject where 1=1 and $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table itcast_subject \
-m 1 

# 校区表:
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/scrm \
--username root \
--password 123456 \
--query 'SELECT 
  *, "2021-09-27" AS start_time
FROM itcast_school where 1=1 and $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table itcast_school \
-m 1 

# 员工表
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/scrm \
--username root \
--password 123456 \
--query 'SELECT 
  *, "2021-09-27" AS start_time
FROM employee where 1=1 and $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table employee \
-m 1

# 部门表
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/scrm \
--username root \
--password 123456 \
--query 'SELECT 
  *, "2021-09-27" AS start_time
FROM scrm_department where 1=1 and $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table scrm_department \
-m 1

ODS层:  （分桶表需要构建临时表）

-- 客户意向表

-- 第一步: 创建一张客户意向表的临时表

CREATE TABLE IF NOT EXISTS itcast_ods.`customer_relationship_temp` (
  `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` int COMMENT '创建人',
  `current_creator` int COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
  `creator_name` STRING COMMENT '创建者姓名',
  `origin_channel` STRING COMMENT '来源渠道',
  `comment` STRING COMMENT '备注',
  `first_customer_clue_id` int COMMENT '第一条线索id',
  `last_customer_clue_id` int COMMENT '最后一条线索id',
  `process_state` STRING COMMENT '处理状态',
  `process_time` STRING COMMENT '处理状态变动时间',
  `payment_state` STRING COMMENT '支付状态',
  `payment_time` STRING COMMENT '支付状态变动时间',
  `signup_state` STRING COMMENT '报名状态',
  `signup_time` STRING COMMENT '报名时间',
  `notice_state` STRING COMMENT '通知状态',
  `notice_time` STRING COMMENT '通知状态变动时间',
  `lock_state` STRING COMMENT '锁定状态',
  `lock_time` STRING COMMENT '锁定状态修改时间',
  `itcast_clazz_id` int COMMENT '所属ems班级id',
  `itcast_clazz_time` STRING COMMENT '报班时间',
  `payment_url` STRING COMMENT '付款链接',
  `payment_url_time` STRING COMMENT '支付链接生成时间',
  `ems_student_id` int COMMENT 'ems的学生id',
  `delete_reason` STRING COMMENT '删除原因',
  `deleter` int COMMENT '删除人',
  `deleter_name` STRING COMMENT '删除人姓名',
  `delete_time` STRING COMMENT '删除时间',
  `course_id` int COMMENT '课程ID',
  `course_name` STRING COMMENT '课程名称',
  `delete_comment` STRING COMMENT '删除原因说明',
  `close_state` STRING COMMENT '关闭装填',
  `close_time` STRING COMMENT '关闭状态变动时间',
  `appeal_id` int COMMENT '申诉id',
  `tenant` int COMMENT '租户',
  `total_fee` DECIMAL COMMENT '报名费总金额',
  `belonged` int COMMENT '小周期归属人',
  `belonged_time` STRING COMMENT '归属时间',
  `belonger_time` STRING COMMENT '归属时间',
  `transfer` int COMMENT '转移人',
  `transfer_time` STRING COMMENT '转移时间',
  `follow_type` int COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
  `transfer_bxg_oa_account` STRING COMMENT '转移到博学谷归属人OA账号',
  `transfer_bxg_belonger_name` STRING COMMENT '转移到博学谷归属人OA姓名',
  `end_time` STRING COMMENT '有效截止时间')
comment '客户关系表'
PARTITIONED BY(start_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

-- 第二步: 编写sqoop命令 将数据导入到临时表 

sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/scrm \
--username root \
--password 123456 \
--query 'SELECT 
  *, "9999-12-31" as end_time , "2021-09-27" AS start_time
FROM customer_relationship where 1=1 and $CONDITIONS' \
--hcatalog-database itcast_ods \
--hcatalog-table customer_relationship_temp \
-m 1

-- 第三步: 执行 insert into + select 导入到目标表

-- 动态分区配置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
-- hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
-- 写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;

insert into table itcast_ods.customer_relationship partition(start_time)
select * from itcast_ods.customer_relationship_temp;
-- 客户线索表  

-- 第一步: 创建一张客户线索表的临时表

CREATE TABLE IF NOT EXISTS itcast_ods.customer_clue_temp (
  id int COMMENT 'customer_clue_id',
  create_date_time STRING COMMENT '创建时间',
  update_date_time STRING COMMENT '最后更新时间',
  deleted STRING COMMENT '是否被删除（禁用）',
  customer_id int COMMENT '客户id',
  customer_relationship_id int COMMENT '客户关系id',
  session_id STRING COMMENT '七陌会话id',
  sid STRING COMMENT '访客id',
  status STRING COMMENT '状态（undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转）',
  users STRING COMMENT '所属坐席',
  create_time STRING COMMENT '七陌创建时间',
  platform STRING COMMENT '平台来源 （pc-网站咨询|wap-wap咨询|sdk-app咨询|weixin-微信咨询）',
  s_name STRING COMMENT '用户名称',
  seo_source STRING COMMENT '搜索来源',
  seo_keywords STRING COMMENT '关键字',
  ip STRING COMMENT 'IP地址',
  referrer STRING COMMENT '上级来源页面',
  from_url STRING COMMENT '会话来源页面',
  landing_page_url STRING COMMENT '访客着陆页面',
  url_title STRING COMMENT '咨询页面title',
  to_peer STRING COMMENT '所属技能组',
  manual_time STRING COMMENT '人工开始时间',
  begin_time STRING COMMENT '坐席领取时间 ',
  reply_msg_count int COMMENT '客服回复消息数',
  total_msg_count int COMMENT '消息总数',
  msg_count int COMMENT '客户发送消息数',
  comment STRING COMMENT '备注',
  finish_reason STRING COMMENT '结束类型',
  finish_user STRING COMMENT '结束坐席',
  end_time STRING COMMENT '会话结束时间',
  platform_description STRING COMMENT '客户平台信息',
  browser_name STRING COMMENT '浏览器名称',
  os_info STRING COMMENT '系统名称',
  area STRING COMMENT '区域',
  country STRING COMMENT '所在国家',
  province STRING COMMENT '省',
  city STRING COMMENT '城市',
  creator int COMMENT '创建人',
  name STRING COMMENT '客户姓名',
  idcard STRING COMMENT '身份证号',
  phone STRING COMMENT '手机号',
  itcast_school_id int COMMENT '校区Id',
  itcast_school STRING COMMENT '校区',
  itcast_subject_id int COMMENT '学科Id',
  itcast_subject STRING COMMENT '学科',
  wechat STRING COMMENT '微信',
  qq STRING COMMENT 'qq号',
  email STRING COMMENT '邮箱',
  gender STRING COMMENT '性别',
  level STRING COMMENT '客户级别',
  origin_type STRING COMMENT '数据来源渠道',
  information_way STRING COMMENT '资讯方式',
  working_years STRING COMMENT '开始工作时间',
  technical_directions STRING COMMENT '技术方向',
  customer_state STRING COMMENT '当前客户状态',
  valid STRING COMMENT '该线索是否是网资有效线索',
  anticipat_signup_date STRING COMMENT '预计报名时间',
  clue_state STRING COMMENT '线索状态',
  scrm_department_id int COMMENT 'SCRM内部部门id',
  superior_url STRING COMMENT '诸葛获取上级页面URL',
  superior_source STRING COMMENT '诸葛获取上级页面URL标题',
  landing_url STRING COMMENT '诸葛获取着陆页面URL',
  landing_source STRING COMMENT '诸葛获取着陆页面URL来源',
  info_url STRING COMMENT '诸葛获取留咨页URL',
  info_source STRING COMMENT '诸葛获取留咨页URL标题',
  origin_channel STRING COMMENT '投放渠道',
  course_id int COMMENT '课程编号',
  course_name STRING COMMENT '课程名称',
  zhuge_session_id STRING COMMENT 'zhuge会话id',
  is_repeat int COMMENT '是否重复线索(手机号维度) 0:正常 1：重复',
  tenant int COMMENT '租户id',
  activity_id STRING COMMENT '活动id',
  activity_name STRING COMMENT '活动名称',
  follow_type int COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
  shunt_mode_id int COMMENT '匹配到的技能组id',
  shunt_employee_group_id int COMMENT '所属分流员工组',
  ends_time STRING COMMENT '有效时间')
comment '客户关系表'
PARTITIONED BY(starts_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='ZLIB');

-- 第二步: 编写sqoop命令 将数据导入到临时表

sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/scrm \
--username root \
--password 123456 \
--query 'SELECT 
  id,create_date_time,update_date_time,deleted,customer_id,customer_relationship_id,session_id,sid,status,user as users,create_time,platform,s_name,seo_source,seo_keywords,ip,referrer,from_url,landing_page_url,url_title,to_peer,manual_time,begin_time,reply_msg_count,total_msg_count,msg_count,comment,finish_reason,finish_user,end_time,platform_description,browser_name,os_info,area,country,province,city,creator,name,"-1" as idcard,"-1" as phone,itcast_school_id,itcast_school,itcast_subject_id,itcast_subject,"-1" as wechat,"-1" as qq,"-1" as email,gender,level,origin_type,information_way,working_years,technical_directions,customer_state,valid,anticipat_signup_date,clue_state,scrm_department_id,superior_url,superior_source,landing_url,landing_source,info_url,info_source,origin_channel,course_id,course_name,zhuge_session_id,is_repeat,tenant,activity_id,activity_name,follow_type,shunt_mode_id,shunt_employee_group_id, "9999-12-31" as ends_time , "2021-09-27" AS starts_time
FROM customer_clue where 1=1 and $CONDITIONS' \
--hcatalog-database itcast_ods \
--hcatalog-table customer_clue_temp \
-m 1

-- 第三步: 执行 insert into + select 导入到目标表

insert into table itcast_ods.customer_clue partition(starts_time)
select * from itcast_ods.customer_clue_temp;

数据清洗转换：

生成DWD层数据：

作用：清洗和转化以及少量的维度退化

维度退化操作: 此层不需要做

清洗操作: 
    将标记删除的数据过滤掉   
转换操作: 
    1) 将create_date_time 转换为 yearinfo  monthinfo dayinfo hourinfo
    2) 将origin_type 转换为 origin_type_state (用于统计线上线下)
           转换逻辑: origin_type的值为: NETSERVICE 或者 PRESIGNUP 认为线上 其余认为线下
    3) 将clue_state 转换为 clue_state_stat (用于统计新老维度)  -- 当前层无法转换的 (只能在DWM)，因为要关联其他表
        转换逻辑:clue_state的值为 VALID_NEW_CLUES 为新客户  其余暂定为老客户
    4) 将校区和学科的 id字段, 如果为 0 或者 null 转换为 -1

希望在灌入到DWD层的时候, 对数据进行采样操作: 比如只想要第5个桶，前提是一共有10个桶

所以最后结果是只有一个桶里面有数据，其余都是空，因为只采样了一个桶，为了节省时间，101个分区，每个分区10个桶太多了，所以只采样一个桶。 

--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true; -- 开启分桶支持, 默认就是true
set hive.enforce.sorting=true; -- 开启强制排序

insert  into table itcast_dwd.itcast_intention_dwd partition(yearinfo,monthinfo,dayinfo)
select  
    id as rid,
    customer_id,
    create_date_time,
    if(itcast_school_id is null OR itcast_school_id = 0 , '-1',itcast_school_id) as itcast_school_id, 
    deleted,
    origin_type,
    if(itcast_subject_id is not null, if(itcast_subject_id != 0,itcast_subject_id,'-1'),'-1') as itcast_subject_id, 
    creator,
    substr(create_date_time,12,2) as hourinfo, 
    if(origin_type in('NETSERVICE','PRESIGNUP'),'1','0') as origin_type_stat,
    substr(create_date_time,1,4) as yearinfo,
    substr(create_date_time,6,2) as monthinfo,
    substr(create_date_time,9,2) as dayinfo 

from itcast_ods.customer_relationship tablesample(bucket 5 out of 10 on id) where deleted = 0 ;

生成DWM层数据：

由于DWM层的字段是来源于事实表和所有维度表中的字段, 此时如果生成DWM层数据, 必须要先将所有的表关联在一起，七表关联数据庞大，要开启各种优化。

所有表的表与表之间的关联条件 ：
    客户意向表.creator = 员工表.id
    员工表.tdepart_id = 部门表.id
    线索表.customer_relationship_id = 客户意向表.id
    客户意向表.itcast_school_id = 校区表.id
    客户意向表.itcast_subject_id = 学科表.id
    客户意向表.customer_id = 客户表.id

 相关优化：

开启优化: 
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true; -- 开启分桶支持, 默认就是true
set hive.enforce.sorting=true; -- 开启强制排序

-- 优化: 
set hive.auto.convert.join=true;  -- map join
set hive.optimize.bucketmapjoin = true; -- 开启 bucket map join
-- 开启SMB map join
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
-- 写入数据强制排序
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin.sortedmerge = true; -- 开启自动尝试SMB连接

注意：相关优化全部开启

说明:
    通过执行发现, 开启优化, 执行速度, 异常的缓慢, 一分钟才可以执行  1% 

原因:    
    当前这种优化方案, 需要有非常的内存资源才可以运行, 如果没有, yarn会安排这些依次执行,导致执行效率更差
    如果在生产环境中, 是完全可以开启的

目前解决方案: 关闭掉所有的优化来执行
set hive.auto.convert.join=false; 
set hive.optimize.bucketmapjoin = false; 

set hive.auto.convert.sortmerge.join=false;
set hive.auto.convert.sortmerge.join.noconditionaltask=false;

set hive.enforce.sorting=false;

set hive.optimize.bucketmapjoin.sortedmerge = false; 

--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true; -- 开启分桶支持, 默认就是true
set hive.enforce.sorting=true; -- 开启强制排序

-- 优化: 
set hive.auto.convert.join=false;  -- map join
set hive.optimize.bucketmapjoin = false; -- 开启 bucket map join
-- 开启SMB map join
set hive.auto.convert.sortmerge.join=false;
set hive.auto.convert.sortmerge.join.noconditionaltask=false;
-- 写入数据强制排序
set hive.enforce.sorting=false;
-- 开启自动尝试SMB连接
set hive.optimize.bucketmapjoin.sortedmerge = false; 

insert into table itcast_dwm.itcast_intention_dwm partition(yearinfo,monthinfo,dayinfo)
select  
    iid.customer_id,
    iid.create_date_time,
    c.area,
    iid.itcast_school_id,
    sch.name as itcast_school_name,
    iid.deleted,
    iid.origin_type,
    iid.itcast_subject_id,
    sub.name as itcast_subject_name,
    iid.hourinfo,
    iid.origin_type_stat,
    -- if(cc.clue_state = 'VALID_NEW_CLUES',1,if(cc.clue_state = 'VALID_PUBLIC_NEW_CLUE','0','-1')) as clue_state_stat, -- 此处有转换
    case cc.clue_state 
        when 'VALID_NEW_CLUES' then '1'
        when 'VALID_PUBLIC_NEW_CLUE' then '0'
        else '-1' 
    end as clue_state_stat,
    emp.tdepart_id,
    dept.name as tdepart_name,
    iid.yearinfo,
    iid.monthinfo,
    iid.dayinfo
from itcast_dwd.itcast_intention_dwd  iid
    left join itcast_ods.customer_clue cc on cc.customer_relationship_id = iid.rid
    left join itcast_dimen.customer c on  iid.customer_id = c.id
    left join itcast_dimen.itcast_subject sub on  iid.itcast_subject_id = sub.id
    left join itcast_dimen.itcast_school sch  on iid.itcast_school_id = sch.id
    left join itcast_dimen.employee emp on iid.creator = emp.id
    left join itcast_dimen.scrm_department dept on emp.tdepart_id = dept.id;

数据分析：

（所有需求都放入DWM表中）

目的: 生产DWS层的数据, 数据来源于DWM

指标:

                意向量

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

6*6=36个需求

统计总意向量：

-- 统计每年 线上线下 新老用户的总意向量（3个需求汇到一张表中）

-- 统计每年每月 线上线下 新老用户的总意向量3个需求汇到一张表中）

-- 统计每年每月每日 线上线下 新老用户的总意向量3个需求汇到一张表中）

-- 统计每年每月每日每小时 线上线下 新老用户的总意向量3个需求汇到一张表中）

-- 统计每年 线上线下 新老用户的总意向量
insert into table itcast_dws.itcast_intention_dws partition(yearinfo,monthinfo,dayinfo)
select 
	count( distinct customer_id) as customer_total, 
	'-1' as area,
	'-1' as itcast_school_id,
	'-1' as itcast_school_name,
	'-1' as origin_type,
	'-1' as itcast_subject_id,
	'-1' as itcast_subject_name,
	'-1' as hourinfo,
	origin_type_stat,
	clue_state_stat,
	'-1' as tdepart_id,
	'-1' as tdepart_name,
	yearinfo as time_str,
	'1' as grouptype,
	'5' as time_type,
	yearinfo,
	'-1' as monthinfo,
	'-1' as dayinfo
from itcast_dwm.itcast_intention_dwm
group by yearinfo,origin_type_stat,clue_state_stat;

-- 统计每年每月 线上线下 新老用户的总意向量
insert into table itcast_dws.itcast_intention_dws partition(yearinfo,monthinfo,dayinfo)
select 
	count( distinct customer_id) as customer_total, 
	'-1' as area,
	'-1' as itcast_school_id,
	'-1' as itcast_school_name,
	'-1' as origin_type,
	'-1' as itcast_subject_id,
	'-1' as itcast_subject_name,
	'-1' as hourinfo,
	origin_type_stat,
	clue_state_stat,
	'-1' as tdepart_id,
	'-1' as tdepart_name,
	concat(yearinfo,'-',monthinfo) as time_str,
	'1' as grouptype,
	'4' as time_type,
	yearinfo,
	monthinfo,
	'-1' as dayinfo
from itcast_dwm.itcast_intention_dwm
group by yearinfo,monthinfo,origin_type_stat,clue_state_stat;
-- 统计每年每月每日 线上线下 新老用户的总意向量
insert into table itcast_dws.itcast_intention_dws partition(yearinfo,monthinfo,dayinfo)
select 
	count( distinct customer_id) as customer_total, 
	'-1' as area,
	'-1' as itcast_school_id,
	'-1' as itcast_school_name,
	'-1' as origin_type,
	'-1' as itcast_subject_id,
	'-1' as itcast_subject_name,
	'-1' as hourinfo,
	origin_type_stat,
	clue_state_stat,
	'-1' as tdepart_id,
	'-1' as tdepart_name,
	concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
	'1' as grouptype,
	'2' as time_type,
	yearinfo,
	monthinfo,
	dayinfo
from itcast_dwm.itcast_intention_dwm
group by yearinfo,monthinfo,dayinfo,origin_type_stat,clue_state_stat;
-- 统计每年每月每日每小时 线上线下 新老用户的总意向量
insert into table itcast_dws.itcast_intention_dws partition(yearinfo,monthinfo,dayinfo)
select 
	count( distinct customer_id) as customer_total, 
	'-1' as area,
	'-1' as itcast_school_id,
	'-1' as itcast_school_name,
	'-1' as origin_type,
	'-1' as itcast_subject_id,
	'-1' as itcast_subject_name,
	hourinfo,
	origin_type_stat,
	clue_state_stat,
	'-1' as tdepart_id,
	'-1' as tdepart_name,
	concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
	'1' as grouptype,
	'1' as time_type,
	yearinfo,
	monthinfo,
	dayinfo
from itcast_dwm.itcast_intention_dwm
group by yearinfo,monthinfo,dayinfo,hourinfo,origin_type_stat,clue_state_stat;

统计咨询中心维度：

-- 统计每年线上线下, 新老用户产生各个咨询中心的意向量（2个需求汇到一张表中）

-- 统计每年线上线下, 新老用户产生各个咨询中心的意向量
insert into table itcast_dws.itcast_intention_dws partition(yearinfo,monthinfo,dayinfo)
select 
	count( distinct customer_id) as customer_total, 
	'-1' as area,
	'-1' as itcast_school_id,
	'-1' as itcast_school_name,
	'-1' as origin_type,
	'-1' as itcast_subject_id,
	'-1' as itcast_subject_name,
	'-1' as hourinfo,
	origin_type_stat,
	clue_state_stat,
	tdepart_id,
	tdepart_name,
	yearinfo as time_str,
	'5' as grouptype,
	'5' as time_type,
	yearinfo,
	'-1' as monthinfo,
	'-1' as dayinfo
from itcast_dwm.itcast_intention_dwm
group by yearinfo,origin_type_stat,clue_state_stat,tdepart_id,tdepart_name;

数据导出：

目的: 从DWS层将数据导出到MYSQL中

第一步: 在mysql中创建目标表

CREATE TABLE IF NOT EXISTS scrm_bi.itcast_intention (
   `customer_total` INT COMMENT '聚合意向客户数',
   `area` varchar(100) COMMENT '区域信息',
   `itcast_school_id` varchar(100) COMMENT '校区id',
   `itcast_school_name` varchar(100) COMMENT '校区名称',
   `origin_type` varchar(100) COMMENT '来源渠道',
   `itcast_subject_id` varchar(100) COMMENT '学科id',
   `itcast_subject_name` varchar(100) COMMENT '学科名称',
   `hourinfo` varchar(100) COMMENT '小时信息',
   `origin_type_stat` varchar(100) COMMENT '数据来源:0.线下；1.线上',
   `clue_state_stat` varchar(100) COMMENT '客户属性：0.老客户；1.新客户',
   `tdepart_id` varchar(100) COMMENT '创建者部门id',
   `tdepart_name` varchar(100) COMMENT '咨询中心名称',
   `time_str` varchar(100) COMMENT '时间明细',
   `groupType` varchar(100) COMMENT '产品属性类别：1.总意向量；2.区域信息；3.校区、学科组合分组；4.来源渠道；5.咨询中心;',
   `time_type` varchar(100) COMMENT '时间维度：1、按小时聚合；2、按天聚合；3、按周聚合；4、按月聚合；5、按年聚合；',
    yearinfo varchar(100) COMMENT '年' ,
    monthinfo varchar(100) COMMENT '月',
    dayinfo varchar(100) COMMENT '日'
)
comment '客户意向dws表';

第二步:执行sqoop, 将数据全部到导出 MySQL中

sqoop export \
--connect "jdbc:mysql://192.168.52.150:3306/scrm_bi?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password 123456 \
--table itcast_intention \
--hcatalog-database itcast_dws \
--hcatalog-table itcast_intention_dws \
-m 1

访问和咨询用户数据模块（增量分析）：略（类比第一模块）

有效线索模块：

开发中，流程类似第一第二模块……

报名用户模块:

 开发中，流程类似第一第二模块……

学生出勤模块：

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

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/613032.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

用户体验优化uxo指的是什么？

用户体验优化uxo指的是什么？

用户体验优化(User Experience Optimization，简称UXO)是一种专注于改善和提升用户在使用企业产品或服务时的整体感受和体验的过程。简单来说，它旨在通过改进产品或服务的设计和功能，使用户在使用过程中感到更加愉悦、满意和高效。用户体验优化…
阅读更多...
桌面怎么分类便签 桌面分类便签设置方法

桌面怎么分类便签 桌面分类便签设置方法

桌面便签，一直是我工作和学习的好帮手。每当灵感闪现或是有待办事项，我都会随手记录在便签上，它们就像我桌面上的小助手，时刻提醒我不要遗漏任何重要事务。 但便签一多，管理就成了问题。一张张五颜六色的便签贴满了我…
阅读更多...
C++ 多态的相关问题

C++ 多态的相关问题

目录 1. 第一题 2. 第二题 3. inline 函数可以是虚函数吗 4. 静态成员函数可以是虚函数吗 5. 构造函数可以是虚函数吗 6. 析构函数可以是虚函数吗 7. 拷贝构造和赋值运算符重载可以是虚函数吗 8. 对象访问普通函数快还是访问虚函数快 9. 虚函数表是什么阶段生成的&…
阅读更多...
华为与达梦数据签署全面合作协议

华为与达梦数据签署全面合作协议

4月26日，武汉达梦数据库股份有限公司（简称“达梦数据”）与华为技术有限公司（简称“华为”）在达梦数据武汉总部签署全面合作协议。 达梦数据总经理皮宇、华为湖北政企业务总经理吕晓龙出席并见证签约；华为湖…
阅读更多...
uniapp、web网页跨站数据交互及通讯

uniapp、web网页跨站数据交互及通讯

来来来，说说你的创作灵感！这就跟吃饭睡觉一样，饿了就找吃的，渴了就倒水张口灌。 最近一个多月实在是忙的没再更新日志，好多粉丝私信说之前的创作于他们而言非常有用！受益菲浅，这里非常感谢粉丝…
阅读更多...
Ubuntu20.04 设置路由器

Ubuntu20.04 设置路由器

1. 网络拓扑图 2. 查看网卡信息 ip a得出如下网卡信息&#xff0c;enp1s0和enp2s0为两个网卡名称&#xff0c;以及相关两个网卡的详细信息&#xff0c;不同设备的网卡名称可能不一样 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group defaul…
阅读更多...
正点原子[第二期]Linux之ARM（MX6U）裸机篇学习笔记-15.4讲 GPIO中断实验-IRQ中断服务函数详解

正点原子[第二期]Linux之ARM（MX6U）裸机篇学习笔记-15.4讲 GPIO中断实验-IRQ中断服务函数详解

前言&#xff1a; 本文是根据哔哩哔哩网站上“正点原子[第二期]Linux之ARM&#xff08;MX6U&#xff09;裸机篇”视频的学习笔记&#xff0c;在这里会记录下正点原子 I.MX6ULL 开发板的配套视频教程所作的实验和学习笔记内容。本文大量引用了正点原子教学视频和链接中的内容。…
阅读更多...
debug技巧之本地调试

debug技巧之本地调试

大家好啊&#xff0c;我是summo&#xff0c;今天给大家分享一下我平时是怎么调试代码的&#xff0c;不是权威也不是教学&#xff0c;就是简单分享一下&#xff0c;如果大家还有更好的调试方式也可以多多交流哦。 如果看过我文章的同学应该知道我是一个Java开发&#xff0c;平时…
阅读更多...
FANUC机器人工具坐标偏移的用法

FANUC机器人工具坐标偏移的用法

一、工具坐标偏移的使用场景 在机器人位置不改变的情况下&#xff0c;工业机器人使用默认工具坐标系示教的一系列运动点位&#xff0c;要保持原本点位位置不变的情况下&#xff0c;改变机器人工具坐标的参数&#xff0c;就要用到机器人坐标转化的功能。在FANUC机器人上体现为机…
阅读更多...
/swagger/index.html#/ 的页面可能存在问题，或者已永久移动到新的网址。

/swagger/index.html#/ 的页面可能存在问题，或者已永久移动到新的网址。

问题背景 在golang的项目中&#xff0c;使用了swagger。在另外一个项目中也使用了swagger,没有发生过这个问题。新的项目中&#xff0c;用了和之前项目同样的web框架&#xff0c;仔细比对了和之前项目的差异&#xff0c;只不过&#xff0c;目录结构做了调整&#xff0c;所以&a…
阅读更多...
网络编程套接字 (二)---udosocket

网络编程套接字 (二)---udosocket

本专栏内容为&#xff1a;Linux学习专栏&#xff0c;分为系统和网络两部分。 通过本专栏的深入学习&#xff0c;你可以了解并掌握Linux。 &#x1f493;博主csdn个人主页&#xff1a;小小unicorn ⏩专栏分类&#xff1a;网络 &#x1f69a;代码仓库&#xff1a;小小unicorn的代…
阅读更多...
Springboot集成Eureka实现注册中心-11

Springboot集成Eureka实现注册中心-11

Spring Cloud Netflix Eureka是Spring Cloud Netflix子项目的核心组件之一&#xff0c;主要用于微服务架构中的服务治理。 什么是注册中心 在微服务架构中往往会有一个注册中心&#xff0c;每个微服务都会向注册中心去注册自己的地址及端口信息&#xff0c;注册中心维护着服务…
阅读更多...
泰迪智能科技大数据开发实训平台功能介绍

泰迪智能科技大数据开发实训平台功能介绍

大数据开发实训平台是面向实训课和课后训练的编程实训平台&#xff0c;平台底层基于Docker技术&#xff0c;采用容器云部署方案&#xff0c;预装大数据相关课程教学所需的实训环境&#xff0c;拥有1主2从的Hadoop集群&#xff0c;还能够自主定制环境&#xff0c;并能够与实训管…
阅读更多...
10.轮转数组

10.轮转数组

文章目录 题目简介题目解答解法一&#xff1a;使用额外的数组代码&#xff1a;复杂度分析&#xff1a; 解法二&#xff1a;数组反转代码&#xff1a;复杂度分析&#xff1a; 题目链接 大家好&#xff0c;我是晓星航。今天为大家带来的是 轮转数组 相关的讲解&#xff01;&#…
阅读更多...
基于VOLOPV2的自动驾驶环境感知系统

基于VOLOPV2的自动驾驶环境感知系统

基于VOLOPV2的自动驾驶环境感知系统是一个复杂的系统&#xff0c;它主要负责实时检测并识别周围环境中的各种物体和信息&#xff0c;为自动驾驶车辆提供必要的感知数据。以下是对该系统的一个简要介绍&#xff1a; 环境感知是自动驾驶系统中的一个关键部分&#xff0c;它依赖于…
阅读更多...
SQL的命令

SQL的命令

目录 创建用户 ​编辑 DDL数据库操作 查询 创建 使用 删除 创建数据库表 在表中修改字段 查询表 DML 添加数据 修改 删除 DQL 查询 创建用户 DDL数据库操作 查询 show databases; 创建 权限问题导致无法创建&#xff0c;连接root修改用户权限 CREATE DATABAS…
阅读更多...
AJAX概述和基本使用

AJAX概述和基本使用

01 【AJAX概述和基本使用】 1.AJAX简介 AJAX 全称为Asynchronous JavaScript And XML&#xff0c;就是异步的JS 和XML 通过AJAX 可以在浏览器中向服务器发送异步请求&#xff0c;最大的优势&#xff1a;无刷新获取数据 AJAX 不是新的编程语言&#xff0c;而是一种将现有的标准…
阅读更多...
Java入门基础学习笔记8——注释

Java入门基础学习笔记8——注释

1、注释&#xff1a; 注释是写在程序中对代码进行解释说明的文件&#xff0c;方便自己和其他人查看&#xff0c;以便理解程序的。 package cn.ensource.note;/**文档注释文档注释 */ public class NoteDemo {public static void main(String[] args) {// 单行注释System.out.…
阅读更多...
数字人金融应用技术指南

数字人金融应用技术指南

根据《北京金融科技产业联盟团体标准管理办法》的规定&#xff0c;2024年3月27日经北京金融科技产业联盟第三届理事会第二次会议审议&#xff0c;批准发布《数字人金融应用技术指南》&#xff08;T/BFIA 027—2024&#xff09;、《图数据库金融应用技术要求》&#xff08;T/BFI…
阅读更多...
如何在两个日期之间获取日志属性

如何在两个日期之间获取日志属性

如果你们想在两个日期之间获取日志属性&#xff0c;需要首先明确你所指的“日志属性”。如果你是指在两个日期之间获取日志&#xff08;例如文本日志文件&#xff09;中的记录&#xff0c;你可以使用 Python 的文件操作来读取日志文件&#xff0c;并根据每行记录中的日期属性进…
阅读更多...
最新文章

Copyright @ 2022~2023