MySQL and Hadoop integration

Dolphin and Elephant: an Introduction

This post is intended for MySQL DBAs or Sysadmins who need to start using Apache Hadoop and want to integrate those 2 solutions. In this post I will cover some basic information about the Hadoop, focusing on Hive as well as MySQL and Hadoop/Hive integration.

First of all, if you were dealing with MySQL or any other relational database most of your professional life (like I was), Hadoop may look different. Very different. Apparently, Hadoop is the opposite to any relational database. Unlike the database where we have a set of tables and indexes, Hadoop works with a set of text files. And… there are no indexes at all. And yes, this may be shocking, but all scans are sequential (full “table” scans in MySQL terms).

So, when does Hadoop makes sense?

First, Hadoop is great if you need to store huge amounts of data (we are talking about Petabytes now) and those data does not require real-time (milliseconds) response time. Hadoop works as a cluster of nodes (similar to MySQL Cluster) and all data are spread across the cluster (with redundancy), so it provides both high availability (if implemented correctly) and scalability. The data retrieval process (map/reduce) is a parallel process, so the more data nodes you will add to Hadoop the faster the process will be.

Second, Hadoop may be very helpful if you need to store your historical data for a long period of time. For example: store the online orders for the last 3 years in MySQL and store all orders (including those mail and phone orders since 1986 in Hadoop for trend analysis and historical purposes).

Integration

The next step after installing and configuring Hadoop is to implement a data flow between Hadoop and MySQL. If you have an OLTP system based on MySQL and you will want to use Hadoop for data analysis (data science) you may want to add a constant data flow between Hadoop and MySQL. For example, one may want to implement a data archiving, where old data is not deleted but rather placed into Hadoop and will be available for a further analysis. There are 2 major ways of doing it:

1. Non realtime: Sqoop
2. Realtime: Hadoop Applier for MySQL

Using Apache Sqoop for MySQL and Hadoop integration

Apache Sqoop can be run from a cronjob to get the data from MySQL and load it into Hadoop. Apache Hive is probably the best way to store data in Hadoop as it uses a table concept and have a SQL like language, HiveQL. Here is how we can import the whole table from MySQL to Hive:

Sqoop import command

Shell

1	$ sqoop import --connect jdbc:mysql://mysql_host/db_name --table ORDERS --hive-import

If you do not have a BLOBs or TEXTs in your table you can use “–direct” option which will probably be faster (it will use mysqldump). Another useful option is “–default-character-set”, for example for utf8 one can use “–default-character-set=utf8″. “–verify” option will help to check for data integrity.

To constantly import only the new rows from the table we can use option “–where “. For example:

Sqoop import command

Shell

1	$ sqoop import --connect jdbc:mysql://mysql_host/db_name --table ORDERS --hive-import --where "order_date > '2013-07-01'"

The following picture illustrates the process:

Using MySQL Applier for Hadoop

Sqoop is great if you need to perform a “batch” import. For a realtime data integration, we can use MySQL Applier for Hadoop. With the MySQL applier Hadoop / Hive will be integrated as if it is additional MySQL slave. MySQL Applier will read binlog events from the MySQL and “apply” those to our Hive table.

The following picture illustrate this process:

Conclusion

In this post I have showed the ways to integrate MySQL and Hadoop (the big picture). In the subsequent post I will show how to implement a data archiving with MySQL using Hadoop/Hive as a target.

Hadoop Interview Questions part 2

31. Explain the Reducer’s reduce phase?
Ans: In this phase the reduce (MapOutKeyType, Iterable, Context) method is called for each pair in the grouped inputs. The output of the reduce task is typically written to the File System via Context. write (ReduceOutKeyType, ReduceOutValType). Applications can use the Context to report progress, set application-level status messages and update Counters, or just indicate that they are alive. The output of the Reducer is not sorted.
32. How many Reducers should be configured?
Ans: The right number of reduces seems to be 0.95 or 1.75 multiplied by (<no. of nodes> * mapreduce.tasktracker.reduce.tasks.maximum).
With 0.95 all of the reduces can launch immediately and start transferring map outputs as the maps finish. With 1.75 the faster nodes will finish their first round of reduces and launch a second wave of reduces doing a much better job of load balancing. Increasing the number of reduces increases the framework overhead, but increases load balancing and lowers the cost of failures.
33. It can be possible that a Job has 0 reducers?
Ans: It is legal to set the number of reduce-tasks to zero if no reduction is desired.
34. What happens if number of reducers are 0?
Ans: In this case the outputs of the map-tasks go directly to the FileSystem, into the output path set by setOutputPath (Path). The framework does not sort the map-outputs before writing them out to the FileSystem.
35. How many instances of Job Tracker can run on a Hadoop Cluster?
Ans: Only one
36. What is the Job Tracker and what it performs in a Hadoop Cluster?
Ans: Job Tracker is a daemon service which submits and tracks the MapReduce tasks to the Hadoop cluster. It runs its own JVM process. And usually it run on a separate machine and each slave node is configured with job tracker node location. The Job Tracker is single point of failure for the Hadoop MapReduce service. If it goes down, all running jobs are halted.
Job Tracker in Hadoop performs following actions
 Client applications submit jobs to the Job tracker.
 The Job Tracker talks to the Name Node to determine the location of the data
 The Job Tracker locates Task Tracker nodes with available slots at or near the data
 The Job Tracker submits the work to the chosen Task Tracker nodes.
 The Task Tracker nodes are monitored. If they do not submit heartbeat signals often enough, they are deemed to have failed and the work is scheduled on a different Task Tracker.
 A Task Tracker will notify the Job Tracker when a task fails. The Job Tracker decides what to do then: it may resubmit the job elsewhere, it may mark that specific record as something to avoid, and it may even blacklist the Task Tracker as unreliable.
 When the work is completed, the Job Tracker updates its status.
 Client applications can poll the Job Tracker for information.
37. How a task is scheduled by a Job Tracker?
Ans: The Task Trackers send out heartbeat messages to the Job Tracker, usually every few minutes, to reassure the Job Tracker that it is still alive. These messages also inform the Job Tracker of the number of available slots, so the Job Tracker can stay up to date with where in the cluster work can be delegated. When the Job Tracker tries to find somewhere to schedule a task within the MapReduce operations, it first looks for an empty slot on the same server that hosts the Data Node containing the data, and if not, it looks for an empty slot on a machine in the same rack.
38. How many instances of Task tracker run on a Hadoop cluster?
Ans: There is one Daemon Task tracker process for each slave node in the Hadoop cluster.
39. What are the two main parts of the Hadoop framework?
Ans: Hadoop consists of two main parts
• Hadoop distributed file system, a distributed file system with high throughput,
• Hadoop MapReduce, a software framework for processing large data sets.
40. Explain the use of Task Tracker in the Hadoop cluster?
Ans: A Task tracker is a slave node in the cluster which that accepts the tasks from Job Tracker like Map, Reduce or shuffle operation. Task tracker also runs in its own JVM Process.
Every Task Tracker is configured with a set of slots; these indicate the number of tasks that it can accept. The Task Tracker starts a separate JVM processes to do the actual work (called as Task Instance) this is to ensure that process failure does not take down the task tracker.
The Task tracker monitors these task instances, capturing the output and exit codes. When the Task instances finish, successfully or not, the task tracker notifies the Job Tracker.
The Task Trackers also send out heartbeat messages to the Job Tracker, usually every few minutes, to reassure the Job Tracker that it is still alive. These messages also inform the Job Tracker of the number of available slots, so the Job Tracker can stay up to date with where in the cluster work can be delegated.
41. What do you mean by Task Instance?
Ans: Task instances are the actual MapReduce jobs which run on each slave node. The Task Tracker starts a separate JVM processes to do the actual work (called as Task Instance) this is to ensure that process failure does not take down the entire task tracker. Each Task Instance runs on its own JVM process. There can be multiple processes of task instance running on a slave node. This is based on the number of slots configured on task tracker. By default a new task instance JVM process is spawned for a task.
42. How many daemon processes run on a Hadoop cluster?
Ans: Hadoop is comprised of five separate daemons. Each of these daemons runs in its own JVM.
Following 3 Daemons run on Master Nodes.NameNode - This daemon stores and maintains the metadata for HDFS.
Secondary Name Node - Performs housekeeping functions for the Name Node. Job Tracker - Manages MapReduce jobs, distributes individual tasks to machines running the Task Tracker. Following 2 Daemons run on each Slave nodes Data Node – Stores actual HDFS data blocks.
Task Tracker – It is Responsible for instantiating and monitoring individual Map and Reduce tasks.
43. How many maximum JVM can run on a slave node?
Ans: One or Multiple instances of Task Instance can run on each slave node. Each task instance is run as a separate JVM process. The number of Task instances can be controlled by configuration. Typically a high end machine is configured to run more task instances.
44. What is NAS?
Ans: It is one kind of file system where data can reside on one centralized machine and all the cluster member will read write data from that shared database, which would not be as efficient as HDFS.
45. How HDFA differs with NFS?
Ans: Following are differences between HDFS and NAS
1. In HDFS Data Blocks are distributed across local drives of all machines in a cluster. Whereas in NAS data is stored on dedicated hardware.
2. HDFS is designed to work with MapReduce System, since computation is moved to data. NAS is not suitable for MapReduce since data is stored separately from the computations
3. HDFS runs on a cluster of machines and provides redundancy using replication protocol. Whereas NAS is provided by a single machine therefore does not provide data redundancy.
46. How does a Name Node handle the failure of the data nodes?
Ans: HDFS has master/slave architecture. An HDFS cluster consists of a single Name Node, a master server that manages the file system namespace and regulates access to files by clients.
In addition, there are a number of DataNodes, usually one per node in the cluster, which manage storage attached to the nodes that they run on.
The Name Node and Data Node are pieces of software designed to run on commodity machines. Name Node periodically receives a Heartbeat and a Block report from each of the DataNodes in the cluster. Receipt of a Heartbeat implies that the Data Node is functioning properly. A Block report
contains a list of all blocks on a Data Node. When Name Node notices that it has not received a heartbeat message from a data node after a certain amount of time, the data node is marked as dead. Since blocks will be under replicated the system begins replicating the blocks that were stored on the dead Data Node. The Name Node orchestrates the replication of data blocks from one Data Node to another. The replication data transfer happens directly between Data Node and the data never passes through the Name Node.
47. Can Reducer talk with each other?
Ans: No, Reducer runs in isolation.
48. Where the Mapper’s Intermediate data will be stored?
Ans: The mapper output (intermediate data) is stored on the Local file system (NOT HDFS) of each individual mapper nodes. This is typically a temporary directory location which can be setup in config by the Hadoop administrator. The intermediate data is cleaned up after the Hadoop Job completes.
49. What is the use of Combiners in the Hadoop framework?
Ans: Combiners are used to increase the efficiency of a MapReduce program. They are used to aggregate intermediate map output locally on individual mapper outputs. Combiners can help you reduce the amount of data that needs to be transferred across to the reducers.
You can use your reducer code as a combiner if the operation performed is commutative and associative.
The execution of combiner is not guaranteed; Hadoop may or may not execute a combiner. Also, if required it may execute it more than 1 times. Therefore your MapReduce jobs should not depend on the combiners’ execution.
50. What is the Hadoop MapReduce API contract for a key and value Class?
Ans: ◦The Key must implement the org.apache.hadoop.io.WritableComparable interface.
◦The value must implement the org.apache.hadoop.io.Writable interface.
51. What is Identity Mapper and Identity Reducer in MapReduce?
Ans: ◦ org.apache.hadoop.mapred.lib.IdentityMapper: Implements the identity function, mapping inputs directly to outputs. If MapReduce programmer does not set the Mapper Class using JobConf.setMapperClass then IdentityMapper.class is used as a default value.
◦org.apache.hadoop.mapred.lib.IdentityReducer: Performs no reduction, writing all input values directly to the output. If MapReduce programmer does not set the Reducer Class using JobConf.setReducerClass then IdentityReducer.class is used as a default value.
52. What is the meaning of speculative execution in Hadoop? Why is it important?
Ans: Speculative execution is a way of coping with individual Machine performance. In large clusters where hundreds or thousands of machines are involved there may be machines which are not performing as fast as others.
This may result in delays in a full job due to only one machine not performing well. To avoid this, speculative execution in Hadoop can run multiple copies of same map or reduce task on different slave nodes. The results from first node to finish are used
53. When the reducers are started in a MapReduce job?
Ans: In a MapReduce job reducers do not start executing the reduce method until the all Map jobs have completed. Reducers start copying intermediate key-value pairs from the mappers as soon as they are available. The programmer defined reduce method is called only after all the mappers have finished.
If reducers do not start before all mappers finish then why does the progress on MapReduce job shows something like Map (50%) Reduce (10%)? Why reducer’s progress percentage is displayed when mapper is not finished yet?
Reducers start copying intermediate key-value pairs from the mappers as soon as they are available. The progress calculation also takes in account the processing of data transfer which is done by reduce process, therefore the reduce progress starts showing up as soon as any intermediate key-value pair for a mapper is available to be transferred to reducer.
Though the reducer progress is updated still the programmer defined reduce method is called only after all the mappers have finished.
54. What is HDFS? How it is different from traditional file systems?
Ans: HDFS, the Hadoop Distributed File System, is responsible for storing huge data on the cluster. This is a distributed file system designed to run on commodity hardware. It has many similarities with existing distributed file systems. However, the differences from other distributed file systems are significant.
◦HDFS is highly fault-tolerant and is designed to be deployed on low-cost hardware.
◦HDFS provides high throughput access to application data and is suitable for applications that have large data sets.
◦HDFS is designed to support very large files. Applications that are compatible with HDFS are those that deal with large data sets. These applications write their data only once but they read it one or more times and require these reads to be satisfied at streaming speeds. HDFS supports write-once-read-many semantics on files.
55. What is HDFS Block size? How is it different from traditional file system block size?
Ans: In HDFS data is split into blocks and distributed across multiple nodes in the cluster. Each block is typically 64Mb or 128Mb in size. Each block is replicated multiple times. Default is to replicate each block three times. Replicas are stored on different nodes. HDFS utilizes the local file system to store each HDFS block as a separate file. HDFS Block size cannot be compared with the traditional file system block size.
57. What is a Name Node? How many instances of Name Node run on a Hadoop Cluster?
Ans: The Name Node is the centerpiece of an HDFS file system. It keeps the directory tree of all files in the file system, and tracks where across the cluster the file data is kept. It does not store the data of these files itself. There is only One Name Node process run on any Hadoop cluster. Name Node runs on its own JVM process. In a typical production cluster its run on a separate machine. The Name Node is a Single Point of Failure for the HDFS Cluster. When the Name Node goes down, the file system goes offline.
Client applications talk to the Name Node whenever they wish to locate a file, or when they want to add/copy/move/delete a file. The Name Node responds the successful requests by returning a list of relevant Data Node servers where the data lives.
58. What is a Data Node? How many instances of Data Node run on a Hadoop Cluster?
Ans: A Data Node stores data in the Hadoop File System HDFS. There is only One Data Node process run on any Hadoop slave node. Data Node runs on its own JVM process. On startup, a Data Node connects to the Name Node. Data Node instances can talk to each other, this is mostly during replicating data.
59. How the Client communicates with HDFS?
Ans: The Client communication to HDFS happens to be using Hadoop HDFS API. Client applications talk to the Name Node whenever they wish to locate a file, or when they want to add/copy/move/delete a file on HDFS. The Name Node responds the successful requests by returning a list of relevant Data Node servers where the data lives. Client applications can talk directly to a Data Node, once the Name Node has provided the location of the data.
60. How the HDFS Blocks are replicated?
Ans: HDFS is designed to reliably store very large files across machines in a large cluster. It stores each file as a sequence of blocks; all blocks in a file except the last block are the same size.
The blocks of a file are replicated for fault tolerance. The block size and replication factor are configurable per file. An application can specify the number of replicas of a file. The replication factor can be specified at file creation time and can be changed later. Files in HDFS are writing-once and have strictly one writer at any time.
The Name Node makes all decisions regarding replication of blocks. HDFS uses rack-aware replica placement policy. In default configurations there are total 3 copies of a data block on HDFS, 2 copies are stored on DataNodes on same rack and 3rd copy on a different rack.