大数据开发之离线数仓项目（2业务数据采集平台）（可面试使用）

第 1 章：电商业务简介

1.1 电商业务流程

用户点击电商首页开始浏览，可能会通过分类查询也可能通过全文搜索寻找自己中意的商品，这些商品无疑都是存储在后台的管理系统中的。
当用户寻找到自己中意的商品，可能会想要购买，将商品添加到购物车后发现需要登录，登录后对商品进行结算，这时候购物车的管理和商品订单信息的生成都会对业务数据库产生影响，会生成响应的订单数据和支付数据。
订单正式生成之后，还会对订单进行跟踪处理，直到订单全部完成。
电商的主要业务流程包括用户前台浏览商品时的商品详情的管理，用户商品加入购物车进行支付时用户个人中心&支付服务的管理，用户支付完成后订单后台服务的管理，这些流涉及到几十个业务数据表。

1.2 电商常识

1.2.1 sku和spu

sku=库存量基本单位。现在已经被引申为产品统一编号的简称，每种产品均对应有唯一的sku号。
spu，商品信息聚合的最小单位，是一组可复用、易检索的标准化信息集合。
例如：iphonex手机就是spu。一台银色、128G内存的iphonex，就是sku。

1.2.2 平台属性和销售属性

1、平台属性

2、销售属性

第 2 章：业务数据介绍

2.1 电商系统表结构

2.1.1 活动信息表（activity_info）

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

2.1.2 活动规则表（activity_rule）

id	编号
activity_id	活动id
activity_type	活动类型
condition_amount	满减金额
condition_num	满减件数
benefit_amount	优惠金额
benefit_discount	优惠折扣
benefit_level	优惠级别

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	编号
name	分类名称

2.1.7 二级分类表（base_category2）

字段名	字段说明
id	编号
name	二级分类名称
category1_id	一级分类名称

2.1.8 三级分类表（base_category3）

字段名	字段说明
id	编号
name	三级分类名称
category2_id	二级分类编号

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	iso3166编码

2.1.11 地区表（base_region）

字段名	字段说明
id	大区id
region_name	大区名称

2.1.12 品牌表（base_trademark）

字段名	字段说明
id	编号
tm_name	属性值
logo_url	品牌logo的图片路径

2.1.13 购物车表（cart_info）

字段名	字段说明
id	编号
user_id	用户id
sku_id	skuid
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	订单编号
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	满额数（3）
condition_num	满件数（4）
activity_id	活动编号
benefit_amount	减金额（1 3）
benefit_discount	折扣（2 4）
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	skuid
spu_id	商品id
is_cancel	是否已取消 0 正常 1 已取消
create_time	创建时间
cancel_time	修改时间

2.1.19 订单明细表（order_detail）

字段名	字段说明
id	编号
order_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	分摊优惠卷减免金额

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

字段名	字段说明
id	编号
order_id	订单id
order_detail_id	订单明细id
activity_id	活动id
activity_rule_id	活动规则
sku_id	skuid
create_time	获取时间

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

字段名	字段说明
id	编号
order_id	订单id
order_detail_id	订单明细id
coupon_id	购物卷id
coupon_use_id	购物卷领用id
sku_Id	skuid
create_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	地区
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	skuid
refund_type	退款类型
refund_num	退款件数
refund_amount	退款金额
refund_reason_type	原因类型
refund_reason_txt	原因内容
refund_status	退款状态（0、待审批 1、已退款）
create_time	创建时间

2.1.24 订单状态流水表（order_status_log）

字段名	字段说明
id	编号
order_id	订单编号
order_status	订单状态
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	回调信息

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	回调信息

2.1.27 sku平台属性表（sku_attr_value）

字段名	字段说明
id	编号
attr_id	属性id（冗余）
value_id	属性值id
sku_id	skuid
attr_name	属性名称
value_name	属性值名称

2.1.28 sku信息表（sku_info）

字段名	字段说明
id	库存id（itemid）
spu_id	商品id
price	价格
sku_name	sku名称
sku_desc	商品规格秒速
weight	重量
tm_id	品牌（冗余）
category3_id	三级分类id（冗余）
sku_default_img	默认显示图片（冗余）
is_sale	是否销售（1、是 0、否）
create_time	创建时间

2.1.29 sku销售属性表（sku_sale_attr_value）

字段名	字段说明
id	id
sku_id	库存单元id
spu_id	spu_Id（冗余）
sale_attr_value_id	销售属性值id
sale_attr_id	销售属性id
sale_attr_name	销售属性值名称
sale_attr_value_name	销售属性值名称

2.1.30 spu信息表（spu_info）

字段名	字段说明
id	商品id
spu_name	商品名称
description	商品描述（后台描述）
category3_id	三级分类id
tm_id	品牌id

2.1.31 spu销售属性表（spu_sale_attr）

字段名	字段说明
id	编号（业务中无关联）
spu_id	商品id
base_sale_attr_id	销售属性id
sale_attr_name	销售属性名称（冗余）

2.1.32 spu销售属性值表（spu_sale_attr_value）

字段名	字段说明
id	销售属性值编号
spu_id	商品id
base_sale_attr_id	销售属性id
sale_attr_value_name	销售属性值名称
sale_attr_name	销售属性名称（冗余）

2.1.33 用户地址表（user_address）|

字段名	字段说明
id	编号
user_id	用户id
province_id	省份id
user_address	用户地址
consignee	收件人
phone_num	联系方式
is_default	是否是默认

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 mysql安装

2.1.1 安装包准备

1、将安装包和jdbc驱动上传到/opt/software，共计6个

01_mysql-community-common-5.7.16-1.el7.x86_64.rpm
02_mysql-community-libs-5.7.16-1.el7.x86_64.rpm
03_mysql-community-libs-compat-5.7.16-1.el7.x86_64.rpm
04_mysql-community-client-5.7.16-1.el7.x86_64.rpm
05_mysql-community-server-5.7.16-1.el7.x86_64.rpm
mysql-connector-java-5.1.27-bin.jar

2、如果是虚拟机按照如下步骤执行
1）卸载自带的mysql-libs

[atguigu@hadoop102 software]$ rpm -qa | grep -i -E mysql\|mariadb | xargs -n1 sudo rpm -e --nodeps

3、如果是阿里云服务器按照如下步骤执行
说明：由于阿里云服务器按照的是linux最小系统板，没有如下工具，所以需要安装
1）卸载mysql依赖，虽然机器上没有装mysql，但是这一步不可少

[atguigu@hadoop102 software]# sudo yum remove mysql-libs

2）下载依赖并安装

[atguigu@hadoop102 software]# sudo yum install libaio
[atguigu@hadoop102 software]# sudo yum -y install autoconf

2.1.2 安装mysql

1、安装mysql依赖

[atguigu@hadoop102 software]$ sudo rpm -ivh 01_mysql-community-common-5.7.16-1.el7.x86_64.rpm
[atguigu@hadoop102 software]$ sudo rpm -ivh 02_mysql-community-libs-5.7.16-1.el7.x86_64.rpm
[atguigu@hadoop102 software]$ sudo rpm -ivh 03_mysql-community-libs-compat-5.7.16-1.el7.x86_64.rpm

2、安装mysql-client

[atguigu@hadoop102 software]$ sudo rpm -ivh 04_mysql-community-client-5.7.16-1.el7.x86_64.rpm

3、安装mysql-server

[atguigu@hadoop102 software]$ sudo rpm -ivh 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm

4、启动mysql

[atguigu@hadoop102 software]$ sudo systemctl start mysqld

5、查看mysql密码

[atguigu@hadoop102 software]$ sudo cat /var/log/mysqld.log | grep password

2.1.3 配置mysql

配置只要是root用户+密码，在任何主机上都能登录mysql数据库
重新设置账号密码。

2.2 业务数据模拟

2.2.1 连接数据库

2.2.2 建表语句

创建数据库名称为gmall，编码utf-8，排序规则为utf8_general_ci

2.2.3 生成业务数据

1、在102的/opt/module/目录下创建db_log文件夹

[atguigu@hadoop102 module]$ mkdir db_log/

2、吧gmall2020-mock-db-2021-11-14.jar和application.properties上传到hadoop102的/opt/module/db_log路径上。
3、根据需求修改application.properties相关配置

logging.level.root=info


spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://hadoop102:3306/gmall?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=000000

logging.pattern.console=%m%n


mybatis-plus.global-config.db-config.field-strategy=not_null


#业务日期
mock.date=2020-06-14
#是否重置  注意：第一次执行必须设置为1，后续不需要重置不用设置为1
mock.clear=1
#是否重置用户 注意：第一次执行必须设置为1，后续不需要重置不用设置为1
mock.clear.user=1

#生成新用户数量
mock.user.count=100
#男性比例
mock.user.male-rate=20
#用户数据变化概率
mock.user.update-rate:20

#收藏取消比例
mock.favor.cancel-rate=10
#收藏数量
mock.favor.count=100

#每个用户添加购物车的概率
mock.cart.user-rate=50
#每次每个用户最多添加多少种商品进购物车
mock.cart.max-sku-count=8 
#每个商品最多买几个
mock.cart.max-sku-num=3 

#购物车来源  用户查询，商品推广，智能推荐, 促销活动
mock.cart.source-type-rate=60:20:10:10

#用户下单比例
mock.order.user-rate=50
#用户从购物中购买商品比例
mock.order.sku-rate=50
#是否参加活动
mock.order.join-activity=1
#是否使用购物券
mock.order.use-coupon=1
#购物券领取人数
mock.coupon.user-count=100

#支付比例
mock.payment.rate=70
#支付方式 支付宝：微信 ：银联
mock.payment.payment-type=30:60:10


#评价比例 好：中：差：自动
mock.comment.appraise-rate=30:10:10:50

#退款原因比例：质量问题 商品描述与实际描述不一致 缺货 号码不合适 拍错 不想买了 其他
mock.refund.reason-rate=30:10:20:5:15:5:5


这个配置文件被用于控制模拟环境中的各种参数，如用户行为、购物车使用、支付方式等，以产生相应的模拟数据。

4、并在该目录下执行，如下命令，生成2020-06-14日期数据

[atguigu@hadoop102 db_log]$ java -jar gmall2020-mock-db-2021-11-14.jar

5、查看gmall数据库，看是否出现

2.2.4 业务数据建模

可借助ezdml这款数据库设计工具，来辅助我们梳理复杂的业务表关系
1、下载地址
http://www.ezdml.com/download_cn.html
2、使用说明
1）新建模型

2）命名模型

3）点击图标，选中模型

4）导入数据库

5）配置数据库连接

6）选择导入的表（标注红点的表不需要导入）

7）建立表关系
第一步：选中主表

第二步：点击连接按钮

第三步：点击从表

第四步：效果展示

第 3 章：业务数据采集模块

3.1 采集通道

这张图描述了一个数据流动和处理的架构，主要用于数据同步和实时分析。这里是每个组件的作用：

MySQL：关系型数据库，用于存储结构化数据。这里的例子中，它包含三个数据表：cart_info（购物车信息），order_info（订单信息），payment_info（支付信息）。

Maxwell：Maxwell 是一个 MySQL 的 binlog（二进制日志）到 Kafka 的同步工具。它的作用是实时捕捉 MySQL 数据库的变化（如插入、更新和删除事件），并将这些变化作为消息推送到 Kafka。

Kafka：是一个分布式流处理平台，可以用于构建实时数据管道和流应用程序。它能够高吞吐量地处理数据流。这里的 topic: topic_db 表示 Maxwell 推送的数据将被存储在名为 topic_db 的 Kafka 主题中。

实时计算（Flink / Spark）：表示使用 Apache Flink 或 Apache Spark 这样的实时计算框架来处理 Kafka 中的数据流。这些框架能够进行复杂的数据处理和分析，例如窗口计算、状态管理和事件时间处理。

高级分析（hive）：这里指的可能是使用 Apache Hive 进行高级数据分析和业务智能报告。Hive 是建立在 Hadoop 之上的数据仓库软件，可以让用户使用类似 SQL 的语言（HiveQL）来查询和管理大数据。

3.2 采集工具

参考之前maxwell文章

3.3 采集通道maxwell配置

1、修改maxwell配置文件config.properties

[atguigu@hadoop102 maxwell]$ vim /opt/module/maxwell/config.properties

2、配置参数如下

log_level=info

producer=kafka
kafka.bootstrap.servers=hadoop102:9092,hadoop103:9092

#kafka topic配置
kafka_topic=topic_db

# mysql login info
host=hadoop102
user=maxwell
password=maxwell
jdbc_options=useSSL=false&serverTimezone=Asia/Shanghai


这段配置是为了设置日志级别，指定Kafka生产者和连接信息（包括Kafka集群的地址和主题），以及设置MySQL数据库的连接信息（包括主机名、用户名、密码以及其他JDBC连接选项）。这样的配置常见于需要处理数据流并存储到数据库的应用中，比如日志处理、消息队列处理等。

3、重新启动maxwell

[atguigu@hadoop102 bin]$ mxw.sh restart

4、通道测试
1）启动zookeeper已经kafka集群
2）启动一个kafka console consumer，消费topic_db数据

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

3）生成模拟数据

[atguigu@hadoop102 bin]$ cd /opt/module/db_log/
[atguigu@hadoop102 db_log]$ java -jar gmall2020-mock-db-2021-11-14.jar 

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":"2020-06-14 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}}

一条数据库日志消息，描述了对一个名为gmall数据库中的cart_info表进行的一项update（更新）操作。这条消息详细记录了更新操作的时间戳、事务ID (xid)、偏移量(xoffset)，以及在更新操作中被修改的数据。

描述了一个数据库更新操作，其中一条购物车记录从未下单状态更新为已下单状态，包含了关于商品、用户和操作的详细信息。