Sqoop Export to MySQL database from HDFS


Sqoop Export to MySQL database from HDFS

Export data back from the HDFS to the RDBMS database. The target table must exist in the target database. The files which are given as input to the Sqoop contain records, which are called rows in table. Those are read and parsed into a set of records and delimited with user-specified delimiter.

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.

Step 1 - Change the directory to /usr/local/hadoop/sbin




$ cd /usr/local/hadoop/sbin

Step 2 - Start all hadoop daemons.




$ start-all.sh

Step 3 - The JPS (Java Virtual Machine Process Status Tool) tool is limited to reporting information on JVMs for which it has the access permissions.




$ jps

Step 4 - Create a emp.txt file.




emp.txt

Step 5 - Add these following lines to emp.txt file.




1201,gopal,manager,50000,TP
1202,manisha,Proofreader,50000,TP
1203,khalil,phpdev,30000,AC
1204,prasanth,phpdev,30000,AC
1205,kranthi,admin,20000,TP


Step 6 - Create a emp_add.txt file.




emp_add.txt

Step 7 - Add these following lines to emp_add.txt file.




1201,288A,vgiri,jublee
1202,108I,aoc,sec-bad
1203,144Z,pgutta,hyd
1204,78B,oldcity,sec-bad
1205,720X,hitec,sec-bad


Step 8 - Create a emp_cnt.txt file.




emp_cnt.txt

Step 9 - Add these following lines to emp_cnt.txt file.




1201,2356742,gopal@tp.com
1202,1661663,manisha@tp.com
1203,8887776,khalil@ac.com
1204,9988774,prasanth@ac.com
1205,1231231,kranthi@tp.com


Step 10 - Copy all these text files in HDFS. In my case all these text files are saved in /home/hduser/Desktop/SQOOP/ directory.




$ hdfs dfs -copyFromLocal /home/hduser/Desktop/SQOOP/emp.txt /sqoop

$ hdfs dfs -copyFromLocal /home/hduser/Desktop/SQOOP/emp_add.txt /sqoop

$ hdfs dfs -copyFromLocal /home/hduser/Desktop/SQOOP/emp_cnt.txt /sqoop


Step 11 - Enter into MySQL command line inteface(CLI). Open a terminal (CTRL + ALT + T) and type the following command.




$ mysql -u root -p

Enter password




Enter password :****

Step 12 - Create a new database 'userdb'




mysql> create database userdb;

Step 13 - Use database 'userdb'




mysql> use userdb;

Step 14 - Create a new table 'employee'




mysql> create table employee(id int,name varchar(20),deg varchar(20),salary int,dept varchar(20), primary key(id));


Step 15 - Change the directory to /usr/local/sqoop/bin




cd /usr/local/sqoop/bin

Step 16 - The following command is used to export the table data (which is in emp.txt file on HDFS) to the employee table in db database of Mysql database server.




$ sqoop export \
--connect jdbc:mysql://localhost/userdb \
--username root \
--password root \
--table employee \
--export-dir hdfs://localhost:9000/sqoop/emp.txt


Step 17 - Verify




mysql> select * from employee;

Step 18 - Create a new table 'employee_address'




mysql> create table employee_address(id int,hno varchar(20),street varchar(20),city varchar(20),primary key(id));

Step 19 - The following command is used to export the table data (which is in emp_add.txt file on HDFS) to the employee table in db database of Mysql database server.




$ sqoop export \
--connect jdbc:mysql://localhost/userdb \
--username root \
--password root \
--table employee_address \
--export-dir hdfs://localhost:9000/sqoop/emp_add.txt


Step 20 - Verify




mysql> select * from  employee_address;

Step 21 - Create a new table 'employee_contact'




mysql> create table employee_contact(id int,phno varchar(20),email varchar(30),primary key(id));

Step 22 - The following command is used to export the table data (which is in emp_cnt.txt file on HDFS) to the employee table in db database of Mysql database server.




$ sqoop export \
--connect jdbc:mysql://localhost/userdb \
--username root \
--password root \
--table employee_contact \
--export-dir hdfs://localhost:9000/sqoop/emp_cnt.txt


Step 23 - Verify




mysql> select * from  employee_contact;



Have any Question or Comment?

Leave a Reply

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