Apache Sqoop Import

Sqoop - Installation

Sqoop - Import-All Tables

Apache Sqoop Import tool is used to import data from a relational database system or a mainframe into HDFS. Sqoop import tool takes the tables as input and reads records row-by-row into Hadoop HDFS. For other types of datasets such as a mainframe, Sqoop Import reads each dataset in Hadoop HDFS. Once the Sqoop Import process is completed the output is generated as a set of files that contain a copy of the imported datasets or tables. The Apache Import task is performed in parallel and hence there are multiple output files are generated. The content of files would be either text delimited or Avro or SequenceFiles defined by the user.

Apache Sqoop Import Syntax

The following syntax is used to import data in Hadoop HDFS.

$sqoop import (gen-args) (imp-args)

Apache Sqoop Import Example

Let us see an example to import data from MySQL database to Hadoop HDFS.

To perform this operation we will create a database name “userdata” in MySQL. Under this database, we will create a table named “employee”. We will import this table's data in Hadoop HDFS.

Before performing any action please make sure that the MySQL database is installed on your system otherwise you can install it using the below steps.

cloudduggu@ubuntu:~$ sudo apt-get install mysql-server
cloudduggu@ubuntu:~$ sudo apt-get update

Now login into MySql and set a password for the root user using the below commands.

cloudduggu@ubuntu:~$ sudo mysql -u root -p

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'cloudduggu';
mysql> FLUSH PRIVILEGES;

Let us create a database named "userdata” and a table named “employee” in MySql and insert data.

Please use below SQL to perform this action.

mysql> CREATE database userdata;
mysql> USE userdata;
mysql> CREATE TABLE employee (empid int, empname VARCHAR(20),city VARCHAR(20), county VARCHAR(20), state VARCHAR(20), zip int);
mysql> INSERT INTO employee VALUES(1001,'Jettie','Bridgewater','Somerset','NJ',8807);
mysql> INSERT INTO employee VALUES(1002,'Lemuel','Bohemia','Suffolk','NJ',11716);
mysql> INSERT INTO employee VALUES(1003,'Melodie','Thousand','Ventura','FL',8876);
mysql> INSERT INTO employee VALUES(1004,'Candida','Buffalo','Tarrant','NJ',8807);
mysql> INSERT INTO employee VALUES(1005,'Karan','Bangor','Okaloosa','PA',8807);
mysql> INSERT INTO employee VALUES(1006,'Andra','Crestview','Philadelphia','NJ',8807);
mysql> INSERT INTO employee VALUES(1007,'Belen','Jacksonville','Somerset','FL',8807);
mysql> INSERT INTO employee VALUES(1008,'Jolanda','Crestview','Douglas','NJ',8807);
mysql> INSERT INTO employee VALUES(1009,'Barrett','Bridgewater','Duval','NJ',8807);
mysql> INSERT INTO employee VALUES(1010,'Ashlyn','Kennedale','Marion','GA',8807);

Apache Sqoop Import Table

We will import this table “employee” from MySQL to Hadoop HDFS using the below Import command.

Command:

cloudduggu@ubuntu:~$ sqoop import  --connect jdbc:mysql://localhost/userdata?serverTimezone=UTC --username root --password cloudduggu --table employee --m 1

Let us understand this command.

sqoop import: It is the starting point of the Import command

--connect jdbc:mysql://localhost/userdata: It is JDBC connection detail for MySQL database.

serverTimezone=UTC: It is used to set MySQL JDBC time zone. If you are getting any time zone issue then you can set it otherwise you can ignore it.

--username root: It is the user name that Import will use to connect with MySQL databases.

-password cloudduggu: It is the password of user “root”.

--table employee: It is the table name that we are importing in Hadoop HDFS.

--m 1: It represents the number of mappers. In this case, we are using one mapper. This parameter is used to control parallelism.

Output:

After completion of the Sqoop Import job, we will receive the below output.

We can use the following command to verify the imported data.

cloudduggu@ubuntu:~$ hadoop fs -cat /user/cloudduggu/employee/part-m-00000

Import in HDFS Target Directory

We will import table “employee” from MySQL to Hadoop HDFS “/importdata” target directory.

Command:

cloudduggu@ubuntu:~$ sqoop import --connect jdbc:mysql://localhost/userdata?serverTimezone=UTC --username root --password cloudduggu --table employee --m 1 --target-dir /importdata

Output:

We will see the following output after the completion of the Apache Sqoop job.

Now we will verify the output using the below command.

cloudduggu@ubuntu:~$ hadoop fs -cat /importdata/part-m-00000

Import Subset Data of Table

Using the “where” clause we can import subset data of a table in HDFS. Here we are importing only those records where "state='NJ'.

Command:

cloudduggu@ubuntu:~$ sqoop import --connect jdbc:mysql://localhost/userdata?serverTimezone=UTC --username root --password cloudduggu --table employee --m 1 --where "state='NJ'" --target-dir /importdata

Output:

Following output, we will see post completion of Apache Sqoop job.

We can use the following command and verify the data.

cloudduggu@ubuntu:~$ hadoop fs -cat /importdata/part-m-00000

Incremental Import

Using Incremental Import we can import only newly added rows of the table. It can be done by adding ‘incremental’, ‘check-column’, and ‘last-value’ options.

We will add below two records in the “employee” table. After that, we will perform the incremental import to load only new records in HDFS.

mysql> insert into employee values(1011,'Mozell','Lafayette','Chester','PA',19320);
mysql> insert into employee values(1012,'Viola','Northridge','Wayne','TX',77301);

Command:

cloudduggu@ubuntu:~$ sqoop import --connect jdbc:mysql://localhost/userdata?serverTimezone=UTC --username root --password cloudduggu --table employee --m 1 --incremental append --check-column empid --last-value 1010 --target-dir /importdata

Output:

Once the Apache Sqoop job is completed, we will see the following output.

We can verify imported data using the below command in Hadoop HDFS.

cloudduggu@ubuntu:~$ hadoop fs -cat /importdata/part-m-00000