Sqoop Export Function

[vc_row][vc_column][vc_text_separator title=”Chapter 5: Sqoop Export Tool” color=”green” style=”double” border_width=”2″ add_icon=”true”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]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.[/vc_column_text][/vc_column][/vc_row][vc_row][vc_column][vc_custom_heading text=”How to export the file from Hadoop to RDBMS” font_container=”tag:h2|text_align:left|color:%2352962e” use_theme_fonts=”yes”][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]Let’s say we have a file in the directory ‘empdetails’ and the file content is something like below-[/vc_column_text][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]

101,     John,   5000,   IT,        Developer

102,     Chris,   6000,   Sales,   Manager

103,     Jamie,  7000,   Support, Director

[/vc_column_text][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]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-[/vc_column_text][/vc_column][/vc_row][vc_row][vc_column][vc_cta h2=”” style=”3d” color=”grey” add_icon=”left” i_icon_fontawesome=”fa fa-code”]mysql> USE empdb;

mysql> CREATE TABLE emp (

id INT NOT NULL PRIMARY KEY,

name VARCHAR(20),

designation VARCHAR(20),

salary INT,

department VARCHAR(10));[/vc_cta][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]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.[/vc_column_text][/vc_column][/vc_row][vc_row][vc_column][vc_cta h2=”” style=”3d” color=”grey” add_icon=”left” i_icon_fontawesome=”fa fa-code” i_on_border=”true”]sqoop export \

–connect jdbc:mysql://localhost/empdb \

–username root \

–table emp \

–export-dir /empdetails[/vc_cta][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]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.[/vc_column_text][/vc_column][/vc_row]

Previous: Sqoop Import ToolChapter 6: Sqoop Jobs