Hadoop SQOUT Usage

Importing Data from using an RDBMS:-

To connect to Mysql Database

/home# Mysql-u root-p

Enter Password:

               After successful login, it will prompt you into Mysql prompt

Mysql>

    Mysql>Show databases;

    To Show all the databases

    To create a new database

Mysql>Create databases sqoop-db;

    To connect to your database

Mysql>use sqoop- db;

    To see the tables in your database

Mysql>show tables;

    To create a new table in your database

Mysql> create table emp(empid int, ename varchar(100),esal double);

    To insert the values into the table

Mysql> Insert into emp values(100.’Gopal’,12000);

    1 row is created in your emp table

    Repeat the insert command to insert the records

    To see the data in your table of your database

Mysql> select* from emp;

    To assign or grant the privileges to your table

Mysql>grant all privileges on emp to’%’@ ’local host’;

    For Authorized users

Mysql>grant all privileges on emp to’ ’@ ’local host’;

    For other users

i.e.,  to provide the access to the database

Importing data into emp table using the sqoop import command:-

Open the Terminal

         Login as root

         root@ local host # mk dir sqoop dir

         To change the dir no sqoop dir

Home/ sqoop dir# sqoop import – connect jdbc: Mysql://local host

/sqoop-db—table emp;

           To connect to the sqoop, first, connect to the database, then import the emp table data into hdfs

            To check whether emp table has came into hdfs

Home/ sqoop dir# hadoop fs –ls/user/root

             To check the data of the emp table in hdfs

Home/ sqoop dir# hadoop fs –ls/user/root/emp

              Now, we get the details of the emp table.

                             To see the emp base

Home/ sqoop dir# hadoop fs –cat/user/root/emp/part-m-00000;

                           By default, sqoop will give 4 mapper are

Part- m -00000

Part- m -00001

Part- m -00002

Part- m -00003

We can restrict no. of mapper when ever we want

Using specified numbers of mapper

#sqoop import – connect jdbc: Mysql://local host/sqoop-db

--table emp –m 1;

Specifying the mapper.

Now, the output B has only one file part-m-00000

Importing the table to the specified forget directors

Sqoop import – connect jdbc:Mysql:// local host/sqoop-db

--table emp – m1 – target –dir’/sqoop new dir’

              Default/user/root

          To check the table

#hadoop fs –ls /sqoop new dir

          To check the data in the table

# hadoop fs-cat/sqoop new dir/part-m-00000

Using delimiters while importing the table in a specific format

Command B

/home/sqoop dir# sqoop import – connect jdbc: Mysql://local host

/sqoop-db – table emp – m1 – target-dir’/emp1’

--fields-terminated by’1’;

       To check the table in hdfs

# hadoop fs –ls /user/root

      To check the data the emp table

# hadoop fs – ls /user/root/ emp1

      We get one output file i.e part –m-00000

      To see the data in that file

# hadoop fs –ls /user/root/emp1/part-m-00000

To get particular columns

/home/sqoop dir #sqoop import – connect jdbc: Mysql://local host

/sqoop-db—table emp-m – target-dir’emp4’

 To see all the data

      /home/sqoop dir #sqoop list – database  – connect jdbc: Mysql://local host;

To see all the tables in a specific database

#sqoop import – connect jdbc: Mysql://local host/sqoop –db

To select the records from the table importing

#sqoop import – connect jdbc : Mysql ://local host /sqoop_db

-query select*from exmp – target dir

‘/query1’-m 1;

To import the records using the where conditions

#sqoop import –connect jdbc: my sqo://local host/sqoop-db

--query ”Select empid, ename from emp where ename

Live ‘r%’and \$condition” – target-dir’emp6’-m1;

Examples using an Eval function in sqoop:-

Difference between Eval and import is that 

• eval just displays the data 
• import means that it imports the data into HDFS

To select the records from the table using eval function

#sqoop eval – connection jdbc: Mysql://local host/sqoop-db

--query “select*from emp”;

To get the first 4 records

#sqoop eval – connect jdbc: Mysql://local host /sqoop-db

Query “select * from Emp”;

 To get the first 4 records

#sqoop eval – connect jdbc:Mysql://localhost /sqoop_db

--query ”select*from employee limit 4”;

To select the records using condition

#sqoop eval – connect jdbc: Mysql://local host/sqoop-db

--query ”select*from employee where ename like” ‘r%’;

To insert the records

#sqoop eval – connect jdbc: Mysql://local host/sqoop-db

--Query ”insert into emp values(999,’Raju’,13000)”;

To update the records

#sqoop eval -- connect jdbc: Mysql://local host/sqoop-db

--query ”update emp set ename=’Gopal’ where empid=999”;

 To create the table

#sqoop eval -- connect jdbc: Mysql://local host/sqoop-db

--query ”create table student(student id int primary key, stud name varchar, marks int)”;

Note:-For above commands, we have to see the output as below

root@ object # Mysql -4 root

Mysql> select * from emp;

Mysql> select * from student;

To generate a code we must know what is internally going on for the commands that we have given.

#sqoop codegen  --connect jdbc: Mysql://local host/

Sqoop-bd – table emp;

Performing the joins between two tables

To create a foreign key between two tables in Mysql

Log in to

Mysql root@ object:~$ Mysql -4 root

Mysql>show data bases;

       show all the data bases

Mysql>create  data base joindb;[creating a new db]

       Data bases createD

       Mysql>use join db;

Data base changed

       Mysql>show tables;

Empty set.

       Mysql>show tables;

Empty set.

       Mysql>create table e mptab(empid int primary key, ename varcher(or) esal double);

       Mysql>desc e mptab;

It all gives the description of the table,

   Create another table as Dept to join with empid as foreign key

Mysql>create table dept tab(deptid int primary key,

dept name varchar(50),deptloc varchar(50),

Empid int, FORE IAN KEY(empid)REFERENCES

      To see the description of the dept tab table

Mysql>desc dep tab;

       Insert the values into emp tab

Mysql>insert into emp tab value(100,’Raja’,15000);

Repeat the command inserting new records

Mysql>insert into emp tab value(100,’Raj’,16000);

To check the data for emp tab table

Mysql>select*from emp tab;

   Insert the values into dept tab

Mysql>insert into dept tab(200,’finame’,’Hyd’100);

    Report the command for inserting the values

Mysql>insert into dept tab(201,’admin’,’pune’101);

   To check the data in the dept tab table

Mysql>select*from dept tab;

Importing the data from two tables using join.

root@ object # sqoop import—connect jdbc: Mysql://local host

/join db – query ”select e. empid, e name, esal, deptid, dept name, deptloc from emptab e JOIN dept tab d ON(e. empid=d. empid)where\$CONDITIONS”

-m 1—target-dir/Test Join;

We get the common records from emp tab and dept tab tables.

         To check the records

root@ object # hadoop fs-cat/test join/part-m-00000

       Records will be displayed

Exporting the data

#sqoop export—connect jdbc: Mysql://db.example.com/foo

--table emp export—export-dir/gopal/ export data

Exported records

Note:-

• If the exported file from hdfs is other than a default file format (‘,’separated) then we have to use –fields-terminated by’1’àpipe

‘’àspace

‘\f’àtab

• In the same exported directory from HDFS , if we have multiple files with multiple schemas , then we have to explicitly mention the file name which we have intended for the export.

 

---