任务16：使用Sqoop将Hive结果数据导入到MySQL

原创于 2025-05-29 08:42:27 发布 · 1.4k 阅读

21 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

任务描述

知识点：

使用Sqoop将Hive导入到MySQL

重点：

基于CentOS系统，安装Sqoop
使用Sqoop命令的方式实现数据的导入和导出

内容：

安装Sqoop
MySQL数据导入到HDFS
HDFS数据导出到MySQL

任务指导

1、Sqoop

Sqoop是一个用来将Hadoop和关系型数据库中的数据相互转移的工具，可以将一个关系型数据库（例如：MySQL ,Oracle ,Postgres等）中的数据导入到Hadoop的HDFS中，也可以将HDFS的数据导入到关系型数据库中。本次任务的内容为使用Sqoop实现数据从MySQL 到HDFS的导入、导出。

2. 安装Sqoop（master机器）

启动Hadoop级群
启动MySQL数据库
安装Sqoop
- 解压Sqoop安装包
- 将MySQL的jdbc驱动包mysql-connector-java-5.1.41-bin.jar复制到Sqoop的lib目录下
- 配置环境变量
- 根据sqoop-env-template.sh复制出sqoop-env.sh
- 修改sqoop-env.sh文件
- 将java-json.jar负责到Sqoop的lib目录下

3. 使用Sqoop命令的方式实现数据的导入（MySQL -> HDFS）

进入MySQL 客户端
在MySQL 中创建一个数据库books，和一个表test_table
使用Sqoop将MySQL 数据导入到HDFS

4. 使用Sqoop命令的方式实现数据的导出（HDFS -> MySQL）

进入MySQL
创建china_all数据库并设置访问权限
创建china_map表，表字段包含：月份，省份，平均气温，平均风速，存放2022年每个月各省份的平均气温及平均风速，为可视化展示部分的地图数据
创建city_precipitation_top10表，表字段包含：月份，城市，平均降水量（6小时），存放2022年每个月平均降水量TOP10的城市，为可视化展示部分的条形图数据
创建city_temp表，表字段包含：月份，城市，平均气温，存放2022年每个月各个城市的平均气温，为可视化展示部分的词云数据
创建province_temp表，表字段包含：省份，月份，平均气温，(预留) 预测气温，存放2022年每个月各个省份的平均气温，为可视化展示部分的折线图数据
创建province_pressure表，表字段包含：月份，省份，平均气压，存放2022年每个月各省份的平均气压，为可视化展示部分的矩形树图数据
创建province_temp_all表，表字段包含：年份，省份，月份，平均气温，存放2000-2022年各省份每月的平均气温，为气温预测部分的训练数据
导出数据到Mysql数据库
Hive所创建的数据库及其表都是默认存放在HDFS的/user/hive/warehouse目录，在/user/hive/warehouse目录下存放着以数据库命名的目录，在/user/hive/warehouse/china_all.db目录下存放在以表命名的目录，在表目录下存放着实际的数据文件

使用Sqoop导出china_map数据到MySQL 中（导出时需要在数据库中创建相应的表，导入的数据要与表中的列相匹配）

使用Sqoop导出city_precipitation_top10数据到MySQL 中

使用Sqoop导出city_temp数据到MySQL 中

使用Sqoop导出province_temp数据到MySQL 中

使用Sqoop导出province_pressure数据到MySQL 中

使用Sqoop导出province_temp_all数据到MySQL 中

任务实现

1. 安装Sqoop（master机器）

启动Hadoop集群，如果集群已经启动，则跳过此步骤：

# start-all.sh

查看MySQL 数据库当前运行状态：

# systemctl status mysqld

如果已经启动，则跳过此步骤；如果没有启动，请使用如下命令启动MySQL ：

# systemctl start mysqld

安装Sqoop
安装比较简单，直接解压即可（本次实验使用的是Sqoop1的版本）

# tar -zxvf /home/software/sqoop-1.4.6-cdh5.9.3.tar.gz -C /home/hadoop/

唯一需要做的就是将MySQL 的jdbc驱动包mysql-connector-java-5.1.41-bin.jar copy到$SQOOP_HOME/lib下。

# cp /home/software/mysql-connector-java-5.1.41-bin.jar /home/hadoop/sqoop-1.4.6-cdh5.9.3/lib/

配置环境变量：/etc/profile

export SQOOP_HOME=/home/hadoop/sqoop-1.4.6-cdh5.9.3
export PATH=:$PATH:$SQOOP_HOME/bin

使环境变量生效

# source /etc/profile

# cd /home/hadoop/sqoop-1.4.6-cdh5.9.3/conf/

根据sqoop-env-template.sh复制出sqoop-env.sh

# cp sqoop-env-template.sh sqoop-env.sh

使用【vim $SQOOP_HOME/conf/sqoop-env.sh】命令修改文件内容如下：

export HADOOP_COMMON_HOME=/home/hadoop/hadoop-2.9.2
export HADOOP_MAPRED_HOME=/home/hadoop/hadoop-2.9.2
export HIVE_HOME=/home/hive/apache-hive-2.1.0-bin

sqoop1需要使用java-json.jar包，该jar已存放在环境的/home/software目录中，直接复制即可：

# cp /home/software/java-json.jar /home/hadoop/sqoop-1.4.6-cdh5.9.3/lib/

2. 使用Sqoop命令的方式实现数据的导入（MySQL -> HDFS）

进入MySQL

# mysql -uroot -p

在MySQL 中创建一个数据库books并设置权限，和一个表test_table

grant语句解析：

grant 权限 on 数据库 to '用户名'@'主机（%表示远程任意主机）' IDENTIFIED BY '密码';

mysql> create database books DEFAULT CHARSET utf8 COLLATE   utf8_general_ci;
mysql> set global validate_password_length=4;
mysql> set global validate_password_policy=0;
mysql> grant all on books.* to 'root'@'%' IDENTIFIED BY 'root';
mysql> grant all on books.* to 'root'@'localhost' IDENTIFIED BY 'root';
mysql> FLUSH PRIVILEGES;
mysql> use books;
mysql> create table test_table(id int,name varchar(20),pass varchar(20));
mysql> insert into test_table values(1,'admin','123456');
mysql> quit;

MySQL导入HDFS

执行命令

# sqoop import --connect   jdbc:mysql://master:3306/books --username root --password root --table   test_table --target-dir /test/books/  -m 1

验证输出

# hadoop fs -cat /test/books/*

3. 使用Sqoop命令的方式实现数据的导出（HDFS -> MySQL）

1）创建数据表

进入MySQL

# mysql -uroot -p

创建china_all数据库并设置访问权限

mysql> create database china_all;
mysql> set global validate_password_length=4;
mysql> set global validate_password_policy=0;
mysql> grant all on china_all.* to 'root'@'%' IDENTIFIED BY 'root';
mysql> grant all on china_all.* to 'root'@'localhost' IDENTIFIED BY 'root';
mysql> FLUSH PRIVILEGES;

创建china_map表，表字段包含：月份，省份，平均气温，平均风速，存放2022年每个月各省份的平均气温及平均风速，为可视化展示部分的地图数据

mysql> use china_all;
mysql> create table china_map
(
month int(4),
province varchar(20),
temp float,
wind_speed float
);

创建city_precipitation_top10表，表字段包含：月份，城市，平均降水量（6小时），存放2022年每个月平均降水量TOP10的城市，为可视化展示部分的条形图数据

mysql> create table city_precipitation_top10
(
month int(4),
city varchar(20),
precipitation_6 float
);

创建city_temp表，表字段包含：月份，城市，平均气温，存放2022年每个月各个城市的平均气温，为可视化展示部分的词云数据

mysql> create table city_temp
(
month int(4),
city varchar(20),
temp float
);

创建province_temp表，表字段包含：省份，月份，平均气温，(预留) 预测气温，存放2022年每个月各个省份的平均气温，为可视化展示部分的折线图数据

mysql> create table province_temp
(
province varchar(20),
month int(4),
temp float,
temp_forecast float
);

创建province_pressure表，表字段包含：月份，省份，平均气压，存放2022年每个月各省份的平均气压，为可视化展示部分的矩形树图数据

mysql> create table province_pressure
(
month int(4),
province varchar(20),
pressure float
);

创建province_temp_all表，表字段包含：年份，省份，月份，平均气温，存放2000-2022年各省份每月的平均气温，为气温预测部分的训练数据

mysql> create table province_temp_all
(
year int(4),
province varchar(20),
month int(4),
temp float
);

2）导出数据到MySQL 数据库

退出MySQL

mysql> quit;

Hive所创建的数据库及其表都是默认存放在HDFS的/user/hive/warehouse目录，在/user/hive/warehouse目录下存放着以数据库命名的目录，在/user/hive/warehouse/china_all.db目录下存放在以表命名的目录，在表目录下存放着实际的数据文件

# hadoop fs -ls /user/hive/warehouse
# hadoop fs -ls /user/hive/warehouse/china_all.db
# hadoop fs -ls /user/hive/warehouse/china_all.db/china_map

查看china_map表数据

# hadoop fs -cat /user/hive/warehouse/china_all.db/china_map/*

使用Sqoop导出china_map数据到MySQL 中（导出时需要在数据库中创建相应的表，导入的数据要与表中的列相匹配）

# sqoop export --connect jdbc:mysql://master:3306/china_all \
--username root  \
--password root \
--table china_map  \
--fields-terminated-by ',' \
--m 1 \
--export-dir /user/hive/warehouse/china_all.db/china_map/*

查看china_map表数据

# mysql -uroot -proot -e "select * from china_all.china_map"

使用Sqoop导出city_precipitation_top10数据到MySQL 中

# sqoop export --connect jdbc:mysql://master:3306/china_all \
--username root  \
--password root \
--table city_precipitation_top10  \
--fields-terminated-by ',' \
--m 1 \
--export-dir /user/hive/warehouse/china_all.db/city_precipitation_top10/*

查看city_precipitation_top10表数据

# mysql -uroot -proot -e "select * from china_all.city_precipitation_top10"

使用Sqoop导出city_temp数据到MySQL 中

# sqoop export --connect jdbc:mysql://master:3306/china_all \
--username root  \
--password root \
--table city_temp  \
--fields-terminated-by ',' \
--m 1 \
--export-dir /user/hive/warehouse/china_all.db/city_temp/*

查看city_temp表数据

# mysql -uroot -proot -e "select * from china_all.city_temp limit 30"

使用Sqoop导出province_temp数据到MySQL 中

# sqoop export --connect jdbc:mysql://master:3306/china_all \
--username root  \
--password root \
--table province_temp  \
--fields-terminated-by ',' \
--m 1 \
--export-dir /user/hive/warehouse/china_all.db/province_temp/*

查看province_temp表数据

# mysql -uroot -proot -e "select * from china_all.province_temp"

使用Sqoop导出province_pressure数据到MySQL 中

# sqoop export --connect jdbc:mysql://master:3306/china_all \
--username root  \
--password root \
--table province_pressure  \
--fields-terminated-by ',' \
--m 1 \
--export-dir /user/hive/warehouse/china_all.db/province_pressure/*

查看province_pressure表数据

# mysql -uroot -proot -e "select * from china_all.province_pressure"

使用Sqoop导出province_temp_all数据到MySQL 中

# sqoop export --connect jdbc:mysql://master:3306/china_all \
--username root  \
--password root \
--table province_temp_all  \
--fields-terminated-by ',' \
--m 1 \
--export-dir /user/hive/warehouse/china_all.db/province_temp_all/*

查看province_temp_all表数据

# mysql -uroot -proot -e "select * from china_all.province_temp_all limit 30"

标签

#hive #sqoop #mysql