实训笔记8.31

实训笔记8.31

• 8.31笔记
• • 一、项目开发流程一共分为七个阶段
  • 二、项目数据清洗预处理的实现
  • 三、项目的数据统计分析阶段
  • • 3.1 概念和技术选项
    • 3.2 Hive数据仓库进行统计分析时两个核心概念
    • 3.3 数据统计分析的实现（最好把所有的HQL代码写到一个SQL文件中，最后统一执行运行） 统计分析必须启动HDFS和YARN
    • 3.4 统计分析部署和运行
  • 四、项目的数据迁移导出阶段
  • 五、项目的数据可视化阶段
  • 六、【项目补充点】
  • 七、相关代码

8.31笔记

一、项目开发流程一共分为七个阶段

1.1 数据产生阶段

1.2 数据采集存储阶段

1.3 数据清洗预处理阶段

1.4 数据统计分析阶段

1.5 数据迁移导出阶段

1.6 数据可视化阶段

二、项目数据清洗预处理的实现

2.1 清洗预处理规则

1. 一条用户行为数据如果字段个数不足16，那么数据不完整，舍弃

2. 一条用户行为数据中如果响应状态码大于等于400的，那么数据访问错误，舍弃

3. 一条用户行为数据中省份 纬度 经度 年龄以-填充的，那么代表数据缺失，舍弃

4. 预处理规则：清洗完成的数据中最后在输出时，有很多的字段我们不需要的，因此我们需要对部分数据进行舍弃，对需要保留的字段数据以\001特殊字符分割输出

技术选项：MapReduce技术

2.2 代码实现

1. 创建Maven项目，引入MR的编程依赖
2. 编写MR程序的Mapper程序和Driver驱动程序
3. 在本地测试运行无问题之后，需要将代码打成jar包上传到大数据环境中在YARN上运行 必须启动YARN

三、项目的数据统计分析阶段

3.1 概念和技术选项

统计分析就是基于我们清洗预处理完成的高质量，从不同的数据纬度聚合数据，或者对数据进行计算得到我们感兴趣的一些指标或者是对网站运营发展有关的一些指标。

统计分析进行数据计算时，可能涉及到大量的聚合操作以及一些排名、排序等等操作，而这些操作也都是数据计算，那么我们就可以使用大数据计算框架完成，而大数据计算框架MapReduce如果要聚合、排序、分组等操作，MR代码就会非常的复杂。因此我们一般做统计分析时有一个想法，既能计算大量的数据，还能快速简单的进行数据的聚合、排名、分组等操作。就可以使用Hive数据仓库技术完成。

3.2 Hive数据仓库进行统计分析时两个核心概念

3.1.1 数据仓库分层

数据仓库建模是用来梳理表和表之间的关系的，便于我们后期进行统计分析。数据仓库分层是我们使用数据仓库进行统计分析的开发流程。

数据仓库分层从最底层开始到最高层主要有如下三层（不同的公司基于三层更加细致的分层）

1. ODS层（数据贴源层）

如果我们要使用Hive数据仓库做统计分析，首先我们需要把清洗预处理完成的数据导入到Hive中加载成为一个数据表，ODS层指的就是把清洗预处理完成的数据原模原样的导入到Hive中，导入进来之后这些表组成了ODS层

1. DW层（数据仓库层）–Hive统计分析的核心 数据仓库建模的阶段

   1. DWD层（明细宽表层）

      1. 把ODS层的数据表可以再次处理一下构建成为一个明细宽表、
      2. 明细宽表一般会把ODS层的字段拆分成更加细粒度的字段，便于我们后期好做统计分析（时间字段）

   2. DIM层（纬表层）

纬度表如果比较多，那么纬度表单独划分到DW的DIM层

1. ADS层（数据应用层）

将统计分析的结果以指标表的形式存储到ADS层

3.2.2 数据仓库建模

建模的目的是为了方便我们后期统计分析

在使用Hive进行数据统计分析时，首先必须先把清洗预处理完成的数据加载到Hive中成为数据表，而且一般在真实的企业项目中，清洗预处理完成数据不止一个，各种各样的数据，数据和数据之间都是有关系的。

所谓的数据仓库建模就是我们在对数据进行清洗预处理的时候，清洗预处理完成之后的多个数据之间的关系梳理建模

1. 数据仓库建模的名词解释

   1. 事实表：一张表中基本全都是外键，如果我们需要查询数据，需要将这个表和各个对应的其它数据表进行关联查询才能得到我们想要的数据 订单表
   2. 维度表：事实表中外键对应的详细信息存储的表，而且他也是我们统计分析时纬度信息 用户表 商品表

2. 数据仓库模型建立有很多种方式的，主要分为

   1. 3NF数据仓库建模

   2. 纬度建模

      1. 星型模型

         事实表直接与纬度表关联，而且只有一级关联

      2. 雪花模型

         事实表直接与维度表关联，纬度表拆分出更加细致的一些纬度表

      3. 星座模型

         在一个数仓中，事实表有多个，每一个事实表都有它自己对应的纬度表，纬度表还有它的二级纬度表

如何完成建模？数据清洗预处理的时候，把数据处理成为合适的模型结构

3.3 数据统计分析的实现（最好把所有的HQL代码写到一个SQL文件中，最后统一执行运行） 统计分析必须启动HDFS和YARN

3.3.1 构建ODS层

ODS层指的是我们把清洗预处理完成的数据不加以任何的处理，直接原模原样的在Hive中构建与之对应的表格，并且把数据装载到表格当中

清洗预处理完成的数据格式以\001特殊字符分割的，这样的话可以避免分隔符和字段的中一些符号冲突，导致装载数据到Hive出现串行的问题。

Hive中数据表有很多分类的：内部表、外部表、分区表、分桶表

3.3.2 构建DWD层

DWD明细宽表层就是把ODS层的数据表字段拆分成为更加细粒度的字段，便于我们后期的统计分析。 DWD层说白了就是在ODS的数据表基础之上在多增加一些冗余字段，但是方便我们后期操作了

1. ODS层的字段如下：

1. 可以拆分的字段主要有两个

   1. 时间字段：后期需要基于细粒度的时间做统计分析

      年

      月

      日

      时

   2. 来源URL字段：后期统计站内站外的流量占比，站内站外的对比是基于HOST主机名/域名——HOST

DWD层这个数据表就属于我们Hive的自有表了，因此明细宽表我们构建成为内部分区表即可

明细宽表中没有数据，明细宽表中的数据从什么地方来？因为DWD层是基于ODS层建立的，因此DWD层的数据需要从ODS层查询获得。 需要从ODS层对应的数据表中查询指定的数据添加到DWD层当中（注意一下分区的问题）。

3.3.3 构建ADS层

ADS层其实就是我们基于DW数据仓库库构建的DWD和DIM层的数据表，进行查询，通过聚合、分组、排序等等操作统计相关的指标，得到指标数据，然后将指标数据存储到一个Hive数据表中。

1. 基于时间纬度的指标

   1. 统计网站每年的用户的流量

      网站每天都会产生数据，每一天数据一增加，那么当前年份的用户访问量必然增加一天的数据

      思路：不是针对明细宽表某一个分区的数据进行统计分析，而是针对于明细宽表中整体数据集进行统计分析（所有的分区进行操作）

      实现：因为在明细宽表中已经拆分除了visit_year字段，因此我们只需要根据visit_year分区聚合数据即可得到，每一年的用户访问量

      select visit_year,count(*) from dwd_user_behavior_detail group by visit_year;

   2. 统计网站每一年不同月份的用户流量

      1. 实现同上
      2. 区别：分组时，需要根据年和月来分组

   3. 统计网站每一年不同月份下每天用户的访问量

      1. 实现同上
      2. 区别：分组的时候，需要根据年、月、日三个字段来分组

   4. 统计网站每一年不同月份下每天的每小时用户的访问量

      1. 实现同上
      2. 区别：分组的时候，需要根据年、月、日、时四个字段来分组

   5. 统计网站每一年每一个月的流量相比于上个月的比例：开窗函数（上边界和下边界），针对每一年不同月份的用户流量指标的二次分析结果

      select 
      	temp.*,
      	concat(round(temp.flow/temp.before_month_flow,1)*100,"%") as rate
      from  (
      	select 
      	  * ,
      	  first_value(flow) over(partition by visit_year order by visit_month asc rows between 1 PRECEDING and CURRENT ROW) as before_month_flow
      	 from ads_month_flow
      ) as temp
      

2. 基于地理纬度的指标

   1. 统计网站不同省份每天用户的流量

      1. 数据统计分析每天执行一次，每天都要统计不同省份在当天的用户流量占比情况
      2. 统计两种方式
         1. 针对明细宽表的数据集整体进行聚合统计
            1. 上面这种方式不太友好，8.31号我要统计，按道理来说只需要统计8.30号采集的数据即可，8.29号的数据不需要统计了
            2. 但是如果针对数据集整体统计的话，8.29号的结果会重新计算一遍
            3. 指标表添加数据时需要覆盖添加
         2. 只针对当前时间分区的数据进行统计
            1. 节省资源
            2. 指标表的数据需要追加添加即可

   2. 统计网站不同省份每月/每年用户流量

      针对的就是数据集整体 而非某一个分区

   3. 每天访问量TOP10的省份

      针对的不是明细宽表 而是我们的前面统计不同省份每天用户流量指标（二次分析），指标统计出来之后需要覆盖添加

      需要使用排名函数

      ads_province_day_flow

      select  temp.date_time, temp.province, temp.flow from{ select    *, row_number() over(partition by data_time order by flow desc) as rank_num from ads_province_day_flow } as temp where temp.rank_num <=10;
      

3. 基于用户纬度的指标

   1. 统计网站不同年龄段用户的流量

      明细宽表当中，存在一个字段代表的是用户的年龄，而用户年龄都是大于等于18岁，小于100岁。

      基于年龄这个字段，我想查看一下网站不同年龄段的用户情况

      用户年龄段

      1. 青年：18-44
      2. 中年：45-59
      3. 中老年：60-79
      4. 老年：80岁以上

      需要用到hive中的分支函数

   2. 统计网站每年/每月/每天的不同年龄段的用户访问量

   3. 统计每天网站的独立访客数

      1. 独立访客数其实就是IP地址，一个IP算一个独立访客，只需要把每天的ip地址去重之后求一个总数，得到每天的独立访客数
      2. 针对某一个分区的，指标数据就是追加

   4. 统计网站每月、每年的独立访问数

      针对数据集整体了

4. 基于终端纬度的指标

   1. 统计网站用户使用的不同浏览器的占比情况

      1. 用户行为数据中有一个字段user_agent，user_agent当中就包含着我们用户使用的浏览器信息情况，基于这个字段统计统计网站不同浏览器的占比情况
      2. 不统计所有的浏览器，我们只统计一些常见的浏览器的占比 IE、淘宝、火狐、欧朋、QQ浏览器 Safari苹果
      3. 不同年龄段用户访问量指标是类似的

   2. 统计网站不同时间段下的不同浏览器的占比情况

5. 基于来源纬度的指标

   1. 统计网站每天站内和站外的流量占比
      1. 用户行为数据中有一个字段referer_url字段，字段代表的是用户访问网站的来源，来源可能是站内的，可能是站外的，现在统计网站站内和站外的来源流量
      2. 区分站内和站外来源，主要看referer_url中referer_host字段，字段代表来源的域名，如果域名是www.bailongma.com那么代表来源是站内的 如果域名不是白龙马 那么代表来源是站外的针对的是数据集整体，覆盖
   2. 统计网站不同时间段的站内和站外的流量占比
   3. 统计网站不同来源网站的占比

6. 指标有很多，可以进行各种自由扩展

3.4 统计分析部署和运行

我们统计分析也是每天执行一次，我们总不能每天运行统计分析，指标代码我们自己手动挨着运行

我们统计分析需要把所有统计分析代码封装到一个xxx.sql文件中，然后到时候统计分析需要执行，我们直接使用hive -f xxx.sql --hiveconf xxx=xxxx

四、项目的数据迁移导出阶段

4.1 概念

现在我们通过Hive数据仓库做的统计分析指标都是在Hive的ADS层存储着。统计的指标的主要目的是为了指导网站的发展和运营的，因此统计完成的数据其中可以做很多操作：基于统计分析的结果进行二次统计分析；基于统计分析的指标结合相关大数据算法做一些数据预测或者数据的深度挖掘；基于统计分析的结果进行可视化大屏的制作。

我们项目中最终需要把统计分析的结果以图表的形式进行可视化展示。

目前现有的可视化技术基本都不太支持从Hive中直接读取数据然后进行可视化展示，但是这些技术支持从RDBMS（MySQL）中读取数据进行可视化展示。

因此我们做可视化大屏之前，需要把Hive数据仓库中ADS层的数据迁移导出到RDBMS关系型数据库当中，然后再借助大数据技术+RDBMS实现数据可视化展示即可

4.2 数据迁移导出的技术选型

我们就是想把Hive数据仓库中数据导出到RDBMS中，目前只学了SQOOP技术

当然除了Sqoop技术以外，还有一个技术DataX（阿里云提供的数据传输工具）

数据迁移导出的开发实现

要把Hive的数据迁移到MySQL中，SQOOP既可以实现把RDBMS数据迁移到大数据环境（导入），同时也支持把大数据环境数据迁移到RDBMS中（导出）

只需要编写针对性的SQOOP导出数据的命令即可，导出的时候需要注意两个问题：

1. MySQL中的必须提前存在和导出的指标表一致的数据表结构
2. 导出数据时，有些指标数据追加到MySQL中（针对于分区的指标统计）–sqoop默认导出就是追加的，但是有些指标需要覆盖原先的MySQL数据表（针对整体数据集的指标统计）–sqoop支持不良好，可以通过sqoop把原始MySQL数据表清空，然后再导出

五、项目的数据可视化阶段

白龙马大屏

六、【项目补充点】

6.1 模拟其他年份，其他月份，其他日期的数据

只需要通过date -s “时间” 系统时间改成我们想要模拟数据的日期即可

把以前产生的userBehavior.log文件删除了

然后启动采集程序 启动数据模拟程序

处理数据，只需要再把系统时间往后调整一天

七、相关代码

7.1 导出

# shell脚本中需要封装所有和数据导出相关的sqoop命令
# 导出每年用户的流量指标，指标针对的是数据集的整体，所以为了防止数据重复，导出的时候需要覆盖导出
# sqoop对覆盖导出适用还是有点问题，折中的方式：先通过sqoop命令把mysql对应的指标表的数据先清空 再导出
echo "======================导出ads_year_flow指标开始======================"
sqoop eval --driver com.mysql.cj.jdbc.Driver --connect 'jdbc:mysql://gz-cdb-8yj5rfsd.sql.tencentcdb.com:63506/project?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8' --username root --password admin777rmrf --query 'truncate year_flow'

sqoop export --driver com.mysql.cj.jdbc.Driver --connect 'jdbc:mysql://gz-cdb-8yj5rfsd.sql.tencentcdb.com:63506/project?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8' --username root --password admin777rmrf --table 'year_flow' --columns visit_year,flow --export-dir /user/hive/warehouse/project.db/ads_year_flow --input-fields-terminated-by '\001' -m 1

echo "===================== 导出ads_year_flow指标成功======================"

# 导出省份每天的流量 针对的分区数据统计 因此这个指标需要追加导出而非覆盖导出
echo "======================导出ads_province_day_flow指标开始======================"

sqoop export --driver com.mysql.cj.jdbc.Driver --connect 'jdbc:mysql://gz-cdb-8yj5rfsd.sql.tencentcdb.com:63506/project?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8' --username root --password admin777rmrf --table 'province_day_flow' --columns data_time,province,latitude,longitude,flow --export-dir /user/hive/warehouse/project.db/ads_province_day_flow --input-fields-terminated-by '\001' -m 1

echo "===================== 导出ads_province_day_flow指标成功======================"

7.2 生成

7.3 收集

# 1、给Flume进程agent起名别  source  channel sink组件起别名
project.sources=s1
project.channels=c1
project.sinks=k1

# 2、配置source关联的数据源  记录用户行为数据的日志文件/root/project/data-gen/userBehavior.log
project.sources.s1.type=exec
project.sources.s1.command=tail -F /root/project/data-gen/userBehavior.log

# 3、配置channel管道  基于内存的
project.channels.c1.type=memory
project.channels.c1.capacity=20000
project.channels.c1.transactionCapacity=10000
project.channels.c1.byteCapacity=104857600

# 4、配置sink关联的目的地 HDFS  HDFS的目的地是一个基于时间的动态目录
project.sinks.k1.type=hdfs
project.sinks.k1.hdfs.path=hdfs://single:9000/dataCollect/%Y-%m-%d
project.sinks.k1.hdfs.round=true
project.sinks.k1.hdfs.roundValue=24
project.sinks.k1.hdfs.roundUnit=hour
project.sinks.k1.hdfs.filePrefix=data
project.sinks.k1.hdfs.fileSuffix=.log
project.sinks.k1.hdfs.useLocalTimeStamp=true
# 文件滚动设置只基于文件的大小的滚动 不基于event滚动、时间滚动
project.sinks.k1.hdfs.rollInterval=0
project.sinks.k1.hdfs.rollCount=0
project.sinks.k1.hdfs.rollSize=134217728
project.sinks.k1.hdfs.fileType=DataStream

# 5、关联agent的各个组件
project.sources.s1.channels=c1
project.sinks.k1.channel=c1

7.4 清理

#!/bin/bash
hadoop jar /root/project/data-clean/project-clean-pre.jar com.sxuek.DataCleanDriver

7.5 分析

#!/bin/bash
yes=`date -d 'yesterday' +%Y-%m-%d`
echo $yes
hive -f /root/project/data-analy/data_analy.sql --hiveconf yesterday=$yes

-- 0、创建一个项目专属的数据库
create database if not exists project;
use project;
-- 1、构建ODS层的数据表，数据表和清洗预处理完成的数据格式一致的表格 而且ODS层的表格是外部分区表
create external table if not exists ods_user_behavior_origin(
   ip_addr string, --ip地址
   visit_time string,-- 浏览时间
   request_url string,-- 行为触发之后的请求网址
   referer_url string,-- 来源网址
   user_agent string,-- 用户使用的浏览器信息
   province string, --省份
   latitude string, -- 纬度
   longitude string, -- 经度
   age int --年龄
)partitioned by(data_gen_time string)
row format delimited fields terminated by '\001'; 

-- 2、需要将清洗预处理完成的昨天的数据（/dataClean/yyyy-MM-dd）导入到ods层的昨天时间分区中。
load data inpath '/dataClean/${hiveconf:yesterday}' into table ods_user_behavior_origin partition(data_gen_time='${hiveconf:yesterday}');

-- 3、构建DWD明细宽表层  就是在ods数据表基础之上增加了五列字段 visit_year  visit_month  visit_day  visit_hour referer_host
create  table if not exists dwd_user_behavior_detail(
   ip_addr string, --ip地址
   visit_time string,-- 浏览时间
   visit_year string, --拆分的浏览年份
   visit_month string,----拆分的浏览月份
   visit_day string,---拆分的浏览天
   visit_hour string,---拆分的浏览时
   request_url string,-- 行为触发之后的请求网址
   referer_url string,-- 来源网址
   referer_host string, -- 来源网址的域名
   user_agent string,-- 用户使用的浏览器信息
   province string, --省份
   latitude string, -- 纬度
   longitude string, -- 经度
   age int --年龄
)partitioned by(data_gen_time string)
row format delimited fields terminated by '\001'; 

-- 4、从贴源数据表查询明细宽表所需的数据，然后把数据增加到明细宽表的昨天的时间分区中
insert overwrite table dwd_user_behavior_detail partition(data_gen_time='${hiveconf:yesterday}')
select 
	ip_addr,
	visit_time,
	date_format(visit_time,'yyyy') as visit_year,
	date_format(visit_time,'MM') as visit_month,
	date_format(visit_time,'dd') as visit_day,
	date_format(visit_time,'HH') as visit_hour,
	request_url,
	referer_url,
	parse_url(referer_url,'HOST') as referer_host,
	user_agent,
	province,
	latitude,
	longitude,
	age
from ods_user_behavior_origin 
where data_gen_time='${hiveconf:yesterday}';

-- 5、构建数据应用层 统计各种各样的指标数据，并且把指标数据保存到Hive对应的指标表中 指标表的结构必须和我们查询的指标数据一致的
--（1）基于时间纬度--统计网站每年用户的流量
create table if not exists ads_year_flow(
   visit_year string,
   flow bigint
)row format delimited fields terminated by '\001';

-- 将统计的结果覆盖添加到基于年份的指标表中 防止年份流量数据重复
insert overwrite table ads_year_flow
select  visit_year,count(*)  from dwd_user_behavior_detail group by visit_year;


-- （2）统计网站每一年不同月份的用户流量
create table if not exists ads_month_flow(
   visit_year string,
   visit_month string,
   flow bigint
)row format delimited fields terminated by '\001';

insert overwrite table ads_month_flow
select  visit_year,visit_month,count(*)  from dwd_user_behavior_detail group by visit_year,visit_month;


-- （3）基于地理纬度的指标--统计网站不同省份每天用户的流量  针对分区的方式完成
create table if not exists ads_province_day_flow(
   data_time string,
   province string,
   latitude string,
   longitude string,
   flow bigint
)row format delimited fields terminated by '\001';

-- 因为这个指标是针对某一个分区的数据进行的 不用担心和以前统计出来的结果冲突 所以追加即可
insert into table  ads_province_day_flow
select data_gen_time,province,latitude,longitude,count(*) from dwd_user_behavior_detail where data_gen_time='${hiveconf:yesterday}'  group by data_gen_time,province,latitude,longitude;


-- （4）统计网站不同年龄段的用户访问量
create table if not exists ads_age_range_flow(
   youth bigint,
   middle_age bigint,
   middle_elderly_age bigint,
   old_age bigint
)row format delimited fields terminated by '\001';


-- 针对的是数据集整体 每天要执行一次  因此这个指标覆盖添加
insert overwrite table ads_age_range_flow
select 
	sum(if(age>=18 and age <=44,1,0)),
	sum(if(age>=45 and age <=59,1,0)),
	sum(if(age>=60 and age <=79,1,0)),
	sum(if(age>=80,1,0))
FROM dwd_user_behavior_detail;


-- （5）基于用户纬度--统计网站每天的独立访客数
create table if not exists ads_day_uv(
   date_time string,
   num bigint
)row format delimited fields terminated by '\001';


insert into table ads_day_uv
select 
	data_gen_time ,
	count(DISTINCT(ip_addr))
FROM dwd_user_behavior_detail WHERE data_gen_time ="${hiveconf:yesterday}" group by data_gen_time;



-- （6）基于终端纬度--统计网站不同浏览器的使用占比
create table if not exists ads_user_agent_flow(
   user_agent string,
   flow bigint
)row format delimited fields terminated by '\001';


insert overwrite table ads_user_agent_flow
select "IE",count(1) from dwd_user_behavior_detail where user_agent like '%MSIE%'
UNION 
select "Firefox",count(1) from dwd_user_behavior_detail where user_agent like '%Firefox%'
UNION 
select "Opera",count(1) from dwd_user_behavior_detail where user_agent like '%Opera%'
UNION 
select  "Safari",count(1) from dwd_user_behavior_detail where user_agent like '%Safari%'
UNION 
select "QQBrowser",count(1) from dwd_user_behavior_detail where user_agent like '%QQBrowser%'
UNION 
select  "TaoBrowser",count(1) from dwd_user_behavior_detail where user_agent like '%TaoBrowser%';


-- （7）基于来源纬度的指标--统计网站站内和站外的流量情况
create table if not exists ads_flow_referer(
   referer string,
   flow bigint
)row format delimited fields terminated by '\001';


insert overwrite table ads_flow_referer
select "站内",count(1) from dwd_user_behavior_detail where referer_host = "www.bailongma.com"
UNION 
select "站外",count(1) from dwd_user_behavior_detail where referer_host != "www.bailongma.com";