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

Leave a Reply

Your email address will not be published. Required fields are marked *