二、数仓设计、Sqoop数据采集从上到下的数仓设计。 Sqoop数据采集遇到HDFS数据与Oracle数据量不符的问题，

分层整体设计

目标：掌握油站分析项目中的分层整体设计
实施

ODS：原始数据层：最接近于原始数据的层次，直接采集写入层次：原始事务事实表
DWD：明细数据层：对ODS层的数据根据业务需求实现ETL以后的结果：ETL以后事务事实表
DWB：基础数据层：类似于以前讲解的DWM，轻度聚合
- 关联：将主题事实的表进行关联，所有与这个主题相关的字段合并到一张表
- 聚合：基于主题的事务事实构建基础指标
- 主题事务事实表
ST：数据应用层：类似于以前讲解的APP，存储每个主题基于维度分析聚合的结果：周期快照事实表
- 供数据分析的报表
DM：数据集市：按照不同部门的数据需求，将暂时没有实际主题需求的数据存储
- 做部门数据归档，方便以后新的业务需求的迭代开发
DWS：维度数据层：类似于以前讲解的DIM：存储维度数据表
数据仓库设计方案
- 从上到下：在线教育：先明确需求和主题，然后基于主题的需求采集数据，处理数据
  - 场景：数据应用比较少，需求比较简单
- 上下到上：一站制造：将整个公司所有数据统一化在数据仓库中存储准备，根据以后的需求，动态直接获取数据
  - 场景：数据应用比较多，业务比较复杂

小结
- 掌握油站分析项目中的分层整体设计
  - ODS：原始数据层
  - DWD：明细数据层
  - DWB：轻度汇总层
  - ST：数据应用层
  - DM：数据集市层
  - DWS：维度数据层

分层具体功能

目标：掌握油站分析的每层的具体功能
实施
- ODS
  - 数据内容：存储所有原始业务数据，基本与Oracle数据库中的业务数据保持一致
  - 数据来源：使用Sqoop从Oracle中同步采集
  - 存储设计：Hive分区表，avro文件格式存储，保留3个月
- DWD
  - 数据内容：存储所有业务数据的明细数据
  - 数据来源：对ODS层的数据进行ETL扁平化处理得到
  - 存储设计：Hive分区表，orc文件格式存储，保留所有数据
- DWB
  - 数据内容：存储所有事实与维度的基本关联、基本事实指标等数据
  - 数据来源：对DWD层的数据进行清洗过滤、轻度聚合以后的数据
  - 存储设计：Hive分区表，orc文件格式存储，保留所有数据
- ST
  - 数据内容：存储所有报表分析的事实数据
  - 数据来源：基于DWB和DWS层，通过对不同维度的统计聚合得到所有报表事实的指标
- DM
  - 数据内容：存储不同部门所需要的不同主题的数据
  - 数据来源：对DW层的数据进行聚合统计按照不同部门划分
- DWS
  - 数据内容：存储所有业务的维度数据：日期、地区、油站、呼叫中心、仓库等维度表
  - 数据来源：对DWD的明细数据中抽取维度数据
  - 存储设计：Hive普通表，orc文件 + Snappy压缩
  - 特点：数量小、很少发生变化、全量采集

业务系统数据

目标：熟悉业务系统核心数据表
实施
- 切换查看数据库

查看数据表
- CISS_BASE：基础数据表
  - 报销项目核算、地区信息、服务商信息、设备信息、故障分类、出差补助信息、油站基础信息等
- CISS_SERVICE、CISS_S：服务数据表
  - 来电受理单信息、改派记录信息、故障更换材料明细信息、综合报销信息、服务单信息、安装单、维修单、改造单信息
- CISS_MATERIAL、CISS_M：仓储物料表
  - 物料申明明细信息、网点物料调配申请等
- ORG：组织机构数据
  - 部门信息、员工信息等
- EOS：字典信息表
  - 存放不同状态标识的字典

核心数据表

运营分析
- 工单分析、安装分析、维修分析、巡检分析、改造分析、来电受理分析
提高服务质量
- 回访分析
运营成本核算
- 收入、支持分析

Sqoop采集数据格式问题

目标：掌握Sqoop采集数据时的问题
路径
- step1：现象
- step2：问题
- step3：原因

实施

现象

step1：查看Oracle中CISS_SERVICE_WORKORDER表的数据条数

-- 执行结果：178609行记录
select count(1) as cnt from CISS_SERVICE_WORKORDER;

step2：采集CISS_SERVICE_WORKORDER的数据到HDFS上

sqoop import \
--connect jdbc:oracle:thin:@oracle.bigdata.cn:1521:helowin \
--username ciss \
--password 123456 \
--table CISS4.CISS_SERVICE_WORKORDER \
--delete-target-dir \
--target-dir /test/full_imp/ciss4.ciss_service_workorder \
--fields-terminated-by "\001" \
-m 1

step3：Hive中建表查看数据条数

进入Hive容器
```
docker exec -it hive bash
```

连接HiveServer

beeline
# 输入账号、密码：root、123456
!connect jdbc:hive2://hive.bigdata.cn:10000

创建测试表

create external table test_text(
line string
)
location '/test/full_imp/ciss4.ciss_service_workorder';

统计行数

# 查询表的数据量：194673
# 与 sqoop从oracle采集的数据量不符
0: jdbc:hive2://hive.bigdata.cn:10000> select count(*) from test_text;
+---------+--+
|   c0    |
+---------+--+
| 194673  |
+---------+--+

问题：Sqoop采集完成后导致HDFS数据与Oracle数据量不符
原因
- sqoop以文本格式导入数据时，默认的换行符是特殊字符
- Oracle中的数据列中如果出现了\n、\r、\t等特殊字符，就会被划分为多行
- Oracle数据
```
# \n 为换行符
id			name				age
001			zhang\nsan			18
```
- Sqoop遇到特殊字段就作为一行
```
001			zhang
san			18
```
- Hive
```
id			name				age
001			zhang 
san			18
```

问题解决：Avro格式

实施

常见格式介绍

类型	介绍
TextFile	Hive默认的文件格式，最简单的数据格式，便于查看和编辑，耗费存储空间，I/O性能较低
SequenceFile	含有键值对的二进制文件，优化磁盘利用率和I/O，并行操作数据，查询效率高，但存储空间消耗最大
AvroFile	特殊的二进制文件，设计的主要目标是为了满足schema evolution，Schema和数据保存在一起
OrcFile	列式存储，Schema存储在footer中，不支持schema evolution，高度压缩比并包含索引，查询速度非常快
ParquetFile	列式存储，与Orc类似，压缩比不如Orc，但是查询性能接近，支持的工具更多，通用性更强

SparkCore缺点：RDD【数据】：没有Schema
SparkSQL优点：DataFrame【数据 + Schema】
Schema：列的信息【名称、类型】

Avro格式特点
- 优点
  - 二进制数据存储，性能好、效率高
  - 使用JSON描述模式，支持场景更丰富
  - Schema和数据统一存储，消息自描述
  - 模式定义允许定义数据的排序
- 缺点
  - 只支持Avro自己的序列化格式
  - 少量列的读取性能比较差，压缩比较低
- 场景：基于行的大规模结构化数据写入、列的读取非常多或者Schema变更操作比较频繁的场景
Sqoop使用Avro格式
```
--as-avrodatafile
```

解决

使用特殊文件格式：AVRO格式

[root@15b0369d3f2a sqoop]# sqoop import \
> -Dmapreduce.job.user.classpath.first=true \
> --connect jdbc:oracle:thin:@oracle.bigdata.cn:1521:helowin \
> --username ciss \
> --password 123456 \
> --table CISS4.CISS_SERVICE_WORKORDER \
> --delete-target-dir \
> --target-dir /test/full_imp/ciss4.ciss_service_workorder \
> --as-avrodatafile \
> --fields-terminated-by "\001" \
> -m 1

# 执行结果：178609行记录
24/08/24 06:53:17 INFO mapreduce.ImportJobBase: Retrieved 178609 records.

# hive
# 建表
0: jdbc:hive2://hive.bigdata.cn:10000> create external table test_text(
. . . . . . . . . . . . . . . . . . .> line string
. . . . . . . . . . . . . . . . . . .> )
. . . . . . . . . . . . . . . . . . .> stored as avro
. . . . . . . . . . . . . . . . . . .> location '/test/full_imp/ciss4.ciss_service_workorder';

# 查询表的数据量：178609
# 与 sqoop从oracle采集的数据量相符
0: jdbc:hive2://hive.bigdata.cn:10000> select count(*) from test_avro;
+---------+--+
|   c0    |
+---------+--+
| 178609  |
+---------+--+

脚本开发思路

目标：实现自动化脚本开发的设计思路分析
路径
- step1：脚本目标
- step2：实现流程
- step3：脚本选型
- step4：单个测试

实施

脚本目标：实现自动化将多张Oracle中的数据表全量或者增量采集同步到HDFS中
实现流程
- a. 获取表名
- b.构建Sqoop命令
- c.执行Sqoop命令
- d.验证结果
脚本选型
- Shell：Linux原生Shell脚本，命令功能全面丰富，主要用于实现自动化Linux指令，适合于Linux中简单的自动化任务开发
- Python：多平台可移植兼容脚本，自身库功能强大，主要用于爬虫、数据科学分析计算等，适合于复杂逻辑的处理计算场景
- 场景：一般100行以内的代码建议用Shell，超过100行的代码建议用Python
- 采集脚本选用：Shell

单个测试

创建一个文件，存放要采集的表的名称

#创建测试目录
mkdir -p /opt/datas/shell
cd /opt/datas/shell/
#创建存放表名的文件
vim test_full_table.txt

ciss4.ciss_base_areas
ciss4.ciss_base_baseinfo
ciss4.ciss_base_csp
ciss4.ciss_base_customer
ciss4.ciss_base_device

创建脚本
```
vim test_full_import_table.sh
```

构建采集的Sqoop命令

sqoop import \
-Dmapreduce.job.user.classpath.first=true \
--connect jdbc:oracle:thin:@oracle.bigdata.cn:1521:helowin \
--username ciss \
--password 123456 \
--table CISS4.CISS_SERVICE_WORKORDER \
--delete-target-dir \
--target-dir /test/full_imp/ciss4.ciss_service_workorder \
--as-avrodatafile \
--fields-terminated-by "\001" \
-m 1

封装脚本

#!/bin/bash
#export path
source /etc/profile
#export the tbname files
TB_NAME=/opt/datas/shell/test_full_table.txt
#export the import opt
IMP_OPT="sqoop import -Dmapreduce.job.user.classpath.first=true"
#export the jdbc opt
JDBC_OPT="--connect jdbc:oracle:thin:@oracle.bigdata.cn:1521:helowin --username ciss --password 123456"

#read tbname and exec sqoop
while read tbname
do
  ${IMP_OPT} ${JDBC_OPT} --table ${tbname^^} --delete-target-dir --target-dir /test/full_imp/${tbname^^} --as-avrodatafile --fields-terminated-by "\001" -m 1
done < ${TB_NAME}

添加执行权限
```
chmod u+x test_full_import_table.sh
```
测试执行
```
sh -x test_full_import_table.sh
```
检查结果

全量及增量采集脚本运行

目标：实现全量采集脚本的运行
实施
- 全量目标：将所有需要将实现全量采集的表进行全量采集存储到HDFS上
  - Oracle表：组织机构信息、地区信息、服务商信息、数据字典等
  - HDFS路径
```
/data/dw/ods/one_make/full_imp/表名/日期
```
- 增量目标：将所有需要将实现全量采集的表进行增量采集存储到HDFS上
  - 工单数据信息、呼叫中心信息、物料仓储信息、报销费用信息等
  - HDFS路径
```
/data/dw/ods/one_make/incr_imp/表名/日期
```
- 运行脚本
  - 全量采集
```
cd /opt/sqoop/one_make
sh -x full_import_tables.sh 
```
  - 增量采集
```
cd /opt/sqoop/one_make
sh -x incr_import_tables.sh 
```
- 特殊问题
  - 因oracle表特殊字段类型，导致sqoop导数据任务失败
  - oracle字段类型为： clob或date等特殊类型
  - 解决方案：在sqoop命令中添加参数，指定特殊类型字段列(SERIAL_NUM)的数据类型为string
    - —map-column-java SERIAL_NUM=String
- 查看结果
  - /data/dw/ods/one_make/full_imp：44张表
  - /data/dw/ods/one_make/incr_imp：57张表

Schema备份及上传

目标：了解如何实现采集数据备份

实施

需求：将每张表的Schema进行上传到HDFS上，归档并且备份

Avro文件HDFS存储

hdfs_schema_dir=/data/dw/ods/one_make/avsc
hdfs dfs -put ${workhome}/java_code/*.avsc ${hdfs_schema_dir}

Avro文件HDFS备份

hdfs_schema_backup_filename=${hdfs_schema_dir}/avro_schema_${biz_date}.tar.gz
hdfs dfs -put ${local_schema_backup_filename} ${hdfs_schema_backup_filename}

运行测试

cd /opt/sqoop/one_make/
./upload_avro_schema.sh

验证结果

/data/dw/ods/one_make/avsc/
*.avsc
schema_20210101.tar.gz