2——Hive数仓项目完整流程（在线教育）

在线教育（二）

一 、教育项目数仓分层

1.1 原有基础分层

ODS层:	源数据层 —— 对原有数据进行对接和数据园的数据保持相同粒度（将数据源的数据完整的拷贝到ODS层）；
	若数据来源于文本文件，可能会需对文本文件进行预处理（Spark）操作，将其中不规则的数 转换为一份结构化的数据，然后灌入ODS层
DW 层:	数据仓库层 —— 进行数据分析的操作
DA 层:	数据应用层 —— 存储DW层分析结果，用于对接后续的应用

1.2 教育项目分层

ODS层: 源数据层
		一般放置 事实表 和 少量 维度表 数据，当维度表过多时，可将其放置在DIM层 
DIM层: 维度层
		存储维度表数据
DW 层: 数据仓库层
	DWD层: 明细层  ——用于对ODS层数据进行 清洗转换 工作，以及进行 少量的 维度退化 操作
		（1）将多个事实表数据合并未一个事实表操作；（2）将维度表放置在ODS层，一般也在DWD层完成 维度退化
	DWM层: 中间层 
		（1）用于维度退化操作； （2）用于进行 提前聚合 操作
	DWS层: 业务层
		进行 细化维度、统计分析操作
DA 层: 数据应用层  ——存储DWS层再次分析的结果，用于对接后续的应用
		例如：DWS层的数据完成了基于订单表的各项统计结果信息，但图标只需要其中销售额，此时从DWS层将销售额的数据提取出来存储到DA层

​ 维度退化：为了减少维度表的关联工作

- 做法：	将数据分析中可能在维度表中需要使用的字段，退化到事实白哦中，这样后续基于维度统计时就不需要再关联维度表，事实		表已经覆盖
- 例如：订单表，原有订单表中只有用户Id 当我们需要根据用户维度进行统计分析的时候，此时需要关联用户表找用户名称。

​ 如果我们提前将名称放置到订单表中，那就不需要关联用户表。 这就是维度退化

• 利与弊： 利：减少后续分析的表关联情况 弊： 数据冗余

二、数仓工具的使用

2.1 HUE的使用

• HUE操作 oozie（工作流调度程序系统）
• HUE操作Hive

一般为主机地址 + 端口号（8889） 即可登录HUE的web页面进行一系列操作

2.2 Sqoop的使用

• Sqoop用于将数据从业务数据库往Hive的导入导出工作

• import（导入）

  • 全量导入HDFS、Hive

  sqoop import
  --connect jdbc:mysql://192.168.52.150:3306/数据库名 \
  --username root \
  --password 123456 \
  --table 数据表名 \
  --hcatlog-database Hive库名 \
  --hcatlog-table Hive表名 \
  --fields-terminated-by '新的分隔符（默认逗号）'
  --delete-target-dir \
  --target-dir 'Hive中新地址' \
  --split-by id \
  -m 2 
  

  • 条件导入

    • where

      sqoop import
      --connect jdbc:mysql://192.168.52.150:3306/数据库名 \
      --username root \
      --password 123456 \
      --table 数据表名 \
      --where '条件' \
      --hcatlog-database Hive库名 \
      --hcatlog-table Hive表名 \
      -m 2 
      

    • SQL

      sqoop import
      --connect jdbc:mysql://192.168.52.150:3306/数据库名 \
      --username root \
      --password 123456 \
      --table 数据表名 \
      --query 'SQL语句 + where 1=1 AND $CONDITIONS' \
      --hcatlog-database Hive库名 \
      --hcatlog-table Hive表名 \
      -m 2 
      	如果SQL语句使用 双引号包裹,  $CONDITIONS前面需要将一个\进行转义, 单引号是不需要的
      

• export（导出至业务数据库）[必须首先在业务数据库中创建表]

  sqoop export \
  --connect jdbc:mysql://192.168.52.150:3306/test \
  --username root \
  --password 123456 \
  --table emp_add_mysql \
  --hcatalog-database hivesqoop \
  --hcatalog-table emp_add_hive \
  -m 1 
  

  sqoop常用参数

  参数	说明
  –connect	连接关系型数据库的URL
  –username	连接数据库的用户名
  –password	连接数据库的密码
  –driver	JDBC的driver class
  –query或–e	将查询结果的数据导入，使用时必须伴随参–target-dir，–hcatalog-table，如果查询中有where条件，则条件后必须加上$CONDITIONS关键字。如果使用双引号包含sql，则$CONDITIONS前要加上\以完成转义：$CONDITIONS
  –hcatalog-database	指定HCatalog表的数据库名称。如果未指定，default则使用默认数据库名称。提供 --hcatalog-database不带选项–hcatalog-table是错误的。
  –hcatalog-table	此选项的参数值为HCatalog表名。该–hcatalog-table选项的存在表示导入或导出作业是使用HCatalog表完成的，并且是HCatalog作业的必需选项。
  –create-hcatalog-table	此选项指定在导入数据时是否应自动创建HCatalog表。表名将与转换为小写的数据库表名相同。
  –hcatalog-storage-stanza ‘stored as orc tblproperties (“orc.compress”=“SNAPPY”)’ \	建表时追加存储格式到建表语句中，tblproperties修改表的属性，这里设置orc的压缩格式为SNAPPY
  -m	指定并行处理的MapReduce任务数量。 -m不为1时，需要用split-by指定分片字段进行并行导入，尽量指定int型。
  –split-by id	如果指定-split by, 必须使用$CONDITIONS关键字, 双引号的查询语句还要加\
  –hcatalog-partition-keys --hcatalog-partition-values	keys和values必须同时存在，相当于指定静态分区。允许将多个键和值提供为静态分区键。多个选项值之间用，（逗号）分隔。比如： --hcatalog-partition-keys year,month,day --hcatalog-partition-values 1999,12,31
  –null-string ‘\N’ --null-non-string ‘\N’	指定mysql数据为空值时用什么符号存储，null-string针对string类型的NULL值处理，–null-non-string针对非string类型的NULL值处理
  –hive-drop-import-delims	设置无视字符串中的分割符（hcatalog默认开启）
  –fields-terminated-by ‘\t’	设置字段分隔符

三、全部流程

3.1 全量数据流程

3.1.1 需求分析

	分别对每一个需求进行分析（涉及维度、涉及指标、涉及字段、涉及表）；如果有多张表时，分析时加表与表的关联条件

3.1.2 分析总结

3.1.3 业务数据准备

在工作环境中无此流程，（将业务数据插入到数据库中）

3.1.4 建模分析

分析出在该项需求中应构建那些数仓分层，每一层有哪些表，每张表有哪些字段。
ODS层: 源数据（事实表和少量维度表）
DIM层: 存储维度表
DWD层: 清洗、转换；维度退化
	建表字段:必须字段（事实表）+清洗+转换+join字段
DWM层: 维度退化；提前聚合
	建表字段:指标字段+各个维度字段+经验字段
DWS层:细化维度，统计字段
	建表字段:指标字段+各维度字段+经验字段

3.1.5 建模操作

• 第一步

  • 存储方式

  

  • 压缩格式

• 类型表：

  • 对当前这份数据是否具有绝对的控制权，肯定——内部表；否定——外部表
  • 内部表数据由Hive自身管理
  • 外部表数据由HDFS管理

• Hive分区

• 依据建模分析对各个层级建表

3.1.6 数据采集

将业务库数据一对一导入至ODS、DIM层（MySQL——>Hive）

• 利用sqoop对所偶数据进行导入操作
• 如若涉及 分桶表 需主义，无法直接从MySQL中导入分桶表，需创建临时表
• 校验数据是否导入成功

3.1.7 数据清洗转换

• DWD层： 清洗转换

  • 从ODS层到DWD层可以做采样操作

    tablesample(bucket x out of y on 列)
    

  • 编写SQL对个字段进行转换操作

• DWM层：维度退化

  • 将所有维度表关联，用 left join 当表过多时，查看各优化是否执行
  • 可能由于资源问题，导致优化后运行速度变慢

3.1.8 数据分析

• DWS层

  针对每个需求，利用DWM层的字段数据 与 DWS层的字段 来对比，进而书写SQL语句 将查询结果灌入DWS层的数据表中

3.1.9 数据导出

• 从 DWS层 导出至 MySQL 中
  • 在MySQL中建表，结构与DWS层中一致
  • 利用sqoop将所有数据导入MySQL中

3.2 增量数据流程

3.2.1 模拟数据（工作环境中无）

根据现有的数据，通过更改时间来模拟新的数据，达到新增效果

3.2.2 数据采集

• ODS层

只需要采集最新的数据即可，才几十需要添加字段：抽取时间

将增量采集的SQL集成到shell脚本中，达到自动完成操作的效果

3.2.3 数据清洗转换

• DWD层

将ODS层的数据转换至DWD层

在运行SQL前需要配置各项优化操作（动态分区配置、Hive压缩、写入时压缩生效）

• 在shell中执行Hive

  ./hive -e|-f 'sql语句|sql脚本' -S
  

3.2.4 数据分析

在增量分析中，以天为例，

• 对年统计当年结果数据；对季度统计当年结果数据；对月统计当年结果数据 存在影响；
• 对当天和小时的统计对历史数据无影响

解决方案：通过删除分区的方案来解决

• 例如：2023-01-09的数据所在分区为：yearinfo=‘2023’ and quarterinfo=‘1’ and monthinfo=‘01’ and dayinfo=‘09’

执行删除：

alter table 表名 drop partition("分区")；

3.2.5 数据导出

在导出到MySQL中也需要将当年、当季度、当月 的数据删除，重新导入

• 解决方案：将当年数据全部删除，重新从DWS层导出