Sqoop for database imports in Hadoop - Mini Project: Build & Apply

Importing Data from a Database using Sqoop

📖 Scenario: You work as a data analyst in a company that stores customer data in a MySQL database. You want to analyze this data using Hadoop tools. To do this, you need to import the customer data from the MySQL database into Hadoop's HDFS using Sqoop.

🎯 Goal: Learn how to use Sqoop commands to import data from a MySQL database table into HDFS step-by-step.

📋 What You'll Learn

Use Sqoop to connect to a MySQL database

Import a specific table called customers from the database

Specify the target directory in HDFS for the imported data

Use a condition to import only customers from a specific country

Display the imported data files in HDFS

💡 Why This Matters

🌍 Real World

Companies often store data in relational databases but want to analyze it using big data tools like Hadoop. Sqoop helps move data easily from databases to Hadoop.

💼 Career

Data engineers and analysts use Sqoop to import data for analysis, reporting, and building data pipelines.

1

Set up the database connection details

Create variables for the database connection details: db_url with value jdbc:mysql://localhost:3306/companydb, db_user with value root, and db_password with value password123.

Hadoop

# Your code here

Hint

Use three variables named exactly db_url, db_user, and db_password with the given string values.

2

Set the target HDFS directory and table name

Create variables table_name with value customers and target_dir with value /user/hadoop/customers_data.

Hadoop

db_url = 'jdbc:mysql://localhost:3306/companydb'
db_user = 'root'
db_password = 'password123'
# Your code here

Hint

Define table_name and target_dir variables with the exact values.

3

Write the Sqoop import command with a condition

Create a variable sqoop_command that stores the full Sqoop import command as a string. The command should use db_url, db_user, db_password, table_name, and target_dir. Add a condition to import only customers where country = 'USA'. Use the format: sqoop import --connect {db_url} --username {db_user} --password {db_password} --table {table_name} --where "country='USA'" --target-dir {target_dir} --delete-target-dir.

Hadoop

db_url = 'jdbc:mysql://localhost:3306/companydb'
db_user = 'root'
db_password = 'password123'
table_name = 'customers'
target_dir = '/user/hadoop/customers_data'
# Your code here

Hint

Use an f-string to build the Sqoop command with the exact options and condition.

4

Print the Sqoop command to run

Write a print statement to display the sqoop_command variable.

Hadoop

db_url = 'jdbc:mysql://localhost:3306/companydb'
db_user = 'root'
db_password = 'password123'
table_name = 'customers'
target_dir = '/user/hadoop/customers_data'
sqoop_command = f"sqoop import --connect {db_url} --username {db_user} --password {db_password} --table {table_name} --where \"country='USA'\" --target-dir {target_dir} --delete-target-dir"
# Your code here

Hint

Use print(sqoop_command) to show the full command.

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