SQOOP EXECUTION ENGINE

Here, we are going to create a table called “student” in MySQL and we will import it in local file system and hadoop distributed file system using sqoop. Since we are going to use HDFS, we need to start the hadoop first.

    $ start-dfs.sh
    $ start-yarn.sh

Now create a table and insert some records in it, execute the follwing commands given below:

    $ mysql -uroot -proot
    mysql>create database test;
    mysql>use test;
    mysql>create table student(id int,name varchar(10));
    mysql>insert into student values(1,'Anoop');
    mysql>insert into student values(2,'Blessy');
    mysql>insert into student values(3,'Maria');
    mysql>insert into student values(4,'Sekhar');
    mysql>exit

In order to test whether sqoop is working or not, then run these following commands from command line:

    $ cd /usr/local/sqoop/bin

Command to generate a jar file

    $ sqoop codegen --connect jdbc:mysql://localhost/test --username root -P --table student

You can take help from the image given below:

It will prompt for MySQL password thus we just need to give the password. This command will create a jar file < tablename >.jar, for this tutorial student.jar, under /tmp/sqoop-hduser compile/87c96b3f6ad4b8ec480d974144b4f3b4. The last directory under tmp may vary on your system. You need to take a bit care here, as this directory will be used in the subsequent commands.

You will get the output like the image given below.

Local Import: Import the table in Local File System:

    $ sqoop import -fs local -jt local -libjars /tmp/sqoop-hduser/compile/87c96b3f6ad4b8ec480d974144b4f3b4/student.jar --connect jdbc:mysql://localhost/test --username root --P --table student

As the example table “student” do not have any primary key column, then we get an exception like the image given below:

So, we will use the “-m 1” field for the table which is having no primary key column.

    $ sqoop import -fs local -jt local -libjars /tmp/sqoop-hduser/compile/87c96b3f6ad4b8ec480d974144b4f3b4/student.jar --connect jdbc:mysql://localhost/test --username root --P --table student -m 1

You can take help from the image given below:

You might get an exception that the FileAlreadyExistException like the highlighted part in the image given below:

Here you need not to get worry, we just need to remove the table using the command give below:

    $ sudo rm -r student

And again execute the previous command like the image given below:

    $ sqoop import -fs local -jt local -libjars /tmp/sqoop-hduser/compile/87c96b3f6ad4b8ec480d974144b4f3b4/student.jar --connect jdbc:mysql://localhost/test --username root --P --table student -m 1

You can take help from the image given below:

Once this command runs successfully, you will get the output like the image given below:

And also a directory called student will get generated in the local file system in the pwd, from where you ran above three commands. You can see the contents of sqoop generated directory “student” using commands given below:

    $ cd student
    $ cat part-m-00000 

You can take help from the image given below:

HDFS Import:

The below command import the contents of student table into the HDFS directory /student. Here, in the HDFS, the student directory must not be available otherwise you will get some exception.

    $ sqoop import -fs local -libjars /tmp/sqoop-hduser/compile/87c96b3f6ad4b8ec480d974144b4f3b4/student.jar --connect jdbc:mysql://localhost/test --username root --P --table student -m 1 --target-dir hdfs://localhost:54310/student

You can take help from the image given below:

Once this command runs successfully, you will get the output like the image given below:

And also contents of the student table, that had been created earlier, will move in the HDFS student directory. You can use cat command to see the contents of student by using the command given below:

    $ hadoop fs -cat /student/part-m-00000

You will get the output like the image given below:

This ends sqoop installation and verfication.