Problem Scenario 1:

Problem Scenario 1:

You have been given MySQL DB with following details.

user=retail_dba

password=cloudera

database=retail_db

table=retail_db.categories

jdbc URL = jdbc:mysql://quickstart:3306/retail_db

Please accomplish following activities.

View Answer

Answer: Solution:

Step 1: Connecting to existing MySQL Database mysql –user=retail_dba –password=cloudera retail_db

Step 2: Show all the available tables show tables;

Step 3: View/Count data from a table in MySQL select count(1} from categories;

Step 4: Check the currently available data in HDFS directory hdfs dfs -Is

Step 5: Import Single table (Without specifying directory).

sqoop import –connect jdbc:mysql://quickstart:3306/retail_db -username=retail_dba -password=cloudera -table=categories

Note: Please check you dont have space between before or after ‘=’ sign. Sqoop uses the MapReduce framework to copy data from RDBMS to hdfs

Step 6: Read the data from one of the partition, created using above command, hdfs dfs -catxategories/part-m-00000

Step 7: Specifying target directory in import command (We are using number of mappers =1, you can change accordingly) sqoop import -connect jdbc:mysql://quickstart:3306/retail_db -username=retail_dba -password=cloudera ~table=categories -target-dir=categortes_target –m 1

Step 8: Check the content in one of the partition file.

hdfs dfs -cat categories_target/part-m-00000

Step 9: Specifying parent directory so that you can copy more than one table in a specified target directory. Command to specify warehouse directory.

sqoop import -.-connect jdbc:mysql://quickstart:3306/retail_db –username=retail dba -password=cloudera -table=categories -warehouse-dir=categories_warehouse –m 1