MapReduce and Sqoop in Big data Hadoop

In this lesson, we will discuss MapReduce and Sqoop

Welcome to the lesson ‘MapReduce and Sqoop’ of Big Data Hadoop tutorial which is a part of ‘big data and hadoop online training’ offered by OnlineITGuru.  This lesson will focus on MapReduce and Sqoop in the Hadoop Ecosystem.

Let’s look at the objectives of this lesson in the next section.

Objectives

After completing this lesson, you will be able to:

• Define MapReduce
• Explain the characteristics of MapReduce
• Understand Advanced MapReduce
• Define Sqoop and its uses
• Analyze importing and exporting data from Hadoop using Sqoop
• Understand the basics of Sqoop 2

Let us now look into what MapReduce is.

Why MapReduce?

Before 2004, huge amounts of data was stored on single servers. If any program ran a query for data stored on multiple servers, logical integration of search results and analysis of data was a nightmare.

Not to mention the massive efforts and expenses that were involved. The threat of data loss, the challenge of data backup, and reduced scalability resulted in the issue snowballing into a crisis of sorts.

To counter this, Google introduced MapReduce in December 2004, and the analysis of datasets was done in less than 10 minutes rather than 8 to 10 days.

Queries could run simultaneously on multiple servers and now logically integrate search results and analyze data in real-time. The USPs of MapReduce are its fault-tolerance and scalability.

In the next section, we will try to understand what MapReduce is.

What is MapReduce?

MapReduce is a programming model that simultaneously processes and analyzes huge datasets logically into separate clusters.

While Map sorts the data, Reduce segregates it into logical clusters, thus removing ’bad’ data and retaining the necessary information.

Let us understand MapReduce with a real-life example - Nutri.

MapReduce: Example

Nutri is a well-known courier facility. It transports documents across the globe.

When the staff receives a courier, they color code is based on the country to which it has to be sent.

The dispatch staff then segregate the courier by the tagged color code. Hence, the reception functions as “Map”, and the dispatch team as “Reduce.”

Let us now understand the MapReduce Analogy in the next section.

MapReduce: Analogy

The MapReduce steps listed in below diagram represent manual vote counting after an election as an analogy.

Step 1: Each poll booth’s ballot papers are counted by a teller. This is a pre-MapReduce step called “input splitting.”

Step 2: Tellers of all booths count the ballot papers in parallel. As multiple tellers are working on a single job, the job execution time will be faster. This is called the “Map method.”

Step 3: The ballot count of each booth under the assembly and parliament seat positions is found, and the total count for the candidates is generated. This is known as “Reduce method”.

Thus, map and reduce help to execute the job quicker than an individual counter. As you have seen in the previous section, Analogy of vote counting is an example to understand the use of MapReduce.

The key reason to perform mapping and reducing is to speed up the job execution of a specific process. This can be done by splitting a process into a number of tasks, thus enabling parallelism.

If one person counts all of the ballot papers and waits for others to finish the ballot count, it could take a month to receive the election results. When many people count the ballots simultaneously, the results are obtained in one or two days. This is how MapReduce works.

In the next section, the MapReduce operation is explained using a real-time problem.

How MapReduce can be used?

Given here is a real-time example of how MapReduce can be used.

The job is to perform a word count of the given paragraph.

The sentence is, "This quick brown fox jumps over a lazy dog. A dog is a man's best friend.”

The MapReduce process consists of phases:

• Inputting
• Splitting
• Mapping
• Shuffling
• Reducing  

Inputting

The Input phase refers to providing data for which the MapReduce process is to be performed. The sentence is used as the input here.

Splitting

The Splitting phase refers to converting a job submitted by the client into a number of tasks. In this example, the job is split into two tasks.

Mapping

The Mapping phase refers to generating a key-value pair for the input. Since this example is about counting words, the sentence is split into words by using the substring method to generate words from lines.

The Mapping phase will ensure that the words generated are converted into keys, and a default value of one is allotted to each key.

Shuffling

The Shuffling phase refers to sorting the data based on the keys. As shown in the section, the words are sorted in ascending order.

Reducing

In this phase, the data is reduced based on the repeated keys by incrementing the value. The word “dog”‚ and the letter “a” are repeated. Therefore, the reducer will delete the key and increase the value depending on the number of occurrences of the key.

This is how the MapReduce operation is performed.

In the next section, we will look at the Map Execution and its phases.

Map Execution Phases

Map execution consists of five phases:

• Map phase
• Partition phase
• Shuffle phase
• Sort phase
• Reduce phase

Map phase

In the map phase, the assigned input split is read from HDFS, where a split could be a file block by default. Furthermore, the input is parsed into records as key-value pairs.

The map function is applied to each record to return zero or more new records. These intermediate outputs are stored in the local file system as a file. They are sorted first by bucket number and then by a key.

At the end of the map phase, information is sent to the master node after its completion.

Partition phase

In the partition phase, each mapper must determine which reducer will receive each output. For any key, regardless of which mapper instance generated it, the destination partition is the same.

Note that the number of partitions will be equal to the number of reducers.

Shuffle phase

In the shuffle phase, input data is fetched from all map tasks for the portion corresponding to the reduce tasks bucket.

Sort phase

In the sort phase, a merge-sort of all map outputs occurs in a single run.

Reduce phase

In the reduce phase, a user-defined reduce function is applied to the merged run. The arguments are a key and the corresponding list of values. The output is written to a file in HDFS.

Map Execution: Distributed Two Node Environment

MapReduce execution in a distributed two-node environment is explained in the diagram given below.

The mappers on each of the nodes are assigned to each input split of blocks. Based on the input format, the Record Reader reads the split as a key-value pair.

The map function is applied to each record to return zero or more new records. These intermediate outputs are stored in the local file system as a file. Thereafter, a partitioner assigns the records to a reducer.

In the shuffling phase, the intermediate key-value pairs are exchanged by all nodes. The key-value pairs are then sorted by applying the key and reduce function. The output is stored in HDFS based on the specified output format.

MapReduce Essentials

The job input is specified in key-value pairs. Each job consists of two stages.

• Stage 1 - A user-defined map function is applied to each input record to produce a list of intermediate key-value pairs.
• Stage 2 - A user-defined reduce function is called once for each distinct key in the map output. Then the list of intermediate values associated with that key is passed.

The essentials of each MapReduce phase are:

• The number of reduce tasks can be defined by the users.
• Each reduce task is assigned a set of record groups which are intermediate records corresponding to a group of keys.
• For each group, a user-defined reduce function is applied to the recorded values.
• The reduce tasks read from every map task, and each read returns the record groups for that reduce task.

Note that the reduce phase cannot start until all mappers have finished processing.

MapReduce Jobs

A job is a full MapReduce program which typically causes multiple maps and reduces functions to be run in parallel over the life of the program. Many copies of the map and reduce functions are forked for parallel processing across the input dataset.

A task is a map or reduces function executed on a subset of data. With this understanding of “job”, and “task”, the ApplicationMaster and NodeManager functions become easy to comprehend.

Application Master and Node Manager functions are explained in the next section.

ApplicationMaster

• The Application Master is responsible for the execution of a single application or MapReduce job.
• It divides the job requests into tasks and assigns those tasks to Node Managers running on the slave node.

Node Manager

• The Node Manager has a number of dynamically created resource containers. The size of a container depends on the number of resources it contains, such as memory, CPU, disk, and network IO.
• It executes map and reduces tasks by launching these containers when instructed by the MapReduce Application Master.

Let us now look into MapReduce and its associated tasks.

MapReduce and Associated Tasks

Some of the associated tasks of MapReduce are as follows:

Map process

The Map process is an initial step to process individual input records in parallel.

Reduce process

The reduce process is all about summating the output with a defined goal as coded in business logic.

Node Manager

Node Manager keeps track of individual map tasks and can run in parallel. A map job runs as a part of container execution by Node Manager on a particular DataNode.

Application Master

The Application Master keeps track of a MapReduce job.

In the next section, we will discuss the Hadoop MapReduce job work interaction.

Hadoop MapReduce - Job Work Interaction

The flow diagram given below represents the Hadoop Job work interaction.

Initially, a Hadoop MapReduce job is submitted by a client in the form of an input file or a number of input split of files containing data.

MapReduce ApplicationMaster distributes the input split to separate NodeManagers. MapReduce ApplicationMaster coordinates with the NodeManagers.

MapReduce ApplicationMaster resubmits the task(s) to an alternate Node Manager if the DataNode fails.

ResourceManager gathers the final output and informs the client of the success or failure status.

Let’s look at some MapReduce characteristics in the next section.

Characteristics of MapReduce

Some of the Characteristics of MapReduce are as follows:

• MapReduce is designed to handle very large scale data in the range of petabytes and exabytes.
• It works well to write once and read many data, also known as WORM data.
• MapReduce allows parallelism without mutexes.
• The Map and Reduce operations are performed by the same processor.
• Operations are provisioned near the data as data locality is preferred.
• Commodity hardware and storage is leveraged in MapReduce.
• The runtime takes care of splitting and moving data for operations.

Let us look at the real-time uses of MapReduce in the next section.

Real-Time Uses of MapReduce

Some of the real-time uses of MapReduce are as follows:

• Simple algorithms such as grep, text-indexing, and reverse indexing
• Data-intensive computing such as sorting
• Data mining operations like Bayesian classification
• Search engine operations like keyword indexing, ad rendering, and page ranking
• Enterprise analytics
• Gaussian analysis for locating extra-terrestrial objects in astronomy
• Semantic web and web 3.0

Ensure that all Hadoop services are live and running. This can be verified in two steps:

1. Use the command “jps”
2. Look for all the services: NameNode, DataNode, NodeManager, Resource Manager.

In the next section of this tutorial, we will learn about the uploading of small data and big data.

Uploading Small Data and Big Data

Small Data consist of block sizes lesser than 256 MB.

The command to upload any Data (Big or Small) from the local system to HDFS is as below.

                                   Let’s look at the steps to build a MapReduce program in the next section.

Writing a MapReduce Program

The steps to build MapReduce Program are as below:

1. Determine the Data: Determine if the data can be made parallel and solved using MapReduce. For example, you need to analyze whether the data is Write Once Read Many, or WORM, in nature.
2. Design and implement a solution:Design and implement a solution as mapper and reducer classes.
3. Compile the source code: Compile the source code with Hadoop Core
4. Package the code: Package the code as jar executable.
5. Configure the application: Configure the application job as to the number of mapper and reducer tasks and to the number of input and output streams.
6. Load the data:Load the data, or use it on previously available data
7. Launch and monitor the job: Launch the program and ensure that everything is running smoothly.
8. Study the results: Once you get the results, analyze it and observe any potential trends or patterns.

Hadoop MapReduce Requirements

The user or developer is required to set up the framework with the following parameters:

• Locations of the job input in the distributed file system
• Locations of the job output in the distributed file system Input format
• Output format
• Class containing the map function
• Class containing the reduce function, which is optional.

If a job does not need a reduce function, there is no need to specify a reducer class. The framework will partition the input, schedule, and execute map tasks across the cluster.

If requested, it will sort the results of the map task, and it will execute the reduce tasks with the map output. The final output will be moved to the output directory, and the job status will be reported to the user.

Steps to Process MapReduce

The image given below shows the set of classes under the user supply and the framework supply.

User supply refers to the set of Java classes and methods provided to a Java developer for developing Hadoop MapReduce applications.

Framework supply refers to defining the workflow of a job which is followed by all Hadoop services.

As shown in the image, the user provides the input location and the input format as required by the program logic. Once the Resource Manager accepts the input, a specific job is divided into tasks by Application Master.

Each task is then assigned to an individual Node Manager. Once the assignment is complete, the Node Manager will start the map task. It performs shuffling, partitioning, and sorting for individual map outputs.

Once the sorting is complete, Reducer starts the merging process. This is also called the reduce task.

The final step is collecting the output, which is performed once all the individual tasks are reduced. This reduction is based on programming logic.

Let us look at the MapReduce Responsibilities in the next section.

MapReduce Responsibilities

The basic user or developer responsibilities of MapReduce are:

• Setting up the job
• Specifying the input location
• Ensuring the input is in the expected format and location.

The framework responsibilities of MapReduce are as follows:

• Distributing jobs among the Application Master and Node Manager nodes of the cluster
• Running the map operation
• Performing the shuffling and sorting operations
• Reducing phases
• Placing the output in the output directory
• Informing the user of the job completion status.

Installing Eclipse IDE

To install Eclipse in the Ubuntu Desktop 14.04, open Ubuntu Software Center. Type “eclipse” in the search bar as shown on the section.

Select Eclipse from the list. Next, click the Install button to continue.

Step 1: Create a New Project in Eclipse

Once Eclipse is installed in your system, create a new project and add the essential jar files to run MapReduce programs.

To create a new project, click the File menu. Select New Project. Alternatively, press Ctrl+N to start the wizard of the new project.

Step 2: Create a New Project in Eclipse

Select Java Project from the list. Then click the Next button to continue.

Step 3: Create a New Project in Eclipse

Type the project name as Word Count, and click the Next button to continue.

Step 4: Create a New Project in Eclipse

Include jar files from the Hadoop framework to ensure the programs locate the dependencies to one location.

Step 5: Create a New Project in Eclipse

Under the Libraries tab, click the Add External JARs button to add the essential jar files.

After adding the jar files, click the Finish button to create the project successfully.

Checking Hadoop Environment for MapReduce

It is important to check whether the machine setup can perform MapReduce operations. To verify this, use the example jar files deployed by Hadoop.

This can be done by running the command shown in the section.

Before executing this command, ensure that the words.txt file resides in the /data/first location.

MapReduce v 2.7 Architecture

The following image shows the MapReduce v 2.7 architecture comprising YARN.

Let us now look at the Advanced MapReduce.

Advanced MapReduce

Given below is the architecture for Advanced MapReduce.

Hadoop MapReduce uses data types to work with user-given mappers and reducers.

The data is read from files into mappers and emitted by mappers to reducers. Processed data is sent back by the reducers.

Data emitted by reducers goes into output files. At every step, data is stored in Java objects.

Let us now understand the Writable Data Types in Advance MapReduce.

Writable Data Types:

In the Hadoop environment, all the input and output objects across the network must obey the Writable interface, which allows Hadoop to read and write data in a serialized form for transmission.

Let’s look at Hadoop interfaces in some more detail.

Interfaces in Hadoop

The interfaces in Hadoop are

• Writable Interface
• Writablecomparable Interface

Writable Interface

As you’ve already seen, a Writable interface allows Hadoop to read and write data in a serialized form for transmission.

A Writable interface consists of two methods: read and write fields as shown below.

interface Writable {

public void readFields(DataInput in);

public void write(DataOutput out);

}

WritableComparable Interface

A WritableComparable interface extends the Writable interface so that the data can be used as a key and not as a value.

As shown below, the WritableComparable implements two methods: compareTo and hashCode.

int compareTo(Object what)

int hashCode()

Let us look at the various data types in Hadoop in the next section.

Data Types in Hadoop

Let’s now examine various data types in Hadoop and their functions.

Data types	Functions
Text	Stores String data
IntWritable	Stores Integer data
LongWritable	Stores Long data
FloatWritable	Stores Float data
DoubleWritable	Stores Double data
Stores	Double data
BytesWritable	Stores Byte data
NullWritable	Placeholder when the value is not needed

A sample data type related to the Writable interface is displayed here:

This data type will need you to implement a Writable interface. MapReduce can specify how its input is to be read by defining an InputFormat.

Input Formats in MapReduce

The following table lists some of the classes of Input Formats provided by the Hadoop framework.

InputFormat classes	Description
KeyValueTextInputFormat	One key-value pair per line
TextInputFormat	Key is the line number, and value is the line
NLineInputFormat	This is similar to TextInputFormat, but the difference is that there are N number of lines that make an input split
MultiFileInputFormat	Input format aggregates multiple files into one split
SequenceFileInputFormat	The input file is a Hadoop sequence file that contains a serialized key-value pair

Let’s look at each of them.

• The first class is KeyValueTextInputFormat, which is used to create a single key-value pair per line.
• TextInputFormat is used to create a program considering a key as the line number and a value as the line itself.
• NLineInputFormat is similar to TextInputFormat except that there is N number of lines that make an input split.
• MultiFileInputFormat is used to implement an input format that aggregates multiple files into one split.
• For the class SequenceFileInputFormat to be implemented, the input file must be a Hadoop sequence file which contains serialized key-value pairs.

Now that you have completed Input Formats in MapReduce, let us look into the classes for MapReduce OutputFormat.

Output Formats in MapReduce

The table given below lists some Output Formats in MapReduce.

OutputFormat classes	Description
TextOutputFormat	It is the default OutputFormat and writes records as lines of text. Each key-value pair is separated by a TAB character. This can be customized using the mapped.textoutputformat.separator property. The corresponding InputFormat is KeyValueTextInputFormat.
SequenceFileOutputFormat	It writes sequence files to save the output. It is compact and compressed.
SequenceFileAsBinaryInputFormat	It writes key and value in raw binary format into a sequential file container.
MapFileOutputFormat	It writes MapFiles as the output. The keys in a MapFile must be added in an order, and the reducer will emit keys in the sorted order.
MultipleTextOutputFormat	It writes data to multiple files whose names are derived from output keys and values.
MultipleSequenceFileOutputFormat	It creates output in multiple files in a compressed form

Let us now understand what distributed cache is.

What is Distributed Cache in Hadoop?

Distributed Cache is a Hadoop feature to cache files needed by applications.

A distributed cache:

• Helps boost efficiency when a map or a reduce task needs access to common data
• Allows a cluster node to read the imported files from its local file system instead of retrieving the files from other cluster nodes
• Allows both single files and archives like zipping and tar.gz.
• Copies file only to slave nodes; if there are no slave nodes in the cluster, then distributed cache copies the files to the master node
• Allows access to the cached files from mapper or reducer applications to make sure that the current working directory is added to the application path
• Allows referencing of the cached files as though they are present in the current working directory

Want to learn more about Big Data Hadoop, why not enroll for Our Big Data Hadoop and Spark Developer Certification course?

Let us now understand how to use Distributed Cache in Hadoop.

Using Distributed Cache

Here are the steps to use distributed cache in Hadoop:

• First, set up the cache by copying the requisite files to the FileSystem as shown below.

• Set up the application's JobConf as shown below.

JobConf job = new JobConf();
DistributedCache.addCacheFile(new URI("/myapp/lookup.dat#lookup.dat"), job);
DistributedCache.addCacheArchive(new URI("/myapp/map.zip", job);
DistributedCache.addFileToClassPath(new Path("/myapp/mylib.jar"), job);
DistributedCache.addCacheArchive(new URI("/myapp/mytar.tar", job);
DistributedCache.addCacheArchive(new URI("/myapp/mytgz.tgz", job);
DistributedCache.addCacheArchive(new URI("/myapp/mytargz.tar.gz", job);

• Use the cached files in the mapper or reducer as shown below.

public static class MapClass extends MapReduceBase implements Mapper<K, V, K, V>
{ private Path[] localArchives; private Path[] localFiles;
public void configure(JobConf job) {
// Get the cached archives/files
File f = new File("./map.zip/some/file/in/zip.txt");
}
public void map(K key, V value,
OutputCollector<K, V> output, Reporter reporter)
throws IOException {
// Use data from the cached archives/files here
// ...
// ...
output.collect(k, v);
}
}

Joins in MapReduce

Joins are relational constructs that can be used to combine relations. In MapReduce, joins are applicable in situations where you have two or more datasets you want to combine.

A join is performed either in the map phase or in the reduce phase by taking advantage of the MapReduce Sort-Merge architecture.

The various join patterns available in MapReduce are:

• Reduce side join: A reduce side join is used for joining two or more large datasets with the same foreign key with any kind of join operation.
• Replicated join: A replicated join is a map-side join that works in situations where one of the datasets is small enough to cache.
• Composite join: A composite join is a map-side join used on very large formatted input datasets sorted and partitioned by a foreign key.
• Cartesian product: A Cartesian product is a map-side join where every single record is paired up with another dataset.

Let us now look into each pattern in detail.

Reduce Side Join

A reduce side join works in the following ways:

• The mapper prepares for join operations.
  • It takes each input record from every dataset
  • It emits a foreign key-record pair.

• The reducer performs a join operation
  • It collects the values of each input group into temporary lists.
  • The temporary lists are then iterated over, and the records from both sets are joined.

Reduce Side Join: When to use It?

A reduce side join can be used:

• When multiple large datasets are being joined by a foreign key
• When flexibility is needed to execute any join operation
• When a large amount of network bandwidth is available
• When there is no limitation on the size of datasets.

An SQL analogy of a reduce side join is:

SELECT users.ID, users.Location,

comments.upVotes

FROM users

[INNER|LEFT|RIGHT] JOIN comments

ON users.ID=comments.UserID

In the output of a reduce side to join, the number of part files equals the number of reduce tasks.

Replicated join

A replicated join is a map-only pattern that works as follows:

• It reads all files from the distributed cache in Hadoop and stores them into in-memory lookup tables.
• The mapper processes each record and joins it with the data stored in memory.
• There is no data shuffled to the reduce phase.
• The mapper provides the final output.

Replicated join: When to use it?

Replicated joins should be used:

• When all datasets, except for the largest one, can fit into the main memory of each map task that is limited by Java Virtual Machine or JVM heap size
• When there is a need for an inner join or a left outer join, with the large input dataset being the “left” part of the operation.

An SQL analogy of a replicated join is:

SELECT users.ID, users.Location,

comments.upVotes

FROM users

[INNER|LEFT|RIGHT] JOIN comments

ON users.ID=comments.UserID

In the output of a replicated join, the number of part files equals the number of map tasks.

Composite Join

A composite join is a map-only pattern working in the following ways:

• All datasets are divided into the same number of partitions.
• Each partition of the dataset is sorted by a foreign key, and all the foreign keys reside in the associated partition of each dataset.
• Two values are retrieved from the input tuple associated with each dataset based on the foreign key and the output to the file system.

Composite join: When to use it?

Composite joins should be used:

• When all datasets are sufficiently large
• When there is a need for an inner join or a full outer join.

An SQL analogy of a composite join is:

SELECT users.ID, users.Location,

comments.upVotes

FROM users

[INNER|LEFT|RIGHT] JOIN comments

ON users.ID=comments.UserID

In the output of a composite join, the number of part files equals the number of map tasks.

Cartesian product

A Cartesian product is a map-only pattern that works in the following ways:

• Datasets are split into multiple partitions.
• Each partition is fed to one or more mappers. For example, in the image shown below, split A-1 and A-2 are fed to three mappers each.

• A RecordReader reads every record of input split associated with the mapper.
• The mapper simply pairs every record of a dataset with every record of all other datasets.

Cartesian product: When to use it?

A Cartesian product should be used:

• When there is a need to analyze relationships between all pairs of individual records
• When there are no constraints on the execution time.

In the output of a Cartesian product, every possible tuple combination from the input records is represented.

An SQL analogy of a Cartesian product is:

SELECT *

FROM users

[CROSS] JOIN comments

In the next section of this sqoop tutorial, let’s try to understand what sqoop is in the next section.

What is Sqoop?

Now that you have learned about MapReduce, let’s talk about Sqoop in Hadoop.

Sqoop, an Apache Hadoop Ecosystem project, is a command-line interface application for transferring data between relational databases and Hadoop.

It supports incremental loads of a single table or a free-form SQL query. Imports can also be used to populate tables in Hive or HBase. Exports can be used to put data from Hadoop into a relational database.

Let us now understand why Sqoop is so important.

Why Sqoop?

While companies across industries are trying to move from structured relational databases like MySQL, Teradata, Netezza, and so on to Hadoop, there were concerns about the ease of transitioning existing databases.

It was challenging to load bulk data into Hadoop or access it from MapReduce. Users had to consider data consistency, production system resource consumption, and data preparation.

Data transfer using scripts was both time consuming and inefficient. Direct access to data from external systems was also complicated. This was resolved with the introduction of Sqoop.

Sqoop allows smooth import and export of data from structured databases. Along with Oozie, Sqoop helps in scheduling and automating import and export tasks.

Sqoop in real life can be used for online marketers, Coupon.com uses Sqoop to exchange data between Hadoop and the IBM Netezza data warehouse appliance.

The organization can query its structured databases and transfer the results into Hadoop using Sqoop. The Apollo group, an education company, also uses Sqoop to extract data from databases as well as to inject the results from Hadoop Jobs back into relational databases.

Sqoop is an Apache Hadoop Eco-system project; its responsibility is to import or export operations across relational databases like MySQL, MS SQL, and Oracle to HDFS.

Let’s discuss the various reasons for using Apache Scoop.

Sqoop and its uses

SQL servers are deployed worldwide. A SQL server is a primary way to accept the data from a user. Nightly processing is being done on SQL servers for years. Hence, Sqoop can be used in many ways such as:

• Sqoop allows you to move data from traditional SQL DB to Hadoop HDFS as Hadoop makes its way into enterprises.
• Transferring the data using automated scripts is inefficient and time-consuming, hence Sqoop is used.
• Traditional DB has reporting, data visualization, and other enterprise built-in applications; however, to handle large data, you need an ecosystem.
• The need to bring the processed data from Hadoop HDFS to the applications like database engine or web services is satisfied by Sqoop.
• Sqoop is required when a database is imported from a Relational Database (RDB) to Hadoop or vice versa.

A Relational Database or RDB refers to any data in a structured format. Databases in MySQL or Oracle are examples of RDB.

While exporting databases from a Relational Database to Hadoop, users must consider the consistency of data, consumption of production system resources, and preparation of data for provisioning downstream pipeline.

While importing the database from Hadoop to a Relational Database, users must keep in mind that directly accessing data residing on external systems within a MapReduce framework complicates applications.

It also exposes the production system to excessive loads originating from cluster nodes. Hence, Sqoop is required in both the scenarios.

Let’s look at the benefits of using Sqoop in the next section.

Benefits of Sqoop

Sqoop offers many benefits such as:

• It transfers data from Hadoop to an RDB, and vice versa.
• It transforms data in Hadoop with the help of MapReduce or Hive without extra coding.
• It is used to import data from an RDB, such as SQL, MySQL, or Oracle into the Hadoop Distributed File System, or HDFS.
• It exports data back to the RDB.

Sqoop processing

Following is a summary of Sqoop processing.

• It runs in a Hadoop Cluster.
• It imports data from the RDB or NoSQL DB to Hadoop.
• It has access to the Hadoop core which helps in using mappers to slice the incoming data into unstructured formats and place the data in HDFS.
• It exports data back into RDB, ensuring that the schema of the data in the database is maintained.

The section gives an outline of the process of how Sqoop performs the execution.

Sqoop Execution Process

Sqoop performs the execution in three steps.

1. The dataset being transferred is divided into partitions.
2. A map-only job is launched with individual mappers responsible for transferring a slice of the dataset.
3. Each record of the data is handled in a type-safe manner as Sqoop uses metadata to infer the data types.

Importing Data Using Sqoop

Use the command shown in the image to import data present in MySQL database using Sqoop, where Simplilearn is the database name and device is the table name.

Sqoop Import Process

The process of the Sqoop import is summarized in the section:

• Gathering of Metadata: Sqoop introspects the database to gather the necessary metadata for the data being imported.
• Job Submitted to the cluster: A map-only Hadoop job is submitted to the cluster by Sqoop.
• Data is transferred: The map-only job performs data transfer using the metadata captured in step one.

The imported data is saved in a directory on HDFS based on the table being imported.

Users can:

• Specify any alternative directory where the files should be populated. By default, these files contain comma delimited fields with newlines separating the different records.
• Users can also override the format in which data is copied by explicitly specifying the field separator and recording terminator characters.
• Users can easily import data in Avro data format by specifying the option‚ as-avrodatafile‚ with the import command.

Apache Sqoop supports different data formats for importing data. It also provides several options for tuning the import operation.

Let’s discuss the process of importing data to Hive and HBase.

Importing data to Hive and HBase

Importing data to Hive and HBase involves the following steps.

Gathering of the Hive metastore:

• Sqoop takes care of populating the Hive metastore with appropriate metadata for the table
• It invokes the necessary commands to load the table or partition.

Data conversion:

• Using Hive import, Sqoop converts the data from the native data types in the external datastore into the corresponding types within Hive.

Data prepared for Hive:

• Sqoop automatically chooses the native delimiter set used by Hive. If the data being imported has a new line or other Hive delimiter characters in it, Sqoop allows the removal of such characters. The data is then correctly populated for consumption in Hive.

Import completion:

• After the import is completed, the user can operate on the table just like any other table in Hive.

Now that you have seen the process of importing data to Hive, let’s talk about the process involved in importing Data to HBase.

When data is imported to HBase:

• Sqoop can populate the data in a particular column family in an HBase table.
• The HBase table and the column family settings are required to import a table to HBase.
• Data imported to HBase is converted to its string representation and inserted as UTF-8 bytes.

Commands to Import Data to HBase

Use the commands shown below to import data to HBase.

• Connect to the database:

$sqoop import --connect jdbc:mysql://database_server/dbname

• Specify the parameters such as username, password, and table-name:

--table table_name --username user_name --password

• Create an HBase table:

--hbase-create-table --hbase-table hive_table_name --column-family DB_type_Setting

Let’s now discuss the process of exporting Data from Hadoop using Sqoop.

Exporting Data from Hadoop Using Sqoop

Use the following command to export data from Hadoop using Sqoop:

We need to perform the following steps to export data from Hadoop using Sqoop.

1. Introspect the database for metadata and transfer the data.
2. Transfer the data from HDFS to the DB. Further

Sqoop divides the input dataset into splits. It uses individual map tasks to push the splits to the database. Each map task performs this transfer over many transactions to ensure optimal throughput and minimal resource utilization.

Now we will look at the various connectors using which we can connect to sqoop to different Databases.

Sqoop Connectors

The different types of Sqoop connectors are:

• Generic JDBC
• Default Sqoop
• Fast-path connectors

Generic JDBC: The Generic JDBC connector can be used to connect to any database that is accessible via JDBC.

Default Sqoop:The Default Sqoop connector is designed for specific databases such as MySQL, PostgreSQL, Oracle, SQL Server, and DB2.

Fast-path connectors:The Fast-path connector specializes in using specific batch tools to transfer data with high throughput. For example, MySQL and PostgreSQL databases.

We have learned that sqoop divides the task into four default mappers. In the next section, we will discuss how parallelism helps Sqoop in dividing tasks other than default mappers.

Controlling Parallelism

By default, Sqoop typically imports data using four parallel tasks called Mappers. Increasing the number of tasks might improve import speed. But note that each task adds load to your database server.

You can influence the number of tasks using the -m or --num-mappers Option, but Sqoop views this only as a hint and might not honor it.

In the following section screenshot image, we set parallelism to 8.

Common Sqoop commands are listed on the next section.

Sqoop commands

The first command on the section is to import the data from the MySQL table sqoop demo to an HDFS directo