Mindmajix

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

Capture 15 To get particular columns

/home/sqoop dir #sqoop import – connect jdbc: Mysql://local host
/sqoop-db—table emp-m – target-dir’emp4’

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

Capture 15 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;

Capture 15 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:-

Capture 15 Difference between Eval and import is that 

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

Capture 15 To select the records from the table using eval function

#sqoop eval – connection jdbc: Mysql://local host/sqoop-db
--query “select*from emp”;

Capture 15 To get the first 4 records

#sqoop eval – connect jdbc: Mysql://local host /sqoop-db
Query “select * from Emp”;

Capture 15 To get the first 4 records

#sqoop eval – connect jdbc:Mysql://localhost /sqoop_db
--query ”select*from employee limit 4”;

Capture 15 To select the records using condition

#sqoop eval – connect jdbc: Mysql://local host/sqoop-db
--query ”select*from employee where ename like” ‘r%’;

Capture 15 To insert the records

#sqoop eval – connect jdbc: Mysql://local host/sqoop-db
--Query ”insert into emp values(999,’Raju’,13000)”;

Capture 15 To update the records

#sqoop eval -- connect jdbc: Mysql://local host/sqoop-db
--query ”update emp set ename=’Gopal’ where empid=999”;

 Capture 15To 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;

Capture 15 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;

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

Capture 15 Exporting the data

#sqoop export—connect jdbc: Mysql://db.example.com/foo
--table emp export—export-dir/gopal/ export data

Capture 15 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.

 


 

0 Responses on Hadoop SQOUT Usage"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.