将MySQL中的用户Sqoop里的表上传到Hive中去

环境准备：

①：启动集群：start-all.sh

②：启动mysql:在root用户下输入：/home/mysql5.7/support-files/mysql.server start

1. 在MySQL中，创建一个用户，用户名为sqoop02，密码为：123456

①创建sqoop02用户:

#CREATE USER 'sqoop02'@'%' IDENTIFIED BY '123456';

②授sqoop02用户所有数据库的读写权限:

#GRANT ALL PRIVILEGES ON *.* TO 'sqoop02'@'%';

③刷新权限，使更改生效：

#FLUSH PRIVILEGES;

1. 登陆MySQL的sqoop02用户，创建一张数据库，数据库的名称为db02

①登陆MySQL的sqoop02用户：

#mysql -u sqoop02 -p

②创建一张数据库，数据库的名称为db02：

mysql>create database db02;

3.在数据库db02中创建10张表，表名和字段可以自定义，每张表的数据不少于5行

①在sqoop02用户下创表：

表1：

mysql> create table student(id char(30) ,name varchar(50), age int, sex char(10));

正在上传…重新上传取消

插入数据：

mysql> insert into db02.student value("20110201","liyi",19,'girl');

mysql> insert into db02.student value("20110201","wangwu",20,'boy');

mysql> insert into db02.student value("20110201","Tom",20,'boy');

mysql> insert into db02.student value("20110202","Kevin",21,'boy');

mysql> insert into db02.student value("20110203","Dachuan",20,'girl');

mysql> insert into db02.student value("20110204","Jane",20,'girl');

插入成功：

正在上传…重新上传取消正在上传…重新上传取消

表2：

mysql> create table student1(id char(30) ,name varchar(50), age int, sex char(10));

插入数据：

mysql> insert into db02.student1 value("20110205","Dawei",22,'boy');

mysql> insert into db02.student1 value("20110206","zhangsan",20,'boy');

mysql> insert into db02.student1 value("20110207","Tony",20,'boy');

mysql> insert into db02.student1 value("20110208","Kangkang",21,'boy');

mysql> insert into db02.student1 value("20110209","Mariy",22,'girl');

mysql> insert into db02.student1 value("20110210","Jeriy",21,'girl');

正在上传…重新上传取消正在上传…重新上传取消

表3：

mysql> create table student2(id char(30) ,name varchar(50), age int, sex char(10));

插入数据：

mysql> insert into db02.student2 value("20110211","Lisi",22,'boy');

mysql> insert into db02.student2 value("20110212","zhangwei",20,'boy');

mysql> insert into db02.student2 value("20110213","Dapao",21,'boy');

mysql> insert into db02.student2 value("20110214","Tiechui",21,'girl');

mysql> insert into db02.student2 value("20110215","Dayan",22,'girl');

mysql> insert into db02.student2 value("20110216","Wanggang",21,'girl');

正在上传…重新上传取消正在上传…重新上传取消

表4：

mysql> create table student3(id char(30) ,name varchar(50), age int, sex char(10));

插入数据：

mysql> insert into db02.student3 value("20110217","Likui",22,'boy');

mysql> insert into db02.student3 value("20110218","wangzai",21,'boy');

mysql> insert into db02.student3 value("20110219","Xiaofang",21,'boy');

mysql> insert into db02.student3 value("20110220","Cuihua",21,'girl');

mysql> insert into db02.student3 value("20110221","Yanzi",20,'girl');

mysql> insert into db02.student3 value("20110222","Wangguihua",21,'girl');

正在上传…重新上传取消正在上传…重新上传取消

表5：

mysql> create table student4(id char(30) ,name varchar(50), age int, sex char(10));

插入数据：

mysql> insert into db02.student4 value("20110223","Yanghongjun",22,'boy');

mysql> insert into db02.student4 value("20110224","Lihongjun",21,'boy');

mysql> insert into db02.student4 value("20110225","Dahaozi",21,'boy');

mysql> insert into db02.student4 value("20110226","Wendaqing",21,'girl');

mysql> insert into db02.student4 value("20110227","Sudasui",20,'girl');

mysql> insert into db02.student4 value("20110228","Wanglaoji",21,'girl');

正在上传…重新上传取消正在上传…重新上传取消

表6：

mysql> create table student5(id char(30) ,name varchar(50), age int, sex char(10));

插入数据：

mysql> insert into db02.student5 value("20110229","Yanghong",22,'boy');

mysql> insert into db02.student5 value("20110230","Lijun",21,'boy');

mysql> insert into db02.student5 value("20110231","Dazi",21,'boy');

mysql> insert into db02.student5 value("20110232","Wendaqi",21,'girl');

mysql> insert into db02.student5 value("20110233","Suda",20,'girl');

mysql> insert into db02.student5 value("20110234","Wanglao",21,'girl');

正在上传…重新上传取消正在上传…重新上传取消

表7：

mysql> create table student6(id char(30) ,name varchar(50), age int, sex char(10));

插入数据：

mysql> insert into db02.student6 value("20110235","Yanhong",22,'boy');

mysql> insert into db02.student6 value("20110236","Lijiu",21,'boy');

mysql> insert into db02.student6 value("20110237","Damaha",21,'boy');

mysql> insert into db02.student6 value("20110238","Wendao",21,'girl');

mysql> insert into db02.student6 value("20110239","Maidong",20,'girl');

mysql> insert into db02.student6 value("20110240","Wahaha",21,'girl');

正在上传…重新上传取消正在上传…重新上传取消

表8：

mysql> create table student7(id char(30) ,name varchar(50), age int, sex char(10));

插入数据：

mysql> insert into db02.student7 value("20110235","Tangseng",55,'boy');

mysql> insert into db02.student7 value("20110236","Sunwukong",66,'boy');

mysql> insert into db02.student7 value("20110237","Zhubajie",77,'boy');

mysql> insert into db02.student7 value("20110238","Saseng",88,'boy');

mysql> insert into db02.student7 value("20110239","Baigujing",20,'girl');

mysql> insert into db02.student7 value("20110240","Yutujing",21,'girl');

正在上传…重新上传取消正在上传…重新上传取消

表9：

mysql> create table student8(id char(30) ,name varchar(50), age int, sex char(10));

插入数据：

mysql> insert into db02.student8 value("20110241","Gaoqiqing",55,'boy');

mysql> insert into db02.student8 value("20110242","Anxin",45,'boy');

mysql> insert into db02.student8 value("20110243","Lixiang",46,'boy');

mysql> insert into db02.student8 value("20110244","Gaoqisheng",34,'boy');

mysql> insert into db02.student8 value("20110245","Mengyu",44,'girl');

mysql> insert into db02.student8 value("20110246","Laomo",51,'boy');

正在上传…重新上传取消正在上传…重新上传取消

表10：

mysql> create table student9(id char(30) ,name varchar(50), age int, sex char(10));

插入数据：

mysql> insert into db02.student9 value("20110247","Gaoqiqiang",55,'boy');

mysql> insert into db02.student9 value("20110248","Lihomgwei",45,'boy');

mysql> insert into db02.student9 value("20110249","Xiaolong",46,'boy');

mysql> insert into db02.student9 value("20110250","Gaosheng",34,'boy');

mysql> insert into db02.student9 value("20110251","Mengyue",44,'girl');

mysql> insert into db02.student9 value("20110252","Laomomo",51,'boy');

正在上传…重新上传取消正在上传…重新上传取消

10张表创建成功

正在上传…重新上传取消正在上传…重新上传取消

②列除mysql数据库中所有数据库或者表：

 # sqoop list-databases -connect jdbc:mysql://192.168.43.216:3306 -username root -password 123456

运行上面指令出现错误，发现是root权限问题

正在上传…重新上传取消正在上传…重新上传取消

解决方法：

#grant all privileges on *.* to ‘sqoop02’@’%’ identified by ‘123456’

正在上传…重新上传取消正在上传…重新上传取消

运行结果：

正在上传…重新上传取消正在上传…重新上传取消

③查看mysql里的数据库

# sqoop list-databases -connect jdbc:mysql://192.168.43.216:3306/mysql -username root -password 123456

正在上传…重新上传取消正在上传…重新上传取消

④检查hive里面有没有同名数据库

正在上传…重新上传取消正在上传…重新上传取消

4.使用ETL工具Sqoop，将MySQL数据库db02中的10张的表结构和数据导入（同步）到大数据平台的Hive中

 #sqoop import -connect jdbc:mysql://192.168.43.216:3306/db02 -username root -password 123456 -table student -hive-import -hive-table bigdata02.st -m 1

注：db02为在sqoop02中创建的数据库，student为在db02数据库中创建的表

Bigdata02为在hive下创建的数据库，st为同步成功后自动生成的表

运行上面指令出现一下错误：

正在上传…重新上传取消正在上传…重新上传取消

解决方法：

输入命令hadoop classpath,

正在上传…重新上传取消正在上传…重新上传取消

将出现的内容复制到

/home/hadoop/software/hadoop-3.3.0/etc/hadoop/yarn-site.xml中

正在上传…重新上传取消正在上传…重新上传取消

再用scp命令下发两个文件到两个从节点中

正在上传…重新上传取消正在上传…重新上传取消

进行以上操作后再运行：

 #sqoop import -connect jdbc:mysql://192.168.43.216:3306/db02 -username root -password 123456 -table student -hive-import -hive-table bigdata02.st -m 1

运行成功！

正在上传…重新上传取消正在上传…重新上传取消

进行后面几个表的同步：

#sqoop import -connect jdbc:mysql://192.168.43.216:3306/db02 -username root -password 123456 -table student1 -hive-import -hive-table bigdata02.st1 -m 1

# sqoop import -connect jdbc:mysql://192.168.43.216:3306/db02 -username root -password 123456 -table student2 -hive-import -hive-table bigdata02.st2 -m 1

# sqoop import -connect jdbc:mysql://192.168.43.216:3306/db02 -username root -password 123456 -table student3 -hive-import -hive-table bigdata02.st3 -m 1

# sqoop import -connect jdbc:mysql://192.168.43.216:3306/db02 -username root -password 123456 -table student4 -hive-import -hive-table bigdata02.st4 -m 1

# sqoop import -connect jdbc:mysql://192.168.43.216:3306/db02 -username root -password 123456 -table student5 -hive-import -hive-table bigdata02.st5 -m 1

# sqoop import -connect jdbc:mysql://192.168.43.216:3306/db02 -username root -password 123456 -table student6 -hive-import -hive-table bigdata02.st6 -m 1

# sqoop import -connect jdbc:mysql://192.168.43.216:3306/db02 -username root -password 123456 -table student7 -hive-import -hive-table bigdata02.st7 -m 1

# sqoop import -connect jdbc:mysql://192.168.43.216:3306/db02 -username root -password 123456 -table student8 -hive-import -hive-table bigdata02.st8 -m 1

# sqoop import -connect jdbc:mysql://192.168.43.216:3306/db02 -username root -password 123456 -table student9 -hive-import -hive-table bigdata02.st9 -m 1

同步成功

正在上传…重新上传取消正在上传…重新上传取消