Chapter 5: Sqoop Export Tool
Like we did for import tool, similarly, we have an export tool to export the data back to RDBMS from Hadoop.
The only thing you should take care here is, you must have the table created in RDBMS before exporting data from Hadoop to a relational database.
As usual, the records of the Hadoop file will be the row of the RDBMS table.
The default operation is to insert all the record from the input files to the database table using the INSERT statement. In update mode, Sqoop generates the UPDATE statement that replaces the existing record into the database.
How to export the file from Hadoop to RDBMS
Let’s say we have a file in the directory ‘empdetails’ and the file content is something like below-
101, John, 5000, IT, Developer
102, Chris, 6000, Sales, Manager
103, Jamie, 7000, Support, Director
And now we want to export this Hadoop file in MySQL database ‘empdb’. Then first create a table ‘emp’ in ‘empdb’ database in MySQL and then export the file.
Let’s first create the table-
mysql> USE empdb; mysql> CREATE TABLE emp ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20), designation VARCHAR(20), salary INT, department VARCHAR(10));
Now once we created the table, it’s time to move the data into MySQL. Use the below commands to export the Hadoop data to MySQL-
sqoop export \ --connect jdbc:mysql://localhost/empdb \ --username root \ --table emp \ --export-dir /empdetails
Now just to confirm how data has been exported from Hadoop to relational database MySQL, check the table content simply by using the select command.
Select * from emp;
This should give you all the rows of the table. These rows will be same as the records available in the HDFS file.