数仓采集项目【06电商系统表结构、MySQL安装、Sqoop安装及使用】_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）

（2）活动规则表（activity_rule）

（3）活动商品关联表（activity_sku）

（4）平台属性表（base_attr_info）

（5）平台属性值表（base_attr_value）

（6）一级分类表（base_category1）

（7）二级分类表（base_category2）

（8）三级分类表（base_category3）

（9）字典表（base_dic）

（10）省份表（base_province）

（11） 地区表（base_region）

（12）品牌表（base_trademark）

（13）购物车表（cart_info）

（14）评价表（comment_info）

（15）优惠券信息表（coupon_info）

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

（17）优惠券领用表（coupon_use）

（18） 收藏表（favor_info）

（19）订单明细表（order_detail）

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

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

（22）订单表(order_info）

（23）退单表（order_refund_info）

（24）订单状态流水表（order_status_log）

（25）支付表（payment_info）

（26）退款表（refund_payment）

（27）SKU平台属性表（sku_attr_value）

（28）SKU信息表（sku_info）

（29）SKU销售属性表（sku_sale_attr_value）

（30）SPU信息表（spu_info）

（31）SPU销售属性表（spu_sale_attr）

（32）SPU销售属性值表（spu_sale_attr_value）

（33）用户地址表（user_address）

（34）用户信息表（user_info）

二 业务数据采集模块

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