02.02第二篇之业务数据采集模块

第2 章业务数据采集模块

2.1 MySQL 安装

2.1.1 安装包准备

1）查看MySQL 是否安装，如果安装了，卸载MySQL

（1）查看

[root@hadoop102 桌面]# rpm -qa|grep mysql 

mysql-libs-5.1.73-7.el6.x86_64

（2）卸载

[root@hadoop102 桌面]# rpm -e --nodeps mysql-libs-5.1.73-7.el6.x86_64

2）解压mysql-libs.zip 文件到当前目录

[root@hadoop102 software]# unzip mysql-libs.zip

[root@hadoop102 software]# ls

mysql-libs.zip

mysql-libs

3）进入到mysql-libs 文件夹下

[root@hadoop102 mysql-libs]# ll

总用量76048

-rw-r--r--. 1 root root 18509960 3 月26 2015

MySQL-client-5.6.24-1.el6.x86_64.rpm

-rw-r--r--. 1 root root 3575135 12 月1 2013

mysql-connector-java-5.1.27.tar.gz

-rw-r--r--. 1 root root 55782196 3 月26 2015

MySQL-server-5.6.24-1.el6.x86_64.rpm

2.1.2 安装MySql 服务器

1）安装mysql 服务端

[root@hadoop102 mysql-libs]# rpm -ivh MySQL-server-5.6.24-1.el6.x86_64.rpm

2）查看产生的随机密码

[root@hadoop102 mysql-libs]# cat /root/.mysql_secret 

wm8QPbW9yW5343M1 

3）查看mysql 状态

[root@hadoop102 mysql-libs]# service mysql status

4）启动mysql

[root@hadoop102 mysql-libs]# service mysql start

2.1.3 安装MySql 客户端

1）安装mysql 客户端

[root@hadoop102 mysql-libs]# rpm -ivh MySQL-client-5.6.24-1.el6.x86_64.rpm

2）链接mysql

[root@hadoop102 mysql-libs]# mysql -uroot -pwm8QPbW9yW5343M1

3）修改密码

mysql>my;

4）退出mysql

mysql>exit

2.1.4 MySql 中user 表中主机配置

配置只要是root 用户+密码，在任何内部主机上都能登录MySQL 数据库。

1）进入mysql

[root@hadoop102 mysql-libs]# mysql -uroot -p000000

2）显示数据库

mysql>show databases;

3）使用mysql 数据库

mysql>use mysql;

4）展示mysql 数据库中的所有表

mysql>show tables;

5）展示user 表的结构

mysql>desc user;

6）查询user 表

mysql>select User, Host, Password from user;

7）修改user 表，把Host 表内容修改为%

mysql>update user set host='%' where host='localhost';

8）删除root 用户的其他host

mysql>

delete from user where Host='hadoop102';

delete from user where Host='127.0.0.1';

delete from user where Host='::1';

9）刷新

mysql>flush privileges;

10）退出

mysql>quit;

2.2 Sqoop 安装

2.2.1 下载并解压

1）下载地址：http://mirrors.hust.edu.cn/apache/sqoop/1.4.6/

2）上传安装包sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz 到hadoop102 的/opt/software 路径中

3）解压sqoop 安装包到指定目录，如：

[atguigu@hadoop102 software]$ tar -zxf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/module/

4）重命名

[atguigu@hadoop102 module]$ mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha sqoop

2.2.2 修改配置文件

1. 进入到/opt/module/sqoop/conf 目录，重命名配置文件

[atguigu@hadoop102 conf]$ mv sqoop-env-template.sh sqoop-env.sh

2. 修改配置文件

[atguigu@hadoop102 conf]$ vim sqoop-env.sh

增加如下内容

export HADOOP_COMMON_HOME=/opt/module/hadoop-2.7.2

export HADOOP_MAPRED_HOME=/opt/module/hadoop-2.7.2

export HIVE_HOME=/opt/module/hive

export ZOOKEEPER_HOME=/opt/module/zookeeper-3.4.10

export ZOOCFGDIR=/opt/module/zookeeper-3.4.10/conf

export HBASE_HOME=/opt/module/hbase

之前忘了切换回atguigu账户了，还是把sqoop的权限改回来

[root@hadoop102 module]# chown atguigu:atguigu sqoop/ -R

2.2.3 拷贝JDBC 驱动

1）进入到/opt/software/mysql-libs 路径，解压mysql-connector-java-5.1.27.tar.gz 到当前路径

[atguigu@hadoop102 mysql-libs]$ tar -zxvf mysql-connector-java-5.1.27.tar.gz

2）进入到/opt/software/mysql-libs/mysql-connector-java-5.1.27 路径，拷贝jdbc 驱动到sqoop的lib 目录下。

[atguigu@hadoop102 mysql-connector-java-5.1.27]$ cp mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop/lib/

2.2.4 验证Sqoop

我们可以通过某一个command 来验证sqoop 配置是否正确：

[atguigu@hadoop102 sqoop]$ bin/sqoop help

出现一些Warning 警告（警告信息已省略），并伴随着帮助命令的输出：

[atguigu@hadoop102 sqoop]$ bin/sqoop list-databases --connect jdbc:mysql://hadoop102:3306/ --username root --password 000000

出现如下输出：

information_schema

metastore

mysql

oozie

performance_schema

2.3 业务数据生成

2.3.1 连接MySQL

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

2.3.2 建表语句

1）通过SQLyog 创建数据库gmall

2）设置数据库编码

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

2.3.2 生成业务数据

1）在hadoop102 的/opt/module/目录下创建db_log 文件夹

[atguigu@hadoop102 module]$ mkdir db_log/

1） 把gmall-mock-db-2020-03-16-SNAPSHOT.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?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-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 日期数据：

[atguigu@hadoop102 db_log]$ 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 日期数据：

[atguigu@hadoop102 db_log]$ java -jar gmall-mock-db-2020-03-16-SNAPSHOT.jar

同时可以登录sql数据，查看数据是否已经入库。

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）在/home/atguigu/bin 目录下创建

[atguigu@hadoop102 bin]$ vim mysql_to_hdfs.sh

添加如下内容：（把注释删除再导入。。）

#! /bin/bash
sqoop=/opt/module/sqoop/bin/sqoop
##T+1的日子操作T的数据
do_date=`date -d '-1 day' +%F`
##-n 判断是否为空
if [[ -n "$2" ]]; then
do_date=$2
fi
##定义了import_data的公共函数，里面两个参数$1是表名  $2 查询规则
import_data(){
## $sqoop代表sqoop=/opt/module/sqoop/bin/sqoop
$sqoop import \
--connect jdbc:mysql://hadoop102:3306/gmall \
--username root \
--password 000000 \
##按照日期每天一分区，每天一个文件夹，按照表+日期的格式存储落盘
--target-dir /origin_data/gmall/db/$1/$do_date \
##判断输出路径有没有，有的话，先删掉，要不然会报存在错
--delete-target-dir \
## $2是import_data（）方法的第二个参数，查询出表中对应对的片段
## 加\$CONDITIONS字段属于固定语法
--query "$2 and \$CONDITIONS" \
##指定mapper的数量，默认底层是四个mapper，没有reducer，因为不需要聚合
--num-mappers 1 \
##字段分隔符
--fields-terminated-by '\t' \
##设置压缩，压缩格式
--compress \
--compression-codec lzop \
##Hive底层用\N 表示null ，而mysql底层用null表示null，如果不做转化的话，null=》“null”变成字符了
--null-string '\\N' \
--null-non-string '\\N'
##压缩格式
hadoop jar /opt/module/hadoop-2.7.2/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

关于脚本的结构

#!/bin/bash

#定义变量 sqoop

#获取时间 不输入：T+1 前一天数据 date -s '-1 day' +%F  输入 $2：输入哪天就是哪天

#公共函数（封装了sqoop导入数据参数： 连接mysql地址、用户名、密码、hdfs目标地址路径（$1)
如果目标地址存在删除、quary（$2 过滤条件）、分隔符\t、开启压缩、压缩方式、hive\N mysql null
对LZO文件创建索引： hadoop jar jar包路径 lzo索引文件的全类名 LZO文件所在位置
)

#具体表函数（全量 1=1 ，增量 createtime = 今天  新增和变化 createtime operatetime)
	公共函数 表名 过滤条件

case $1 in
"表名"） {
	具体导表1
};;

"first"） {
	导表 24 （包含省份，地区）
};;

"all"） {
	
	导表 22 
};;
esac

关于where 1 =1 ，与上面的命令拼接才不会报错。。另也有可能空值sql注入问题

说明1：

[ -n 变量值] 判断变量的值，是否为空

– 变量的值，非空，返回true

– 变量的值，为空，返回false

说明2：

查看date 命令的使用，[atguigu@hadoop102 ~]$ date --help

2）修改脚本权限

[atguigu@hadoop102 bin]$ chmod 777 mysql_to_hdfs.sh

3）初次导入

[atguigu@hadoop102 bin]$ mysql_to_hdfs.sh first 2020-03-10

4）每日导入

[atguigu@hadoop102 bin]$ mysql_to_hdfs.sh all 2020-03-11

2.6 关于同步策略的补充（旧版内容）

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

Ø 全量表：存储完整的数据。

Ø 增量表：存储新增加的数据。

Ø 新增及变化表：存储新增加的数据和变化的数据。

Ø 拉链表：对新增及变化表做定期合并。

2.6.1 实体表同步策略

实体表：比如用户，商品，商家，销售员等

实体表数据量比较小：通常可以做每日全量，就是每天存一份完整数据。即每日全量。

2.6.2 维度表同步策略

维度表：比如订单状态，审批状态，商品分类

维度表数据量比较小：通常可以做每日全量，就是每天存一份完整数据。即每日全量。

说明：

1）针对可能会有变化的状态数据可以存储每日全量。

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

2.6.3 事务型事实表同步策略

事务型事实表：比如，交易流水，操作日志，出库入库记录等。

因为数据不会变化，而且数据量巨大，所以每天只同步新增数据即可，所以可以做成每日增量表，即每日创建一个分区存储。

2.6.4 周期型事实表同步策略

周期型事实表：比如，订单、请假、贷款申请等

这类表从数据量的角度，存每日全量的话，数据量太大，冗余也太大。如果用每日增量的话无法反应数据变化。

每日新增及变化量，包括了当日的新增和修改。一般来说这个表，足够计算大部分当日数据的。但是这种依然无法解决能够得到某一个历史时间点（时间切片）的切片数据。

所以要用利用每日新增和变化表，制作一张拉链表，以方便的取到某个时间切片的快照数据。所以我们需要得到每日新增及变化量。

拉链表：

name姓名	start新名字创建时间	end名字更改时间
张三	1990/1/1	2018/12/31
张小三	2019/1/1	2019/4/30
张大三	2019/5/1	9999-99-99
。。。	。。。	。。。

库入库记录等。

因为数据不会变化，而且数据量巨大，所以每天只同步新增数据即可，所以可以做成每日增量表，即每日创建一个分区存储。

2.6.4 周期型事实表同步策略

周期型事实表：比如，订单、请假、贷款申请等

这类表从数据量的角度，存每日全量的话，数据量太大，冗余也太大。如果用每日增量的话无法反应数据变化。

每日新增及变化量，包括了当日的新增和修改。一般来说这个表，足够计算大部分当日数据的。但是这种依然无法解决能够得到某一个历史时间点（时间切片）的切片数据。

所以要用利用每日新增和变化表，制作一张拉链表，以方便的取到某个时间切片的快照数据。所以我们需要得到每日新增及变化量。

拉链表：

name姓名	start新名字创建时间	end名字更改时间
张三	1990/1/1	2018/12/31
张小三	2019/1/1	2019/4/30
张大三	2019/5/1	9999-99-99
。。。	。。。	。。。

select * from user where start =<’2019-1-2’ and end>=’2019-1-2’