数据仓库管理与实战

该笔记为视频教程笔记
课程链接：https://www.bilibili.com/video/BV1qv411y7Wv/?spm_id_from=333.999.0.0&vd_source=4042938bc91623dd366533263863ec49

1. 课程介绍

数据仓库的广泛应用
- 传统数据仓库
- 大数据数据仓库
体系化数据仓库理论
- 什么是数据仓库？它是如何发展而来的？
- 数据仓库的整体架构
- 数据仓库建模
- 数据仓库生产经验
- 数据仓库落地&实践开发

主题	知识点
简介	诞生背景
	基本概述
	技术方案
架构	通用架构
	ETL流程
	数据积存（ODS层）
	数据分析（DWD、DWS、ADS层）
建模	OLTP、OLAP异同
	ROLAP、MOLAP建模
	多维分析
最佳实践	数据仓库表类型
	ETL策略
	任务调度
项目实战	大数据数据仓库集群搭建
	复购率分析
	GMV（成交总额）分析

1.1 诞生背景

数据仓库诞生原因
- 历史数据积存
- 企业数据分析需要
历史数据积存
- 使用频率低，堆积在业务库中，导致性能下降
企业数据分析需要
- 各部门自己建立独立的数据抽取系统，导致数据不一致

1.2 基本概述

数据仓库(data warehouse , dw)

是一个面向主题的、集成的、非易失的且随时间变化的数据集合
主要用于组织积累的历史数据并使用分析方法(OLAP、数据分析) 进行分析整理，进而辅助角色，为管理者、企业系统提供数据支持，构建商业智能

数据仓库特点

面向主题，根据主题将原始数据集合在一起
集成：原始数据来源于不同数据源，要整合成最终数据，需要经过抽取、清晰、转换的过程

非易丢：保存的数据是一系列历史快照，不允许被修改，只允许通过工具进行查询、分析
时变性：数仓会定期接收、集成新的数据，从而反映出数据的最新变化

数据仓库 VS 数据库

数据库面向事务设计，属于OLTP（在线事务处理）系统，主要操作是随机读写：在设计时尽量避免冗余，常常用符合范式规范来设计
数据仓库是面向主题设计，属于OLAP(在线分析处理)，主要操作是批量读写，关注数据整合，以及分析、处理性能，会有意引入冗余，采用反范式方式设计

	数据库	数据仓库
面向	事务	分析
数据类型	细节、事务	综合、清洗过的数据
数据特点	当前的、最新的	历史的、跨时间维护
目的	日常操作	长期信息需求、决策支持
设计模型	基于ER模型，面向应用	星型/雪花模型，面向主题
操作	读/写	大多为读
数据规模	GB到TB	>=TB

数据仓库建设方案

传统数据仓库
- 由关系型数据库组成MPP（大规模并行处理）集群
- 问题：扩展性有限；热点问题（某一个节点的数据访问频率过高，可以使用加盐来均匀分布，但会带来额外的操作）
大数据数据仓库
- 利用大数据天然的扩展性，完成海量数据的存放
- 将SQL转换为大数据计算引擎任务，完成数据分析
- 问题：SQL支持率、事务支持

1.3 MPP & 分布式架构

MPP架构

传统数仓中常见的技术架构，将单机数据库节点组成集群，提升整体处理性能
节点间为非共享架构(Share Nothing)，每个节点都有独立的磁盘存储系统和内存系统
每台数据节点通过专用网络或者商业通用网络相互连接，彼此协同计算，作为整体提供服务
设计上优先考虑C(一致性)，其次考虑A（可用性），尽量最好P（分区容错性）
优点
- 运算方式精细，延迟低，吞吐低
- 适合中等规模的结构化数据处理
缺点
- 存储位置不透明，通过Hash确定数据所在的物理节点，查询任务在所有节点均会执行
- 并行计算时，单节点瓶颈会成为整个系统短板，容错性差
- 分布式事务的实现会导致扩展性降低

分布式架构

大数据中常见的技术架构，也称为Hadoop架构/批处理架构
各节点实现场地自治（可以单独运行局部应用），数据在集群中全局透明共享
每台节点通过局域网或广域网相连，节点间的通信开销较大，在运算时致力减少数据移动
优先考虑的是P（分区容错性），然后是A（可用性），最好再考虑C（一致性）

MPP + 分布式架构

1.4 常见产品

传统数据仓库

Oracle RAC
DB2
Teradata
Greenplum(开源 Postgre SQL)

大数据数据仓库

Hive
Spark SQL
HBase(适合 NOSQL)
Impala(数据查询引擎)
HAWQ（Greenplum在Hadoop的移植产品）
TIDB（New SQL MPP + SMP，底层NoSQL，支持SQL，主要兼容MySQL）

2. 架构

2.1 架构图

Other：Flume、LogStash(日志、文件)

ODS：存储原始数据，定期删除业务数据库数据

DWD：存储三范式的数据

DWS：宽表

ADS：数据集市层

2.2 ETL流程

Extract 抽取
- 数据源分为结构化数据、非结构化数据、半结构化数据
- 结构化数据一般采用JDBC、数据库日志方式（CDC），非|半结构化数据会监听文件变动
- 抽取方式
  - 有全量同步、增量同步两种方式
  - 全量会将全部数据进行抽取，一般用于初始数据状态
  - 增量会检测数据的变动，抽取发生变动的数据，一般用于数据更新
Transform 转换
- 数据清洗：对出现的重复、二义性、不完整、违反业务或逻辑规则等问题的数据进行统一的处理
- 数据转换：对数据进行标准化处理，进行字段、数据类型、数据定义的转换
- 结构化数据在转换过程中的逻辑较为简单，非|半结构化数据的转换会较为复杂
Load 转换
- 将最后处理玩的数据导入到对应的目标源里

ETL规则的设计和实施约占整个数据仓库搭建工作流的60%和80% （如果是非结构化数据会花更多时间）

结构化数据ETL工具

Sqoop
Kettle（可视化）
Datastage（收费）
Informatica（收费）
Kafka

非|半结构化数据ETL工具

Flume
Logstash

2.3 数据积存

操作数据层（ODS）

数据与原业务数据保持一致，可以增加字段用来进行数据管理
存储的历史数据是只读的，提供业务系统查询使用
业务系统对历史数据完成修改后，将update_type字段更新为UPDATE，追加回ODS中
在离线数仓中，业务数据定期通过ETL流程导入到ODS中，导入方式有全量、增量两种
- 全量：第一次导入时，选择此种方式
- 增量：第一次导入时，每次只需要导入新增、更改的数据，建议使用外连接&全覆盖方式

2.4 数据明细层(DWD)

数据明细层对ODS层的数据进行清洗、标准化、维度退化（时间、分类、地域）
数据仍然满足3NF模型，为分析运算做准备

2.5 数据汇总层（DWS）

按照分析主题进行计算汇总，存放便于分析的宽表
存储模型并非3NF，而是注重数据聚合，复杂查询、处理性能更优的数仓模型，如维度模型

2.6 数据应用层（ADS）

数据应用层也被成为数据集市
存储数据分析结果，为不同业务场景提供接口，减轻数据仓库的负担
数据仓库擅长数据分析，直接开放业务查询接口，会加重其负担

3. 建模方法

3.1 基本概念

OLTP系统建模方法

OLTP(在线事务处理)系统中，主要操作是随机读写
为了保证数据一致性、减少冗余，常使用关系模型
在关系模型中，使用三范式规则来减少冗余

OLAP(在线联机分析)

OLAP系统，主要操作是复杂分析查询，关注数据整合，以及分析、处理性能
OLAP根据数据存储的方式不同，又分为ROLAP、MOLAP、HOLAP

OLAP系统分类

ROLAP(Relation OLAP，关系型OLAP)：使用关系模型构建，存储系统一般为RDBMS
MOLAP(Multidimensional OLAP，多维型OLAP)：预先聚合计算，使用多维数组的形式保存数据结果，加快查询时间
HOLAP(Hybrid OLAP，混合架构的OLAP)：ROLAP和MOLAP两者的集成；如低层是关系型的，高层是多维矩阵的；查询效率高于ROLAP，低于MOLAP

3.2 ROLAP

ROLAP系统建模方法：典型的数据仓库建模方法有ER模型、维度模型、Data Value、Anchor

ER模型
- 出发点是整合数据，为数据分析决策服务
- 需要全面了解业务和数据
- 实施周期长
- 对建模人员能力要求高
分为星型模型、雪花模型、星座模型
- 星座
  - 是基于多个事实表，事实表之间会共享一些维度表
  - 是大型数据仓库中的常态，是业务增长的结果，与模型设计无关
- 雪花
  - 具有多层维度，比较接近三范式设计，较为灵活
维度模型
- 为分析需求服务，更快完成需求分析
- 具有较好大规模复杂查询相应性能
- 最流行的数仓建模经典
Data Value
- ER模型的衍生
- 强调数据的历史性、可追溯、原子性
- 弱化一致性处理和整合
- 引入范式，应对源系统的扩展性
Anchor
- Data Value模型的衍生
- 初衷为设计一个高度可扩展模型
- 会带来较多的join操作

什么是宽表模型？

宽表模型是维度模型的衍生，适合join性能不佳的数据仓库产品
宽表模型将维度冗余到事实表中，形成宽表，以此减少join操作

3.3 MOLAP

MOLAP系统建模方法(对应AWS)

MOLAP将数据进行预结算，并将聚合结果存储到CUBE模型中
CUBE模型以多维数组的形式，物化到存储系统中，加快后续的查询
生成CUBE需要大量的时间、空间，维度预处理可能会导致数据膨胀

常见MOLAP产品
Kylin
Druid

3.4 多维分析

OLAP多维分析

OLAP主要操作是复杂查询，可以多表关联，使用COUNT、SUM、AVG等函数
OLAP对复杂查询操作做了直观的定义，包括钻取、切片、切块、旋转

钻取
对维度不同层次的分析，通过改变维度的层次来变化分析的粒度
钻取包括上卷（Roll-up）、下钻（Drill-down）
上卷（Roll-up），也称为向上钻取，指从低层次到高层次的切换
下钻（Drill-down），指从高层次到低层次的切换

驱蚊剂，防晒装备，急救用品 -》户外防护用品（上钻）

年 -》季度，月，周，日（下钻）

切片（Slice）、切块（Dice）

选择某个维度进行分割成为切片
按照多维进行的切片成为切块

旋转（Pivot）
对维度方向的互换，类似于交换坐标轴上卷（Roll-up）

4. 最佳实践

4.1 表的分类

维度建模中的表类型

事实表
维度表
事务事实表
周期快照事实表
累计快照事实表

事实表

一般是指一个现实存在的业务对象，比如用户，商品，商家，销售员等

维度表

一般是指对应一些业务状态，代码的解释表，也可以成为码表
通常使用维度对事实表中的数据进行统计、聚合运算

订单状态	状态名称	商品分类编号	分类名称
1	未支付	1	生活
2	已支付	2	科技
3	发货中	3	少儿
4	已发货
5	已完成

事务事实表

随着业务不断产生的数据，一旦产生不会再变化，如交易流水、操作日志、出库入库记录

周期快照事实表
随着业务周期型的推进而变化，完成间隔周期内的度量统计，如年、季度累计
使用周期+状态度量的组合，如年累计订单数，年是周期，订单总数是量度

累计快照事实表
记录不确定周期的度量统计，完全覆盖一个事实的生命周期，如订单状态表
通常有多个时间字段，用于记录生命周期中的关键时间点
只有一条记录，针对此记录不断更新

累计快照事实表实现方式一
使用日期分区表，全量数据记录，每天的分区存储昨天全量数据与当天增量数据合并的结果
数据量大会导致全量表膨胀，存储大量永远不更新的冷数据，对性能影响较大
适用于数据量少的情况

实现方式二

使用日期分区表，推测数据最长生命周期，存储周期内数据：周期外的冷数据存储到归档表
需要保留多天的分区数据，存储消耗依然很大

实现方式三

使用日期分区表，以业务实体的结束时间分区，每天的分区存放当天结束的数据：设计一个时间非常大的分区，如9999-12-31，存放截止当前未结束的数据
已结束的数据存放到相应分区，存放未结束的分区，数据量也不会很大，ETL性能好
无存储浪费，数据全局唯一
业务系统可能无法标识业务主体的结束时间（第三方系统），可以使用其他相关业务系统的结束标志作为此业务系统的结束，也可以使用最长生命周期或前端系统的数据归档时间

拉链表

拉链表记录每条信息的生命周期，用于保留数据的所有历史（变更）状态
拉链表将表数据的随机修改方式，变为顺序追加

4.2 ETL策略

全量同步

数据初始化装在一定使用全量同步的方式
因为业务、技术原因，使用全量同步的方式做周期数据更新，直接覆盖原有数据即可

增量同步

传统数据整合方案中，大多采用merge方式（update+insert）
主流大数据平台不支持update操作，可采用全外连接+数据全量覆盖方式（如果担心数据更新出错，可以采用分区方式，每条保存最新的全量版本，保留较短周期）

4.3 任务调度

为什么需要任务调度

解决任务单元间的依赖关系
自动化完成任务的定时执行

常见任务类型

Shell
Java程序
Mapreduce程序
SQL脚本

常见调度工具
Azkaban
Oozie

特性	Azkaban	Oozie
工作流描述语言	text file with key/value pairs	XML
是否要web容器	yes	yes
进度跟踪	web page	web page
Hadoop job调度支持	yes	yes
运行模式	daemon	daemon
事件通知	yes	no
需要安装	yes	yes
支持的hadoop版本	currently unknown	0.20+
重试支持	yes	workflownode evel
运行任意命令	yes	yes

5. 项目实战

5.1 项目概述

项目背景：

某电商企业，因数据积存、分析需要，筹划搭建数据仓库，提供数据分析访问接口
项目一期需要完成数仓建设，并完成用户复购率的分析计算，支持业务查询需求

复购率计算

复购率是指在一段时间间隔内，多次重复购买产品的用户，占全部人数的比率
统计各个一级品类下，品牌月单次复购率，和多次复购率

5.2 数据描述

用户表(user_info)

字段	含义
id	用户id
name	姓名
brithday	生日
gender	性别
email	邮箱
user_level	用户等级
create_time	创建时间

商品表

字段	含义
id	skuId
spu_id	spuId
price	价格
sku_name	商品名称
sku_desc	商品概述
weight	重量
tm_id	品牌id
category3_id	品类id(三级品类)
create_time	创建时间

订单表

字段	含义
id	订单编号
total_amount	订单金额
order_status	订单状态
user_id	用户id
payment_way	支付方式
out_trade_no	支付流水号
create_time	创建时间

订单详情表

字段	含义
id	订单编号
order_id	订单号
user_id	用户id
sku_id	商品id
sku_name	商品名称
order_price	下单价格
sku_num	商品数量
create_time	创建时间

数据描述

商品一级分类表(base_category1)

字段	定义
id	id
name	名称

商品二级分类表(base_category2)

字段	含义
id	id
name	名称
category1_id	一级品类id

商品三级分类表(base_category3)

字段	含义
id	id
name	名称
category2_id	二级品类id

支付流水表

字段	含义
id	编号
out_trade_no	对外业务编号
order_id	订单编号
user_id	用户编号
alipay_trade_no	支付宝交易流水账号
total_amount	支付金额
subject	交易内容
payment_type	支付类型
payment_time	支付时间

5.3 架构设计

5.4 环境搭建

环境说明

CentOS 7.2
Hadoop 2.7.7
Hive 1.2.1
Tez 0.9.1
Mysql 5.7.28
Sqoop 1.4.6
Azkaban 2.5.0
Presto 0.196

集群规划

使用三台虚拟机进行搭建

	Hadoop	Hive&Tez	Mysql	Sqoop	Azkaban	Presto
node01	1(master)				1	1
node02	1		1		1(master)	1(master)
node03	1	1		1	1	1

0.使用脚本安装三个节点

1.安装各种软件

2.测试三个节点是否互通

5.5 项目开发

整体开发流程

业务数据生成
ETL数据导入
创建ODS层，并完成HDFS数据接入
创建DWD层，并完成ODS层数据导入
创建DWS层，导入DWD层数据
创建ADS层，完成复购率计算
编写脚本，将ADS层的数据导出到MySQL中，供业务查询
使用Azkaban调度器，实现脚本自动化运行

业务数据生成

进入Mysql，创建数据库Mall

 export MYSQL_PWD=DBa2020*
mysql -uroot -e "create database mall;"

上传数据生成sql脚本到Mysql安装节点
使用命令方式，将数据生成sql脚本导入到Mysql中
使用命令方式，将数据生成sql脚本导入到mysql中

 mysql -uroot mall < {pathToSQL}

进入mysql，生成数据

 use mall;
-- 生成日期2020-06-10日数据，订单300个，用户200个，商品sku300个，不删除数据
call init_data('2020-06-10',300,200,300,false);

编写脚本(sqoop_import.sh)

 #!/bin/bash
 
#传入两个参数，$1为为表名，$2为时间，决定抽取特定日期的数据
 
db_date=$2
echo $db_date
db_name=mall
 
import_data() {
    sqoop import \
  --connect jdbc:mysql://node02:3306/$db_name \
  --username root \
  --password DBa2020* 
  # 指定分割符  \表示换行
  --fields-terminated-by "\t" \
  # 存放的目录
  --target-dir /origin_data/$db_name/db/$1/$db_date \
  # 目标目录已存在则删除
  --delete-target-dir \
  # 并发执行的数量
  --num-mappers 1 \
  --query "$2"' and $CONDITIONS;'
  
}
 
import_sku_info(){
    import_data "sku_info" "select id,spu_id,price,sku_name,sku_desc,weight,tm_id,category3_id,create_time from sku_info where 1=1 "
}
 
import_user_info(){
    import_data "user_info" "select id,name,birthday,gender,email,user_level,create_time from user_info where 1=1"
}
 
import_base_category1(){
    import_data "base_category1" "select id,name,category1_id from base_category1 where 1=1"
}
 
import_base_category2(){
    import_data "base_category2" "select id,name,category1_id from base_category2 where 1=1"
}
 
import_order_detail(){
    import_data "order_detail" "select od.id,order.id,user_id,sku_id,sku_name,order_price,sku_num,o.create_time from order_info o,order_detail od where o.id = od.order_id and DATE_FORMAT(create_time,'%Y-%m-%d')='$db_date'"
}
 
import_order_info(){
    import_data "order_info" "select id,total_amount,order_status,user_id,payment_way,out_trade_no,create_time,operate_time from order_info where (DATE_FORMAT(create_time,'%Y-%m-%d')='$db_date' or DATE_FORMAT(operate_time,'%Y-%m-%d')='$db_date')" 
}
 
import_payment_info(){
    import_data "payment_info" "select id,out_trade_no,order_id,user_id,alipay_trade_no,total_amount,subject,payment_type,payment_time from payment_info where
    (DATE_FORMAT(payment_time,'%Y-%m-%d')='$db_date'" 
}
 
case $1 in
  "base_category1")
  	import_base_category1
;;
  "base_category2")
    import_base_category2
;;
  "base_category2")
    import_base_category3
;;
  "order_info")
    import_order_info
;;
  "order_detail")
    import_order_detail
;;
  "sku_info")
    import_sku_info
;;
  "user_info")
    import_user_info
;;
  "payment_info")
    import_payment_info
;;
  "all")
  import_base_category1
  import_base_category2
  import_base_category3
  import_order_info
  import_order_detail
  import_sku_info
  import_user_info
  import_payment_info
;;
easc

执行命令

 ./sqoop_import.sh all 2020-08-29

ods_sql.sql

 --hive的数据
-- 创建订单表
create table ods_order_info (
 
) comment '订单表'
--根据日期分区
partitioned by (`dt` string)
row format delimited fields terminated by '\t'
location '/warehouse/mall/ods/ods_order_info/'
-- 压缩方式
tblproperties ("parquet.compression"="snappy")
 
--订单详情表
 
--商品表
 
--用户表
 
--一级分类表
 
--二级分类表
 
--三级分类表
 
--支付流水表

ods数据导入ods_db.sh

 #!/bin/bash
#传入一个参数 $1为时间
do_date=$1
APP=mall
hive=hive
 
sql="
load data inpath '/origin_data/$APP/db/order_info/$do_date' OVERWRITE into table $APP"".dos_order_info partition(dt='$do_date')
 
load data inpath '/origin_data/$APP/db/order_detail/$do_date' OVERWRITE into table $APP"".dos_order_detail partition(dt='$do_date')
# ...六张表
$hive -e "$sql"

执行命令 ./ods_db.sh 2020-08-29
dwd层创建和数据接入(dwd_ddl.sql)

 -- 创建六张表
-- 商品分类表与之前有区别，加了6个字段
create external table dwd_sku_info(
	`id` string comment 'skuId',
    spu_id
    price
    sku_name
    sku_desc
    weight
    tm_id
    category3_id
    category2_id
    category1_id
    category3_name
    category2_name
    category1_name
    create_time
)

执行sql文件 hive -f /home/warehouse/sql/dwd_ddl.sql
导入数据dwd_db.sh

 #!/bin/bash
 
APP=mall
hive=hive
# 指定日期
if [ -n $1 ] ; then
	log_date=$1
else
# 否则是昨天
	log_date=`date -d `-1 day" +%F"
fi
 
sql="
set hive.exec.dynamic.partition.mode=nonstrict;
 
insert overwrite table "$APP".dwd_order_info partition(dt)
# 为简便用了*号，实际环境必须指定字段
select * from "$APP".ods_order_info
where dt='$log_date' and id is not null;
"
# 商品分类表因为多了6个字段，所以要join

dws层创建&导入 (聚合成宽表)
常见sql和sh，同上
aws sql

 insert into table "$APP".ads_sale_tm_category1_stat_mn
select
	mn.sku_tm_id,
	mn.sku_category1_id,
	mn.sku_category1_name,
	sum(if(mn.order_count >= 1,1,0)) buycount,
	sum(if(mn.order_count >= 2,1,0)) buyTwiceLast,
	sum(if(mn.order_count >= 2,1,0) / sum(if(mn.order_count >= 1,1,0))) buyTwiceLastRatio
from mn
group by mn.sku_tm_id,mn.sku_category1_id,mn.sku_category1_name;

posted @ 2023-04-06 16:50 LHX2018 阅读(177) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· DAMA数据管理知识体系指南-V1

· 5-软件工程

· 数据仓库入门

· 数据仓库理论

· 数据仓库基本概念

阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了

2025年3月

日

一

二

三

四

五

六

LHX2018