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.
Subscribe to our youtube channel to get new updates..!
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.