业务调研
源数据存储位置
通过调研商城系统的原有的技术架构,了解到数据存储在 MySQL 数据库。
此处假设使用腾讯云 MySQL 数据库。
目标业务场景分析
通过分析目标业务场景:各城市、各品类的销售情况,因此我们需要获取以下几张表:
订单表:(此处先忽略订单明细等子表设计,假设订单表包含商品 ID、商品数量、商品价格、收货地址、下单时间等信息)。
商品表:(此处先忽略 SKU 等子表设计,假设商品表包含商品 ID、商品品类等信息)。
城市表:(假设地理位置编码表仅到城市级别,城市表包含:城市编码、城市名称)。
商品品类表:(假设品类仅存在一级类目,类目表包含:品类编码、品类名称)。
实际结构
以下为调研到的订单表与商品表的实际结构:
1. 订单表(orders)
字段名 | 字段类型 | 字段长度 | 字段说明 | 示例 |
order_id | INT | 10 | 订单 ID,主键,自增 | 10001 |
product_id | INT | 10 | 商品 ID,外键 | 1001 |
quantity | INT | 5 | 商品数量,正整数 | 2 |
unit_price | DECIMAL(10,2) | - | 商品单价,保留两位小数 | 99.99 |
amount | DECIMAL(10,2) | - | 商品金额小计,即商品数量乘以单价 | 199.98 |
order_time | DATETIME | - | 下单时间,记录精确到分钟 | "2024-04-04 10:30:00" |
shipping_city_id | INT | 10 | 收货地址城市 ID,外键 | 1101 |
shipping_address | TEXT | - | 收货地址,包含省、市、区、详细地址 | "北京市朝阳区 XXX 小区" |
2. 商品表(products)
字段编码 | 字段类型 | 字段长度 | 字段说明 | 示例 |
product_id | INT | 10 | 商品 ID,主键,自增 | 1001 |
category_id | INT | 10 | 商品品类 ID,外键 | 101 |
product_name | VARCHAR(100) | - | 商品名称 | "智能手机" |
3. 城市表(cities)
字段编码 | 字段类型 | 字段长度 | 字段说明 | 示例 |
city_id | INT | 10 | 城市编码,主键,自增 | 1101 |
city_name | VARCHAR(50) | - | 城市名称 | "北京市" |
商品品类表(categories)
字段编码 | 字段类型 | 字段长度 | 字段说明 | 示例 |
category_id | INT | 10 | 品类ID,主键,自增 | 1 |
category_name | VARCHAR(50) | - | 品类名称 | "电子产品" |
结构设计
根据业务场景需要,下面按照最终业务输出,涉及数仓分层和数据表结构。
模型规范
模型规范可以帮助团队统一数据仓库设计规则,统一数据开发过程,更好地沉淀数据资产,为建设数据服务与数据集市打下基础。
在数仓模型规范设计过程中,会包含以下多个类目,如数据域、主体域等。
在此场景中,核心目的为数据集成与数据开发过程,因此在此教程中不做数据模型规范的详细教学。
以下为本场景的相关模型规范示例:
类目 | 中文描述 | 英文名称 |
业务分类 | 销售 | trade |
数据域 | 订单 商品 | order product |
业务过程 | 订单创建 | ordercreate |
主题域 | 商品 | product |
维度 | 日期 城市 品类 | date city category |
指标 | 销售额 销售数量 | amount quantity |
数仓分层
1. 数据引入层 ODS
将没有经过任何处理的原始数据导入到数据仓库。ODS 层的数据表结构与原始数据所在的数据系统中的表结构一致。
因此,我们需要根据原始数据创建4张hive表(此处不需要建表操作,后续教学中会有涉及),表结构与 MySQL 源数据表完全相同。
以下为四张表的命名:
订单表:ods_order_order
商品表:ods_product_product
类目表:ods_product_category
城市表:ods_order_city
说明:
建议命名格式为:ods_{数据域}_{自定义内容}。
2. 公共维度层 DIM
此处重点介绍数据同步逻辑,暂时忽略维度层设计。
说明:
建议将维度表中的字段属性将冗余到明细数据表中。
3. 明细数据层 DWD
构建最细颗粒度的明细数据表,在此表中可以适当冗余一些字段,减少明细数据表与维度表的关联。
构建明细表:此处不需要建表操作,后续教学中会有涉及。
商品销售情况明细表:dwd_trade_order_ordercreate_productsales。
说明:
建议命名格式为:dwd_{业务分类}_{数据域}_{业务过程}_{自定义内容}。
字段编码 | 字段类型 | 字段长度 | 字段说明 | 示例 |
order_id | INT | 10 | 订单 ID,主键 | 10001 |
product_id | INT | 10 | 商品 ID | 1001 |
category_id | INT | 10 | 商品品类 ID | 101 |
category_name | STRING | 50 | 商品品类名称 | "电子产品" |
product_name | STRING | 50 | 商品名称 | "智能手机" |
quantity | INT | 5 | 商品数量,正整数 | 2 |
unit_price | DECIMAL | 10,2 | 商品单价,保留两位小数 | 99.99 |
amount | DECIMAL | 10,2 | 商品金额小计,即商品数量乘以单价 | 199.98 |
order_time | DATETIME | - | 下单时间,精确到分钟 | "2024-04-04 10:30:00" |
shipping_city_id | INT | 10 | 收货地址城市 ID,外键 | 1101 |
shipping_city_name | STRING | 50 | 城市名称 | "北京市" |
shipping_address | TEXT | - | 收货地址,包含省、市、区、详细地址 | "北京市朝阳区 XXX 小区" |
pt_date | STRING | 50 | 分区字段 | "2024-04-01" |
补充说明:Hive 表分区
什么是分区
分区是一种重要的数据库优化技术,它通过将数据集划分为更小的、逻辑上独立的部分,来提高性能、简化管理、降低成本,并提高数据的可用性和安全性。
尤其在大数据场景下,对表设置分区尤其重要。
Hive 表分区存储的好处
对 Hive 进行分区存储的好处可体现以下多个方面:
优点 | 说明 |
提高查询性能 | 通过将数据分散存储在不同的分区中,查询时可以针对特定分区进行,避免了扫描整个表的数据,从而显著减少了查询时间。 |
优化数据管理 | 分区是一种逻辑上的数据组织方式,便于进行数据维护、清理和批量操作,如备份和恢复。 |
水平扩展 | 分区可以水平分散数据存储压力,将数据物理上分布到不同的存储单元,提高了系统的扩展性。 |
减少数据倾斜 | 在数据量不均匀的情况下,分区可以避免数据倾斜问题,即避免某些分区的数据量过大,而其他分区数据量过小。 |
数据隔离 | 分区可以用于数据隔离,例如,可以根据时间将数据划分为不同的分区,便于实现数据的版本控制和历史数据的管理。 |
减少数据加载时间 | 在数据加载或 ETL 过程中,可以更快地将数据加载到特定的分区,而不需要对整个表进行操作。 |
节省存储空间 | 通过分区可以删除或归档旧的分区数据,从而节省存储空间。 |
并行处理 | 分区表可以更好地利用 Hadoop 的 MapReduce 并行处理能力,因为查询可以并行地在不同的分区上执行。 |
数据安全和访问控制 | 分区可以用于实现更细粒度的数据访问控制,例如,可以对某些分区设置更严格的访问权限。 |
维护数据完整性 | 分区可以确保数据的完整性,因为每个分区可以有自己的数据完整性约束。 |
支持数据的冷热分层 | 通过分区,可以根据数据的使用频率将其分为“热数据”和“冷数据”,并采取不同的存储策略。 |
简化数据 ETL 过程 | 在数据抽取、转换和加载过程中,分区可以简化数据的组织和处理流程。 |
提高数据可用性 | 分区可以提高数据的可用性,因为即使某个分区不可用,也不影响对其他分区的访问。 |
因此,在创建 Hive 表时尽量在建立之初就规划好分区字段。
4. 汇总数据层 DWS
构建可供业务使用粒度的汇总指标数据表。
构建汇总表:此处不需要建表操作,后续教学中会有涉及。
商品销售情况每日汇总表:dws_trade_order_productsales_1d。
建议命名格式为:dws_{业务分类}_{数据域}_{自定义内容}_{时间周期}。
字段编码 | 字段类型 | 字段长度 | 字段说明 | 示例 |
order_date | DATE | - | 日期 | 2021-04-01 |
city_id | INT | 10 | 城市 ID | 1 |
category_id | INT | 10 | 商品品类 ID | 1 |
city_name | STRING | 50 | 城市名称 | "北京" |
category_name | STRING | 50 | 商品品类名称 | "电子产品" |
quantity | INT | 10 | 商品总销量 | 100 |
amount | DECIMAL | (10, 2) | 商品总销售额 | 9999.99 |
pt_date | STRING | 50 | 分区字段 | "2021-04-01" |
5. 应用数据层 ADS
构建面向最终业务分析需求的指标表,由于此场景比较简单,此处暂时忽略。