Here we are connecting to MySQL through JDBC connectors and using the database Acadgild. Here it is necessary to specify the MySQL ‘s username and password and the table name.
In our case, the command will be as shown below:

Here ‘-m’ specifies the number of map task that can be run simultaneously and ‘m1’ means that only one map task can run.Note: If we do not use -m1 at the end of the statement, for each record in the MySQL table we will get separate files in the HDFS.
Now the data in RDBMS has been successfully imported into HDFS. By default, the files will be stored here: /user/$user_name/table_name/part-m-00000 file.
Refer to the below image for the same.

Importing all the Tables in a Database to HDFS

We can import all the tables present in a database by specifying import-all-tables as shown in the below command:

When we specify import-all-tables in the command, Sqoop will internaly call a ‘import all tables’ tool. This tool will import all the tables into the HDFS so that we can specify the directory of our own by using —target-dir.While using the above command, for every table a MapReduce job will be launched and each table will get imported into HDFS sequentially.
We now have two tables in our MySQL database.
When we check the /user/$User_name/ directory of HDFS, we can see that the two tables have been successfully imported. You can refer the below screen shot for the same..

Importing table data into a Specific Directory in HDFS

We can also import data to a specific directory by specifying it in the command as –target-dir as shown in the below command:

As the sequence file stores the contents in binary format, we will get the binary output as shown in the below screen shot.We have now successfully stored the RDBMS data in a sequence file using Sqoop.

Importing Table as a Avro File into HDFS

We can also store the RDBMS data as an Avro file by specifying –as-avrodatafile as shown in the below command:

When storing a file using Avro file format, we will get the output file with .avro extension and the contents inside the file will be in binary format. We can see the same in the below screenshot:We have now successfully stored the contents of RDBMS in an Avro file using Sqoop.
The above mentioned scenarios are just few of the circumstances for importing data from RDBMS to HDFS using Sqoop.
Now, let’s take a look at how to export data from HDFS to RDBMS using Sqoop.

Export Data from HDFS to RDBMS

To export the data in HDFS to MySQL, first we need to create a table in MYSQL which matches the schema of the data in HDFS.Let’s export the Customers data set shown in the below screen shot in HDFS to RDBMS.
It consists of the fields, Customer_Id, Customer_name and Customer_city.
Let’s create a table in MYSQL using the below command:

The data has now been successfully transferred from HDFS to RDBMS. We can check the data in MySQL using the below command:

select * from customers;

We can see that the contents of dataset is now available inside the table customers, which is present in MySQL. Thus we have successfully transferred data from HDFS to RDBMS.

Hope this post helped you to learn how to import and export data between HDFS and RDBMS. Keep visiting our website Acadgild for more updates on Big Data and other technologies. Click here to learn Big Data Hadoop Development.