4.4 案例：利用Sqoop进行数据迁移至Mysql数据库
4.4 案例：利用Sqoop进行数据迁移至Mysql数据库
【实验目的】
【实验原理】
【实验环境】
【实验内容】
【实验步骤】
1.Sqoop概述
Sqoop是一款开源的工具，主要用于在Hadoop生态系统（Hadoop、Hive等)与传统的数据库(MySQL、Oracle等)间进行数据的传递，可以将一个关系型数据库中的数据导入到Hadoop的HDFS中，也可以将HDFS的数据导入到关系型数据库中。
Sqoop导入原理：
在导入开始之前，Sqoop使用JDBC来检查将要导入的表。他检索出表中所有的列以及列的SQL数据类型。这些SQL类型（varchar、integer）被映射到Java数据类型（String、Integer等），在MapReduce应用中将使用这些对应的Java类型来保存字段的值。Sqoop的代码生成器使用这些信息来创建对应表的类，用于保存从表中抽取的记录。Sqoop启动的MapReduce作业用到一个InputFormat，他可以通过JDBC从一个数据库表中读取部分内容。
Hadoop提供的DataDriverDB InputFormat能为查询结果进行划分传给指定个数的map任务。为了获取更好的导入性能，查询会根据一个“划分列”来进行划分。Sqoop会选择一个合适的列作为划分列（通常是表的主键）。在生成反序列化代码和配置InputFormat之后，Sqoop将作业发送到MapReduce集群。Map任务将执行查询并将ResultSet中的数据反序列化到生成类的实例，这些数据要么直接保存在SequenceFile文件中，要么在写到HDFS之前被转换成分割的文本。Sqoop不需要每次都导入整张表，用户也可以在查询中加入到where子句，以此来限定需要导入的记录。
Sqoop导出原理：
Sqoop导出功能的架构与其导入功能非常相似，在执行导出操作之前，Sqoop会根据数据库连接字符串来选择一个导出方法。一般为JDBC。然后，Sqoop会根据目标表的定义生成一个Java类。这个生成的类能够从文本文件中解析记录，并能够向表中插入合适类型的值。接着会启动一个MapReduce作业，从HDFS中读取源数据文件，使用生成的类解析记录，并且执行选定的导出方法。
2.准备环境
切换到vmuser用户(vm123456)
1首先创建名为edu4的目录
mkdir -p /data/edu4
再切换目录到/data/edu4目录下，下载实验所需的文件。
cd /data/edu4
wget http://pinglun
wget http://creationtime_out
wget http://userlevelname_out
ls -lh
sudo chown -R vmuser:vmuser /data/edu4
执行启动Hadoop命令，并使用JPS查看Hadoop是否启动。
cd /apps/hadoop/sbin
./start-all.sh
jps
启动MySQL服务
查看MySQL服务是否已经启动，按下q键退出。
systemctl status mysqld
mysql -h localhost -P 3306 -u root -p
3.启动Hive
hive
在Hive中创建数据库名为edu4，并切换到edu4下
CREATE DATABASE edu4;
USE edu4;
在Hive中创建pinglun表，
|
CREATE TABLE pinglun ( productid STRING, commentcount INT, goodcount INT, generalcount INT, poorcount INT, goodrateshow FLOAT, generalrateshow FLOAT, poorrateshow FLOAT, guid STRING, content STRING, creationtime STRING, score INT, nickname STRING, userlevelname STRING, userclientshow STRING, ismobile STRING, days INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; |
在ROW FORMAT DELIMITED子句中，指定了行格式为分隔符格式，并通过FIELDS TERMINATED BY '\t'指定了字段之间的分隔符为制表符（\t）。
使用LOAD DATA命令将位于本地文件系统路径/data/edu4/pinglun下的数据加载到名为pinglun的表中
LOAD DATA LOCAL INPATH '/data/edu4/pinglun' INTO TABLE pinglun;
3.需求一，将Hive表中数据，导入到MySQL
统计pinglun中，用户使用各设备类型数量，并将结果导到mysql中。
1首先在Hive中，创建一张表，命名为ismobilehive，用于存储统计结果。使用STORED AS TEXTFILE将表存储为文本文件格式。
CREATE TABLE ismobilehive (
ismobile STRING,
num INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
ismobilehive表，有两个字段：设备类型及设备数量
2.在Hive中，统计设备类型数量，并将结果临时存储在hive中的ismobilehive表中。
使用INSERT INTO语句将计算得到的结果插入到名为ismobilehive的表中。通过SELECT语句从名为pinglun的表中选择需要的数据，并进行处理。
INSERT INTO TABLE ismobilehive
SELECT
CASE
WHEN ismobile = 'true' THEN 1
WHEN ismobile = 'false' THEN 0
END AS ismobile,
COUNT(1) AS num
FROM pinglun
GROUP BY ismobile;
在SELECT子句中，使用CASE表达式将字符串类型的ismobile列转换为对应的数值类型（1或0）。然后，使用COUNT(1)计算每个ismobile值的数量。
查看ismobile表。
SELECT * FROM ismobilehive;
3.新打开一个命令行终端，连接Mysql（密码：strongs）
mysql -h localhost -P 3306 -u root -p
在mysql中，创建名为edu4out的数据库，用于存储导过来的数据
使用CREATE DATABASE语句创建名为edu4out的数据库。IF NOT EXISTS选项用于在数据库已经存在时避免创建重复的数据库。DEFAULT CHARSET utf8 COLLATE utf8_general_ci用于设置数据库的默认字符集和排序规则。
CREATE DATABASE IF NOT EXISTS edu4out DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
USE edu4out;
在Mysql中创建名为ismobilesql的表，用于储蓄Hive中ismobilehive表的数据。此表也包含两个字段设备类型及设备数量
CREATE TABLE ismobilesql (
ismobile VARCHAR(10),
num INT
);
4.再新开启一个终端模拟器，切换至vmuser用户（密码：vm123456）
进行一个Sqoop的测试，通过Sqoop查看Mysql中的数据库来验证Sqoop是否可用。通过此步验证，可以测试出Sqoop以及Mysql是否可以正常连接
cd /apps/sqoop/bin/
./sqoop list-databases \
--connect jdbc:mysql://localhost:3306/ \
--username root \
--password strongs
5.，使用Sqoop命令将Hive中的ismobilehive表导入到Mysql的ismobilesql中。
./sqoop export \
--connect jdbc:mysql://localhost:3306/edu4out?characterEncoding=UTF-8 \
--username root \
--password strongs \
--table ismobilesql \
--export-dir /user/hive/warehouse/edu4.db/ismobilehive/000000_0 \
--input-fields-terminated-by '\t' \
--m 2
--connect参数指定MySQL数据库的连接URL，并设置字符编码为UTF-8。
--username和--password参数提供数据库的用户名和密码。
--table参数指定目标表为ismobilesql，即要将数据导入到MySQL中的表。
--export-dir指定导出数据的目录，/user/hive/warehouse/edu4.db/ismobilehive/000000_0是Hive表数据所在的目录。
--input-fields-terminated-by参数指定输入字段的分隔符为制表符（\t）。
--m参数指定Sqoop并行任务的数量。
回到mysql数据库终端，查看mysql中ismobilesql表，数据内容
SELECT * FROM ismobilesql;
这样，我们就将Hive中的ismobilehive表数据成功导入到Mysql中了。
结果列出了Mysql中的所有数据库，证明Sqoop可以与Mysql正常连接。
4.需求二，将Hive表中数据，导入到MySQL
将hive中多久进行评论数据统计结果导入到Mysql中的dayshive表中
1.在Hive中，创建用户评论周期分析结果表dayshive，包含两个字段多久评论及数量
DROP TABLE dayshive ;
CREATE TABLE dayshive (
day INT,
num INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
2.对用户评论间隔时间进行统计，并将统计结果导入到Hive中的dayshive表中。
INSERT INTO TABLE dayshive
SELECT days, COUNT(1) AS num
FROM pinglun
GROUP BY days
ORDER BY num DESC;
查看Hive表dayshive中的数据
3.在Mysql端口创建dayssql表，用于存储dayshive中的数据。dayssql中同样包含两个字段间隔时间及数量
CREATE TABLE dayssql (
day INT,
num INT
);
4.使用Sqoop命令将Hive的dayshive表导入到Mysql的dayssql里。
./sqoop export \
--connect jdbc:mysql://localhost:3306/edu4out?characterEncoding=UTF-8 \
--username root \
--password strongs \
--table dayssql \
--export-dir /user/hive/warehouse/edu4.db/dayshive/000000_0 \
--input-fields-terminated-by '\t' \
--m 2
查看mysql中dayssql表。
SELECT * FROM dayssql;
5.需求三，将HDFS中数据导出到MySQL中
将HDFS中的存储有评论日期数据统计结果文件creationtime_out导入到MySQL中creationtimesql表中
在HDFS上创建目录/myedu4/in，并将/data/edu4下的creationtime_out和userlevelname_out文件上传到HDFS中的in目录下。
hadoop fs -mkdir -p /myedu4/in
hadoop fs -put /data/edu4/creationtime_out /myedu4/in
hadoop fs -put /data/edu4/userlevelname_out /myedu4/in
hadoop fs -ls /myedu4/in
27.在MySQL端创建creationtimesql表，包含两个字段（creationtime date，num int）
CREATE TABLE creationtimesql (
creationtime DATE,
num INT
);
28.使用Sqoop命令将HDFS中的creationtime_out表导入到Mysql中。
./sqoop export \
--connect jdbc:mysql://localhost:3306/edu4out?characterEncoding=UTF-8 \
--username root \
--password strongs \
--table creationtimesql \
--export-dir /myedu4/in/creationtime_out \
--input-fields-terminated-by '\t' \
--m 2
在Mysql中，查看creationtimesql表。
SELECT * FROM creationtimesql;
这样，我们就将四项需求的分析结果导入到MySQL中了！
