// Start MySQL
$ sudo mysql -uroot -pcloudera
mysql> create database ohm;
Query OK, 1 row affected (0.00 sec)
mysql> use ohm;
Database changed
CREATE TABLE employee
(
id INT,
first_name VARCHAR(100),
last_name VARCHAR(100),
gender VARCHAR(10),
designation VARCHAR(20),
city VARCHAR(20),
country VARCHAR(20)
);
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (1, 'Jervis', 'Roll', 'Male', 'Director of Sales', 'Thi Tran Lac', 'Vietnam');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (2, 'Gordon', 'Maltster', 'Male', 'Marketing Manager', 'Mabu', 'China');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (3, 'Griff', 'Godsafe', 'Male', 'Actuary', 'Kipit', 'Philippines');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (4, 'Gracie', 'Franken', 'Female', 'Assistant Manager', 'Xiabuji', 'China');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (5, 'Joelly', 'Wellbank', 'Female', 'Account Coordinator', 'Whitehorse', 'Canada');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (6, 'Bab', 'Havock', 'Female', 'Accountant II', 'Basyūn', 'Egypt');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (7, 'Carmine', 'Courage', 'Female', 'Account Coordinator', 'Boyeros', 'Cuba');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (8, 'Estella', 'Marvell', 'Female', 'Structural Analysis Engineer', 'Stettler', 'Canada');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (9, 'Celie', 'Trevaskiss', 'Female', 'Assistant Manager', 'Criuleni', 'Moldova');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (10, 'Madison', 'Ranyell', 'Male', 'Research Associate', 'Angatel', 'Philippines');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (11, 'Haydon', 'Faughey', 'Male', 'Safety Technician IV', 'Masalovka', 'Russia');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (12, 'Michele', 'Zarfai', 'Male', 'Legal Assistant', 'Karatau', 'Kazakhstan');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (13, 'Ruthi', 'Bowmer', 'Female', 'Analog Circuit Design manager', 'Peski', 'Russia');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (14, 'Adolphus', 'Pickthorne', 'Male', 'Senior Developer', 'Mae Fa Luang', 'Thailand');
INSERT INTO employee (id, first_name, last_name, gender, designation, city, country) VALUES (15, 'Kat', 'Dymocke', 'Female', 'Geological Engineer', 'Markópoulo Oropoú', 'Greece');
// Display databases;
$ sqoop list-databases -connect jdbc:mysql://localhost -username root -password cloudera;
information_schema
cm
firehose
hue
metastore
mysql
nav
navms
ohm
oozie
retail_db
rman
// Display tables
$ sqoop list-tables -connect jdbc:mysql://localhost/ohm -username root -password cloudera;
employee
person
// Create a table in Hive
hive> create database ohm;
OK
Time taken: 2.041 seconds
hive> show tables;
OK
Time taken: 0.244 seconds
// Import a table from Mysql To Hive
sqoop import \
-connect jdbc:mysql://localhost:3306/ohm \
-driver com.mysql.jdbc.Driver \
-username root \
-password cloudera \
-table employee \
-hive-import \
-split-by id \
-hive-table ohm.employee
hive> use ohm;
OK
Time taken: 0.017 seconds
hive> show tables;
OK
employee
Time taken: 0.029 seconds, Fetched: 1 row(s)
hive> select * from employee;
OK
1 Jervis Roll Male Director of Sales Thi Tran Lac Vietnam
2 Gordon Maltster Male Marketing Manager Mabu China
3 Griff Godsafe Male Actuary Kipit Philippines
4 Gracie Franken Female Assistant Manager Xiabuji China
5 Joelly Wellbank Female Account Coordinator Whitehorse Canada
6 Bab Havock Female Accountant II Basyūn Egypt
10 Madison Ranyell Male Research Associate Angatel Philippines
11 Haydon Faughey Male Safety Technician IV Masalovka Russia
12 Michele Zarfai Male Legal Assistant Karatau Kazakhstan
13 Ruthi Bowmer Female Analog Circuit Desig Peski Russia
14 Adolphus Pickthorne Male Senior Developer Mae Fa Luang Thailand
15 Kat Dymocke Female Geological Engineer Markópoulo Oropoú Greece
Time taken: 0.92 seconds, Fetched: 12 row(s)
// Verify the record count in MySQL, HDFS and Hive
mysql> use ohm;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select count(1) from employee;
+----------+
| count(1) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
// do record count from all the files in warehouse
hdfs dfs -cat /user/hive/warehouse/ohm.db/employee/* | wc -l
12
// do record count from the table
hive> select count(1) from employee;
OK
12
