文章目录
day03-数仓设计
一、主题和主题域（了解）
在数仓设计时，先确认需求，再进行数仓的分层设计和表设计
在确认需求时就要明确
分析的主题及要计算的数据
数据域：计算的数据根据不同的
业务过程进行数据的划分，将数据进行分类，分类后的数据就叫做数据域
- 基于用户角度分析业务流程
- 浏览业务
- 用户访问首页，列表页，详情页
- 得到访问页面的数据内容 ：页面内容 ，停留时间，访问用户等等
- 将浏览过程中产生的数据划归浏览数据域（访问数据域）（日志数据域）
- 购买业务
- 加入购物车，确认下单，订单支付
- 购物车数据，订单数据，支付数据 可以归为一类 购买数据域 交易数据域
- 配送业务
- 出库，配送，签收
主题域：是根据不同需求方提出的
分析内容确认，对需求内容进行归类，形成主题域销售需求：销售额，销售量，销售成本（损耗，物流费用…）指标 销售主题域
营销需求：用户访问，用户注册量，用户下单量，销售额，销售量 指标 营销主题域
主题域的需求依赖数据域中的数据进行计算
主题域和数据域的关系就像饭店做饭
数据域： 采购食材，对食材进行分类 蔬菜类 海鲜类 肉类等等
主题域： 用户点菜，对用户的需求进行分类 川菜 粤菜。。。
主题域需要从多个数据域中选择数据进行计算加工数据
1-1 主题和主题域介绍
主题根据需求是对指标数据进行归类，每个分类是一个主题
主题域根据业务分析需求的划分领域，将联系较为紧密的数据主题的集合在一起
主题域下面可以有多个主题，主题还可以划分成更多的子主题，主题和主题之间的建设可能会有交叉现象（数据交叉）
主题域：
川菜(主题域)
- 鱼香（主题）：鱼香肉丝（子主题），鱼香茄子。。。
- 麻辣（主题）：麻婆豆腐，麻辣兔头。。。
销售主题域
- 售卖主题
- 不同门店的售卖分析（子主题） sum(订单金额)，count(订单id) group by 门店
- 销售额
- 销售量
- 不同品类的售卖分析（子主题）
- 核销主题
1-2 主题域的划分
- 按照系统划分
- 生产系统 生产主题域
- 商品原材料库存核销数据 商品主题
- 订单主题
- 财务系统
- 商品原材料库存核销数据 商品主题
- 销售主题
- 人力系统
- 生产系统 生产主题域
- 按照部门划分
- 人力部门 人力主题域
- 员工主题 员工数据
- 招聘主题 招聘数据
- 生产部门
- 销售部门
- 后勤部门
- 人力部门 人力主题域
- 按照业务划分
- 门店零售业务
- 批发业务
- 团购业务
- 按照行业经验
- 银行证券业
- 当事人、产品、协议、事件、资产、财务、机构、地域、营销、渠道
- 银行证券业
按照业务功能划分主题域
销售主题域：
- 核销主题
- 订单已完成支付，并且用户已经签收，只统计已完成的订单
- 子主题 不同的维度分析
- 不同门店的核销统计分析
- 销售量
- 销售额
- 不同城市的核销统计分析
- 销售量
- 销售额
- 不同商品的核销统计
- 销售量
- 销售额
- 售卖主题
- 所有订单数据统计，不管订单是否完成
用户主题域:
- 会员主题
- 消费金额
- 消费周期
供应链主题域
- 库存主题
- 订单主题
线上商城主题域
- 用户画像
- 用户访问频次
- 用户的消费周期
- 用户的消费金额
二、数据仓库和数据集市(理解熟悉)
数据集市就是数据仓库的一个子集，它主要面向部门级业务，并且只面向某个特定的主题数据集市由业务部门定义、设计和开发，业务部门进行管理和维护
数据湖
数据仓库处理的是结构化数据
数据湖能处理所有类型数据
三、数仓设计（了解）
技术负责人或架构师进行数仓设计
3-1 数仓分层设计
数据仓库最基础分层 ，分层本质就是创建不同的数据库，
原始数据层 ODS
- 将数据源的数据导入数仓ods层
- 创建一个ods的数据库，然后按照数据源中的表创建ods库下的hive表
数据仓库层 DW 数据处理
数据服务层 ADS 计算结果
数据分层设计到的有什么作用？
简化复杂问题。
通过将复杂的数据处理过程分解为多个步骤或层次，可以使问题更加易于理解和处理。
# 统计不同用户的消费金额(不统计未支付的) select user_id ,user_name, sum(price) from tb_user left join tb_order on tb_user.id = tb_order.user_id where pay_status = '已支付' group by user_id ,user_name第一层 数据过滤 create table dwd_tb_order as select * ftom tb_order where pay_status = '已支付' 第二层数据关联 create table dwd_tb_user_order as select * from tb_user left join dwd_tb_order on tb_user.id = tb_order.user_id 第三层 分组计算 select user_id ,user_name,sum(price) from dwd_tb_user_order group by user_id ,user_name select user_id ,user_name,count(order_id) from dwd_tb_user_order group by user_id ,user_name结构更清晰。
每个数据层都有其明确的作用域，这有助于在使用表时方便地定位和理解。
数据血缘追踪。
分层结构使得数据血缘关系更加明确，便于追踪和调试。
用空间换时间。
通过预处理大量数据，可以提高应用系统的用户体验（效率），虽然这可能导致数据仓库中存在冗余数据。
数据重复使用，减少重复开发。
规范的数据分层可以促进数据和指标的统一，减少重复开发，并提高数据复用率。
数据隔离和屏蔽原始数据异常。
分层结构可以帮助隔离原始数据的异常或敏感性，保护真实数据与统计数据解耦。
数据安全。
通过分层，可以更方便地对不同层的数据进行权限管理，屏蔽敏感数据。
增强扩展性和便于后期维护。
不分层可能会导致整个数据清洗过程受到源业务系统规则变化的影响，而分层可以简化数据清洗过程，提高扩展性，便于后期维护。
常见的数仓分层设计
数仓的基本分层 ODS DW APP/ADS
每个公司会根据自身情况对DW层再次进行划分
DW：
- DWD
- DWM
- DWS
数仓设计核心原则： 不为了分层而分层
每个公司在选择分层时有可能会合并分层 DWD层和DWM层合并成一层
有的公司数据有特殊性，可以对这些特殊数据单独创建分层：对维度数据单独创建的维度分层 DIM层
美团数仓设计： https://tech.meituan.com/2020/03/12/delivery-data-governance.html
阿里数仓设计： https://www.alibabacloud.com/help/zh/maxcompute/getting-started/divide-a-data-warehouse-into-layers?spm=a2c63.p38356.0.0.4efb51acivvAHP
3-2 数仓建表设计
数仓一般采用维度建模方式来设计不同的表
Inmon提出了三范式建模，主要应用在业务开发时的表设计
三范式的设计核心：数据的原子性，数据避免重复存储
第一范式：字段数据的原子性，数据不可被拆分
第二范式：行数据的原子性 行数据不重复
第三范式：表数据的原子性
维度模型是
Ralph Kimall所倡导，他的《数据仓库工具箱》是数据仓库工程领域最流行的数仓建模经典。维度建模以分析决策的需求出发构建模型，构建的数据模型为分析需求服务，因此它重点解决用户如何更快速完成分析需求，同时还有较好的大规模复杂查询的响应性能。Ralph Kimall的维度建模采用的是自上而下的设计思想
上游需求设计下游需要计算的表数据
依据需求分析数仓的搭建
参考2.1章节
2.1基本概念
2.1.1 收集业务需求与数据实现
数据产品经理或数据分析师 开始维度建模工作前，项目组需要理解业务需求，以及作为基础的源数据的实际情况。 通过与业务代表交流来发现需求，用于理解他们的基于
关键性能指标、竞争性商业问题、 决策制定过程、支持分析需求的目标。同时，数据实际情况可以通过与源系统专家交流， 构建高层次数据分析访问数据可行性来揭示。收集给类计算的指标
原始价格 100元 100块
折扣价格 90元 90元
原子指标 业务度量值 派生指标 维度+原子指标 衍生指标 维度+修饰+时间周期+原子指标 销售额 不同时间的销售额、不同区域的销售 不同区域+实际+7日+销售额：不同省份的7日实际销售额 销售量 2.1.2 协作维度建模研讨
数据产品经理或数据分析师 维度模型应该由主题专家与企业数据管理代表合作设计而成。工作由数据建模者负责，但模型应该通过与业务代表开展一系列高级别交互讨论获得。这些讨论组也为丰富业务需求提供了一种机会。维度模型不应该由那些不懂业务以及业务需求的人来设计，协作 是成功的关键。
对指标分类确认主题域，编写指标体系文档
2.1.3 4步骤维度设计过程
维度模型设计期间主要涉及4个主要的决策：
(1)选择业务过程
- 从数据域中确认需求用到数据内容，
数据探查，找打需要计算的数据(2)声明粒度
- 确认数据计算的大小，从维度方向上考虑
- 2023年，2024年数据
- 时间粒度
- 以年进行计算 得到的数据只有两行
- 以月进行计算 最多得到24行数据
- 以天进行计算 最多得到730行数据
- 选择最小粒度度作为表的粒度单位，可以应对未来可能得数据需求变化
- 可以基于最小粒度实现数据的上卷
(3)确认维度
- 确认分析的方向
- 时间维度
- 分析随着时间的变化，指标数据的变化情况
- 区域维度
- 通过分析不同区域确认公司的主要业务城市
- 品类维度
(4)确认事实
- 从指标体系中找对应指标
要回答上述问题，需要考虑业务需求以及协作建模阶段涉及的底层数据源。按照业务过程、粒度、维度、事实声明的流程，设计组确定表名和列名、示例领域值以及业务规则。 而业务数据管理代表必须参与详细的设计活动，以确保涵盖正确的业务。
2.1.4 业务过程
业务过程是组织完成的操作型活动，例如，获得订单、处理保险索赔、学生课程注册 或每个月每个账单的快照等。业务过程事件建立或获取性能度量，并转换为事实表中的事实。多数事实表关注某一业务过程的结果。过程的选择是非常重要的，因为过程定义了特定的设计目标以及对粒度、维度、事实的定义。每个业务过程对应企业数据仓库总线矩阵 的一行。
2.1.5 粒度
声明粒度是维度设计的重要步骤。粒度用于确定某一事实表中的行表示什么。粒度声明是设计必须履行的合同。在选择维度或事实前必须声明粒度，因为每个候选维度或事实 必须与定义的粒度保持一致。在所有维度设计中强制实行一致性是保证BI 应用性能和易用 性的关键。在从给定的业务过程获取数据时，原子粒度是
最低级别的粒度。我们强烈建议 从关注原子级别粒度数据开始设计，因为原子粒度数据能够承受无法预期的用户查询。上卷汇总粒度对性能调整来说非常重要，但这样的粒度往往要猜测业务公共问题。针对不同的事实表粒度，要建立不同的物理表，在同一事实表中不要混用多种不同的粒度。2.1.6 描述环境的维度
维度提供围绕某一业务过程事件所涉及的“谁、什么、何处、何时、为什么、如何” 等背景。维度表包含 BI 应用所需要的用于过滤及分类事实的描述性属性。牢牢掌握事实表 的粒度，就能够将所有可能存在的维度区分开。当与给定事实表行关联时，任何情况下都 应使维度保持单一值。
维度表有时被称为数据仓库的“灵魂”,因为维度表包含确保DW/BI 系统能够被用作 业务分析的入口和描述性标识。主要的工作都放在数据管理与维度表的开发方面，因为它 们是用户BI 经验的驱动者。
分析事实表数据时，分析的方向就是维度数据
不同门店的销售量，门店表就是维度表
不同商品的销售量 ，商品表就是维度表
2.1.7 用于度量的事实
事实涉及来自业务过程事件的度量，基本上都是以数量值表示。一个事实表行与按照 事实表粒度描述的度量事件之间存在一对一关系，因此事实表对应一个物理可观察的事件。 在事实表内，所有事实只允许与声明的粒度保持一致。例如，在零售事务中，销售产品的数量与其总额是良好的事实，然而商店经理的工资不允许存在于零售事务中。
用户产生数据都是要分析的事实表数据，计算的指标来自哪些表，这些表就是事实表。
根据维度建模的过程，梳理数仓的表
1-通过数据探查找到对应的计算原始数据表。 直接在数仓中按照原始数据表的结构设计表，将对应表数据保存在ods层
- 订单表
- 商品表
- 用户表
2-对探查的数据确认事实表和维度表，然后对表进行清洗转化保存dwd
- 维度表
- 商品表
- 事实表
- 订单表
- 用户表
维度数据对应表就是维度表 ，指标对应表就是事实表- 表分类后，按照清洗转化的需求，在原来表基础上增加清洗转化字段
- 不同年龄段人数
- 对用户表中的年龄字段进行转化。增加新字段保存
- 该部分数据保存在dwd层
3-事实和维度表关联 宽表
- 将计算的维度数据和事实数据关联在一起方便后续的计算
- 宽表的字段数量满足计算需求即可，不要将所有字段放入宽表
- 时间维度表
- 年，月，日，周，季度
- 关联维度表时，如果计算的粒度到天，则只需要关联指定天的字段即可、
- insert into 设计的表 select day,price from 事实表 join 时间维度
4-需求的计算
根据需求进行多维计算
1-不同门店的销售量
select sum(id) form 表 group by 门店
2-不同城市的销售量
select sum(id) form 表 group by 城市
两个需求整合
方式1 ：
insert into 设计表 select 门店字段，sum(id) as门店销售额，null as 城市 ，0 as 城市销售额 form 表 group by 门店 union select null as 门店，0 as门店销售额，城市，sum(id) as 城市销售额 form 表 group by 城市方式2：
- grouping sets 多维分组计算
- 可以会根据指定固定维度自动组合得到不同维度的数据放入一张表
根据计算内的结果设计字段保存计算的数据
5-数据展示
- 从多维计算的中筛选需要展示的数据
- 根据展示的字段信息设计字段内容
- 不同门店的销售量
星状模型
每个事实表都自己独立的维度表，会造成重复创建维度表
用户表 每天用户的注册量，不同城市的用户注册量
雪花模型
星座模型
多个事实表可以关联相同的维度表
项目中采用星座模型
3-3 定义规范设计
- 表命名
分层_主题_实体（原始表名）+业务（交易）+维度_分区
分层 ods，dw，dwd
主题 sale（销售主题） user（用户主题）
实体+业务+维度
-
示例：
-
store_goods_statistics_day
-
store_member_statistics_day
-
mysql中的表名+计算维度
-
ods_sale_store_goods_statistics_day_dt
规范不是前置要求，不同公司可能根据业务设计表名
- 字段类型规范
- 数量类型整数为 bigint，int
- 金额类型为decimal(27, 2)，表示：27位有效数字，其中小数部分2位
- 数量类型小数为decimal(27, 3)，表示：27位有效数字，其中小数部分3位
- 字符串(名字，描述信息等)类型为string
- 日期类型为string 2022-10-10
- 时间类型为timestamp 2022-10-10 12:10:10
四、DIM维度层数据处理（掌握）
指定一个DIM层，专门处理维度数据
4-1 维度表介绍
Mysql的dim数据库
4-2 DIM层表数据处理
I-hive表的中文问题解决
在mysql中执行如下语句
use 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;
II-创建DIM层表
创建原始数据表，根据mysql中的维度表字段数据创建dim层中表数据
表需要进行分区，一天为单位进行分区保存数据
-- 查看建表语句
show create table 表名
III-导入dataxweb任务
启动服务
/export/server/datax-web-2.1.2/bin/start-all.sh
IV-执行datax任务导入ods原始表数据
V-处理维度表数据
hive的配置
-- 开启动态分区方案
-- 开启非严格模式
set hive.exec.dynamic.partition.mode=nonstrict;
-- 开启动态分区支持(默认true)
set hive.exec.dynamic.partition=true;
-- 设置各个节点生成动态分区的最大数量: 默认为100个 (一般在生产环境中, 都需要调整更大)
set hive.exec.max.dynamic.partitions.pernode=10000;
-- 设置最大生成动态分区的数量: 默认为1000 (一般在生产环境中, 都需要调整更大)
set hive.exec.max.dynamic.partitions=100000;
-- hive一次性最大能够创建多少个文件: 默认为10w
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;
分类表
分类表（
ods_dim_category_f）拉平处理
insert overwrite table dwd_dim_category_statistics_i partition(dt)
select t1.id,
t1.category_no,
t1.category_name,
t2.id,
t2.category_no,
t2.category_name,
t3.id,
t3.category_no,
t3.category_name,
0 status,
date_add(current_date,-1) dt
from dim.ods_dim_category_f t1
join dim.ods_dim_category_f t2 on t1.id = t2.parent_id
join dim.ods_dim_category_f t3 on t2.id = t3.parent_id
商品表
商品表(
ods_dim_goods_info_f)处理将分类编号替换为一二三级分类ID、编码和名称
关联分类表，将商品表中的category_no 转为对应的分类信息
insert into dwd_dim_goods_i partition (dt)
select id,
goods_no,
goods_name,
first_category_id,
first_category_no,
first_category_name,
second_category_id,
second_category_no,
second_category_name,
third_category_id,
third_category_no,
third_category_name,
brand_no,
spec,
sale_unit,
life_cycle_status,
tax_rate_status,
tax_rate,
tax_value,
order_multiple,
pack_qty,
split_type,
is_sell_by_piece,
is_self_support,
is_variable_price,
is_double_measurement,
is_must_sell,
is_seasonal,
seasonal_start_time,
seasonal_end_time,
is_deleted,
goods_type,
create_time,
update_time,
date_add(current_date, -1) dt
from ods_dim_goods_info_f t1
left join dwd_dim_category_statistics_i t2 on t1.category_no = t2.third_category_no;
门店商品表
门店商品表（
ods_dim_store_goods_f）处理将分类ID替换为对应一二三级分类ID、编码和名称
统商品表逻辑一样
insert overwrite table dwd_dim_store_goods_i partition(dt)
select uid,
store_no,
goods_no,
goods_name,
first_category_no,
first_category_name,
second_category_no,
second_category_name,
third_category_no,
third_category_name,
is_clear,
is_must_order,
is_orderable,
order_multiple,
min_order_qty,
vendor_no,
vendor_name,
group_no,
group_name,
dc_no,
dc_name,
tag,
create_time,
update_time,
is_deleted,
date_add(current_date, -1) dt
from ods_dim_store_goods_f t1
left join dwd_dim_category_statistics_i t2 on t1.category_no = t2.third_category_no;
门店日清商品表处理
门店日清商品表处理
日清商品，不满足以下要求的商品需要清理掉不再入库，主要是一些生鲜类和现做的食物
- 一切以实物为标准,不允许变色、不新鲜产品入库。
- 骨类入库存放时间不得超过24小时。
- 上冰台的所有促销品当天尽量要做到日清,对于上冰台的当日未销售完的产品,未变色,不影响第二天销售的可以入库。
- 对于化冻的禽副产品当日必须销售完毕,猪副产品根据品相颜色以实物相论。
insert into dwd_dim_store_clear_goods_i partition(dt)
select * from dwd_dim_store_goods_i
where is_clear=1;
交易类型表
不需要做任何处理
insert into dwd_dim_source_type_map_i partition(dt)
select
company,
original_source_type,
original_source_type_name,
source_type,
source_type_name,
is_online,
current_date() dt
from ods_dim_source_type_map_f;
时间维度表
不需要做任何处理
insert into dwd_dim_date_f
select * from ods_dim_date_f;
门店表处理
需要使用的表,将如下三张表的数据关联在一起
分店信息表 ods_dim_store_f
- 需要表中的所有字段
分店面积明细表 ods_dim_store_area_info_f
- store_no 门店编号
- area_type_no 面积类型编号
- area 面积类型名称
- 1-门店面积信息可以从分店面积明细表中获取。先取实际经营面积，如果取不到（实际经营面积为空或0）再取经营面积。
with tb1 as ( -- 将7和8的数据放到同一行 select *, lead(area) over (partition by store_no order by area_type_no) as area2 from ods_dim_store_area_info_f where area_type_no in (7, 8)) select store_no, -- tb1计算后 area代表 7面积 area2 8的面积 if(area2 is null or area2 = 0, area, area2) as area, if(area2 is null or area2 = 0, 7, 8) as area_type_no from tb1 where area_type_no = 7分店分组信息表 ods_dim_store_group_f
- store_group_name 分组名称
1-门店面积信息可以从分店面积明细表中获取。先取实际经营面积，如果取不到（实际经营面积为空或0）再取经营面积。
2-区域名称信息从店组信息表中取，store_group_type_no = ‘04’即对应区域的编码和名称。
3-store_type_code和management_type_code 需要转换为整数类型
完整的实际代码
-- 1-获取门店经营面积
with tb1 as (
-- 将7和8的数据放到同一行
select *, lead(area) over (partition by store_no order by area_type_no desc ) as area2
from ods_dim_store_area_info_f
where area_type_no in (7, 8)),
tb2 as(
select store_no,
-- tb1计算后 area代表 8面积 area2 7的面积
if(area is null or area = 0, area2, area) as area,
if(area is null or area = 0, 7, 8) as area_type_no
from tb1
where area_type_no = 8)
insert into dwd_dim_store_i partition(dt)
-- 2-门店信息的类型进行转化
select
tb3.id,
tb3.store_no,
store_name,
cast(management_type_code as int) store_sale_type,
cast(store_group_no as int) as store_type_code,
city_id,
city_name,
region_code,
tb4.store_group_name as region_name,
worker_num,
manager_name,
telephone,
opening_date,
open_time,
close_time,
status,
tb3.is_deleted,
tb3.create_time,
tb3.update_time,
area as store_area,
decoration_code,
if(flag=16,1,0) is_day_clear,
current_date() as dt
from ods_dim_store_f tb3
-- 关联面积计算结果表
left join tb2 on tb3.store_no=tb2.store_no
-- 关联分组计算结果表
left join ods_dim_store_group_f tb4 on tb3.region_code = tb4.store_group_no and store_group_type_no = '04';
扫一扫
1195
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
