离线数仓-业务数据采集平台部分

1电商业务流程

电商的业务流程可以以一个普通用户的浏览足迹为例进行说明，用户点开电商首页开始浏览，可能会通过分类查询也可能通过全文搜索寻找自己中意的商品，这些商品无疑都是存储在后台的管理系统中的。

当用户寻找到自己中意的商品，可能会想要购买，将商品添加到购物车后发现需要登录，登录后对商品进行结算，这时候购物车的管理和商品订单信息的生成都会对业务数据库产生影响，会生成相应的订单数据和支付数据。

订单正式生成之后，还会对订单进行跟踪处理，直到订单全部完成。

电商的主要业务流程包括用户前台浏览商品时的商品详情的管理，用户商品加入购物车进行支付时用户个人中心&支付服务的管理，用户支付完成后订单后台服务的管理，这些流程涉及到了十几个甚至几十个业务数据表，甚至更多。

电商常识

SKU与SPU

        SKU=Stock Keeping Unit（库存量基本单位），现在已经被引申为产品统一编号的简称，每种产品均对应有唯一的SKU号。

        SPU（Standard Product Unit）：是商品信息聚合的最小单位，是一组可复用、易检索的标准化信息集合。

例如：iPhoneX手机就是SPU。一台银色、128G内存的、支持联通网络的iPhoneX，就是SKU。

SPU表示一类商品。同一SPU的商品可以共用商品图片、海报、销售属性等。

平台属性和销售属性

2业务数据介绍

2.1电商数据表结构

以下为本电商数仓系统涉及到的业务数据表结构关系。这34个表以订单表、用户表、SKU商品表、活动表和优惠券表为中心，延伸出了优惠券领用表、支付流水表、活动订单表、订单详情表、订单状态表、商品评论表、编码字典表退单表、SPU商品表等，用户表提供用户的详细信息，支付流水表提供该订单的支付详情，订单详情表提供订单的商品数量等情况，商品表给订单详情表提供商品的详细信息。本次讲解以此34个表为例，实际项目中，业务数据库中表格远远不止这些。

2.1.1 活动信息表（activity_info）

字段名	字段说明
id	活动ID
activity_name	活动名称
activity_type	活动类型（1：满减，2：折扣）
activity_desc	活动描述
start_time	开始时间
end_time	结束时间
create_time	创建时间
operate_time	修改时间

2.1.2 活动规则表（activity_rule）

id	编号
activity_id	活动ID
activity_type	活动类型
condition_amount	满减金额
condition_num	满减件数
benefit_amount	优惠金额
benefit_discount	优惠折扣
benefit_level	优惠级别
create_time	创建时间
operate_time	修改时间

2.1.3 活动商品关联表（activity_sku）

字段名	字段说明
id	编号
activity_id	活动ID
sku_id	SKU_ID
create_time	创建时间

2.1.4 平台属性表（base_attr_info）

字段名	字段说明
id	编号
attr_name	属性名称
category_id	品类ID
category_level	品类层级

2.1.5 平台属性值表（base_attr_value）

字段名	字段说明
id	编号
value_name	属性值名称
attr_id	属性ID

2.1.6 一级分类表（base_category1）

字段名	字段说明
id	一级品类ID
name	一级品类名称
create_time	创建时间
operate_time	修改时间

2.1.7 二级分类表（base_category2）

字段名	字段说明
id	二级品类ID
name	二级品类名称
category1_id	一级品类ID
create_time	创建时间
operate_time	修改时间

2.1.8 三级分类表（base_category3）

字段名	字段说明
id	三级品类ID
name	三级品类名称
category2_id	二级品类ID
create_time	创建时间
operate_time	修改时间

2.1.9 字典表（base_dic）

字段名	字段说明
dic_code	编号
dic_name	编码名称
parent_code	父编号
create_time	创建日期
operate_time	修改日期

2.1.10 省份表（base_province）

字段名	字段说明
id	省份ID
name	省份名称
region_id	地区ID
area_code	行政区位码
iso_code	旧版国际标准地区编码
iso_3166_2	新版国际标准地区编码
create_time	创建时间
operate_time	修改时间

2.1.11 地区表（base_region）

字段名	字段说明
id	地区ID
region_name	地区名称
create_time	创建时间
operate_time	修改时间

2.1.12 品牌表（base_trademark）

字段名	字段说明
id	品牌ID
tm_name	品牌名称
logo_url	品牌LOGO的图片路径
create_time	创建时间
operate_time	修改时间

2.1.13 购物车表（cart_info）

字段名	字段说明
id	编号
user_id	用户ID
sku_id	SKU_ID
cart_price	放入购物车时价格
sku_num	数量
img_url	图片连接
sku_name	SKU名称 (冗余)
is_checked	是否被选中
create_time	创建时间
operate_time	修改时间
is_ordered	是否已下单
order_time	下单时间
source_type	来源类型
source_id	来源编号

2.1.14 评价表（comment_info）

字段名	字段说明
id	编号
user_id	用户ID
nick_name	用户昵称
head_img	图片
sku_id	商品SKU_ID
spu_id	商品SPU_ID
order_id	订单ID
appraise	评价 1 好评 2 中评 3 差评
comment_txt	评价内容
create_time	创建时间
operate_time	修改时间

2.1.15 优惠券信息表（coupon_info）

字段名	字段说明
id	购物券编号
coupon_name	购物券名称
coupon_type	购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券
condition_amount	满额数
condition_num	满件数
activity_id	活动编号
benefit_amount	减免金额
benefit_discount	折扣
create_time	创建时间
range_type	范围类型 1、商品(spuid) 2、品类(三级分类id) 3、品牌
limit_num	最多领用次数
taken_count	已领用次数
start_time	可以领取的开始时间
end_time	可以领取的结束时间
operate_time	修改时间
expire_time	过期时间
range_desc	范围描述

2.1.16 优惠券优惠范围表（coupon_range）

字段名	字段说明
id	编号
coupon_id	优惠券ID
range_type	范围类型 1、商品(spuid) 2、品类(三级分类id) 3、品牌
range_id	范围ID

2.1.17 优惠券领用表（coupon_use）

字段名	字段说明
id	编号
coupon_id	购物券ID
user_id	用户ID
order_id	订单ID
coupon_status	购物券状态（1：未使用 2：已使用）
get_time	获取时间
using_time	使用时间
used_time	支付时间
expire_time	过期时间

2.1.18 收藏表（favor_info）

字段名	字段说明
id	编号
user_id	用户ID
sku_id	SKU_ID
spu_id	SPU_ID
is_cancel	是否已取消 0 正常 1 已取消
create_time	创建时间
operate_time	修改时间

2.1.19 订单明细表（order_detail）

字段名	字段说明
id	编号
order_id	订单ID
sku_id	SKU_ID
sku_name	SKU名称（冗余)
img_url	图片链接（冗余)
order_price	购买价格(下单时sku价格）
sku_num	购买个数
create_time	创建时间
source_type	来源类型
source_id	来源编号
split_total_amount	分摊总金额
split_activity_amount	分摊活动减免金额
split_coupon_amount	分摊优惠券减免金额
operate_time	修改时间

2.1.20 订单明细活动关联表（order_detail_activity）

字段名	字段说明
id	编号
order_id	订单ID
order_detail_id	订单明细ID
activity_id	活动ID
activity_rule_id	活动规则ID
sku_id	SKU_ID
create_time	创建时间
operate_time	修改时间

2.1.21 订单明细优惠券关联表（order_detail_coupon）

字段名	字段说明
id	编号
order_id	订单ID
order_detail_id	订单明细ID
coupon_id	购物券ID
coupon_use_id	购物券领用ID
sku_id	SKU_ID
create_time	获取时间
operate_time	修改时间

2.1.22 订单表(order_info）

字段名	字段说明
id	编号
consignee	收货人
consignee_tel	收件人电话
total_amount	总金额
order_status	订单状态
user_id	用户ID
payment_way	付款方式
delivery_address	送货地址
order_comment	订单备注
out_trade_no	订单交易编号（第三方支付用)
trade_body	订单描述(第三方支付用)
create_time	创建时间
operate_time	修改时间
expire_time	失效时间
process_status	进度状态
tracking_no	物流单编号
parent_order_id	父订单编号
img_url	图片路径
province_id	省份ID
activity_reduce_amount	活动减免金额
coupon_reduce_amount	优惠券减免金额
original_total_amount	原价金额
feight_fee	运费金额
feight_fee_reduce	运费减免金额
refundable_time	可退款时间（签收后30天）

2.1.23 退单表（order_refund_info）

字段名	字段说明
id	编号
user_id	用户ID
order_id	订单ID
sku_id	SKU_ID
refund_type	退款类型
refund_num	退货件数
refund_amount	退款金额
refund_reason_type	原因类型
refund_reason_txt	原因内容
refund_status	退款状态（0：待审批 1：已退款）
create_time	创建时间
operate_time	修改时间

2.1.24 订单状态流水表（order_status_log）

字段名	字段说明
id	编号
order_id	订单编号
order_status	订单状态
create_time	创建时间
operate_time	修改时间

2.1.25 支付表（payment_info）

字段名	字段说明
id	编号
out_trade_no	对外业务编号
order_id	订单编号
user_id	用户ID
payment_type	支付类型（微信 支付宝）
trade_no	交易编号
total_amount	支付金额
subject	交易内容
payment_status	支付状态
create_time	创建时间
callback_time	回调时间
callback_content	回调信息
operate_time	修改时间

2.1.26 退款表（refund_payment）

字段名	字段说明
id	编号
out_trade_no	对外业务编号
order_id	订单编号
sku_id	SKU_ID
payment_type	支付类型（微信 支付宝）
trade_no	交易编号
total_amount	退款金额
subject	交易内容
refund_status	退款状态
create_time	创建时间
callback_time	回调时间
callback_content	回调信息
operate_time	修改时间

2.1.27 SKU平台属性表（sku_attr_value）

字段名	字段说明
id	编号
attr_id	属性ID（冗余)
value_id	属性值ID
sku_id	SKU_ID
attr_name	属性名称
value_name	属性值名称
create_time	创建时间
operate_time	修改时间

2.1.28 SKU信息表（sku_info）

字段名	字段说明
id	SKU_ID
spu_id	SPU_ID
price	价格
sku_name	SKU名称
sku_desc	商品规格描述
weight	重量
tm_id	品牌ID(冗余)
category3_id	三级品类ID（冗余)
sku_default_img	默认显示图片(冗余)
is_sale	是否销售（1：是 0：否）
create_time	创建时间
operate_time	修改时间

2.1.29 SKU销售属性表（sku_sale_attr_value）

字段名	字段说明
id	编号
sku_id	SKU_ID
spu_id	SPU_ID（冗余）
sale_attr_value_id	销售属性值ID
sale_attr_id	销售属性ID
sale_attr_name	销售属性名称
sale_attr_value_name	销售属性值名称
create_time	创建时间
operate_time	修改时间

2.1.30 SPU信息表（spu_info）

字段名	字段说明
id	SPU_ID
spu_name	SPU名称
description	商品描述(后台简述）
category3_id	三级品类ID
tm_id	品牌ID
create_time	创建时间
operate_time	修改时间

2.1.31 SPU销售属性表（spu_sale_attr）

字段名	字段说明
id	编号（业务中无关联）
spu_id	SPU_ID
base_sale_attr_id	销售属性ID
sale_attr_name	销售属性名称（冗余）
create_time	创建时间
operate_time	修改时间

2.1.32 SPU销售属性值表（spu_sale_attr_value）

字段名	字段说明
id	销售属性值编号
spu_id	SPU_ID
base_sale_attr_id	销售属性ID
sale_attr_value_name	销售属性值名称
sale_attr_name	销售属性名称（冗余）
create_time	创建时间
operate_time	修改时间

2.1.33 用户地址表（user_address）

字段名	字段说明
id	编号
user_id	用户ID
province_id	省份ID
user_address	用户地址
consignee	收件人
phone_num	联系方式
is_default	是否是默认
create_time	创建时间
operate_time	修改时间

2.1.34 用户信息表（user_info）

字段名	字段说明
id	编号
login_name	用户名称
nick_name	用户昵称
passwd	用户密码
name	用户姓名
phone_num	手机号
email	邮箱
head_img	头像
user_level	用户级别
birthday	用户生日
gender	性别 M男,F女
create_time	创建时间
operate_time	修改时间
status	状态

2.1.35 营销坑位表（promotion_pos）

字段名	字段说明
id	营销坑位ID
pos_location	营销坑位位置
pos_type	营销坑位类型
promotion_type	营销类型
create_time	创建时间
operate_time	修改时间

2.1.36 营销渠道表（promotion_refer）

字段名	字段说明
id	营销渠道ID
refer_name	营销渠道名称
create_time	创建时间
operate_time	修改时间

电商业务表关系图

后台管理系统图

2.2数据模拟

2.2.1 MySQL安装

2.2.2 连接MySQL

通过MySQL可视化客户端连接数据库(hadoop102)（我mac上用的是DBeaver），尚硅谷示例用的是Navicat

2.2.3 建表语句

1）创建数据库

2）设置数据库名称为gmall，编码（字符集）为utf8mb4，排序规则为utf8mb4_general_ci

3）导入数据库结构脚本（gmall.sql）

信息日志显示Finished

2.2.4 业务数据建模

可借助EZDML这款数据库设计工具，来辅助我们梳理复杂的业务表关系。

下载地址：EZDML - 下载

使用说明：

        新建模型——命名模型——选中模型——导入数据库——配置数据库连接

——选择导入的表

——建立表关系：

第一步：点击选中主表（主键所在表）

第二步：点击连接

第三步：点击从表，配置连接条件

效果

使用技巧：

1）缩略图

2）热键

按住shift键，用鼠标点击表，进行多选，可实现批量移动。

按住ctrl键，用鼠标圈选表，也可进行多选，实现批量移。

3业务数据采集模块

3.1采集通道（Maxwell+Kafka）

3.2采集工具 Maxwell

Maxwell 是一个开源的 MySQL 数据库中间件，主要用于捕获 MySQL 数据库中的变更事件，并将其以 JSON 格式输出到消息队列中，如 Apache Kafka 或 RabbitMQ，以实现实时数据同步和流式处理。

Maxwell 的主要特点和功能包括：

1. 变更数据捕获（Change Data Capture, CDC）：Maxwell 可以监视 MySQL 数据库中的表，实时捕获表中的变更操作，如插入、更新、删除等，然后将这些变更操作以 JSON 格式输出。

2. JSON 格式输出：捕获的变更事件以 JSON 格式输出，可以方便地被消息队列消费者或其他数据处理工具进行处理和分析。

3. 异步处理：Maxwell 使用异步的方式进行数据捕获和输出，不会对 MySQL 数据库的性能产生太大的影响。

4. 可配置性：Maxwell 提供了丰富的配置选项，可以根据需求定制捕获和输出的行为，如指定要捕获的表、指定输出的消息队列等。

5. 支持多种消息队列：Maxwell 支持将捕获的变更事件输出到多种消息队列中，如 Apache Kafka、RabbitMQ 等，使其能够与现有的消息系统进行集成。

3.3采集通道Maxwell配置

1）启动ZooKeeper，Kafka集群及Maxwell

1. 配置 Maxwell： 编辑 Maxwell 的配置文件，通常为 config.properties。在配置文件中，需要指定 MySQL 数据库的连接信息、要监视的数据库和表、输出到的消息队列类型和连接信息等。确保您的配置文件符合您的需求，并且包含正确的参数。

2. 启动 Maxwell： 在配置完成后，通过命令行启动 Maxwell。可以使用类似以下的命令：

   maxwell --config /path/to/your/config.properties

2）启动一个Kafka Console Consumer，消费topic_db数据

[atguigu@hadoop102 maxwell]$ cd $KAFKA_HOME
[atguigu@hadoop103 kafka]$ bin/kafka-console-consumer.sh --bootstrap-server hadoop102:9092 --topic topic_db

3）生成模拟数据

[atguigu@hadoop102 bin]$ lg.sh

4）观察Kafka消费者能否消费到数据

{"database":"gmall","table":"cart_info","type":"update","ts":1592270938,"xid":13090,"xoffset":1573,"data":{"id":100924,"user_id":"93","sku_id":16,"cart_price":4488.00,"sku_num":1,"img_url":"http://47.93.148.192:8080/group1/M00/00/02/rBHu8l-sklaALrngAAHGDqdpFtU741.jpg","sku_name":"华为 HUAWEI P40 麒麟990 5G SoC芯片 5000万超感知徕卡三摄 30倍数字变焦 8GB+128GB亮黑色全网通5G手机","is_checked":null,"create_time":"2022-06-08 09:28:57","operate_time":null,"is_ordered":1,"order_time":"2021-10-17 09:28:58","source_type":"2401","source_id":null},"old":{"is_ordered":0,"order_time":null}}