mysql与hdfs的数据迁移（sqoop）

107 阅读 0 评论 71 点赞

我是靠谱客的博主安详红酒，这篇文章主要介绍mysql与hdfs的数据迁移（sqoop），现在分享给大家，希望可以做个参考。

文章目录

- - 1、查看mysql数据
  - 2、查看mysql表
  - 3、mysql表导入到hdfs
  - 4、查看hdfs
  - 5、清空dept表
  - 6、数据从hdfs导出到MySQL表
  - 7、查询dept表
  - 8、Sqoop Import 增量导入到 HDFS

打开一个 dektop 终端，不要打开多个终端，所用数据看上一篇

1、查看mysql数据

hadoop@ddai-desktop:~$ sqoop list-databases --connect jdbc:mysql://ddai-master:3306/ --username sqoop --password Dai@123456

在这里插入图片描述

2、查看mysql表

hadoop@ddai-desktop:~$ sqoop list-tables --connect jdbc:mysql://ddai-master:3306/sqoop --username sqoop --password Dai@123456

在这里插入图片描述

3、mysql表导入到hdfs

打开hadoop，用上一篇创建的表

hadoop@ddai-desktop:~$ sqoop import --connect jdbc:mysql://ddai-master:3306/sqoop --username sqoop --password Dai@123456 --table dept -m 1 --target-dir /user/dept

在这里插入图片描述

4、查看hdfs

复制代码

1
2
hadoop@ddai-desktop:~$ hdfs dfs -ls /user/dept

在这里插入图片描述

复制代码

1
2
hadoop@ddai-desktop:~$ hdfs dfs -cat /user/dept/part-m-00000

在这里插入图片描述

5、清空dept表

复制代码

1
2
3
4
hadoop@ddai-desktop:~$ mysql -hddai-master -usqoop -pDai@123456 
mysql> use sqoop;
mysql> truncate dept;

在这里插入图片描述

6、数据从hdfs导出到MySQL表

hadoop@ddai-desktop:~$ sqoop export --connect jdbc:mysql://ddai-master:3306/sqoop --username sqoop --password Dai@123456 --table dept -m 1 --export-dir /user/dept

在这里插入图片描述

7、查询dept表

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
hadoop@ddai-desktop:~$ mysql -hddai-master -usqoop -pDai@123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 24
Server version: 8.0.26-0ubuntu0.20.04.2 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use sqoop;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from dept;
+------+-----------+
| id   | name      |
+------+-----------+
| 1245 | 云计算    |
| 3512 | 物联网    |
| 3545 | 大数据    |
+------+-----------+
3 rows in set (0.00 sec)

mysql> quit
Bye

8、Sqoop Import 增量导入到 HDFS

复制代码

hadoop@ddai-desktop:~$ mysql -hddai-master -usqoop -pDai@123456 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 25
Server version: 8.0.26-0ubuntu0.20.04.2 (Ubuntu)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use sqoop;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into dept values(7854,'今天是2021七夕');
Query OK, 1 row affected (0.00 sec)

mysql> select * from dept;
+------+---------------------+
| id   | name                |
+------+---------------------+
| 1245 | 云计算              |
| 3512 | 物联网              |
| 3545 | 大数据              |
| 7854 | 今天是2021七夕      |
+------+---------------------+
4 rows in set (0.00 sec)

mysql> quit
Bye

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
hadoop@ddai-desktop:~$ mysql -hddai-master -usqoop -pDai@123456 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 25
Server version: 8.0.26-0ubuntu0.20.04.2 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use sqoop;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into dept values(7854,'今天是2021七夕');
Query OK, 1 row affected (0.00 sec)

mysql> select * from dept;
+------+---------------------+
| id   | name                |
+------+---------------------+
| 1245 | 云计算              |
| 3512 | 物联网              |
| 3545 | 大数据              |
| 7854 | 今天是2021七夕      |
+------+---------------------+
4 rows in set (0.00 sec)

mysql> quit
Bye

hadoop@ddai-desktop:~$ sqoop import --connect jdbc:mysql://ddai-master:3306/sqoop --username sqoop --password Dai@123456 --table dept -m 1 --target-dir /user/dept -incremental append --check-column id

在这里插入图片描述