当前位置:   article > 正文

数仓采集项目【06电商系统表结构、MySQL安装、Sqoop安装及使用】_mysql商城数据库表sku spu

mysql商城数据库表sku spu

文章目录

一 电商业务简介

1 SPU和SKU

SPU(Standard Product Unit):是商品信息聚合的最小单位,是一组可复用、易检索的标准化信息集合。

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

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

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

2 电商系统表结构

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

电商业务表
在这里插入图片描述
后台管理系统表
在这里插入图片描述

(1)活动信息表(activity_info)

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

(2)活动规则表(activity_rule)

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

(3)活动商品关联表(activity_sku)

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

(4)平台属性表(base_attr_info)

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

(5)平台属性值表(base_attr_value)

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

(6)一级分类表(base_category1)

字段名字段说明
id编号
name分类名称

(7)二级分类表(base_category2)

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

(8)三级分类表(base_category3)

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

(9)字典表(base_dic)

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

(10)省份表(base_province)

字段名字段说明
idid
name省名称
region_id大区id
area_code行政区位码
iso_code国际编码
iso_3166_2ISO3166编码

(11) 地区表(base_region)

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

(12)品牌表(base_trademark)

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

(13)购物车表(cart_info)

字段名字段说明
id编号
user_id用户id
sku_idskuid
cart_price放入购物车时价格
sku_num数量
img_url图片文件
sku_namesku名称 (冗余)
is_checked
create_time创建时间
operate_time修改时间
is_ordered是否已经下单
order_time下单时间
source_type来源类型
source_id来源编号

(14)评价表(comment_info)

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

(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范围描述

(16)优惠券优惠范围表(coupon_range)

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

(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过期时间

(18) 收藏表(favor_info)

字段名字段说明
id编号
user_id用户名称
sku_idskuid
spu_id商品id
is_cancel是否已取消 0 正常 1 已取消
create_time创建时间
cancel_time修改时间

(19)订单明细表(order_detail)

字段名字段说明
id编号
order_id订单编号
sku_idsku_id
sku_namesku名称(冗余)
img_url图片名称(冗余)
order_price购买价格(下单时sku价格)
sku_num购买个数
create_time创建时间
source_type来源类型
source_id来源编号
split_total_amount
split_activity_amount
split_coupon_amount

(20)订单明细活动关联表(order_detail_activity)

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

(21)订单明细优惠券关联表(order_detail_coupon)

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

(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天)

(23)退单表(order_refund_info)

字段名字段说明
id编号
user_id用户id
order_id订单id
sku_idskuid
refund_type退款类型
refund_num退货件数
refund_amount退款金额
refund_reason_type原因类型
refund_reason_txt原因内容
refund_status退款状态(0:待审批 1:已退款)
create_time创建时间

(24)订单状态流水表(order_status_log)

字段名字段说明
id
order_id
order_status
operate_time

(25)支付表(payment_info)

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

(26)退款表(refund_payment)

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

(27)SKU平台属性表(sku_attr_value)

字段名字段说明
id编号
attr_id属性id(冗余)
value_id属性值id
sku_idskuid
attr_name属性名称
value_name属性值名称

(28)SKU信息表(sku_info)

字段名字段说明
id库存id(itemID)
spu_id商品id
price价格
sku_namesku名称
sku_desc商品规格描述
weight重量
tm_id品牌(冗余)
category3_id三级分类id(冗余)
sku_default_img默认显示图片(冗余)
is_sale是否销售(1:是 0:否)
create_time创建时间

(29)SKU销售属性表(sku_sale_attr_value)

字段名字段说明
idid
sku_id库存单元id
spu_idspu_id(冗余)
sale_attr_value_id销售属性值id
sale_attr_id
sale_attr_name
sale_attr_value_name

(30)SPU信息表(spu_info)

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

(31)SPU销售属性表(spu_sale_attr)

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

(32)SPU销售属性值表(spu_sale_attr_value)

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

(33)用户地址表(user_address)

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

(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状态

二 业务数据采集模块

在这里插入图片描述

1 MySQL安装

sudo yum install -y libaio
#卸载自带的Mysql-libs(如果之前安装过mysql,要全都卸载掉)
rpm -qa | grep -i -E mysql\|mariadb | xargs -n1 sudo rpm -e --nodeps
#将安装包和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
#安装mysql依赖
sudo rpm -ivh 01_mysql-community-common-5.7.16-1.el7.x86_64.rpm
sudo rpm -ivh 02_mysql-community-libs-5.7.16-1.el7.x86_64.rpm
sudo rpm -ivh 03_mysql-community-libs-compat-5.7.16-1.el7.x86_64.rpm
#安装mysql-client
sudo rpm -ivh 04_mysql-community-client-5.7.16-1.el7.x86_64.rpm
#mysql-server
sudo rpm -ivh 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm
#初始化
sudo mysqld --initialize --user=mysql
#启动mysql
sudo systemctl start mysqld
#查看服务状态
sudo systemctl status mysqld
#查看mysql密码,辅助
sudo cat /var/log/mysqld.log | grep password
#用刚刚查到的密码进入mysql(如果报错,给密码加单引号)
mysql -uroot -p 'password'
#设置复杂密码(由于mysql密码策略,此密码必须足够复杂)
set password=password("Qs23=zs32");
#更改mysql密码策略
set global validate_password_length=4;
set global validate_password_policy=0;
#设置简单好记的密码
set password = password('123456')
#设置远程连接
use mysql
select user, host from user;
update user set host="%" where user="root";
flush privileges;
quit;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41

2 业务数据生成

(1)连接mysql

通过MySQL可视化客户端连接数据库。


在这里插入图片描述

(2)建表

通过SQLyog创建数据库gmall

在这里插入图片描述

设置数据库名称为gmall,编码为utf-8排序规则为utf8_general_ci
在这里插入图片描述

导入数据库结构脚本(gmall.sql

在这里插入图片描述

在linux环境下执行脚本

创建数据库,使用数据库

将脚本文件上传到linux中

在数据库中执行source /opt/software/mysql-rpms/gmall.sql;

(3)生成业务数据

#在hadoop102的/opt/module/目录下创建db_log文件夹
mkdir db_log/
#把gmall2020-mock-db-2021-01-22.jar和application.properties上传到hadoop101的/opt/module/db_log路径上。
#根据需求修改application.properties相关配置
###注意第一次启动需要将以下值置为1,之后可更改为0
#mock.clear=1
#mock.clear.user=1
java -jar gmall2020-mock-db-2021-01-22.jar
#查看gmall数据库,观察是否有数据出现
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
#生成业务数据脚本
cp log db.sh

#!/bin/bash
for i in hadoop101 
do
  ssh $i 'cd /opt/module/db_log ; java -jar gmall2020-mock-db-2021-01-22.jar 1>/dev/null 2>&1 &'
done


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

(4)业务数据建模

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

下载地址:http://www.ezdml.com/download_cn.html

3 Sqoop安装

Apache Sqoop™ 是一种工具,用于在 Apache Hadoop 和结构化数据存储(如关系数据库)之间高效传输批量数据。

Sqoop官网:https://sqoop.apache.org/

#下载地址:http://mirrors.hust.edu.cn/apache/sqoop/1.4.6/
#上传安装包到hadoop101的/opt/software路径中
tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/module/
mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha/ sqoop-1.4.6
#进入到/opt/module/sqoop/conf目录,重命名配置文件
mv sqoop-env-template.sh sqoop-env.sh
vim sqoop-env.sh
#增加如下内容
export HADOOP_COMMON_HOME=/opt/module/hadoop-3.1.3
export HADOOP_MAPRED_HOME=/opt/module/hadoop-3.1.3
export HIVE_HOME=/opt/module/hive
export ZOOKEEPER_HOME=/opt/module/zookeeper-3.5.7
export ZOOCFGDIR=/opt/module/zookeeper-3.5.7/conf
#配置环境变量
sudo vim /etc/profile.d/my_env.sh
#SQOOP_HOME
export SQOOP_HOME=/opt/module/sqoop-1.4.6
export PATH=$PATH:$SQOOP_HOME/bin
#拷贝JDBC驱动
cp mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop-1.4.6/lib/
#查看是否拷贝成功
ll | grep mysql-connector-java-5.1.27-bin.jar 
#使用一下命令验证sqoop
sqoop help
#测试Sqoop是否能够成功连接数据库
sqoop list-databases --connect jdbc:mysql://hadoop101:3306/ --username root --password 123456
#出现以下输出内容
information_schema
gmall
mysql
performance_schema
sys
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32

4 Sqoop案例–从mysql将数据导入到HDFS

#将user_info 表中的 id, login_name, nick_name 字段的值导入到HDFS中,要求只导入id 大于等于100 小于等于200的数据
select id,login_name,nick_name from user_info where id >= 100 and id <= 200
#执行以下语句前先启动hadoop
#方法一
sqoop import \
--connect jdbc:mysql://hadoop101:3306/gmall \
--username root \
--password 123456 \
--table user_info \
--columns id,login_name,nick_name \
--where "id >=100 and id <=200" \
--target-dir /testsqoop \
--delete-target-dir \
--num-mappers 2 \
--fields-terminated-by "/t" \
--split-by id
#方法二
sqoop import \
--connect jdbc:mysql://hadoop101:3306/gmall \
--username root \
--password 123456 \
--query "select id,login_name,nick_name from user_info where id >=100 and id <=200 and \$CONDITIONS" \
--target-dir /testsqoop \
--delete-target-dir \
--num-mappers 2 \
--fields-terminated-by "/t" \
--split-by id

---------------------------------------------------------------------------------
#分析
select id,login_name,nick_name from user_info where id >=100 and id <=200 此条语句在执行的时候会分解为
select id,login_name,nick_name from user_info where id >=100 and id <=200  and id <=149
select id,login_name,nick_name from user_info where id >=100 and id <=200  and id >=150
这两条语句,后续程序执行时当修改语句,会放在$CONDITIONS的位置
如果sql语句没有条件,写成
select id,login_name,nick_name from user_info where 1=1 and  \$CONDITIONS
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/592016
推荐阅读
相关标签
  

闽ICP备14008679号