Cloudera CCA175 CCA Spark and Hadoop Developer Exam Online Training

Question #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.

Reveal Solution Hide Solution

Question #2

Problem Scenario 2:

There is a parent organization called "ABC Group Inc", which has two child companies named Tech Inc and MPTech.

Both companies employee information is given in two separate text file as below. Please do the following activity for employee details.

Tech Inc.txt

Reveal Solution Hide Solution

Question #3

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.

Reveal Solution Hide Solution

Correct 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;

Question #4

Problem Scenario 4: 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.

Import Single table categories (Subset data} to hive managed table, where category_id between 1 and 22

Reveal Solution Hide Solution

Question #4

Problem Scenario 4: 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.

Import Single table categories (Subset data} to hive managed table, where category_id between 1 and 22

Reveal Solution Hide Solution

Question #4

Problem Scenario 4: 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.

Import Single table categories (Subset data} to hive managed table, where category_id between 1 and 22

Reveal Solution Hide Solution

Question #4

Problem Scenario 4: 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.

Import Single table categories (Subset data} to hive managed table, where category_id between 1 and 22

Reveal Solution Hide Solution

Question #4

Problem Scenario 4: 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.

Import Single table categories (Subset data} to hive managed table, where category_id between 1 and 22

Reveal Solution Hide Solution