Problem Scenario 5 [SQOOP]

CCA 175 Hadoop and Spark Developer Exam Preparation - Problem Scenario 5

PLEASE READ THE INTRODUCTION TO THIS SERIES. CLICK ON HOME LINK AND READ THE INTRO BEFORE ATTEMPTING TO SOLVE THE PROBLEMS

Video walkthrough of this problem is available at [PART 1 CLICK HERE] AND [PART 2 CLICK HERE]

Click here for the video version of this series. This takes you to the youtube playlist of videos. 

Sqoop is one of the important topics for the exam. Based on generally reported exam pattern from anonymous internet bloggers, you can expect 2 out of 10 questions on this topic related to Data Ingest and Data Export using Sqoop. Hence, 20% of the exam score can be obtained just by practicing simple Sqoop concepts. Sqoop can be mastered easily (i.e in a few hours) at the skill level that CCA 175 exam is expecting you to demonstrate. I created this problem focusing on Sqoop alone, if you are able to perform this exercise on your own or at worst using just the sqoop user guide then there is a very very high chance that you can score the 20% easily.

Pre-Work: Please perform these steps before solving the problem
1. Login to MySQL using below commands on a fresh terminal window
    mysql -u retail_dba -p
    Password = cloudera
2. Create a replica product table and name it products_replica
    create table products_replica as select * from products
3. Add primary key to the newly created table
    alter table products_replica add primary key (product_id);
4. Add two more columns
    alter table products_replica add column (product_grade int, product_sentiment varchar(100))
5. Run below two update statements to modify the data
    update products_replica set product_grade = 1  where product_price > 500;
    update products_replica set product_sentiment  = 'WEAK'  where product_price between 300 and  500;
    

Problem 5: Above steps are important so please complete them successfully before attempting to solve the problem

1. Using sqoop, import products_replica table from MYSQL into hdfs such that fields are separated by a '|' and lines are separated by '\n'. Null values are represented as -1 for numbers and "NOT-AVAILABLE" for strings. Only records with product id greater than or equal to 1 and less than or equal to 1000 should be imported and use 3 mappers for importing. The destination file should be stored as a text file to directory  /user/cloudera/problem5/products-text. 
2. Using sqoop, import products_replica table from MYSQL into hdfs such that fields are separated by a '*' and lines are separated by '\n'. Null values are represented as -1000 for numbers and "NA" for strings. Only records with product id less than or equal to 1111 should be imported and use 2 mappers for importing. The destination file should be stored as a text file to directory  /user/cloudera/problem5/products-text-part1. 
3. Using sqoop, import products_replica table from MYSQL into hdfs such that fields are separated by a '*' and lines are separated by '\n'. Null values are represented as -1000 for numbers and "NA" for strings. Only records with product id greater than 1111 should be imported and use 5 mappers for importing. The destination file should be stored as a text file to directory  /user/cloudera/problem5/products-text-part2.
4. Using sqoop merge data available in /user/cloudera/problem5/products-text-part1 and /user/cloudera/problem5/products-text-part2 to produce a new set of files in /user/cloudera/problem5/products-text-both-parts
5. Using sqoop do the following. Read the entire steps before you create the sqoop job.
   • create a sqoop job Import Products_replica table as text file to directory /user/cloudera/problem5/products-incremental. Import all the records.
   • insert three more records to Products_replica from mysql
   • run the sqoop job again so that only newly added records can be pulled from mysql
   • insert 2 more records to Products_replica from mysql
   • run the sqoop job again so that only newly added records can be pulled from mysql
   • Validate to make sure the records have not be duplicated in HDFS
6. Using sqoop do the following. Read the entire steps before you create the sqoop job.
   • create a hive table in database named problem5 using below command 
   • create table products_hive  (product_id int, product_category_id int, product_name string, product_description string, product_price float, product_imaage string,product_grade int,  product_sentiment string);
   • create a sqoop job Import Products_replica table as hive table to database named problem5. name the table as products_hive. 
   • insert three more records to Products_replica from mysql
   • run the sqoop job again so that only newly added records can be pulled from mysql
   • insert 2 more records to Products_replica from mysql
   • run the sqoop job again so that only newly added records can be pulled from mysql
   • Validate to make sure the records have not been duplicated in Hive table
7. Using sqoop do the following. .
   • insert 2 more records into products_hive table using hive. 
   • create table in mysql using below command   
   • create table products_external  (product_id int(11) primary Key, product_grade int(11), product_category_id int(11), product_name varchar(100), product_description varchar(100), product_price float, product_impage varchar(500), product_sentiment varchar(100));
   • export data from products_hive (hive) table to (mysql) products_external table. 
   • insert 2 more records to Products_hive table from hive
   • export data from products_hive table to products_external table. 
   • Validate to make sure the records have not be duplicated in mysql table

Solution: 

Try your best to solve the above scenario without going through the solution below. If you could then use the solution to compare your result. If you could not then I strongly recommend that you go through the concepts again (this time in more depth). Each step below provides a solution to the points mentioned in the Problem Scenario. 

Step 1: 

sqoop import \
--connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
--username retail_dba \
--password cloudera \
--table products_replica \
--target-dir /user/cloudera/problem5/products-text \
--fields-terminated-by '|' \
--lines-terminated-by '\n' \
--null-non-string -1 \
--null-string "NOT-AVAILABLE" \
-m 3 \
--where "product_id between 1 and 1000" \
--outdir /home/cloudera/sqoop1 \
--boundary-query "select min(product_id), max(product_id) from products_replica where product_id between 1 and 1000";

Step 2: 

sqoop import \
--connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
--username retail_dba \
--password cloudera \
--table products_replica \
--target-dir  /user/cloudera/problem5/products-text-part1  \
--fields-terminated-by '*' \
--lines-terminated-by '\n' \
--null-non-string -1000 \
--null-string "NA" \
-m 2 \
--where "product_id <= 1111 " \
--outdir /home/cloudera/sqoop2 \
--boundary-query "select min(product_id), max(product_id) from products_replica where product_id <= 1111";

Step 3: 

sqoop import \
--connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
--username retail_dba \
--password cloudera \
--table products_replica \
--target-dir  /user/cloudera/problem5/products-text-part2  \
--fields-terminated-by '*' \
--lines-terminated-by '\n' \
--null-non-string -1000 \
--null-string "NA" \
-m 5 \
--where "product_id > 1111 " \
--outdir /home/cloudera/sqoop3 \
--boundary-query "select min(product_id), max(product_id) from products_replica where product_id > 1111"

Step 4: 

sqoop merge \
--class-name products_replica \
--jar-file mp/sqoop-cloudera/compile/66b4f23796be7625138f2171a7331cd3/products_replica.jar \
--new-data /user/cloudera/problem5/products-text-part2 \
--onto /user/cloudera/problem5/products-text-part1 \
--target-dir /user/cloudera/problem5/products-text-both-parts \
--merge-key product_id;

Step 5: 

On terminal -

sqoop job --create first_sqoop_job \
-- import \
--connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
--username "retail_dba" \
--password "cloudera" \
--table products_replica \
--target-dir /user/cloudera/problem5/products-incremental \
--check-column product_id \
--incremental append \
--last-value 0;

sqoop job --exec first_sqoop_job

On MySQL command line -

mysql> insert into products_replica values (1346,2,'something 1','something 2',300.00,'not avaialble',3,'STRONG');

mysql> insert into products_replica values (1347,5,'something 787','something 2',356.00,'not avaialble',3,'STRONG');

On terminal - 

sqoop job --exec first_sqoop_job

On MYSQL Command Line

insert into products_replica values (1376,4,'something 1376','something 2',1.00,'not avaialble',3,'WEAK');

insert into products_replica values (1365,4,'something 1376','something 2',10.00,'not avaialble',null,'NOT APPLICABLE');

On terminal - 

sqoop job --exec first_sqoop_job

Step 6: 

On Terminal window-

sqoop job \
--create hive_sqoop_job \
-- import \
--connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
--username "retail_dba" \
--password "cloudera" \
--table products_replica  \
--check-column product_id \
--incremental append \
--last-value 0 \
--hive-import \
--hive-table products_hive \
--hive-database problem5;

On Hive window:

create database problem5;

use problem5;

create table products_hive  (product_id int, product_category_id int, product_name string, product_description string, product_price float, product_imaage string,product_grade int,  product_sentiment string);

On Terminal window

sqoop job --exec hive_sqoop_job

On MySQL window

insert into products_replica values (1378,4,'something 1376','something 2',10.00,'not avaialble',null,'NOT APPLICABLE');

insert into products_replica values (1379,4,'something 1376','something 2',10.00,'not avaialble',null,'NOT APPLICABLE');

On Terminal Window

sqoop job --exec hive_sqoop_job

On Hive Window

select * from products_hive

Step 7: 

On Hive Window

use problem5;

insert into table products_hive values (1380,4,'something 1380','something 2',8.00,'not avaialble',3,'NOT APPLICABLE');

insert into table products_hive values (1381,4,'something 1380','something 2',8.00,'not avaialble',3,'NOT APPLICABLE');

On MYSQL window

create table products_external  (product_id int(11) primary Key, product_grade int(11), product_category_id int(11), product_name varchar(100), product_description varchar(100), product_price float, product_impage varchar(500), product_sentiment varchar(100));

On Terminal 

sqoop export \

--username "retail_dba" \

--password "cloudera" \

--connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \

 --export-dir /user/hive/warehouse/problem5.db/products_hive/ \

--input-fields-terminated-by '\001' \

--input-null-non-string "null" \

--input-null-string "null" \

--update-mode allowinsert \

--update-key product_id \

--columns "product_id,product_category_id,product_name,product_description,product_price,product_impage,product_grade,product_sentiment" --table products_external;

On Hive Window 

insert into table products_hive values (1382,4,'something 1380','something 2',8.00,'not avaialble',3,'NOT APPLICABLE');

insert into table products_hive values (1383,4,'something 1380','something 2',8.00,'not avaialble',3,'NOT APPLICABLE');

On Terminal Window:

sqoop export \
--username "retail_dba" \
--password "cloudera" \
--connect "jdbc:mysql://quickstart.cloudera:3306/retail_db" \
--export-dir /user/hive/warehouse/problem5.db/products_hive/ \
--input-fields-terminated-by '\001' \
--input-null-non-string "null" \
--input-null-string "null" \
--update-mode allowinsert \
--update-key product_id \
--columns "product_id,product_category_id,product_name,product_description,product_price,product_impage,product_grade,product_sentiment" --table products_external;

To Validate

On Hive

select count(*) from problem5.products_hive;

on MySQL

select count(*) from products_replica;