Apache Sqoop Export Command Usage

posted on Nov 20th, 2016

Apache Sqoop

Apache Sqoop is a command-line interface application for transferring data between relational databases and Hadoop. It supports incremental loads of a single table or a free form SQL query as well as saved jobs which can be run multiple times to import updates made to a database since the last import. Imports can also be used to populate tables in Hive or HBase. Exports can be used to put data from Hadoop into a relational database. Sqoop got the name from sql+hadoop. Sqoop became a top-level Apache project in March 2012.

Pre Requirements

1) A machine with Ubuntu 14.04 LTS operating system.

2) Apache Hadoop pre installed (How to install Hadoop on Ubuntu 14.04)

3) MySQL Database pre installed (How to install MySQL Database on Ubuntu 14.04)

4) Apache Sqoop pre installed (How to install Sqoop on Ubuntu 14.04)

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;

Apache Sqoop Export Command Usage

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;

Apache Sqoop Export Command Usage

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;

Apache Sqoop Export Command Usage

Please share this blog post and follow me for latest updates on

facebook             google+             twitter             feedburner

Previous Post                                                                                          Next Post

Labels : Apache Sqoop Installation on Ubuntu   Apache Sqoop Import Command Example   Apache Sqoop Job Command Example   Apache Sqoop Codegen Command Example   Apache Sqoop Eval Command Example   Apache Sqoop List-tables Command Example   Apache Sqoop List-tables Command Example