实战-数据仓库构建（五）

问题导读：

1. 电商业务的流程是怎么样的？
2. 电商业务库表结构如何设计？
3. 电商表的同步策略是怎么样的？

一、电商业务简介

1.1 电商业务流程



1.2 电商常识（SKU、SPU）

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

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

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

SPU 表示一类商品。好处就是：可以共用商品图片，海报、销售属性等

1.3 电商业务表结构

电商表结构



1.3.1 订单表（order_info）



1.3.2 订单详情表（order_detail）



1.3.3 SKU 商品表（sku_info）



1.3.4 用户表（user_info）



1.3.5 商品一级分类表（base_category1）



1.3.6 商品二级分类表（base_category2）



1.3.7 商品三级分类表（base_category3）



1.3.8 支付流水表（payment_info）



1.3.9 省份表（base_province）



1.3.10 地区表（base_region）



1.3.11 品牌表（base_trademark）



1.3.12 订单状态表（order_status_log）



1.3.13 SPU 商品表（spu_info）



1.3.14 商品评论表（comment_info）



1.3.15 退单表（order_refund_info）



1.3.16 加购表（cart_info）



1.3.17 商品收藏表（favor_info）



1.3.18 优惠券领用表（coupon_use）



1.3.19 优惠券表（coupon_info）



1.3.20 活动表（activity_info）



1.3.21 活动订单关联表（activity_order）



1.3.22 优惠规则表（activity_rule）



1.3.23 编码字典表（base_dic）



1.3.24 活动参与商品表（activity_sku）（暂不导入）



1.4 时间相关表

1.4.1 时间表（date_info）



1.4.2 假期表（holiday_info）



1.4.3 假期年表（holiday_year）



二、业务数据采集模块



2.1 MySQL 安装

MySQL详细安装步骤请点击博客【Linux】MySQL安装

2.2 Sqoop 安装

MySQL详细安装步骤请点击博客【Sqoop】Sqoop入门解析

2.3 业务数据生成

2.3.1 连接 MySQL

通过 MySQL 操作可视化工具 SQLyog 连接 MySQL

2.3.2 建表语句

1）通过 SQLyog 创建数据库 gmall
2）设置数据库编码



3）导入数据库结构脚本（gmall2020-03-16.sql）

2.3.3 生成业务数据

1）在 node01 的 /opt/modules/目录下创建 db_log 文件夹

2）把 gmall-mock-db-2020-03-16-SNAPSHOT.jar 和 application.properties 上传到 node01
的/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://node01:3306/gmall?charac
terEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=hadoop
logging.pattern.console=%m%n
mybatis-plus.global-config.db-config.field-strategy=not_null
#业务日期
mock.date=2020-03-10
#是否重置
mock.clear=1
#是否生成新用户
mock.user.count=50
#男性比例
mock.user.male-rate=20
#收藏取消比例
mock.favor.cancel-rate=10
#收藏数量
mock.favor.count=100
#购物车数量
mock.cart.count=10
#每个商品最多购物个数
mock.cart.sku-maxcount-per-cart=3
#用户下单比例
mock.order.user-rate=80
#用户从购物中购买商品比例
mock.order.sku-rate=70
#是否参加活动
mock.order.join-activity=1
#是否使用购物券
mock.order.use-coupon=1
#购物券领取人数
mock.coupon.user-count=10
#支付比例
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-03-10 日期数据：

java -jar gmall-mock-db-2020-03-16-SNAPSHOT.jar

5）在配置文件 application.properties 中修改

mock.date=2020-03-11
mock.clear=0

6）再次执行命令，生成 2020-03-11 日期数据：

java -jar gmall-mock-db-2020-03-16-SNAPSHOT.jar

2.4 同步策略

数据同步策略的类型包括：全量表、增量表、新增及变化表
 

• 全量表：存储完整的数据
• 增量表：存储新增加的数据
• 新增及变化表：存储新增加的数据和变化的数据
• 特殊表：只需要存储一次

2.4.1 全量同步策略



2.4.2 增量同步策略



2.4.3 新增及变化策略

每日新增及变化，就是存储创建时间和操作时间都是今天的数据
适用场景为，表的数据量大，既会有新增，又会有变化
例如：用户表、订单表、优惠卷领用表

2.4.4 特殊策略

某些特殊的维度表，可不必遵循上述同步策略

• 1）客观世界维度

没变化的客观世界的维度（比如性别，地区，民族，政治成分，鞋子尺码）可以只存一
份固定值。
 

• 2）日期维度

日期维度可以一次性导入一年或若干年的数据
 

• 3）地区维度

省份表、地区表

2.5 业务数据导入 HDFS

2.5.1 分析表同步策略



2.5.2 脚本编写

1）创建脚本

vim mysql_to_hdfs.sh

 2）添加如下内容：

vim mysql_to_hdfs.sh

#! /bin/bash
sqoop=/opt/modules/sqoop/bin/sqoop
do_date=`date -d '-1 day' +%F`
if [[ -n "$2" ]]; then
do_date=$2
fi
import_data(){
$sqoop import \
--connect jdbc:mysql://node01:3306/gmall \
--username root \
--password hadoop \
--target-dir /origin_data/gmall/db/$1/$do_date \
--delete-target-dir \
--query "$2 and \$CONDITIONS" \
--num-mappers 1 \
--fields-terminated-by '\t' \
--compress \
--compression-codec lzop \
--null-string '\\N' \
--null-non-string '\\N'
hadoop jar /opt/modules/hadoop/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /origin_data/gmall/db/$1/$do_date
}

import_order_info(){
import_data order_info "select
id,
final_total_amount,
order_status,
user_id,
out_trade_no,
create_time,
operate_time,
province_id,
benefit_reduce_amount,
original_total_amount,
feight_fee
from order_info
where (date_format(create_time,'%Y-%m-%d')='$do_date'
or date_format(operate_time,'%Y-%m-%d')='$do_date')"
}

import_coupon_use(){
import_data coupon_use "select
id,
coupon_id,
user_id,
order_id,
coupon_status,
get_time,
using_time,
used_time
from coupon_use
where (date_format(get_time,'%Y-%m-%d')='$do_date'
or date_format(using_time,'%Y-%m-%d')='$do_date'
or date_format(used_time,'%Y-%m-%d')='$do_date')"
}

import_order_status_log(){
import_data order_status_log "select
id,
order_id,
order_status,
operate_time
from order_status_log
where
date_format(operate_time,'%Y-%m-%d')='$do_date'"
}
import_activity_order(){
import_data activity_order "select
id,
activity_id,
order_id,
create_time
from activity_order
where
date_format(create_time,'%Y-%m-%d')='$do_date'"
}

import_user_info(){
import_data "user_info" "select
id,
name,
birthday,
gender,
email,
user_level,
create_time,
operate_time
from user_info
where (DATE_FORMAT(create_time,'%Y-%m-%d')='$do_date'
or DATE_FORMAT(operate_time,'%Y-%m-%d')='$do_date')"
}

import_order_detail(){
import_data order_detail "select
od.id,
order_id,
user_id,
sku_id,
sku_name,
order_price,
sku_num,
od.create_time
from order_detail od
join order_info oi
on od.order_id=oi.id
where
DATE_FORMAT(od.create_time,'%Y-%m-%d')='$do_date'"
}

import_payment_info(){
import_data "payment_info" "select
id,
out_trade_no,
order_id,
user_id,
alipay_trade_no,
total_amount,
subject,
payment_type,
payment_time
from payment_info
where
DATE_FORMAT(payment_time,'%Y-%m-%d')='$do_date'"
}

import_comment_info(){
import_data comment_info "select
id,
user_id,
sku_id,
spu_id,
order_id,
appraise,
comment_txt,
create_time
from comment_info
where date_format(create_time,'%Y-%m-%d')='$do_date'"
}

import_order_refund_info(){
import_data order_refund_info "select
id,
user_id,
order_id,
sku_id,
refund_type,
refund_num,
refund_amount,
refund_reason_type,
create_time
from order_refund_info
where
date_format(create_time,'%Y-%m-%d')='$do_date'"
}

import_sku_info(){
import_data sku_info "select
id,
spu_id,
price,
sku_name,
sku_desc,
weight,
tm_id,
category3_id,
create_time
from sku_info where 1=1"
}

import_base_category1(){
import_data "base_category1" "select
id,
name
from base_category1 where 1=1"
}

import_base_category2(){
import_data "base_category2" "select
id,
name,
category1_id
from base_category2 where 1=1"
}

import_base_category3(){
import_data "base_category3" "select
id,
name,
category2_id
from base_category3 where 1=1"
}

import_base_province(){
import_data base_province "select
id,
name,
region_id,
area_code,
iso_code
from base_province
where 1=1"
}

import_base_region(){
import_data base_region "select
id,
region_name
from base_region
where 1=1"
}

import_base_trademark(){
import_data base_trademark "select
tm_id,
tm_name
from base_trademark
where 1=1"
}

import_spu_info(){
import_data spu_info "select
id,
spu_name,
category3_id,
tm_id
from spu_info
where 1=1"
}

import_favor_info(){
import_data favor_info "select
id,
user_id,
sku_id,
spu_id,
is_cancel,
create_time,
cancel_time
from favor_info
where 1=1"
}

import_cart_info(){
import_data cart_info "select
id,
user_id,
sku_id,
cart_price,
sku_num,
sku_name,
create_time,
operate_time,
is_ordered,
order_time
from cart_info
where 1=1"
}

import_coupon_info(){
import_data coupon_info "select
id,
coupon_name,
coupon_type,
condition_amount,
condition_num,
activity_id,
benefit_amount,
benefit_discount,
create_time,
range_type,
spu_id,
tm_id,
category3_id,
limit_num,
operate_time,
expire_time
from coupon_info
where 1=1"
}

import_activity_info(){
import_data activity_info "select
id,
activity_name,
activity_type,
start_time,
end_time,
create_time
from activity_info
where 1=1"
}

import_activity_rule(){
import_data activity_rule "select
id,
activity_id,
condition_amount,
condition_num,
benefit_amount,
benefit_discount,
benefit_level
from activity_rule
where 1=1"
}

import_base_dic(){
import_data base_dic "select
dic_code,
dic_name,
parent_code,
create_time,
operate_time
from base_dic
where 1=1"
}

case $1 in
"order_info")
import_order_info
;;
"base_category1")
import_base_category1
;;
"base_category2")
import_base_category2
;;
"base_category3")
import_base_category3
;;
"order_detail")
import_order_detail
;;
"sku_info")
import_sku_info
;;
"user_info")
import_user_info
;;
"payment_info")
import_payment_info
;;
"base_province")
import_base_province
;;
"base_region")
import_base_region
;;
"base_trademark")
import_base_trademark
;;
"activity_info")
import_activity_info
;;
"activity_order")
import_activity_order
;;
"cart_info")
import_cart_info
;;
"comment_info")
import_comment_info
;;
"coupon_info")
import_coupon_info
;;
"coupon_use")
import_coupon_use
;;
"favor_info")
import_favor_info
;;
"order_refund_info")
import_order_refund_info
;;
"order_status_log")
import_order_status_log
;;
"spu_info")
import_spu_info
;;
"activity_rule")
import_activity_rule
;;
"base_dic")
import_base_dic
;;
"first")
import_base_category1
import_base_category2
import_base_category3
import_order_info
import_order_detail
import_sku_info
import_user_info
import_payment_info
import_base_province
import_base_region
import_base_trademark
import_activity_info
import_activity_order
import_cart_info
import_comment_info
import_coupon_use
import_coupon_info
import_favor_info
import_order_refund_info
import_order_status_log
import_spu_info
import_activity_rule
import_base_dic
;;
"all")
import_base_category1
import_base_category2
import_base_category3
import_order_info
import_order_detail
import_sku_info
import_user_info
import_payment_info
import_base_trademark
import_activity_info
import_activity_order
import_cart_info
import_comment_info
import_coupon_use
import_coupon_info
import_favor_info
import_order_refund_info
import_order_status_log
import_spu_info
import_activity_rule
import_base_dic
;;
esac

说明 1：

[ -n 变量值 ] 判断变量的值，是否为空
-- 变量的值，非空，返回 true
-- 变量的值，为空，返回 false

查看 date 命令的使用，date --help

2）修改脚本权限

chmod 770 mysql2hdfs.sh

3）初次导入

mysql2hdfs.sh first 2020-03-10

4）每日导入

mysql2hdfs.sh all 2020-03-11

注意：此过程可能会执行10-20分钟左右，请耐心等待！

2.5.3 项目经验

Hive 中的 Null 在底层是以“\N”来存储，而 MySQL 中的 Null 在底层就是 Null，为了
保证数据两端的一致性。在导出数据时采用–input-null-string 和–input-null-non-string 两个参
数。导入数据时采用–null-string 和–null-non-string

三、数据环境准备

3.1 安装 Hive2.3

（PS：博主曾经安装过Hive，不过版本是Hive-1.1.0的，但是这次使用的是Hive2.3，配置稍有不同，所以就重新在这里写一遍配置！）

1）上传 apache-hive-2.3.0-bin.tar.gz 到/opt/software 目录下，并解压到/opt/modules

tar -zxf apache-hive-2.3.6-bin.tar.gz -C /opt/modules 

2）修改 apache-hive-2.3.6-bin 名称为 hive

mv apache-hive-2.3.6-bin hive 

3）将 Mysql 的 mysql-connector-java-5.1.27-bin.jar 拷贝到 /opt/modules/hive/lib

cp /opt/software/mysql-libs/mysql-connector-java-5.1.27/mysql-connector-java-5.1.27-bin.jar /opt/modules/hive/lib/ 

4）在/opt/modules/hive/conf 路径上，修改hive-env.sh，添加如下配置



5）在/opt/modules/hive/conf 路径上，创建 hive-site.xml 文件

vim hive-site.xml

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
        <property>
                <name>javax.jdo.option.ConnectionURL</name>
                <value>jdbc:mysql://node01:3306/hive?createDatabaseIfNotExist=true</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionDriverName</name>
                <value>com.mysql.jdbc.Driver</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionUserName</name>
                <value>root</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionPassword</name>
                <value>hadoop</value>
        </property>
        <property>
                <name>hive.metastore.warehouse.dir</name>
                <value>/user/hive/warehouse</value>
        </property>
        <property>
                <name>hive.cli.print.header</name>
                <value>true</value>
        </property>
        <property>
                <name>hive.cli.print.current.db</name>
                <value>true</value>
        </property>
        <property>
                <name>hive.metastore.schema.verification</name>
                <value>false</value>
        </property>
        <property>
                <name>datanucleus.schema.autoCreateAll</name>
                <value>true</value>
        </property>
        <property>
                <name>hive.server2.thrift.bind.host</name>
                <value>node01</value>
        </property>
        <property>
                <name>hive.metastore.uris</name>
                <value>thrift://node01:9083</value>
        </property>
</configuration>

6）启动！

nohup ./bin/hive --service metastore &
nohup ./bin/hiveserver2 &
./bin/beeline
!connect jdbc:hive2://node01:10000

3.2 Hive 集成引擎 Tez

Tez 是一个 Hive 的运行引擎，性能优于 MR。为什么优于 MR 呢？看下图



用 Hive 直接编写 MR 程序，假设有四个有依赖关系的 MR 作业，上图中，绿色是 ReduceTask，云状表示写屏蔽，需要将中间结果持久化写到 HDFS
Tez 可以将多个有依赖的作业转换为一个作业，这样只需写一次 HDFS，且中间节点较少，从而大大提升作业的计算性能

3.2.1 安装包准备

1）下载 tez 的依赖包：http://tez.apache.org

2）将 apache-tez-0.9.1-bin.tar.gz 上传到 HDFS 的 /tez 目录下

1. hdfs dfs -mkdir /tez
2. hdfs dfs -put /opt/software/apache-tez-0.9.1-bin.tar.gz /tez

3）解压缩 apache-tez-0.9.1-bin.tar.gz,并重命名为 tez

1. tar -zxf apache-tez-0.9.1-bin.tar.gz -C /opt/modules
2. mv /opt/modules/apache-tez-0.9.1-bin tez

3.2.2 集成 Tez
1）进入到 Hive 的配置目录：/opt/modules/hive/conf

1. cd /opt/modules/hive/conf

2）在 Hive 的/opt/modules/hive/conf 下面创建一个 tez-site.xml 文件
vim tez-site.xml添加如下内容

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
        <property>
                <name>tez.lib.uris</name>
                <value>${fs.defaultFS}/tez/apache-tez-0.9.1-bin.tar.gz</value>
        </property>
        <property>
                <name>tez.use.cluster.hadoop-libs</name>
                <value>true</value>
        </property>
        <property>
                <name>tez.history.logging.service.class</name>
                <value>org.apache.tez.dag.history.logging.ats.ATSHistoryLoggin
        gService</value>
        </property>
</configuration>

2）在 hive-env.sh 文件中添加 tez 环境变量配置和依赖包环境变量配置
vim hive-env.sh添加如下配置

export TEZ_HOME=/opt/modules/tez #是你的 tez 的解压目录
export TEZ_JARS=""
for jar in `ls $TEZ_HOME |grep jar`; do
export TEZ_JARS=$TEZ_JARS:$TEZ_HOME/$jar
done
for jar in `ls $TEZ_HOME/lib`; do
export TEZ_JARS=$TEZ_JARS:$TEZ_HOME/lib/$jar
done
# HIVE_AUX_JARS_PATH是引用外部Jar包的路径
export HIVE_AUX_JARS_PATH=/opt/modules/hadoop/share/hadoop/common/hadoop-lzo-0.4.20.jar$TEZ_JARS

3）在 hive-site.xml 文件中添加如下配置，更改 hive 计算引擎

<property>
        <name>hive.execution.engine</name>
        <value>tez</value>
</property>

3.2.3 测试

1）启动 Hive

2）创建表

1. create table student(id int,name string);

3）向表中插入数据

1. insert into student values(1,"zhangsan");

4）如果没有报错就表示成功了

1. select * from student;

3.2.4 注意事项

1）运行 Tez 时检查到用过多内存而被 NodeManager 杀死进程问题：

Caused by: org.apache.tez.dag.api.SessionNotRunning: TezSession
has already shutdown. Application application_1546781144082_0005
failed 2 times due to AM Container for appattempt_1546781144082_0005_000002 exited with exitCode: -103
For more detailed output, check application tracking
page:http://hadoop103:8088/cluster/app/application_15467811440
82_0005Then, click on links to logs of each attempt.
Diagnostics: Container
[pid=11116,containerID=container_1546781144082_0005_02_000001]
is running beyond virtual memory limits. Current usage: 216.3 MB
of 1 GB physical memory used; 2.6 GB of 2.1 GB virtual memory used.
Killing container.

这种问题是从机上运行的 Container 试图使用过多的内存，而被 NodeManager kill 掉了

2）解决方法：

（1）关掉虚拟内存检查，修改 yarn-site.xml,添加如下配置

<property>
        <name>yarn.nodemanager.vmem-check-enabled</name>
        <value>false</value>
</property>

（2）修改后分发配置文件，并重新启动 hadoop 集群

至此，MySQL、Sqoop、Hive环境已经搭建完毕，数据也已生产至HDFS，博主将会在下一章开启数仓建模搭建的过程！