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

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 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 

1. eval just displays the data 
2. import means that it imports the data into HDFS

Frequently asked Mapreduce Interview Questions

• 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:-

1. 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

2. 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.

List of Big Data Courses: