Import all tables from MySql database to HDFS
In this blog, I will show you the way to import all tables from MySql database.
STEP 1. First connect to MySQL database and check the no of tables.
cloudera@cloudera-vm:~$ mysql -uroot -ptiger
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 64
Server version: 5.1.61-0ubuntu0.10.10.1 (Ubuntu)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
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> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| test |
+——————–+
3 rows in set (0.04 sec)
mysql> use test;
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.
Here are three tables that need to be import to HDFS.
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| company |
| employee |
| student |
+—————-+
3 rows in set (0.00 sec)
STEP 2. Fire the following SQOOP command.
sqoop import-all-tables –connect jdbc:mysql://127.0.0.1:3306/test –username root –password tiger –m 1 -warehouse-dir /user/cloudera/alltables
STEP 3. Crosscheck if the part-file created on HDFS.
cloudera@cloudera-vm:~$ hadoop fs -ls /user/cloudera/alltables/company
Found 3 items
-rw-r–r– 1 cloudera supergroup 0 2019-01-21 22:10 /user/cloudera/alltables/company/_SUCCESS
drwxr-xr-x – cloudera supergroup 0 2019-01-21 22:09 /user/cloudera/alltables/company/_logs
-rw-r–r– 1 cloudera supergroup 37 2019-01-21 22:10 /user/cloudera/alltables/company/part-m-00000
