数仓业务数据采集模块
文章目录
-
MySQL安装
-
- 安装包准备
- 安装MySQL
- 配置MySQL
-
业务数据生成
-
- 连接MySQL
- 建表语句
- 生成业务数据
-
Sqoop安装
-
- 下载并解压
- 修改配置文件
- 拷贝JDBC驱动
- Sqoop基本使用
-
同步策略
-
- 全量同步策略
- 增量同步策略
- 新增及变化策略
- 特殊策略
-
通过HDFS平台完成业务数据的传输操作
-
设计并实施分析表同步的优化方案
-
详细说明该脚本的功能和作用
-
确保每天都能自动执行的数据同步流程
-
项目经验
-
MySQL安装
安装包准备
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在虚拟机环境中按照以下步骤操作：第一步，在已经安装MySQL的情况下删除自带的Mysql-libs包。
[root@hadoop102 software]$ rpm -qa | grep -i -E mysql\|mariadb | xargs -n1 sudo rpm -e --nodeps
[root@hadoop102 software]$ rpm -qa | grep -i -E mysql\|mariadb 在使用阿里云服务器时，请注意：由于其预装的是Linux基础版本，默认不包含某些工具包，默认情况下不包含MySQL数据库服务，默认情况下不包含某些工具包，默认情况下不包含某些工具包，默认情况下不包含某些工具包），因此需要进行相应的安装配置。
[root@hadoop102 software]# sudo yum remove mysql-libs（2）下载依赖并安装
[root@hadoop102 software]# sudo yum install libaio
[root@hadoop102 software]# sudo yum -y install autoconf安装MySQL
1）安装MySQL依赖
[root@hadoop102 software]$ sudo rpm -ivh 01_mysql-community-common-5.7.16-1.el7.x86_64.rpm
[root@hadoop102 software]$ sudo rpm -ivh 02_mysql-community-libs-5.7.16-1.el7.x86_64.rpm
[root@hadoop102 software]$ sudo rpm -ivh 03_mysql-community-libs-compat-5.7.16-1.el7.x86_64.rpm2）安装mysql-client
[root@hadoop102 software]$ sudo rpm -ivh 04_mysql-community-client-5.7.16-1.el7.x86_64.rpm3）安装mysql-server
[root@hadoop102 software]$ sudo rpm -ivh 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm请特别注意，在遇到以下错误提示时，请确保使用rpm版本4.1及以上更新时，默认会进行软件包签名验证。
warning: 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
libaio.so.1()(64bit) is needed by mysql-community-server-5.7.16-1.el7.x86_64解决办法
[root@hadoop102 software]$ sudo rpm -ivh 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm --force --nodeps4）启动MySQL
[root@hadoop102 software]$ sudo systemctl start mysqld5）查看MySQL密码
[root@hadoop102 software]$ sudo cat /var/log/mysqld.log | grep password配置MySQL
只要配置为root用户身份并带有密码，在任何主机上都可以成功登录MySQL数据库。
第一步是输入最近获取的密码访问MySQL（如果出现错误提示，则需将该密码包裹在单引号内）。
[root@hadoop102 software]$ mysql -uroot -p'password'2）设置复杂密码（由于MySQL密码策略，此密码必须足够复杂）
mysql> set password=password("Qs23=zs32");3）更改MySQL密码策略
mysql> set global validate_password_length=4;
mysql> set global validate_password_policy=0;4）设置简单好记的密码
mysql> set password=password("000000");5）进入MySQL库
mysql> use mysql6）查询user表
mysql> select user, host from user;7）修改user表，把Host表内容修改为%
mysql> update user set host="%" where user="root";8）刷新
mysql> flush privileges;9）退出
mysql> quit;业务数据生成
连接MySQL
通过MySQL可视化客户端连接数据库。
建表语句
- 利用SQLyog工具生成数据库
- 设定数据库名为gmall，并将其编码设置为utf-8以及排序规则设为utf8_general_ci
- 导入包含 databases structure 的脚本文件(gmall.sql)
注意：完成后，请右键单击刷新一下对象浏览器窗口, 以便查看 database 中的 tables。
生成业务数据
1）在hadoop102的/opt/module/目录下创建db_log文件夹
[root@hadoop102 module]$ mkdir db_log/2）将jar文件gmall2020-mock-db-2021-01-22.jar以及application.properties配置文件配置到Hadoop集群指定目录hadoop102/opt/module/db_log中。
3）根据具体需求设置application.properties相关参数的值。
ogging.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-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日期数据：
[root@hadoop102 db_log]$ java -jar gmall2020-mock-db-2021-01-22.jar5）查看gmall数据库，观察是否有2020-06-14的数据出现
Sqoop安装
下载并解压
1）sqoop 官方网站：http://sqoop.apache.org
2）获取镜像的链接：http://mirrors.hust.edu.cn/apache/sqoop/1.4.6/
3）将sqoop版本号复制至Hadoop版本号，并将其复制到hadoop102服务器的/opt/software文件夹内
4）将sqoop安装包解压至特定目录
[root@hadoop102 software]$ tar -zxf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/module/5）解压sqoop安装包到指定目录，如：
[root@hadoop102 module]$ mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha/ sqoop修改配置文件
1）进入到/opt/module/sqoop/conf目录，重命名配置文件
[root@hadoop102 conf]$ mv sqoop-env-template.sh sqoop-env.sh2）修改配置文件
[root@hadoop102 conf]$ 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拷贝JDBC驱动
1）复制MySQL Connector Java版本5.1.27 jar文件至/opt/software目录位置
2）导航至/opt/software路径后，在sqoop的应用程序库目录中复制所需的JDBC驱动文件。
[root@hadoop102 software]$ cp mysql-connector-java-5.1.27.jar /opt/module/sqoop/lib/2.3.4 验证Sqoop
（1）我们可以通过某一个command来验证sqoop配置是否正确：
[root@hadoop102 sqoop]$ bin/sqoop help（2）出现一些Warning警告（警告信息已省略），并伴随着帮助命令的输出：
Available commands:
codegen Generate code to interact with database records
create-hive-table Import a table definition into Hive
eval Evaluate a SQL statement and display the results
export Export an HDFS directory to a database table
help List available commands
import Import a table from a database to HDFS
import-all-tables Import tables from a database to HDFS
import-mainframe Import datasets from a mainframe server to HDFS
job Work with saved jobs
list-databases List available databases on a server
list-tables List available tables in a database
merge Merge results of incremental imports
metastore Run a standalone Sqoop metastore
version Display version information2.3.5 测试Sqoop是否能够成功连接数据库
[root@hadoop102 sqoop]$ bin/sqoop list-databases --connect jdbc:mysql://hadoop102:3306/ --username root --password 000000出现如下输出：
information_schema
metastore
mysql
performance_schemaSqoop基本使用
将mysql中user_info表数据导入到HDFS的/test路径
bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/gmall \
--username root \
--password 000000 \
--table user_info \
--columns id,login_name \
--where "id>=10 and id<=30" \
--target-dir /test \
--delete-target-dir \
--fields-terminated-by '\t' \
--num-mappers 2 \
--split-by id同步策略
数据同步策略的类型包括：全量同步、增量同步、新增及变化同步、特殊情况
1，全量表：存储完整的数据。
2，增量表：存储新增加的数据。
3，新增及变化表：存储新增加的数据和变化的数据。
4，特殊表：只需要存储一次。
全量同步策略
增量同步策略
新增及变化策略
特殊策略
某些特定的表可无需遵循上述统一协调策略。例如那些保持不变的表（如地区表、省份表、民族表）只需存档一份固定值。
业务数据导入HDFS
分析表同步策略
在生产环境中的一些小型企业出于简便考虑也会进行全量导入。对于规模较大的中型及以上公司，则会严格遵循同步策略进行数据导入。
业务数据首日同步脚本
1）脚本编写
（1）在/root/bin目录下创建mysql_to_hdfs_init.sh
[root@hadoop102 bin]$ vim mysql_to_hdfs_init.sh添加如下内容：
[ -n 变量名 ] 用于检查指定变量是否为非空字符串
- 如果变量赋有非空值，则返回true；
- 否则返回false。
获取日期信息的操作可以通过以下命令执行：
[root@hadoop102 ~]$ date --help
（2）增加脚本执行权限
[root@hadoop102 bin]$ chmod +x mysql_to_hdfs_init.sh2）脚本使用
[root@hadoop102 bin]$ mysql_to_hdfs_init.sh all 2020-06-14业务数据每日同步脚本
1）脚本编写
（1）在/root/bin目录下创建mysql_to_hdfs.sh
[root@hadoop102 bin]$ vim mysql_to_hdfs.sh添加如下内容：
（2）增加脚本执行权限
[root@hadoop102 bin]$ chmod +x mysql_to_hdfs.sh2）脚本使用
[root@hadoop102 bin]$ mysql_to_hdfs.sh all 2020-06-15项目经验
Hive中的Null值以\N的形式存储于底层结构中，在MySQL中则直接表示为Null类型。为了避免两端数据格式的不一致，在导出操作时应分别使用--input-null-string和--input-null-non-string选项来处理不同类型的Null值；而在数据导入操作时，则应通过--null-string和--null-non-string选项来完成对应的数据读取设置。