项目实战-知行教育大数据分析平台-05

目录

        5、建模操作

        6、数据抽取

        7、数据清洗和转换

        8、数据维度退化

        9、数据统计分析

        10、数据导出

        11、拉链表的实现（重点）-项目增量流程

        12、hive的索引

        13、hive的相关优化

        （1）Hive并行编译

        （2）Hive并行执行

        （3）Hive的小文件合并

        （4）矢量化查询

        （5）读取零拷贝

        14、hive的数据倾斜优化（面试重点）

        （1）group by数据倾斜

        （2）join数据倾斜（面试重点）

        （3）union all优化

        （4）如何感知是否有数据倾斜问题发生

        （5）关联优化器

---

        5、建模操作

        ODS层：

-- 写入时压缩生效
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');
-- 该表为内部表,因为该表的数据可能会发生变更，后续可能会对其进行覆盖等操作,可以看到有end_time字段,即该表是一个拉链表，设置拉链表的目的也是为了利于变更数据的维护。
-- 客户意向表可以不设为分桶表,因为ODS层的客户意向表不涉及join操作

-- 客户线索表
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');

-- 客户线索表必须是分桶表,因为DWD层到DWM层过程中需要涉及到DWD层的大表与ODS层的客户线索表(大表)进行连接即：customer_relationship.id = customer_clue.customer_relationship_id

        DIM层：

CREATE DATABASE IF NOT EXISTS itcast_dimen;

-- 客户表,可能大型公司所面对的客户量多，该表变为中型表，这个时候可以考虑按id字段分桶
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');

-- 维度表的数据相对于ODS层的事实表来说，一般变动不大，如果发生了变动直接覆盖即可，不会影响后续分析，因此不需要end_time字段,内部表|外部表都可以,向客户部，员工表还可能存在点变化可以使用内部表，学科表、校区表、部门表大概率不会发生变化可以设为外部表
-- 为了统一，都设为内部表
-- 维度表数据一般很小属于小表范畴,没必要使用高压缩比的zlib，因此这里使用snappy压缩方式
-- 既然维度表属于小表,后续与客户意向表join时使用map join优化即可，不需要分桶

        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');

-- 从是否需要join的角度来看,DWM层的数据在后续就不需要join了，因此不需要分桶（分桶对多表提升）
-- 从数据分析的角度看,分桶表不仅仅可以提升join的效率，在查询过程中如果统计字段时分桶字段，如果where条件字段是分桶字段都可以提升效率，因此需要分桶（分桶对单表提升）
-- 考虑到后续统计过程中会按照customer_id统计,因此按该字段分桶会提升效率,当然也可以不同设置分桶

        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.来源渠道；6.咨询中心;',
   `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');

# 本看板没有季度维度,但是为了与第一看板保持一致,这里也设有季度,不用即可.

        6、数据抽取

        利用sqoop将mysql中各个表的数据抽取到ODS层和DIM层各个对应表中。

        DIM层数据抽取(可以直接利用sqoop抽取数据)

# 客户表
sqoop import \
--connect jdbc:mysql://hadoop01: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://hadoop01: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://hadoop01: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://hadoop01: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://hadoop01: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

# 注意在实际生产环境中不建议写select *

        ODS层数据抽取(不可以直接利用sqoop抽取数据)

        第一步：创建临时表

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');

-- 客户线索表
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将mysql中数据导入至临时表

sqoop import \
--connect jdbc:mysql://hadoop01: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

sqoop import \
--connect jdbc:mysql://hadoop01: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

# 9999-12-31表示还不知道end_time永久有效
# 在原始数据中有的数据全为null，把这些数据改为-1标识

        第三步：利用insert 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;

-- 开启分桶支持，默认已经开启
set hive.enforce.bucketing=true;

-- 开启强制排序
set hive.enforce.sorting=true;

insert into table itcast_ods.customer_relationship partition(start_time)
select * from itcast_ods.customer_relationship_temp;

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

-- insert into 后面不需要指定分桶字段,系统会自动识别。

        7、数据清洗和转换

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 null or itcast_subject_id = 0,'-1',itcast_subject_id) 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,     -- 线上1线下0
    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 where deleted = 0;

-- deleted在本需求中0：false；1：true
-- if(itcast_school_id is not null,if(itcast_school_id !=0,itcast_school_id,'-1'),'-1')
-- 执行过程可能会很慢，因为本身数据量多，分区多，同时itcast_intention_dwd还是分桶表，需要在每个分区在分10个桶。

        额外补充：如果向DWD层加载数据时，对ODS层数据进行采样操作，只加载第5个桶：

explain
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 null or itcast_subject_id = 0,'-1',itcast_subject_id) 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,     -- 线上1线下0
    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;

        可能会出现的错误：

        查询YARN的log日志：发现是动态分区太多，超过最大允许的分区数了，最大允许100个分区，实际目前有101个分区。

        解决方法：重新设置最大允许的动态分区数

        set hive.exec.max.dynamic.partitions.pernode=10000;     # 每次操作允许的最大分区数
        set hive.exec.max.dynamic.partitions=100000;                 # 总共允许的最大分区数
        set hive.exec.max.created.files=150000;                           # 允许最大创建文件数

        注意：如果我们对来源表做了数据采样工作，则该数据会自动放到目标表的相同桶当中（例如采样了ODS层第5号桶，然后加载至DWD层，则加载的数据只会在DWD层的第5号桶中；如果加载了第4，7号桶，加载的数据只会保存至目标表的对应桶中），前提是两桶的bucket数相同。

        可以看出只有第5个桶有数据，说明数据采样成功了。

        8、数据维度退化

        编写sql：

select
    dwd.customer_id,
    dwd.create_date_time,
    cus.area,
    dwd.itcast_school_id,
    sch.name as itcast_school_name,
    dwd.deleted,
    dwd.origin_type,
    dwd.itcast_subject_id,
    sub.name as itcast_subject_name,
    dwd.hourinfo,
    dwd.origin_type_stat,
    if(clue.clue_state = 'VALID_NEW_CLUES','1',if(clue.clue_state = 'VALID_PUBLIC_NEW_CLUE','0','-1')) as clue_state_stat,    # 新用户1，老用户0，无线用户-1
    e.tdepart_id,
    dept.name as tdepart_name,
    dwd.yearinfo,
    dwd.monthinfo,
    dwd.dayinfo
from itcast_dwd.itcast_intention_dwd dwd
    left join itcast_ods.customer_clue clue on dwd.rid = clue.customer_relationship_id
    left join itcast_dimen.customer cus on dwd.customer_id = cus.id
    left join itcast_dimen.employee e on dwd.creator = e.id
    left join itcast_dimen.scrm_department dept on e.tdepart_id = dept.id
    left join itcast_dimen.itcast_subject sub on dwd.itcast_subject_id = sub.id
    left join itcast_dimen.itcast_school sch on dwd.itcast_school_id = sch.id;

-- 一定要用left join以dwd层的事实表为主.
-- 通过观察customer_clue表的clue_state字段数据,发现该字段有3种值，VALID_NEW_CLUES为新用户，VALID_PUBLIC_NEW_CLUE为老用户，否则为无效数据。
case clue.clue_state
    when 'VALID_NEW_CLUES' then '1'   
    when 'VALID_PUBLIC_NEW_CLUE' then '0'
    else '-1' 
end as clue_state_stat

        查看这条SQL的相关优化是否执行：

        第一：开启优化策略（其他配置已开启）

        set hive.auto.convert.join=true;   # 开启Map join支持
        set hive.auto.convert.join.noconditionaltask.size=512000000;   # 设置小表最大阈值
        set hive.optimize.bucketmapjoin = true;
        --开启SMB map join支持
        set hive.auto.convert.sortmerge.join=true;
        set hive.auto.convert.sortmerge.join.noconditionaltask=true;
        set hive.optimize.bucketmapjoin.sortedmerge = true;    # 开启自动尝试SMB连

        第二：通过explain查看是否优化

         除此以外，其他表也有Map join优化过程。

        执行sql语句，发现及其缓慢，可能需要3-4小时时间才能完成。思考：通过explain查看其执行计划，可知明明使用了优化策略为什么还是怎么慢？

        原因：当前的优化方案，需要较大的内存资源才可以运行，如果资源不够，yarn会安排这些优化过程依次执行，反而效率更差了。如果在生产环境中，资源充分是不会发生这种问题的。

        目前的解决方案：关闭所有的优化方案。set....=false

        关闭以后在执行sql，10-20分钟即可完成

        最终sql：

--动态分区配置
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;
set hive.enforce.sorting=true;
set hive.auto.convert.join=true;   # 开启Map join支持
set hive.auto.convert.join.noconditionaltask.size=512000000;   # 设置小表最大阈值
set hive.optimize.bucketmapjoin = true;
--开启SMB map join支持
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
set hive.optimize.bucketmapjoin.sortedmerge = true;    # 开启自动尝试SMB连

insert into table itcast_dwm.itcast_intention_dwm partition(yearinfo,monthinfo,dayinfo)
select
    dwd.customer_id,
    dwd.create_date_time,
    cus.area,
    dwd.itcast_school_id,
    sch.name as itcast_school_name,
    dwd.deleted,
    dwd.origin_type,
    dwd.itcast_subject_id,
    sub.name as itcast_subject_name,
    dwd.hourinfo,
    dwd.origin_type_stat,
    if(clue.clue_state = 'VALID_NEW_CLUES','1',if(clue.clue_state = 'VALID_PUBLIC_NEW_CLUE','0','-1')) as clue_state_stat,    # 新用户1，老用户0，无线用户-1
    e.tdepart_id,
    dept.name as tdepart_name,
    dwd.yearinfo,
    dwd.monthinfo,
    dwd.dayinfo
from itcast_dwd.itcast_intention_dwd dwd
    left join itcast_ods.customer_clue clue on dwd.rid = clue.customer_relationship_id
    left join itcast_dimen.customer cus on dwd.customer_id = cus.id
    left join itcast_dimen.employee e on dwd.creator = e.id
    left join itcast_dimen.scrm_department dept on e.tdepart_id = dept.id
    left join itcast_dimen.itcast_subject sub on dwd.itcast_subject_id = sub.id
    left join itcast_dimen.itcast_school sch on dwd.itcast_school_id = sch.id;

        9、数据统计分析

        # 统计总意向量

        # 统计每年线上线下新老用户的总意向量

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 group_type,
    '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 group_type,
    '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,
    tdepart_id,
    tdepart_name,
    yearinfo as time_str,
    '6' as group_type,
    '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;

        # 统计每年每月线上线下新老用户各个咨询中心的意向量   。。。

        # 统计每年每月每天线上线下新老用户各个咨询中心的意向量  。。。

        # 统计每年每月每天每小时线上线下新老用户各个咨询中心的意向量   。。。

        sql详见intention.sql

        10、数据导出

        第一：在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.来源渠道；6.咨询中心;',
   `time_type` varchar(100) COMMENT '时间维度：1、按小时聚合；2、按天聚合；3、按季度聚合；4、按月聚合；5、按年聚合；',
    yearinfo varchar(100) COMMENT '年', 
    monthinfo varchar(100) COMMENT '月', 
    dayinfo varchar(100) COMMENT '天'
)comment 'mysql客户意向dws表';

        第二：sqoop导出

sqoop export \
--connect "jdbc:mysql://hadoop01: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

        至此意向用户主题看板的全量操作完成

        11、拉链表的实现（重点）-项目增量流程

        在上述建模过程中ODS层的表设置为了拉链表，在增量操作过程中拉链表是如何实现的呢？

        拉链表的实现步骤：

        1）在ODS层创建一张与原始拉链表结构完全一致的表（新增-更新表），该表用于存放当天新增和修改的数据，用完会自动清空。

        2）利用sqoop将新增数据和修改的数据导入到新增-更新表中。

        3）基于新增-更新表数据，原始拉链表找到发生变更的数据，并修改其end_time。

        4）将新增-更新表的数据追加到原始拉链表中。

        具体来讲：原始拉链表如何知道新增-更新表中那些数据是新增的数据，那些数据是更新的数据呢？此外，假设原始拉链表识别出在新增-更新表中那些是更新的数据，那它又是基于什么方法找到本表中的过期数据的呢？通过left join即可实现

        假设当前有以下原始拉链表与新增-更新表

         不直接覆盖原始拉链表的原因是：避免union all后的结果出错或异常，如果遇到这种清空且直接将错误的数据覆盖到原始拉链表后，由于原始拉链表原有的数据已经没有了，导致无法重新来一遍操作，工作无法进行。如果将数据先保存在临时表中，即使数据有误，原始拉链表数据并没有变更，可以重复进行操作直到临时表数据正确，然后覆盖掉原始拉链表，即使覆盖过程出错，原始拉链表数据丢失，但临时表中依然保存了正确的数据，再次进行覆盖即可。

        拉链表的详细实现步骤：

        1）在ODS层创建一张与原始拉链表结构完全一致的表（新增-更新表），该表用于存放当天新增和修改的数据，用完会自动清空。

        2）利用sqoop将新增数据和修改的数据导入到新增-更新表中。

        3）将原始拉链表与新增-更新表进行left join操作，筛选出非空且有效的数据，修改其对应的end_time字段。非空表示可能发生更新的数据。

        4）将3）的结果union all新增-更新表，此外将union all的结果覆盖保证在一张与原始拉链表结构一致的临时表中。之后清空新增-更新表数据（也可以不清空，使用抽取时间start_time字段过滤即可）。

        5）将临时表的数据覆盖到原始拉链表。

        注意增量流程只有ODS层这里与上一个主题不一样，其他地方都类似，只需要注意，后面ODS层到DWD层过程中需要加个where end_time = '9999-12-31'这个条件，保证提取的数据都是当前有效的数据。

        12、hive的索引

       （1）索引的优化思想

        索引的作用：加快查询效率

        思考：为什么索引可以提升查询效率？

        如果没有索引，这条select语句会进行全表扫描，如果表的数据很多时，效率会很慢。引入索引，不仅使得查询语句的扫描量下降，同时在查询过程中对索引表和原表的查询变更为对主键的查询，而按照主键查询的效率是最高的。

         索引的优化方向主要就是：1）降低扫描量（分区、分桶的目的也是为了这个）；2）尽可能的将对非主键的查询转化为按照主键查询。

        （2）hive索引

        hive支持索引，但是hive中的索引与关系型数据库中的索引并不相同，比如hive不支持主键和外键。hive索引可以建立在表的某些列上，以提升一些操作的效率，例如减少MapReduce任务中需要读取的数据快的数量。在实际情况下，分区，分桶和索引都是非常好的提升查询效率的方法（一般分桶和索引要优于分区），但是分桶由于SMB Map join优化机制对关联键要求严格，对资源要求严格，所以并不是总能生效。此外分桶表的数据导入操作比较麻烦，因此索引就显得特别方便。

        hive提供了三种索引：原始索引、row group index（行组索引）、bloom filter index（布隆过滤索引）

        1）原始索引（目前已不在使用，在hive3.0以上已经不在支持）

        hive的原始索引在每次建立和更新数据后，不会自动更新，需要手动重建索引表（数据量大的时候，极其耗时），会触发一个MR job。此外，原始索引提升效率一般。

        2）row group index

        row group index只能应用在ORC文件中，ORC文件会将导入的数据分成若干个script（分片，默认每个script为256M），每个script包含3个部分：index date，row date，stripe footer。row date保存了原数据，以列的方式存放。stripe footer保存了stripe的元数据。index date保存了一些row date的简单索引信息（至于到底是什么索引信息，不需要关注）。

        如果当前的ORC文件没有开启row group index，则index date中只保存了一些简单的索引信息，查询时会对整表进行扫描。如果ORC文件开启了row group index，则index date中除了保存一些简单的索引之外，还会保存row date各个字段取值的最大值max和最小值min，以加快后续对ORC文件的查询效率。当查询中有<,>,=操作时，系统会自动根据max/min值，跳过不会用到的stripes，这样就降低了扫描量，提升了查询速度。我们将上述在index date部分增添max、min的索引称为row group index（也称min-max index大小对比索引，或storage index）

        开启row group index：

        建表时设置属性：TBLPROPERTIES (’orc.create.index’=’true’)

        需要注意的是，为了使Row Group Index有效利用，向表中加载数据时，必须对需要使用索引的字段进行排序，否则，min/max会失去意义。另外，这种索引主要用于数值型字段的查询过滤优化上。假设已经开启了row group index，且需要使用索引的字段也已经排好序，在查询之前还需要设置set hive.optimize.index.filter=true，该设置用于启动自动使用索引，如果不设置可能会出现有索引不用的情况。

        row group index生效条件：

        A：要求表必须是ORC存储格式

        B：向表中加载数据时，必须对需要使用索引的字段进行排序，否则，min/max会失去意义

        C：在创建表时必须开启row group index，否则没用

        D：主要应用于<,>,=场景中，且为数值类型字段，否则意义不大

create table test(
    sid int,
    name string,
    address string,
    age int,
    score int
) stored as orc
TBLPROPERTIES('orc.compress'='snappy','orc.create.index'='true')

-- 需求select * from test where score > 90;

-- 假设目前有一张有数据的stu表，结构与test一直

insert into table test
select * from stu order by score;

set hive.optimize.index.filter=true;
select * from test where score > 90;

        3）bloom filter index

        bloom filter index只能应用在ORC文件中，如果针对某字段开启了bloom filter index则在index date中会保存该字段所有的取值情况，当查询条件中包含对该字段的=号过滤时候，先从index date中获取是否包含该值，如果不包含，则跳过该stripe，减低扫描量，提升查询效率。

        开启bloom filter index：

        TBLPROPERTIES ("orc.bloom.filter.columns"="address")

        TBLPROPERTIES ("orc.bloom.filter.columns"="字段1，字段2,....")

        bloom filter index生效条件：

        A：要求表必须是ORC存储格式

        B：在创建表时必须开启bloom filter index，否则没用。需要对表中那个字段进行等值查询，就可以为这个字段设置布隆过滤索引

        C：必须是等值查询，不支持<,>；不需要对索引字段排序；不局限于数据类型，任何类型都可以

create table test(
    sid int,
    name string,
    address string,
    age int,
    score int
) stored as orc
TBLPROPERTIES('orc.compress'='snappy','orc.create.index'='true',"orc.bloom.filter.columns"="address")

-- 需求select * from test where score > 90 and address in ('北京','东北');

-- 假设目前有一张有数据的stu表，结构与test一直

insert into table test
select * from stu order by score;

set hive.optimize.index.filter=true;
select * from test where score > 90 and address in ('北京','东北');

        思考：在实际生产过程中，这些索引该如何选择呢？

        A：对于row group index，建议常开即可，即使不知道后续需要按那个字段查询也直接开启即可，不需要排序，可能本身插入的数据就是排好序的，无形之间就提升了查询速度。

        B：对于bloom filter index，不建议常开，只针对=号过滤的字段开启，例如join等值连接字段（a.id = b.id），where中等值查询的字段，清洗字段（deleted = false）等等。

        13、hive的相关优化

        （1）Hive并行编译

        在默认情况下，同一时间只允许一个session操作Hive（在Hive中运行sql），并上锁。如果有多个session一起来执行sql，此时只会对某个session的请求进行处理，其他session只能等待。公司一般只设置一个Hive，但是公司有不同的部门，显然可能在同一时间有多个session操作Hive，默认情况下是不允许的，那真的要一个一个等待执行吗，效率是不是太低了，极大影响公司的正常运营。解决方法：需要设置hive.driver.parallel.compilation为true（开启并行编译），设置完之后，各个session就可以同时在Hive中编译查询，提高了团队工作效率。

        修改hive.driver.parallel.compilation.global.limit的值，用于设置同一时间最多允许多少个session同时访问Hive。0和负值表示无限制。上述两项配置需要在cm中配置为全局配置。

        （2）Hive并行执行

        Hive会将一个查询转化为一个或多个阶段，包括：MapReduce阶段、抽样阶段、合并阶段、limit阶段等。默认情况下，一次只执行一个阶段。不过，如果某些阶段不是互相依赖，是可以并行执行的，进而提升查询效率。Hive的并行执行是并行编译的细化。注意一般情况下没有任何一个sql的执行阶段完全相互独立，或多或少总是存在依赖的，我们只能对那些不依赖的阶段进行并行执行。

        set hive.exec.parallel=true;      # 是否开启并行、执行，默认为false。

        set hive.exec.parallel.thread.number=16;     # 最大允许并行执行的数量（线程数）

        上述两项配置建议在session窗口下配置。

        思考：我们如何知道当前sql执行的各个阶段有没有依赖呢？----explain

         关于并行优化，必须要求我们的服务器有资源，否则即使开了并行优化，也不会执行。

        （3）Hive的小文件合并

        思考：小文件有什么影响呢？

        HDFS角度：每一个小文件都会有一份元数据，当小文件过多时，会导致出现大量的元数据存储在Namenode的内存中，从而导致内存使用率增大，一旦Namenode内存已满，即使Datanode依然有存储空间，也是无法存储的。

        MR角度：在运行MR的时候，每一个文件至少是一个文件切片，也就意味着至少需要运行一个Map task，当小文件过多后就会导致产生大量的Map task，而每一个Map task只处理极少的数据，导致资源被大量占用，运行的时间都没有申请资源时间长，资源被大量浪费掉。

        假设目前有6个文件：150M,10M,8M,300M,12M,6M需要运行多少个Map task（默认一个Map task最大处理128M大小的文件）----9个Map task

        思考：从hive角度如何解决小文件过多的问题呢？在执行sql的时候，输出的文件数量尽量少一些，使用limit 10。

        还可以配置一些配置项进行小文件合并：

        hive.merge.mapfiles    # 是否开启Map端小文件合并（适用于MR只有Map没有Reduce情况）

        hive.merge.mapredfiles      # 是否开启Reduce端小文件合并

        hive.merge.size.per.task       # 合并后MR输出文件的最大大小，默认128M

        hive.merge.smallfiles.avgsize       # 设置输出文件的平均大小，当输出文件的平均大小小于此设置值时（系统会认为出现了大量小文件情况），会启动一个独立的map-reduce任务进行文件merge，默认值为16M。

        例如：设合并后MR输出文件的最大值为128M，平均阈值为50M，现有一个MR程序输出了以下几个文件：1M,10M,5M,3M,150M,80M,2M;这些文件的平均值<50M，此时系统认为出现了小文件过多的问题，会对小文件进行合并（这里我们假设这些文件结构一致可以合并，实际情况下并不是所有文件都能合并），此时合并的结果为：128M，123M两个文件。

        上述4个配置项建议在cm中进行全局配置

        （4）矢量化查询

        hive中表的实际存储位置是在hdfs中，对表的select查询就会涉及到去hdfs中读取数据的过程，默认情况下hive只能一行一行的读取。如果数据量很大的情况下读取效率很低，会导致查询效率低。因此需要开启矢量化查询set hive.vectorized.execution.enabled=true;,开启之后可以批量化的读取数据（每批1024行），并且一次性对整个批次数据进行处理，而不是一个一个处理，读取速度得到大幅度提升，查询效率也进一步提升。注意：要想开启矢量化查询，就必须使用ORC格式来存储数据，其他格式不支持矢量化查询。

        （5）读取零拷贝

        默认情况下select后面指定的字段即使不用也会放入内存，开启读取零拷贝可以使在hive读取数据时，只读取跟SQL语句相关的列，不使用的列不进行读取，从而减少读取数据量，提升效率，减少内存开销。注意：读取零拷贝只支持ORC格式文件，

        开启方法：set hive.exec.orc.zerocopy=true;

        例子：A表有a,b,c,d,e五个字段

        select a，b，b  from A where b = xxx and c between xx and xxx；

        显然d，e这两个字段当前的SQL并没有使用，默认情况下会读取全表的数据，不管用与不用。开启读取零拷贝后，读取数据时就不会将d，e这两个字段读取到内存中，只读取用到的字段。

        14、hive的数据倾斜优化（面试重点）

        思考：什么是数据倾斜？

        在运行MR的过程中，有多个Reduce，每一个Reduce拿到的数据不是很均匀，导致其中某一个或某几个Reduce拿到的数据量远远大于其他的Reduce拿到的数据量。

        思考：数据倾斜会导致什么问题？

        1）执行效率下降，运行时间极慢（整个MR运行时间，主要看最后一个reduce的结束时间）

        2）由于其中某几个Reduce长时间运行，资源长期被占用，一旦超时，YARN会强制回收资源，导致运行失败。

        3）导致节点出现宕机问题

        思考：在执行什么SQL的时候，会出现多个Reduce的情况

        1）多表join

        2）执行group by

        3）对分桶表执行相关操作（分几个桶就会有几个map和reduce，每个map和reduce处理一个桶数据，因此数据倾斜主要受如何分桶有关系。如果分桶字段数据不均匀，则会发生数据倾斜，解决方法只能是更换分桶字段，分桶字段最好是主键列，或者自定义一个自增列，按照该列分桶）

        较容易出现数据倾斜问题发生的情况是join和group by

        （1）group by数据倾斜

         shuffle阶段：

        k2：大数据一班

        v2：{s01,张三,大数据一班}{s02,李四,大数据一班}{s04,赵六,大数据一班}{s06,周八,大数据一班}{s07,李九,大数据一班}

        k2：大数据二班

        v2：{s03,王五,大数据二班}{s05,田七,大数据二班}

        计算hash取模分配reduce，假设reduce有两个。

         发现上述group by由于班级数据本身不均匀，发生数据倾斜，reduce1接收的数据量很大，而reduce2接收的数据量很小。

        解决方案：

        方案一：采用combiner规约（提前聚合）的方式来解决（将reduce的逻辑提前在每一个map中做一遍）

        配置：hive.map.aggr=true       # group  by时开启map端提前聚合操作

        shuffle阶段：

        k2：大数据一班

        v2：3，2

        k2：大数据二班

        v2：1，1

        hash取模分配reduce，假设reduce有两个

        通过在map端增加一个提前聚合操作，来减少从map端到reduce端的数据量，从而缓解数据倾斜，一定成都加快执行速度。

        方案二：负载均衡（大combiner）

        核心：采用两个MR来解决，第一个MR负责将数据均匀的落在不同的Reduce上，在各个reduce上进行局部聚合统计操作，形成一个局部的结果（这个过程就是负载均衡的过程）。第二个MR接收这个局部结果，按照相同的key发往同一个reduce的方案进行reduce的分配，然后完成最终的聚合统计操作。（combiner方案是在map端增加一个提前聚合操作，而大combiner是重新执行一个MR来完成提前聚合操作）

        配置：hive.groupby.skewindata=true    # 开启负载均衡

        注意：如果开启了负载均衡，hive不支持在多个列上多次进行去重操作：（distinct只能写一次）

         注意select count（distinct uid，uname） from...   # 将uid和uname看成整体去重

                select distinct uid，uname from...   # 只对uid去重

        （2）join数据倾斜（面试重点）

        解决方案：

        方案一：通过采用Map join、Bucket map join、SMB map join。将reduce端的join操作提前到map端进行，使得整个MR只有map task，而map端基本不会存在数据倾斜问题。

        注意采用Map join、Bucket map join、SMB map join，在使用的时候，都必须满足相关的条件，但是很多时候，无法满足这些条件，意味着方案一不可使用。

        方案二：将那些容易产生倾斜的key值数据从当前的MR中提取出来，这样剩下的数据就没有数据倾斜问题了，可以正常执行MR了。对于那些提取出来的不均衡数据单独跑一个MR来处理。最后将两个MR的结果进行合并（一般是union all），合并也会在开启一个MR来实现。

        思考：我们是如何知道join字段的那些key值分布不均匀，容易产生数据倾斜呢？

        此时会分两种情况：

        情况一：明确知道表中那些key值有倾斜问题，由于实现已经知道了倾斜的数据是join字段的那个key，此时可以使用编译期解决方案：首先开启join数据倾斜编译期优化支持：set hive.optimize.skewjoin.compiletime=true;,其次在建表的时候，提前指定好那个字段中的那个key出现数据倾斜，这样在执行的时候，hive会直接将这些倾斜的key值以及对应的数据从当前的MR中提取出去，单独跑一个MR来处理。

create table test(
    id int,
    name string,
    address string
)skewed by (id) on (倾斜的key值)     -- 倾斜的字段和key值
stored as directories;               -- 固定的语法，为提取出的倾斜数据创建子目录单独存放

create table test1(
    tid int,
    tname string,
    taddress string
)skewed by (tid) on (倾斜的key值)
stored as directories;

set hive.optimize.skewjoin.compiletime=true;
select * from test join test1 on test.id = test1.tid;

        情况二：不知道表中那些key值有倾斜问题，可以使用运行期解决方案，MR在运行的过程中系统会自动识别join字段下的那些key出现倾斜（hive会记录每一个key出现的次数，如果超过阈值，则认为发生倾斜），如果检测到某个key倾斜了，hive会自动将其从当前的MR中提取出去，单独跑一个MR来处理。

        配置项：set hive.optimize.skewjoin=true;     # 开启join数据倾斜运行期优化支持

                       set hive.skewjoin.key=100000;       # 阈值，当key出现多少个时，认为有倾斜（在实际生产环境下这个值需要按照实际情况来调整）

        （3）union all优化

        上述join数据倾斜优化过程中方案二会使用union all进行合并，而union all的合并默认情况下会再跑一个MR，效率低。对union all优化的目的就是想办法将union all对应的MR去掉，通过减少一个MR，来提升查询效率。

        配置项：set hive.ooptimize.union.remove=true;    # 开启union all优化支持（只要开启遇到union all的时候不需要重新开启一个MR也能实现同样的合并功能，至于具体怎么做的，后续可以详细了解），一般会与join的数据倾斜优化支持一同开启

        （4）如何感知是否有数据倾斜问题发生

        1）查看job history历史日志，他会详细指出各个reduce的执行时间（适用于MR已经执行完成）

        2）对于正在运行的MR，需要借助于HUE来查看各个reduce的执行时间：

         如果有一个reduce的运行时间明显比其他reduce运行时间长，我们认为发生数据倾斜

        （5）关联优化器

        配置项：set.hive.optimize.correlation=true;    ＃开启关联优化器（开启shuffle共享）

        在Hive的一些复杂关联查询中，可能同时还包含有group by等能够触发shuffle的操作，有些时候shuffle操作是可以共享的，通过关联优化器选项，可以尽量减少复杂查询中的shuffle，从而提升性能。

        总结：  

-- 常开项: 
set hive.exec.parallel=true;  是否开启并行执行
set hive.exec.parallel.thread.number=16;  最大允许并行执行的数量
set hive.vectorized.execution.enabled=true; 矢量化查询
set hive.exec.orc.zerocopy=true; 读取零拷贝
set hive.optimize.correlation=true; 关联优化器

-- 针对性开启:
set hive.map.aggr=true; 开启 group by combiner数据倾斜方案
set hive.groupby.skewindata=true;开启groupby 负载均衡优化
set hive.optimize.skewjoin.compiletime=true; join的编译期优化
set hive.optimize.skewjoin=true; 是否开启运行期倾斜解决join
set hive.skewjoin.key=100000; 当key出现多少个的时候, 认为有倾斜
set hive.optimize.union.remove=true; union all优化

        补充，hive的文件存储格式

        hive支持多种文件存储格式：TextFile、SequenceFile、ORC、Parquet等，不同的文件存储格式具有不同的存储特点，像以二进制存储数据的的SequenceFile文件格式，使得数据的空间感很低；像列式存储ORC、Parquet借助索引可以提高查询效率。

        在建表时stored as <file format> 用于设置文件存储格式，默认是TextFile格式

        假设当前文件100M

        1）TextFile--100M

        TextFile文件格式是按行存储的，最常用的数据文件格式就是TextFile文件，因此hive的默认格式为TextFile，设置为TextFile文件格式，在hive中导入数据时仅仅将数据拷贝到hdfs即可不进行任何处理，原数据文件大小与hdfs的文件大小一致。

        优点：简单，便于共享

        缺点：消耗存储空间，io性能低，不能进行并行操作，查询效率低

        应用场景：适合小量数据的存储和查询

        hive中的数据量一般都很庞大，且比较注重查询效率，因此在hive中一般不使用TextFile

        2）SequenceFile--120M

        hive中同样一般不使用SequenceFile

        3）Parquet--80M

        Parquet是一种列式存储文件格式，IO性能，查询性能都很好，支持高压缩，但是该文件格式不支持数据的修改操作update，insert，delete，只能进行查询select。适用于字段特别特别多，无更新，只取部分列的查询。

        hive中一般也不使用

        4）ORC（hive中经常使用的格式）--20M

        ORC文件格式可以降低存储空间的消耗的同时加快查询效率，支持高压缩，支持索引，适用于海量数据的存储。