Hadoop Sqout Usage

• (4.0)

• | 1246 Ratings

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 

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: