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: