Problem Scenario 3: You have been given MySQL DB with following details.

Problem Scenario 3: 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: Import Single table (Subset data} Note: Here the ‘ is the same you find on – key

sqoop import –connect jdbc:mysql://quickstart:3306/retail_db –username=retail_dba -password=cloudera -table=categories ~warehouse-dir= categories_subset –where ‘category_id’=22 –m 1

Step 2: Check the output partition

hdfs dfs -cat categoriessubset/categories/part-m-00000

Step 3: Change the selection criteria (Subset data)

sqoop import –connect jdbc:mysql://quickstart:3306/retail_db –username=retail_dba -password=cloudera -table=categories ~warehouse-dir= categories_subset_2 –where ’category_id’>22 -m 1

Step 4: Check the output partition

hdfs dfs -cat categories_subset_2/categories/part-m-00000

Step 5: Use between clause (Subset data)

sqoop import –connect jdbc:mysql://quickstart:3306/retail_db –username=retail_dba -password=cloudera -table=categories ~warehouse-dir=categories_subset_3 –where "’category_id’ between 1 and 22" –m 1

Step 6: Check the output partition

hdfs dfs -cat categories_subset_3/categories/part-m-00000

Step 7: Changing the delimiter during import.

sqoop import –connect jdbc:mysql://quickstart:3306/retail_db –username=retail dba -password=cloudera -table=categories -warehouse-dir=:categories_subset_6 –where "/’categoryjd /’ between 1 and 22" -fields-terminated-by=’|’ -m 1

Step 8: Check the.output partition

hdfs dfs -cat categories_subset_6/categories/part-m-00000

Step 9: Selecting subset columns

sqoop import –connect jdbc:mysql://quickstart:3306/retail_db –username=retail_dba -password=cloudera -table=categories –warehouse-dir=categories subset col -where "/’category id/’ between 1 and 22" -fields-terminated-by=T -columns=category name, category id –m 1

Step 10: Check the output partition

hdfs dfs -cat categories_subset_col/categories/part-m-00000

Step 11: Inserting record with null values (Using mysql} ALTER TABLE categories modify category_department_id int(11); INSERT INTO categories values ^NULL/TESTING’); select" from categories;

Step 12: Encode non string null column

sqoop import –connect jdbc:mysql://quickstart:3306/retail_db –username=retail dba -password=cloudera -table=categories –warehouse-dir=categortes_subset_17 -where ""category_id" between 1 and 61" -fields-terminated-by=, |’ –null-string-N’ -null-non-string=, N’ –m 1

Step 13: View the content

hdfs dfs -cat categories_subset_17/categories/part-m-00000

Step 14: Import all the tables from a schema (This step will take little time)

sqoop import-all-tables -connect jdbc:mysql://quickstart:3306/retail_db –username=retail_dba -password=cloudera -warehouse-dir=categories_si

Step 15: View the contents

hdfs dfs -Is categories_subset_all_tables

Step 16: Cleanup or back to originals.

delete from categories where categoryid in (59, 60);

ALTER TABLE categories modify category_department_id int(11) NOTNULL;

ALTER TABLE categories modify category_name varchar(45) NOT NULL;

desc categories;