Day17:业务分析:访问与咨询主题

知识点01:回顾

  1. OLTP与OLAP的应用和区别?
    • 联机事务处理
      • 面向业务数据存储
      • 数据使用者:用户
      • 特点:数据量小、支持事务、性能比较高
    • 联机分析处理
      • 面向数据分析处理
      • 数据使用者:决策层、运营人员
      • 特点:数据量大、不需要支持事务、性能要求不高
  2. 数据仓库的功能与应用场景是什么?
    • 功能:为了满足OLAP的应用场景而 实现的数据管理模型
      • 存储:统一化数据管理
      • 处理:规范化的数据加工
    • 应用:统一化的和规范化的数据管理场景
      • 离线数据仓库:Hive
      • 实时数据仓库:Kafka
  3. 数据仓库的核心特点有哪些?
    • 面向主题:以分析主题的形式来管理数据
      • 数据库:面向业务
      • 数据仓库:面向主题
        • 数据仓库:DW:存储整个公司所有数据
          • 数据集市/主题域:DM:根据每个部门的需求划分不同的数据
            • 分析主题:每个主题所对应的数据结果
    • 数据集成:整体存放了整个公司所有需要处理的数据
      • 数据仓库本身不产生数据,也不使用数据
    • 非易失:一般没有更新和删除的业务需求
    • 时变性:数据会随着时间的变化数据不断更新
  4. 什么是指标?常见的指标有哪些?什么是维度?常见的维度有哪些?
    • 指标:对数据统计分析的结果,通过数值来进行体现
    • 常见指标:UV、PV、IP、跳出率、二跳率、平均访问时长
    • 维度:看待事实的角度,用于细化指标的结果,精确发现问题
    • 常见维度:时间维度、地区维度、平台维度……
  5. 事实指标值分为哪几类?事实表分为哪几类?
    • 事实指标值的分类
      • 可累加事实:在任何维度下,指标的值都可以进行累加
      • 半可累加事实:在一定维度下,指标的值可以进行累加
      • 不可累加事实:在任何维度下,指标的值都不可以进行累加
    • 事实表的分类
      • 事务事实表:原始数据表
        • 每一条订单的信息
      • 周期快照事实表:按照一定周期对事务事实进行聚合分析的结果
        • 每个月订单的汇总结果
      • 累积快照事实表
      • 无事实事实表:维度关联的
  6. 什么是星型模型?与雪花模型有什么区别?
    • 星型模型:所有维度表直接关联在事实表上,维度表没有子维度表
      • 优点:减少了数据关联,提高了查询性能
      • 缺点:数据存在冗余
    • 雪花模型:维度表有子维度表
      • 优点:数据没有冗余
      • 缺点:数据关联比较麻烦,性能比较差
  7. 维度数据发生变化,如何处理发生变化的数据?
    • SCD1:直接以新的状态覆盖老的状态
    • SCD2:拉链表:记录所有状态,通过对应的时间去标记每个状态
      • start:状态开始时间
      • end :状态结束时间,如果是最新状态,一般给9999-12-31
    • SCD3:通过增加列来实现记录新的状态
  8. 为什么要分层,常见的层次有哪些以及功能是什么?
    • 设计:分层决定了数据处理步骤
    • 常见的层级
      • ODS:原始数据层
        • 存放原始数据
      • DW:数据仓库层
        • DWD:明细数据层
          • 对ODS层的数据进行ETL
        • DWM:中间数据层
          • 对DWD层的数据进行轻度聚合
        • DWS:汇总数据层
          • 对上一层数据进行汇总聚合,得到所有指标的宽表
      • DA:数据应用
        • 应用的数据结果

知识点02:目标

  • 实现访问与咨询分析模块的构建
  • step1:掌握访问与咨询需求
    • 流程
    • 数据内容
    • 分析需求
  • step2:数据仓库设计
    • 通过原始数据以及最终想要得到的结果
    • 分析每一步应该怎么做:分层
    • 每一步的结果的格式:建模
  • step3:实现分析过程
    • SQL实现每一步转换

知识点03:访问与咨询业务流程

  • 目标掌握访问与咨询的业务流程

  • 路径

    • step1:业务过程
    • step2:数据记录
  • 实施

    • 整个项目需求
    • 基于不同维度统计分析每一步转化率
      • 访问、咨询、意向、报名
        • 访问转咨询转化率
          • 咨询人数 / 访问人数
      • 基于不同维度实现学员考勤管理分析
        • 出勤、迟到、请假、旷课
    • 业务过程
      • 访问:用户访问网站或者APP等平台,用户访问的信息会被后天的客服系统记录
        • 访问用户
      • 咨询:如果用户平台的内容感兴趣,就会在聊天窗口中进行咨询,咨询的信息也会被记录在客服系统中
        • 咨询用户
      • 区分:数据中有没有聊天的数据,如果发送消息,就是咨询用户
    • 数据记录
      • 客服系统会将用户的访问与咨询的信息记录在后台的数据库中
      • 整个数据中记录了用户的每一条访问信息
      • 表中有一个字段:msg_count,用于区分用户是否咨询
        • msg_count:表示发送消息的条数
          • 如果用户只访问了,没有咨询,该字段值为0或者null
          • 如果用户咨询了,这个字段的值大于0
  • 小结

    • 了解基本的业务流程和数据源位置

知识点04:访问与咨询需求

  • 目标掌握访问与咨询的业务需求

  • 实施

    • 每天总访问客户量

      • 指标:访问用户量

      • 计算:count(distinct usreid)

      • 维度:时间:年、季度、月、天、小时

      • 字段:年【2021】、季度【2】、月【03】、天【15】、小时【12】、用户id

      • SQL

        select
        	年,
        	月,
        	天,
        	count(distinct 用户id)
        from table
        group by 年、月、天
        
    • 每天每个地区的访问用户数

      • 指标:访问用户量

      • 计算:count(distinct usreid)

      • 维度

        • 时间:年、季度、月、天、小时
        • 地区
      • 字段:年【2021】、季度【2】、月【03】、天【15】、小时【12】、地区、用户id

      • SQL

        select
        	年,
        	月,
        	天,
        	地区,
        	count(distinct 用户id)
        from table
        group by 年、月、天、地区
        
    • 每小时访客转咨询率趋势

      • 指标:咨询率

      • 计算:每个小时咨询用户数 / 每个小时的访问用户数 = 每个小时的咨询率

      • 维度:小时

      • 字段:小时、每个小时的咨询用户数、每小时的访问用户数

      • SQL

        select
        	小时,
        	咨询用户数 /  访问用户数
        from table
        
    • 每天各个来源渠道访问用户量占比

      • 什么叫做来源渠道?

        • 来源渠道指的是用户访问的来源
        • 直接输入网址
        • 搜索引擎
        • 广告链接
        • 网站导航
      • 目的:统计分析用户的来源,考虑实现精准运营投放

      • 指标:访问用户量

      • 维度:天、来源渠道

    • 每天每个搜索来源的访问用户量占比

      • 什么叫做搜索来源?

        • 用户通过搜索引擎访问平台,具体的是哪种搜索引擎
        • 百度
        • 谷歌
        • 搜狗
        • 必应
      • 指标:访问用户量

      • 维度:天、搜索来源

    • 每天每个来源页面的访问用户量排行榜:Top10

      • 什么叫做来源页面?

        • 用户进入平台之前访问的页面是哪个,用户从哪个页面进入平台
      • 指标:访问用户量

      • 维度:天、来源页面

  • 小结

    • 业务需求有哪些?
      • 指标:访问用户量、咨询用户量、咨询率
        • Session个数统计
        • IP个数统计
      • 维度
        • 时间:年、季度、月、天、小时
        • 地区
        • 来源渠道
        • 搜索来源
        • 来源页面
      • 组合维度分析
        • 时间:五种
        • 时间 + 地区
        • 时间 + 来源渠道……

知识点05:访问与咨询原始数据

  • 目标了解访问与咨询的原始数据内容

  • 路径

    • step1:数据存储
    • step2:数据表
  • 实施

    • 数据存储

      • 数据来源:来自于客服系统,存储在数据库中

Day17:业务分析:访问与咨询主题

- 来自于用户的访问,用户没每访问一个页面就会记录一条日志信息

  ```
  id	userid		sessionId		ip				create_time				url		refere_url
  1	userid1		sesionid1	192.168.111.11		2020-11-11 12:30:30		url1	www.baidu.com
  2	userid1		sesionid1	192.168.111.11		2020-11-11 12:30:31		url2	url1
  3	userid1		sesionid2	192.168.111.11		2020-11-11 14:30:31		url3	www.sougou.com
  4	userid2		sesionid3	192.168.111.12		2020-11-11 14:30:31		url3	www.baidu.com
  ```

  - UV:2
  - SessionId:3
  - PV:4
  - IP:2
  • 数据表与核心字段

    • 这两张表逻辑上是一张表,存储的时候将一张表分成两张表存储了,这张表的列多而且部分字段的存储值比较大,分成了两张存储

      • 关联字段:id
    • web_chat_ems_2019_07

        id INT comment '主键',
        create_date_time STRING comment '数据创建时间',
        session_id STRING comment 'sessionId',
        sid STRING comment '访客id',
        create_time STRING comment '会话创建时间',
        seo_source STRING comment '搜索来源',
        seo_keywords STRING comment '关键字',
        ip STRING comment 'IP地址',
        area STRING comment '地域',
        country STRING comment '所在国家',
        province STRING comment '省',
        city STRING comment '城市',
        origin_channel STRING comment '投放渠道',
        user_match STRING comment '所属坐席',
        manual_time STRING comment '人工开始时间',
        begin_time STRING comment '坐席领取时间 ',
        end_time STRING comment '会话结束时间',
        last_customer_msg_time_stamp STRING comment '客户最后一条消息的时间',
        last_agent_msg_time_stamp STRING comment '坐席最后一下回复的时间',
        reply_msg_count INT comment '客服回复消息数',
      msg_count INT comment '客户发送消息数',
        browser_name STRING comment '浏览器名称',
      os_info STRING comment '系统名称')
      
      • id:用于关联合并信息表

      • session_id:指标:统计session个数

      • sid:指标:统计访客个数

      • create_time:时间维度

    • seo_source:搜索来源维度

      • ip:指标:统计IP个数

      • area:地区维度

      • origin_channel:来源渠道维度

      • msg_count:用于区分是否是一条咨询数据

    • web_chat_text_ems_2019_07

        id INT COMMENT '主键来自MySQL',
        referrer STRING comment '上级来源页面',
        from_url STRING comment '会话来源页面',
        landing_page_url STRING comment '访客着陆页面',
        url_title STRING comment '咨询页面title',
        platform_description STRING comment '客户平台信息',
        other_params STRING comment '扩展字段中数据',
        history STRING comment '历史访问记录'
      
      • id:实现关联
      • from_url:来源页面维度
  • 小结

    • 了解原始数据中的核心原始数据字段

知识点06:访问数仓设计分析

  • 目标掌握数据仓库设计的分析过程

  • 路径

    • step1:数仓需求
    • step2:ODS设计
    • step3:DWD设计
    • step4:DWS/APP设计
  • 实施

    • 数仓需求

      • 结果:事实表

        时间	地区	搜索来源	来源渠道	来源页面	UV			Session			IP
        2020  -1	-1			-1		-1		1000			2000		100
        2020 上海   -1			-1		-1		1000			2000		100
        
        • 正常情况下,预想的事实表的格式

        • 问题1:如果将所有的维度下的指标结果都放在一张事实表,不同维度有的维度字段没有值怎么办?

          • 需求中的维度

            • 时间

              时间		UV			Session			IP
              
            • 时间+地区

              时间		地区		UV			Session			IP
              
            • 时间+来源渠道

              时间		来源渠道		UV			Session			IP
              
          • 如果不基于这个维度,将这个维度的值设置为-1

        • 问题2:所有维度的结果都在一张表中,如果我想获取某个维度下的结果,怎么区分呢?

          • 对每条数据属于哪种维度构建标记
          • 自定义标记的逻辑
      • 维度标记

        时间	地区	搜索来源	来源渠道	来源页面	UV			Session		IP		flag1	flag2
        2020  -1	-1		-1		-1		1000			2000		100       5			1
        

      2020 上海 -1 -1 -1 1000 2000 100 5 2

      
      - flag1:用于标记哪种时间维度
        - 1-小时
        - 2-天
        - 3-月
        - 4-季度
        - 5-年
      - flag2:用于标记哪种组合维度 = 基础维度 + 其他维度
        - 1-时间
        - 2-时间+地区
        - 3-时间+来源渠道
        - 4-时间+搜索来源
        - 5-时间+来源页面
      
      
    • ODS设计

      • 原始数据层:直接存放原始数据
      • 判断:原始数据是否是一个标准的结构化数据
        • 如果是:直接放入ODS层
        • 如果不是:先做ETL,变成结构化格式,再加载到ODS
      • 直接MySQL中的客服数据直接采集到ODS层
        • web_chat_ems
        • web_chat_text_ems
      • 采集:Sqoop
    • DWD设计

      • step1:将两张表合并一张明细表
      • step2:过滤掉不需要的数据:非法的行,用不到的列
      • step3:构造维度
      select
         a.create_time,
         substr(a.create_time,0,4) as yearinfo,
         ceil(substr(a.create_time,6,2) / 3)
         substr(a.create_time,6,2) as monthinfo,
         substr(a.create_time,9,2) as dayinfo,
         substr(a.create_time,12,2) as hourinfo,
         a.orgin_channel,
         a.seo_source,
         a.area,
         b.from_url,
         a.sid,
         a.sessionId,
         a.ip,
         a.msg_count
      from  web_chat_ems a join web_chat_text_ems b on a.id = b.id
      where substr(create_time,0,10) = 昨天的日期;
      
      • 日期函数:year、month、day、hour、quater
    • DWS/APP设计

      • 对DWD层的数据基于维度进行分组聚合,统计每天的UV、Session和IP的个数

      • 统计每天的UV、Session、IP

        select
          yearinfo,
          monthinfo,
          dayinfo,
          '-1' as hourinfo,
          '-1' as orgin_channel,
          '-1' as seo_source,
          '-1 ' as area,
          '-1'  as from_url,
          count(distinct sid) as uv,
          count(distinct sessionId) as sessionId,
          count(distinct ip) as ip,
          '2' as flag1,
          '1' as flag2
        from  dwd
        group by yearinfo,monthinfo,dayinfo;
        
      • 统计每天每个地区的UV、Session、IP

        select
          yearinfo,
          monthinfo,
          dayinfo,
          '-1' as hourinfo,
          '-1' as orgin_channel,
          '-1' as seo_source,
          area,
          '-1'  as from_url,
          count(distinct sid) as uv,
          count(distinct sessionId) as sessionId,
          count(distinct ip) as ip,
          '2' as flag1,
          '2' as flag2
        from  dwd
        group by yearinfo,monthinfo,dayinfo,area;
        
  • 小结

    • 访问分析的数仓设计为几层?
      • ODS:存放原始数据
      • DWD
        • step1:关联
        • step2:过滤
        • step3:构建维度
      • DWS:聚合

知识点07:修改Hive注释支持中文

  • 目标实现Hive注释中文支持的配置

  • 实施

    • step1:修改元数据表注解为UTF8

      • 注释的信息也是存储在Hive的元数据汇总
      • Hive元数据存在:MySQL
        • 以数据库级别存在
      • MySQL中存储中文:需要配置UTF8的编码
        • Hive表与数据库的信息不能为UTF8编码的
      • 解决:对Hive中存储注释的表的列的编码进行修改
      alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
      alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
      alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
      alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
      alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
      
    • step2:修改连接配置

      <property>
          <name>javax.jdo.option.ConnectionURL</name>
          <value>jdbc:mysql://node3:3306/hivemetadata?createDatabaseIfNotExist=true&amp;characterEncoding=UTF-8</value>
        </property>
      
    • step3:重启metastore和hiveserver,重新建hive表,中文正常

  • 小结

    • 提供的虚拟机已经修改过,知道如何实现即可

知识点08:访问ODS层构建及数据采集

  • 目标实现访问数据的数据采集

  • 路径

    • step1:采集需求
    • step2:Hive建表
    • step3:全量采集
    • step4:增量采集
  • 实施

    • 采集需求

      • ODS层的表:与业务表保持一致
        • web_chat_ems
        • web_chat_text_ems
    • Hive建表

      • itcast_ods.web_chat_ems

        --写入时压缩生效
        use itcast_ods;
        set hive.exec.orc.compression.strategy=COMPRESSION;
        
        CREATE EXTERNAL TABLE IF NOT EXISTS itcast_ods.web_chat_ems (
          id INT comment '主键',
          create_date_time STRING comment '数据创建时间',
          session_id STRING comment 'sessionId',
          sid STRING comment '访客id',
          create_time STRING comment '会话创建时间',
          seo_source STRING comment '搜索来源',
          seo_keywords STRING comment '关键字',
          ip STRING comment 'IP地址',
          area STRING comment '地域',
          country STRING comment '所在国家',
          province STRING comment '省',
          city STRING comment '城市',
          origin_channel STRING comment '投放渠道',
          user_match STRING comment '所属坐席',
          manual_time STRING comment '人工开始时间',
          begin_time STRING comment '坐席领取时间 ',
          end_time STRING comment '会话结束时间',
          last_customer_msg_time_stamp STRING comment '客户最后一条消息的时间',
          last_agent_msg_time_stamp STRING comment '坐席最后一下回复的时间',
          reply_msg_count INT comment '客服回复消息数',
          msg_count INT comment '客户发送消息数',
          browser_name STRING comment '浏览器名称',
          os_info STRING comment '系统名称')
        comment '访问会话信息表'
        PARTITIONED BY(starts_time STRING)
        ROW FORMAT DELIMITED
        FIELDS TERMINATED BY '\t'
        stored as orc
        location '/user/hive/warehouse/itcast_ods.db/web_chat_ems_ods'
        TBLPROPERTIES ('orc.compress'='ZLIB');
        
      • itcast_ods.web_chat_text_ems

        CREATE EXTERNAL TABLE IF NOT EXISTS itcast_ods.web_chat_text_ems (
          id INT COMMENT '主键来自MySQL',
          referrer STRING comment '上级来源页面',
          from_url STRING comment '会话来源页面',
          landing_page_url STRING comment '访客着陆页面',
          url_title STRING comment '咨询页面title',
          platform_description STRING comment '客户平台信息',
          other_params STRING comment '扩展字段中数据',
          history STRING comment '历史访问记录'
        ) comment 'EMS-PV测试表'
        PARTITIONED BY(start_time STRING)
        ROW FORMAT DELIMITED 
        FIELDS TERMINATED BY '\t'
        stored as orc
        location '/user/hive/warehouse/itcast_ods.db/web_chat_text_ems_ods'
        TBLPROPERTIES ('orc.compress'='ZLIB');
        
    • 全量采集

      • web_chat_ems

        sqoop import \
        --connect jdbc:mysql://node3:3306/nev \
        --username root \
        --password 123456 \
        --driver com.mysql.jdbc.Driver \
        --query 'select id, create_date_time, session_id, sid, create_time, seo_source, seo_keywords, ip, area, country, province, city, origin_channel, user as user_match, manual_time, begin_time, end_time, last_customer_msg_time_stamp, last_agent_msg_time_stamp, reply_msg_count, msg_count, browser_name, os_info, "2019-07-01" as starts_time from web_chat_ems_2019_07 where $CONDITIONS' \
        --hcatalog-database itcast_ods \
        --hcatalog-table web_chat_ems \
        --hcatalog-storage-stanza 'stored as orc tblproperties ("orc.compress"="ZLIB")' \
        -m 2 \
        --split-by id
        

Day17:业务分析:访问与咨询主题

- web_chat_text_ems

  ```shell
  sqoop import \
  --connect jdbc:mysql://node3:3306/nev \
  --username root \
  --password 123456 \
  --driver com.mysql.jdbc.Driver \
  --query 'select id,referrer,from_url,landing_page_url,url_title,platform_description,other_params,history, "2019-07-01" as start_time from web_chat_text_ems_2019_07 where $CONDITIONS' \
  --hcatalog-database itcast_ods \
  --hcatalog-table web_chat_text_ems \
  --hcatalog-storage-stanza 'stored as orc tblproperties ("orc.compress"="ZLIB")' \
  -m 2 \
  --split-by id
  ```
  • 增量采集

    • append:实现增量采集

      sqoop import \
      --connect jdbc:mysql://node3:3306/nev \
      --username root \
      --password 123456 \
      --driver com.mysql.jdbc.Driver \
      --query 'select id, create_date_time, session_id, sid, create_time, seo_source, seo_keywords, ip, area, country, province, city, origin_channel, user as user_match, manual_time, begin_time, end_time, last_customer_msg_time_stamp, last_agent_msg_time_stamp, reply_msg_count, msg_count, browser_name, os_info, date_sub(now,1) as starts_time from web_chat_ems_2019_07 where $CONDITIONS' \
      --hcatalog-database itcast_ods \
      --hcatalog-table web_chat_ems \
      --hcatalog-storage-stanza 'stored as orc tblproperties ("orc.compress"="ZLIB")' \
      --incremental append \
      --check-column id \
      --last-value 100 \
      -m 2 \
      --split-by id
      
    • 直接过滤方式

      sqoop import \
      --connect jdbc:mysql://node3:3306/nev \
      --username root \
      --password 123456 \
      --driver com.mysql.jdbc.Driver \
      --query 'select id, create_date_time, session_id, sid, create_time, seo_source, seo_keywords, ip, area, country, province, city, origin_channel, user as user_match, manual_time, begin_time, end_time, last_customer_msg_time_stamp, last_agent_msg_time_stamp, reply_msg_count, msg_count, browser_name, os_info, date_sub(now,1) as starts_time from web_chat_ems_2019_07 where substr(create_time,0,10) = date_sub(now(),1) $CONDITIONS' \
      --hcatalog-database itcast_ods \
      --hcatalog-table web_chat_ems \
      --hcatalog-storage-stanza 'stored as orc tblproperties ("orc.compress"="ZLIB")' \
      -m 2 \
      --split-by id
      
  • 小结

    • 实现ODS层的构建

知识点09:访问DWD层构建

  • 目标实现访问分析DWD层的构建

  • 路径

    • step1:分析
    • step2:建表
    • step3:构建
  • 实施

    • 分析

      • step1:将两张表合并一张明细表

      • step2:过滤掉不需要的数据:非法的行,用不到的列

      • step3:构造维度

        insert into table itcast_dwd.visit_consult_dwd partition (yearinfo,monthinfo,dayinfo)
        select
           a.create_time,
              ceil(substr(a.create_time,6,2) / 3),
           substr(a.create_time,12,2) as hourinfo,
           a.orgin_channel,
           a.seo_source,
           a.area,
           b.from_url,
           a.sid,
           a.sessionId,
           a.ip,
           a.msg_count,
           substr(a.create_time,0,4) as yearinfo,
        
           substr(a.create_time,6,2) as monthinfo,
           substr(a.create_time,9,2) as dayinfo,
        from  web_chat_ems a join web_chat_text_ems b on a.id = b.id
        where substr(create_time,0,10) = 昨天的日期;
        
    • 建表

      create table if not exists itcast_dwd.visit_consult_dwd(
        session_id STRING comment '七陌sessionId',
        sid STRING comment '访客id',
        create_time bigint comment '会话创建时间',
        seo_source STRING comment '搜索来源',
        ip STRING comment 'IP地址',
        area STRING comment '地域',
        msg_count int comment '客户发送消息数',
        origin_channel STRING COMMENT '来源渠道',
        referrer STRING comment '上级来源页面',
        from_url STRING comment '会话来源页面',
        landing_page_url STRING comment '访客着陆页面',
        url_title STRING comment '咨询页面title',
        platform_description STRING comment '客户平台信息',
        other_params STRING comment '扩展字段中数据',
        history STRING comment '历史访问记录',
        hourinfo string comment '小时',
        quarterinfo string comment '季度'
      )
      comment '访问咨询DWD表'
      partitioned by(yearinfo String, monthinfo String, dayinfo string)
      row format delimited fields terminated by '\t'
      stored as orc
      location '/user/hive/warehouse/itcast_dwd.db/visit_consult_dwd'
      tblproperties ('orc.compress'='SNAPPY');
      
    • 构建

      --本地模式
      set hive.exec.mode.local.auto=true;
      --动态分区配置
      set hive.exec.dynamic.partition=true;
      set hive.exec.dynamic.partition.mode=nonstrict;
      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;
      
      
      insert into table itcast_dwd.visit_consult_dwd partition (yearinfo, monthinfo, dayinfo)
      select
          wce.session_id,
          wce.sid,
          unix_timestamp(wce.create_time, 'yyyy-MM-dd HH:mm:ss.SSS') as create_time,
          wce.seo_source,
          wce.ip,
          wce.area,
          cast(if(wce.msg_count is null, 0, wce.msg_count) as int) as msg_count,
          wce.origin_channel,
          wcte.referrer,
          wcte.from_url,
          wcte.landing_page_url,
          wcte.url_title,
          wcte.platform_description,
          wcte.other_params,
          wcte.history,
          substr(wce.create_time, 12, 2) as hourinfo,
          ceil(substr(wce.create_time, 6, 2) / 3.0) as quarterinfo,
          substr(wce.create_time, 1, 4) as yearinfo,
          substr(wce.create_time, 6, 2) as monthinfo,
          substr(wce.create_time, 9, 2) as dayinfo
      from itcast_ods.web_chat_ems wce inner join itcast_ods.web_chat_text_ems wcte
      on wce.id = wcte.id;
      
      • if语法

        if(条件,true的结果,false的结果)
        
  • 小结

    • 实现DWD层的构建

知识点10:访问DWS层构建

  • 目标:实现DWS层的构建

  • 路径

    • step1:分析
    • step2:建表
    • step3:构建
  • 实施

    • 分析

      • 不同维度下的访问用户量

      • DWD

        时间维度	来源渠道	搜索来源	地区	来源页面	userid		sessionid		ip
        
      • DWS

        时间	地区	搜索来源	来源渠道	来源页面	UV			Session		IP		flag1	flag2
        
      • 分组聚合

        • 按照对应的维度进行分组聚合得到指标,并进行标记维度即可
    • 建表

      CREATE TABLE IF NOT EXISTS itcast_dws.visit_dws (
        sid_total INT COMMENT '根据sid去重求count',
        sessionid_total INT COMMENT '根据sessionid去重求count',
        ip_total INT COMMENT '根据IP去重求count',
        area STRING COMMENT '区域信息',
        seo_source STRING COMMENT '搜索来源',
        origin_channel STRING COMMENT '来源渠道',
        hourinfo STRING COMMENT '创建时间,统计至小时',
        quarterinfo STRING COMMENT '季度',
        time_str STRING COMMENT '时间明细',
        from_url STRING comment '会话来源页面',
        groupType STRING COMMENT '产品属性类型:1.地区;2.搜索来源;3.来源渠道;4.会话来源页面;5.总访问量',
        time_type STRING COMMENT '时间聚合类型:1、按小时聚合;2、按天聚合;3、按月聚合;4、按季度聚合;5、按年聚合;')
      comment 'EMS访客日志dws表'
      PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
      ROW FORMAT DELIMITED
      FIELDS TERMINATED BY '\t'
      stored as orc
      location '/user/hive/warehouse/itcast_dws.db/visit_dws'
      TBLPROPERTIES ('orc.compress'='SNAPPY');
      
    • 构建

      • 时间维度

        • 每个小时的用户总个数、会话总个数及IP总个数

          --本地模式
          set hive.exec.mode.local.auto=true;
          --动态分区配置
          set hive.exec.dynamic.partition=true;
          set hive.exec.dynamic.partition.mode=nonstrict;
          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;
          
          insert into table itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select 
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              '-1' as seo_source,
              '-1' as origin_channel,
              hourinfo,
              quarterinfo,
              concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
              '-1' as from_url,
              '5' as grouptype,
              '1' as time_type,
              yearinfo, monthinfo, dayinfo
          from itcast_dwd.visit_consult_dwd
          where concat(yearinfo,monthinfo,dayinfo)='20190701'
          group by yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;
          
        • 每天的用户总个数、会话总个数及IP总个数

          insert into table itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              '-1' as seo_source,
              '-1' as origin_channel,
              '-1' as hourinfo,
              quarterinfo,
              concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
              '-1' as from_url,
              '5' as grouptype,
              '2' as time_type,
              yearinfo, monthinfo, dayinfo
          from itcast_dwd.visit_consult_dwd
          where concat(yearinfo,monthinfo,dayinfo)='20190701'
          group by yearinfo, quarterinfo, monthinfo, dayinfo;
          
        • 每个月的用户总个数、会话总个数及IP总个数

          insert into table itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              '-1' as seo_source,
              '-1' as origin_channel,
              '-1' as hourinfo,
              quarterinfo,
              concat(yearinfo,'-',monthinfo) as time_str,
              '-1' as from_url,
              '5' as grouptype,
              '3' as time_type,
              yearinfo, monthinfo,
              '-1' as dayinfo
          from itcast_dwd.visit_consult_dwd
          where concat(yearinfo,monthinfo,dayinfo)='20190701'
          group by yearinfo, quarterinfo, monthinfo;
          
        • 每个季度的用户总个数、会话总个数及IP总个数

          insert into itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              '-1' as seo_source,
              '-1' as origin_channel,
              '-1' as hourinfo,
              quarterinfo,
              concat(yearinfo,'-Q',quarterinfo) as time_str,
              '-1' as from_url,
              '5' as grouptype,
              '4' as time_type,
              yearinfo,
              '-1' as monthinfo,
              '-1' as dayinfo
          from itcast_dwd.visit_consult_dwd
          where concat(yearinfo,monthinfo,dayinfo)='20190701'
          group by yearinfo, quarterinfo;
          
          
        • 每年的用户总个数、会话总个数及IP总个数

          INSERT  INTO TABLE itcast_dws.visit_dws PARTITION (yearinfo,monthinfo,dayinfo)
          select
             COUNT(DISTINCT wce.sid) as sid_total,
             COUNT(DISTINCT wce.session_id) as sessionid_total,
             COUNT(DISTINCT wce.ip) as ip_total,
             '-1' as  area,
             '-1' as seo_source,
             '-1' as origin_channel,
             '-1' as hourinfo,
             '-1' as quarterinfo,
             wce.yearinfo as time_str,
             '-1' as from_url,
             '5' as groupType,
             '5' as time_type,
             wce.yearinfo as yearinfo,
             '-1' as monthinfo,
             '-1' as dayinfo
          from itcast_dwd.visit_consult_dwd wce
          where concat(yearinfo,monthinfo,dayinfo)='20190701'
          group by wce.yearinfo;
          
      • 地区+时间维度

        • 每个地区每个小时维度下的用户总个数、会话总个数及IP总个数

          --分区
          set hive.exec.mode.local.auto=true;
          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;
          
          insert into table itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select
              count(distinct sid)        as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip)         as ip_total,
              area,
              '-1' as seo_source,
              '-1' as origin_channel,
              hourinfo,
              quarterinfo,
              concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
              '-1' as from_url,
              '1' as grouptype,
              '1' as time_type,
              yearinfo, monthinfo, dayinfo
          from itcast_dwd.visit_consult_dwd
          where concat(yearinfo,monthinfo,dayinfo)='20190701' and instr(area,"中国") > 0
          group by area, yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;
          
          • instr:用于在一个字符串中查找一个子串,如果有,就返回这个子串位置
            • str1:itcast
            • str2:ca
            • instr(str1,str2) = 3
        • 每个地区每天维度下的用户总个数、会话总个数及IP总个数

          insert into itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select 
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              area,
              '-1' as seo_source,
              '-1' as origin_channel,
              '-1' as hourinfo,
              quarterinfo,
              concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
              '-1' as from_url,
              '1' as grouptype,
              '2' as time_type,
              yearinfo, monthinfo, dayinfo
          from itcast_dwd.visit_consult_dwd 
          where concat(yearinfo,monthinfo,dayinfo)='20190701' and instr(area,"中国") > 0
          group by area, yearinfo, quarterinfo, monthinfo, dayinfo;
          
        • 每个地区每个月维度下的用户总个数、会话总个数及IP总个数

          insert into itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select 
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              area,
              '-1' as seo_source,
              '-1' as origin_channel,
              '-1' as hourinfo,
              quarterinfo,
              concat(yearinfo,'-',monthinfo) as time_str,
              '-1' as from_url,
              '1' as grouptype,
              '3' as time_type,
              yearinfo, monthinfo,
              '-1' as dayinfo
          from itcast_dwd.visit_consult_dwd 
          where concat(yearinfo,monthinfo,dayinfo)='20190701' and instr(area,"中国") > 0
          group by area, yearinfo, quarterinfo, monthinfo;
          
        • 每个地区每个季度维度下的用户总个数、会话总个数及IP总个数

          insert into itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select 
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              area,
              '-1' as seo_source,
              '-1' as origin_channel,
              '-1' as hourinfo,
              quarterinfo,
              concat(yearinfo,'-Q',quarterinfo) as time_str,
              '-1' as from_url,
              '1' as grouptype,
              '4' as time_type,
              yearinfo,
              '-1' as monthinfo,
              '-1' as dayinfo
          from itcast_dwd.visit_consult_dwd 
          where concat(yearinfo,monthinfo,dayinfo)='20190701' and instr(area,"中国") > 0
          group by area, yearinfo, quarterinfo;
          
          
        • 每个地区每年维度下的用户总个数、会话总个数及IP总个数

          INSERT  INTO TABLE itcast_dws.visit_dws PARTITION (yearinfo,monthinfo,dayinfo)
          select 
             COUNT(DISTINCT wce.sid) as sid_total,
             COUNT(DISTINCT wce.session_id) as sessionid_total,
             COUNT(DISTINCT wce.ip) as ip_total,
             wce.area as area,
             '-1' as seo_source,
             '-1' as origin_channel,
             '-1' as hourinfo,
             '-1' as quarterinfo,
             wce.yearinfo as time_str,
             '-1' as from_url,
             '1' as groupType,
             '5' as time_type,
             wce.yearinfo as yearinfo,
             '-1' as monthinfo,
             '-1' as dayinfo
          from itcast_dwd.visit_consult_dwd wce
          where concat(yearinfo,monthinfo,dayinfo)='20190701' and instr(area,"中国") > 0
          group by wce.area,wce.yearinfo;
          
      • 搜索来源+时间维度

        • 每个搜索来源中每个小时的用户总个数、会话总个数及IP总个数

          insert into itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select 
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              seo_source,
              '-1' as origin_channel,
              hourinfo,
              quarterinfo,
              concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
              '-1' as from_url,
              '2' as grouptype,
              '1' as time_type,
              yearinfo, monthinfo, dayinfo
          from itcast_dwd.visit_consult_dwd 
          where concat(yearinfo,monthinfo,dayinfo)='20190701' and length(seo_source) > 0
          group by seo_source, yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;
          
        • 每个搜索来源中每天的用户总个数、会话总个数及IP总个数

          insert into itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select 
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              seo_source,
              '-1' as origin_channel,
              '-1' as hourinfo,
              quarterinfo,
              concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
              '-1' as from_url,
              '2' as grouptype,
              '2' as time_type,
              yearinfo, monthinfo, dayinfo
          from itcast_dwd.visit_consult_dwd 
          where concat(yearinfo,monthinfo,dayinfo)='20190701' and length(seo_source) > 0
          group by seo_source, yearinfo, quarterinfo, monthinfo, dayinfo;
          
        • 每个搜索来源中每个月的用户总个数、会话总个数及IP总个数

        • 每个搜索来源中每个季度的用户总个数、会话总个数及IP总个数

        • 每个搜索来源中每年的用户总个数、会话总个数及IP总个数

      • 来源渠道+时间维度

        • 每个来源渠道中每个小时的用户总个数、会话总个数及IP总个数

          insert into itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              '-1' as seo_source,
              origin_channel,
              hourinfo,
              quarterinfo,
              concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
              '-1' as from_url,
              '3' as grouptype,
              '1' as time_type,
              yearinfo, monthinfo, dayinfo
          from itcast_dwd.visit_consult_dwd
          where concat(yearinfo,monthinfo,dayinfo)='20190701'
          group by origin_channel, yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;
          
        • 每个来源渠道中每天的用户总个数、会话总个数及IP总个数

          insert into itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              '-1' as seo_source,
              origin_channel,
              '-1' as hourinfo,
              quarterinfo,
              concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
              '-1' as from_url,
              '3' as grouptype,
              '2' as time_type,
              yearinfo, monthinfo, dayinfo
          from itcast_dwd.visit_consult_dwd
          where concat(yearinfo,monthinfo,dayinfo)='20190701'
          group by origin_channel, yearinfo, quarterinfo, monthinfo, dayinfo;
          
        • 每个来源渠道中每个月的用户总个数、会话总个数及IP总个数

        • 每个来源渠道中每个季度的用户总个数、会话总个数及IP总个数

        • 每个来源渠道中每年的用户总个数、会话总个数及IP总个数

      • 来源页面+时间维度

        • 每个来源页面中每个小时的用户总个数、会话总个数及IP总个数

          insert into itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select 
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              '-1' as seo_source,
              '-1' as origin_channel,
              hourinfo,
              quarterinfo,
              concat(yearinfo,'-',monthinfo,'-',dayinfo,' ',hourinfo) as time_str,
              from_url,
              '4' as grouptype,
              '1' as time_type,
              yearinfo, monthinfo, dayinfo
          from itcast_dwd.visit_consult_dwd 
          where concat(yearinfo,monthinfo,dayinfo)='20190701' and length(from_url) > 0
          group by from_url, yearinfo, quarterinfo, monthinfo, dayinfo, hourinfo;
          
        • 每个来源页面中每天的用户总个数、会话总个数及IP总个数

          insert into itcast_dws.visit_dws partition (yearinfo, monthinfo, dayinfo)
          select 
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              '-1' as seo_source,
              '-1' as origin_channel,
              '-1' as hourinfo,
              quarterinfo,
              concat(yearinfo,'-',monthinfo,'-',dayinfo) as time_str,
              from_url,
              '4' as grouptype,
              '2' as time_type,
              yearinfo, monthinfo, dayinfo
          from itcast_dwd.visit_consult_dwd 
          where concat(yearinfo,monthinfo,dayinfo)='20190701' and length(from_url) > 0
          group by from_url, yearinfo, quarterinfo, monthinfo, dayinfo;
          
        • 每个来源页面中每个月的用户总个数、会话总个数及IP总个数

        • 每个来源页面中每个季度的用户总个数、会话总个数及IP总个数

        • 每个来源页面中每年的用户总个数、会话总个数及IP总个数

  • 小结

    • 实现DWS层的构建

知识点11:访问APP层构建

  • 目标实现APP层的构建

  • 路径

    • step1:分析
    • step2:建表
    • step3:构建
  • 实施

    • 分析

      • 应用的工具需要从MySQL数据中读取结果进行报表应用
      • 将Hive中分析的结果导出到MySQL
    • 建表:MySQL

      create database if not exists scrm_bi;
      use scrm_bi;
      drop table if exists itcast_visit;
      
      CREATE TABLE `itcast_visit` (
        sid_total int(11) COMMENT '根据sid去重求count',
        sessionid_total int(11) COMMENT '根据sessionid去重求count',
        ip_total int(11) COMMENT '根据IP去重求count',
        area varchar(32) COMMENT '区域信息',
        seo_source varchar(32) COMMENT '搜索来源',
        origin_channel varchar(32) COMMENT '来源渠道',
        hourinfo varchar(32) COMMENT '小时信息',
        quarterinfo varchar(32) COMMENT '季度',
        time_str varchar(32) COMMENT '时间明细',
        from_url varchar(2083) comment '会话来源页面',
        groupType varchar(32) COMMENT '产品属性类型:1.地区;2.搜索来源;3.来源渠道;4.会话来源页面;5.不考虑',
        time_type varchar(32) COMMENT '时间聚合类型:1、按小时聚合;2、按天聚合;3、按月聚合;4、按季度聚合;5、按年聚合;',
        yearinfo varchar(32) COMMENT '年信息',
        monthinfo varchar(32) COMMENT '月信息',
        dayinfo varchar(32) COMMENT '日信息'
      )ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
      
    • 构建

      sqoop export \
      --connect "jdbc:mysql://node3:3306/scrm_bi?useUnicode=true&characterEncoding=utf-8" \
      --username root \
      --password '123456' \
      --driver com.mysql.jdbc.Driver \
      --table itcast_visit \
      --hcatalog-database itcast_dws \
      --hcatalog-table visit_dws \
      -m 1
      
  • 小结

    • 实现APP层的构建

知识点12:咨询业务及ODS、DWD构建

  • 目标了解咨询业务需求

  • 路径

    • step1:需求
    • step2:数据
    • step3:ODS与DWD构建
  • 实施

    • 需求:基于不同维度统计咨询数据指标

      • 指标:咨询人数,咨询Session个数、咨询IP个数
      • 维度:时间、地区、来源渠道、搜索来源、来源页面
    • 数据

      • 客服系统记录的每个访问的访问信息,包含了咨询数据

      • 与访问分析的区别是什么?

        • 访问的数据中包含了咨询的数据

        • 咨询了,就一定访问了

        • 访问了,不一定咨询了

        • 咨询分析:只对咨询的数据进行处理

      • 如何辨别用户是否咨询了?

        • msg_count:用于区分这条数据是咨询还是访问数据
        • 访问数据:0
        • 咨询数据:>0
    • ODS与DWD构建

      • 直接复用访问分析的ODS与DWD
  • 小结

    • 了解咨询业务分析的需求

知识点13:咨询DWS层构建

  • 目标实现咨询DWS的构建

  • 路径

    • step1:分析
    • step2:建表
    • step3:实现
  • 实施

    • 分析

      • 基本与访问的DWS是一致的
      • 区别
        • 访问DWS:基于所有数据进行分组聚合
        • 咨询DWS:基于咨询数据进行分组聚合
        • msg_count > 0
    • 建表

      set hive.exec.orc.compression.strategy=COMPRESSION;
      CREATE TABLE IF NOT EXISTS itcast_dws.consult_dws
      (
        sid_total INT COMMENT '根据sid去重求count',
        sessionid_total INT COMMENT '根据sessionid去重求count',
        ip_total INT COMMENT '根据IP去重求count',
        area STRING COMMENT '区域信息',
        origin_channel STRING COMMENT '来源渠道',
        hourinfo STRING COMMENT '创建时间,统计至小时',
        quarterinfo 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
      LOCATION '/user/hive/warehouse/itcast_dws.db/consult_dws'
      TBLPROPERTIES ('orc.compress'='SNAPPY');
      
    • 实现

      • 时间维度

        • 每个小时的咨询用户指标

          --动态分区配置
          set hive.exec.dynamic.partition=true;
          set hive.exec.max.dynamic.partitions=2000;
          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;
          --小时
          insert into itcast_dws.consult_dws partition (yearinfo, monthinfo, dayinfo)
          select
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              '-1' as origin_channel,
              hourinfo,
              quarterinfo,
              concat_ws('-',yearinfo,monthinfo,dayinfo) as time_str,
              '5',
              '1',
              yearinfo,monthinfo,dayinfo
          from itcast_dwd.visit_consult_dwd
          where msg_count >= 1 and concat(yearinfo,monthinfo,dayinfo)='20190701' 
          group by yearinfo, quarterinfo, monthinfo, dayinfo,hourinfo;
          
        • 每天咨询访问量

          insert into itcast_dws.consult_dws partition (yearinfo, monthinfo, dayinfo)
          select
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              '-1' as origin_channel,
              '-1' as hourinfo,
              quarterinfo,
              concat_ws('-',yearinfo,monthinfo,dayinfo) as time_str,
              '5',
              '2',
              yearinfo,monthinfo,dayinfo
          from itcast_dwd.visit_consult_dwd
          where msg_count >= 1 and concat(yearinfo,monthinfo,dayinfo)='20190701' 
          group by yearinfo, quarterinfo, monthinfo, dayinfo;
          
      • 地区+时间维度

        • 每个地区每天的咨询访问指标

          insert into itcast_dws.consult_dws partition (yearinfo, monthinfo, dayinfo)
          select
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              area,
              '-1' as origin_channel,
              '-1' as hourinfo,
              quarterinfo,
              concat_ws('-',yearinfo,monthinfo,dayinfo) as time_str,
              '1',
              '2',
              yearinfo,monthinfo,dayinfo
          from itcast_dwd.visit_consult_dwd
          where msg_count >= 1 and concat(yearinfo,monthinfo,dayinfo)='20190701' and instr(area,"中国") > 0
          group by area,yearinfo, quarterinfo, monthinfo, dayinfo;
          
      • 来源渠道+时间维度

        • 每小时各来源渠道的咨询用户指标

          insert into itcast_dws.consult_dws partition (yearinfo, monthinfo, dayinfo)
          select
              count(distinct sid) as sid_total,
              count(distinct session_id) as sessionid_total,
              count(distinct ip) as ip_total,
              '-1' as area,
              origin_channel,
              '-1' as hourinfo,
              quarterinfo,
              concat_ws('-',yearinfo,monthinfo,dayinfo) as time_str,
              '3',
              '2',
              yearinfo,monthinfo,dayinfo
          from itcast_dwd.visit_consult_dwd
          where msg_count >= 1 and concat(yearinfo,monthinfo,dayinfo)='20190701'
          group by origin_channel,yearinfo, quarterinfo, monthinfo, dayinfo;
          
  • 小结

    • 实现DWS层的构建

知识点14:咨询APP层构建

  • 目标

  • 路径

    • step1:分析
    • step2:建表
    • step3:实现
  • 实施

    • 分析

      • 将咨询的结果从Hive中导出到MySQL中
    • 建表

      use scrm_bi;
      drop table if exists itcast_consult;
      
      CREATE TABLE `itcast_consult` (
        sid_total int(11) COMMENT '根据sid去重求count',
        sessionid_total int(11) COMMENT '根据sessionid去重求count',
        ip_total int(11) COMMENT '根据IP去重求count',
        area varchar(32) COMMENT '区域信息',
        origin_channel varchar(32) COMMENT '来源渠道',
        hourinfo varchar(32) COMMENT '小时信息',
        quarterinfo varchar(32) COMMENT '季度',
        time_str varchar(32) COMMENT '时间明细',
        groupType varchar(32) COMMENT '产品属性类型:1.地区;2.搜索来源;3.来源渠道;4.会话来源页面;5.不考虑',
        time_type varchar(32) COMMENT '时间聚合类型:1、按小时聚合;2、按天聚合;3、按月聚合;4、按季度聚合;5、按年聚合;',
        yearinfo varchar(32) COMMENT '年信息',
        monthinfo varchar(32) COMMENT '月信息',
        dayinfo varchar(32) COMMENT '日信息'
      )ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
      
    • 实现

      sqoop export \
      --connect "jdbc:mysql://node3:3306/scrm_bi?useUnicode=true&characterEncoding=utf-8" \
      --username root \
      --password 123456 \
      --driver com.mysql.jdbc.Driver \
      --table itcast_consult \
      --hcatalog-database itcast_dws \
      --hcatalog-table consult_dws \
      -m 1
      
  • 小结

    • 实现咨询APP层的构建

知识点15:可视化方案

  • 目标:了解常见的可视化方案
  • 实施
    • 开源工具
      • 开源的工具:不想花钱,快速的使用可视化平台
      • SuperSet等工具
      • 优点:免费,大多的图标都支持
      • 缺点:bug比较多,定制型的需求比较差
    • 商业化工具
      • 商业化BI平台:有钱,快速构建
      • FineBI 、PowerBI、Tableau
      • 优点:图表非常全面美观,上手非常快、定制化程序相对比较高,比较稳定
      • 缺点:要钱
    • 自定义开发
      • 场景:希望高度定制化、有钱、不着急
      • 自己开发:Java
      • Echarts或者Highcharts等工具
      • 优点:定制化程度非常高,免费,图表支持非常全面
      • 缺点:开发周期比较长
  • 小结
    • 了解常用的可视化方案

知识点16:FineBI的介绍及部署

  • 目标:了解FineBI的功能及实现FineBI的部署
  • 实施
    • 参考部署文档部署即可
   use scrm_bi;
   drop table if exists itcast_consult;
   
   CREATE TABLE `itcast_consult` (
     sid_total int(11) COMMENT '根据sid去重求count',
     sessionid_total int(11) COMMENT '根据sessionid去重求count',
     ip_total int(11) COMMENT '根据IP去重求count',
     area varchar(32) COMMENT '区域信息',
     origin_channel varchar(32) COMMENT '来源渠道',
     hourinfo varchar(32) COMMENT '小时信息',
     quarterinfo varchar(32) COMMENT '季度',
     time_str varchar(32) COMMENT '时间明细',
     groupType varchar(32) COMMENT '产品属性类型:1.地区;2.搜索来源;3.来源渠道;4.会话来源页面;5.不考虑',
     time_type varchar(32) COMMENT '时间聚合类型:1、按小时聚合;2、按天聚合;3、按月聚合;4、按季度聚合;5、按年聚合;',
     yearinfo varchar(32) COMMENT '年信息',
     monthinfo varchar(32) COMMENT '月信息',
     dayinfo varchar(32) COMMENT '日信息'
   )ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  • 实现

    sqoop export \
    --connect "jdbc:mysql://node3:3306/scrm_bi?useUnicode=true&characterEncoding=utf-8" \
    --username root \
    --password 123456 \
    --driver com.mysql.jdbc.Driver \
    --table itcast_consult \
    --hcatalog-database itcast_dws \
    --hcatalog-table consult_dws \
    -m 1
    
  • 小结

    • 实现咨询APP层的构建

知识点15:可视化方案

  • 目标:了解常见的可视化方案
  • 实施
    • 开源工具
      • 开源的工具:不想花钱,快速的使用可视化平台
      • SuperSet等工具
      • 优点:免费,大多的图标都支持
      • 缺点:bug比较多,定制型的需求比较差
    • 商业化工具
      • 商业化BI平台:有钱,快速构建
      • FineBI 、PowerBI、Tableau
      • 优点:图表非常全面美观,上手非常快、定制化程序相对比较高,比较稳定
      • 缺点:要钱
    • 自定义开发
      • 场景:希望高度定制化、有钱、不着急
      • 自己开发:Java
      • Echarts或者Highcharts等工具
      • 优点:定制化程度非常高,免费,图表支持非常全面
      • 缺点:开发周期比较长
  • 小结
    • 了解常用的可视化方案

知识点16:FineBI的介绍及部署

  • 目标:了解FineBI的功能及实现FineBI的部署
  • 实施
    • 参考部署文档部署即可
上一篇:Ollydbg 中断方法浅探


下一篇:MyBatis入门