Sqoop for database imports in Hadoop - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this Sqoop import command?

Consider the following Sqoop command to import data from a MySQL database into HDFS:

sqoop import \
--connect jdbc:mysql://localhost/employees \
--username user \
--password pass \
--table employees \
--target-dir /user/hadoop/employees_data \
--num-mappers 1

What will be the result of running this command?

A Data from the 'employees' table is imported into HDFS directory '/user/hadoop/employees_data' as SequenceFiles using one mapper.

B Data from the 'employees' table is imported into HDFS directory '/user/hadoop/employees_data' as Avro files using multiple mappers.

C Data from the 'employees' table is imported into HDFS directory '/user/hadoop/employees_data' as text files using one mapper.

D The command will fail because the '--num-mappers' option cannot be set to 1.

💡 Hint

Think about the default file format and the effect of '--num-mappers 1'.

The command imports the 'employees' table data into the specified HDFS directory as text files. The '--num-mappers 1' option means only one mapper is used, so the import is done in a single task. The default file format is text unless specified otherwise.

🧠 Conceptual

intermediate

Which option controls the number of parallel tasks in Sqoop import?

In Sqoop, you want to speed up data import by running multiple parallel tasks. Which command-line option controls how many parallel mappers Sqoop uses during import?

A --num-mappers

B --parallel-tasks

C --mapper-count

D --task-parallelism

💡 Hint

Look for the option that specifies the number of mappers.

The '--num-mappers' option sets how many parallel map tasks Sqoop will use to import data. The other options do not exist in Sqoop.

🔧 Debug

advanced

Why does this Sqoop import command fail with a connection error?

Given this command:

sqoop import \
--connect jdbc:mysql://localhost:3306/employees \
--username user \
--password pass \
--table employees \
--target-dir /user/hadoop/employees_data

The command fails with a connection refused error. What is the most likely cause?

A The username or password is incorrect.

B The MySQL server is not running or not reachable at localhost:3306.

C The '--table' option is misspelled.

D The '--target-dir' path is invalid in HDFS.

💡 Hint

Connection refused usually means the server is unreachable.

A connection refused error means Sqoop cannot reach the MySQL server at the given host and port. This usually means the server is down or blocked by firewall. Other options would cause different errors.

❓ data_output

advanced

What is the number of files created after this Sqoop import?

Run this Sqoop import command:

sqoop import \
--connect jdbc:mysql://localhost/employees \
--username user \
--password pass \
--table employees \
--target-dir /user/hadoop/employees_data \
--num-mappers 4

Assuming the import succeeds, how many part files will be created in the target directory?

A 5 part files, one extra for metadata

B 1 part file, because files are merged

C 0 files, because data is stored in a database

D 4 part files, one per mapper

💡 Hint

Each mapper writes one output file.

Sqoop uses one mapper per parallel task, and each mapper writes one part file in the target directory. So with 4 mappers, 4 part files are created.

🚀 Application

expert

How to import only rows matching a condition using Sqoop?

You want to import only employees with salary greater than 50000 from the 'employees' table using Sqoop. Which option should you use to filter rows during import?

A --where "salary > 50000"

B --condition salary > 50000

C --filter salary > 50000

D --query "SELECT * FROM employees WHERE salary > 50000"

💡 Hint

Look for the option that allows SQL WHERE clause filtering.

The '--where' option lets you specify a SQL WHERE clause to filter rows during import. The '--query' option requires a full query with a placeholder for splitting, so it is more complex to use.

Practice

1. What is the main purpose of Sqoop in Hadoop?

easy

A. To process data inside Hadoop using MapReduce

B. To export data from Hadoop to relational databases

C. To import data from relational databases into Hadoop

D. To visualize data stored in Hadoop

Solution

1. Step 1: Understand Sqoop's role

   Sqoop is designed to transfer data between relational databases and Hadoop.

2. Step 2: Identify import function

   Its main use is importing data from databases into Hadoop's storage.

3. Final Answer:

   To import data from relational databases into Hadoop -> Option C

4. Quick Check:

   Sqoop imports data = A [OK]

Hint: Remember Sqoop imports data into Hadoop from databases [OK]

Common Mistakes:

• Confusing import with export
• Thinking Sqoop processes data inside Hadoop
• Assuming Sqoop is for visualization

2. Which of the following is the correct basic Sqoop import command syntax?

easy

A. sqoop export --connect jdbc:mysql://localhost/db --table mytable --target-dir /user/hadoop/mytable

B. sqoop import --connect jdbc:mysql://localhost/db --table mytable --target-dir /user/hadoop/mytable

C. sqoop import --table mytable --connect jdbc:mysql://localhost/db --output-dir /user/hadoop/mytable

D. sqoop import --connect jdbc:mysql://localhost/db --table mytable --output-dir /user/hadoop/mytable

Solution

1. Step 1: Identify correct command type

   Import commands use 'sqoop import' with connection, table, and target directory.

2. Step 2: Check option correctness

   sqoop import --connect jdbc:mysql://localhost/db --table mytable --target-dir /user/hadoop/mytable uses correct flags: --connect, --table, --target-dir.

3. Final Answer:

   sqoop import --connect jdbc:mysql://localhost/db --table mytable --target-dir /user/hadoop/mytable -> Option B

4. Quick Check:

   Correct import syntax = A [OK]

Hint: Use --target-dir for import destination, not --output-dir [OK]

Common Mistakes:

• Using 'export' instead of 'import'
• Mixing --output-dir with import command
• Incorrect order of parameters

3. What will be the effect of running this command?

sqoop import --connect jdbc:mysql://localhost/db --table employees --split-by emp_id --num-mappers 4 --target-dir /user/hadoop/employees

medium

A. Imports employees table using 1 mapper ignoring split-by

B. Fails because --split-by cannot be used with --num-mappers

C. Exports employees table to MySQL using 4 mappers

D. Imports employees table using 4 parallel mappers splitting by emp_id

Solution

1. Step 1: Analyze command options

   The command imports data from 'employees' table using 4 mappers and splits data by 'emp_id'.

2. Step 2: Understand parallel import

   Using --split-by with --num-mappers enables parallel import for faster data transfer.

3. Final Answer:

   Imports employees table using 4 parallel mappers splitting by emp_id -> Option D

4. Quick Check:

   --split-by + --num-mappers = parallel import [OK]

Hint: Use --split-by column to enable parallel import with multiple mappers [OK]

Common Mistakes:

• Thinking --split-by disables parallelism
• Confusing import with export
• Assuming default 1 mapper always used

4. You run this Sqoop import command but get an error:

sqoop import --connect jdbc:mysql://localhost/db --table sales --num-mappers 4 --target-dir /user/hadoop/sales

What is the most likely cause?

medium

A. Missing --split-by column for parallel import

B. Incorrect --target-dir path format

C. Using --num-mappers with import is invalid

D. Table 'sales' does not exist in database

Solution

1. Step 1: Check command parameters

   The command uses --num-mappers 4 but does not specify --split-by column.

2. Step 2: Understand parallel import requirements

   Sqoop requires a --split-by column to divide data for multiple mappers; missing it causes error.

3. Final Answer:

   Missing --split-by column for parallel import -> Option A

4. Quick Check:

   Parallel import needs --split-by column [OK]

Hint: Always specify --split-by when using multiple mappers [OK]

Common Mistakes:

• Assuming --num-mappers works without --split-by
• Thinking --target-dir format causes error
• Ignoring table existence check

5. You want to import a large 'orders' table from MySQL into Hadoop quickly. The table has a numeric primary key 'order_id'. Which Sqoop command will best speed up the import?

hard

A. sqoop import --connect jdbc:mysql://localhost/db --table orders --split-by order_id --num-mappers 8 --target-dir /user/hadoop/orders

B. sqoop import --connect jdbc:mysql://localhost/db --table orders --split-by order_date --num-mappers 8 --target-dir /user/hadoop/orders

C. sqoop import --connect jdbc:mysql://localhost/db --table orders --num-mappers 8 --target-dir /user/hadoop/orders

D. sqoop import --connect jdbc:mysql://localhost/db --table orders --target-dir /user/hadoop/orders

Solution

1. Step 1: Identify best split column

   Use the numeric primary key 'order_id' for --split-by to evenly divide data.

2. Step 2: Use multiple mappers for parallel import

   Setting --num-mappers 8 enables 8 parallel tasks to speed up import.

3. Step 3: Evaluate other options

   sqoop import --connect jdbc:mysql://localhost/db --table orders --target-dir /user/hadoop/orders uses single mapper (slow). sqoop import --connect jdbc:mysql://localhost/db --table orders --num-mappers 8 --target-dir /user/hadoop/orders misses --split-by (error). sqoop import --connect jdbc:mysql://localhost/db --table orders --split-by order_date --num-mappers 8 --target-dir /user/hadoop/orders splits by 'order_date' which may not be numeric or evenly distributed.

4. Final Answer:

   sqoop import --connect jdbc:mysql://localhost/db --table orders --split-by order_id --num-mappers 8 --target-dir /user/hadoop/orders -> Option A

5. Quick Check:

   Use numeric PK with --split-by and multiple mappers [OK]

Hint: Split by numeric primary key and increase mappers for speed [OK]

Common Mistakes:

• Not specifying --split-by with multiple mappers
• Choosing non-numeric or uneven split column
• Using single mapper for large tables