SQOOP
Commands
Sqoop Help
Sqoop Help Import
Sqoop Help Export
Open another CLI window and enter Sql Mode,
In the mysql shell:
show databases;
create database sqoop_class;
show databases;
Now, create table in database sqoop_class, there are two ways. First enter into
sqoop_class and then create table
OR use following command,
create table sqoop_class.test1 (name varchar (200), age varchar(200), province
varchar(200));
Now, insert data into the created table,
Insert into sqoop_class.test1 (name,age,province) values (‘Nabeel’, ‘23’,
‘Punjab’);
Insert into sqoop_class.test1 (name,age,province) values (‘Bilal’, ‘24’, ‘Punjab’);
Insert into sqoop_class.test1 (name,age,province) values (‘Muneeb’, ‘24’,
‘Punjab’);
Insert into sqoop_class.test1 (name,age,province) values (‘Fawad’, ‘25’, ‘KPK’);
Insert into sqoop_class.test1 (name,age,province) values (‘Ahmed’, ‘28’,
‘Sindh’);
Insert into sqoop_class.test1 (name,age,province) values (‘Irfan’, ‘24’, ‘Sindh’);
Insert into sqoop_class.test1 (name,age,province) values (‘Farhan’, ‘24’, ‘Sindh’);
To check if the data is properly inserted into the created table,
Next Portion
There are a few things to note before moving on to the next portion. Sqoop works with
connectors and
drivers. For this we use
--connect
--driver
As of right now you are on your local machine so these commands will look something as
follows:
--connect jdbc:mysql://localhost/sqoop_test
--driver com.mysql.jdbc.Driver
The –connect command will connect you to the sqoop_test database.
Now move to the first shell and on the root directory enter the following command:
sqoop import --connect jdbc:mysql://localhost/sqoop_class --driver com.mysql.jdbc.Driver --
username root --m 1 --columns name,age,province --table test1 --target-dir
/sqoop/sqoop_hive1 --fields-terminated-by ","
Now, to check if mysql table is wrote in HDFS or not.
Now the data is there but you want to view it in a presentable format.
Now go to give and run the following code on hive terminal or hive query editor worksheet:
create table sqoop_test1 (name varchar(200), age varchar(200), province varchar(200)) row
format delimited fields terminated by ',' location '/sqoop/sqoop_hive1/';
Import Subset of Table Data:
We can import a subset of a table using the ‘where’ clause in Sqoop import tool. It executes the
corresponding SQL query in the respective database server and stores the result in a target directory in
HDFS by using where clause.
sqoop import --connect jdbc:mysql://localhost/sqoop_class --driver com.mysql.jdbc.Driver --
username root --m 1 --columns name,age,province --table test1 --where "province='Punjab'" -
-target-dir /sqoop/sqoop_hive2 --fields-terminated-by ",";
We can also use AND, OR & NOT operator in where clause.
sqoop import --connect jdbc:mysql://localhost/sqoop_class --driver com.mysql.jdbc.Driver
--username root --m 1 --columns name,age,province --table test1 --where "province='Punjab'
AND “age”=’23’" --target-dir /sqoop/sqoop_hive3/whereQuery/AND --fields-terminated-by
",";
Now create another table
create table sqoop_class.test2 (emp_id int(200), name varchar(200), dept
varchar(200));
Insert into sqoop_class.test2 (emp_id,name,dept) values (‘1’, ‘farhan’,
‘Computer’);
Insert into sqoop_class.test2 (emp_id,name,dept) values (‘2’, ‘mohsin’,
‘Software’);
Now, load this table in HDFS
sqoop import --connect jdbc:mysql://localhost/sqoop_class --driver com.mysql.jdbc.Driver --
username root --m 1 --columns emp_id,name,dept --table test2 --target-dir
/sqoop/sqoop_hive/incrementalImport --fields-terminated-by ","
Now add a new row to test2 table and use incremental import
approach.
Insert into test2 values(3, ‘saeed’, ‘Mechanical’)
sqoop import --connect jdbc:mysql://localhost/sqoop_class --driver com.mysql.jdbc.Driver --
username root --m 1 --columns emp_id,name,dept --table test2 --target-dir
/sqoop/sqoop_hive/incrementalImport --fields-terminated-by "," --incremental append --
check-column emp_id --last-value 2;
Import all tables:
If we want to import all table from a database(mysql) then we use sqoop import all table command.
Each table will be imported as a directory in HDFS lets see an example.
Suppose we want to import test1 and test2 (all the tables present in one
database) into HDFS
sqoop import-all-tables --connect jdbc:mysql://localhost/sqoop_class --driver
com.mysql.jdbc.Driver --username root --m 1
Note: at path /user/root same name of table should not already exist.
