Excel InputFormat for Hadoop MapReduce

Excel Spreadsheet Input Format for Hadoop Map Reduce
I want to read a Microsoft Excel spreadsheet using Map Reduce, and found that I cannot use Text Input format of Hadoop to fulfill my requirement. Hadoop does not understand Excel spreadsheet so I landed upon writing custom Input format to achieve the same.
Hadoop works with different types of data formats like flat text files to databases. An InputSplit is nothing more than a chunk of several blocks; it should be pretty rare to get a block boundary ending up at the exact location of a end of line (EOL). Each such split is processed by a single map. Some of my records located around block boundaries should be therefore split in 2 different blocks.

1. How Hadoop can guarantee all lines from input files are completely read?
2. How Hadoop can consolidate a line that is starting on block B and that ends up on B+1?
3. How Hadoop can guarantee we do not miss any line?
4. Is there a limitation in term of line’s size? Can a line be greater than a block (i.e. spanned over more than 2 blocks)? If so, is there any performance issue?

Let’s take an example to get the concept more clear: Suppose my data set is composed on a single 300Mb file, spanned over 3 different blocks (blocks of 128Mb), and suppose that I have been able to get 1 InputSplit for each block. Let’s imagine now 3 different scenarios

Hadoop MapReduce Input split

File is composed on 6 lines of 50Mb each

• The first Reader will start reading bytes from Block B1, position 0. The first two EOL will be met at respectively 50Mb and 100Mb. 2 lines (L1 & L2) will be read and sent as key / value pairs to Mapper 1 instance. Then, starting from byte 100Mb, we will reach end of our Split (128Mb) before having found the third EOL. This incomplete line will be completed by reading the bytes in Block B2 until position 150Mb. First part of Line L3 will be read locally from Block B1, second part will be read remotely from Block B2 (by the mean of FSDataInputStream), and a complete record will be finally sent as key / value to Mapper 1.

• The second Reader starts on Block B2, at position 128Mb. Because 128Mb is not the start of a file, there is strong chance our pointer is located somewhere in an existing record that has been already processed by previous Reader. We need to skip this record by jumping out to the next available EOL, found at position 150Mb. Actual start of RecordReader 2 will be at 150Mb instead of 128Mb.

We can wonder what happens in case a block starts exactly on an EOL. By jumping out until the next available record (through readLine method), we might miss 1 record. Before jumping to next EOL, we actually need to decrement initial “start” value to “start – 1″. Being located at least 1 offset before EOL; we ensure no record is skipped.

Maximum size for a single record
There is a maximum size allowed for a single record to be processed. This value can be set using mapred.linerecordreader.maxlength parameter.

You can also find the whole project on Github

ExcelParser.java

	package com.sreejithpillai.excel.parser;
	import java.io.IOException;
	import java.io.InputStream;
	import java.util.Iterator;
	import org.apache.commons.logging.Log;
	import org.apache.commons.logging.LogFactory;
	import org.apache.poi.hssf.usermodel.HSSFSheet;
	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
	import org.apache.poi.ss.usermodel.Cell;
	import org.apache.poi.ss.usermodel.Row;
	public class ExcelParser {
	private static final Log LOG = LogFactory.getLog(ExcelParser.class);
	private StringBuilder currentString = null;
	private long bytesRead = 0;
	public String parseExcelData(InputStream is) {
	try {
	HSSFWorkbook workbook = new HSSFWorkbook(is);
	// Taking first sheet from the workbook
	HSSFSheet sheet = workbook.getSheetAt(0);
	// Iterate through each rows from first sheet
	Iterator<Row> rowIterator = sheet.iterator();
	currentString = new StringBuilder();
	while (rowIterator.hasNext()) {
	Row row = rowIterator.next();
	// For each row, iterate through each columns
	Iterator<Cell> cellIterator = row.cellIterator();
	while (cellIterator.hasNext()) {
	Cell cell = cellIterator.next();
	switch (cell.getCellType()) {
	case Cell.CELL_TYPE_BOOLEAN:
	bytesRead++;
	currentString.append(cell.getBooleanCellValue() + "\t");
	break;
	case Cell.CELL_TYPE_NUMERIC:
	bytesRead++;
	currentString.append(cell.getNumericCellValue() + "\t");
	break;
	case Cell.CELL_TYPE_STRING:
	bytesRead++;
	currentString.append(cell.getStringCellValue() + "\t");
	break;
	}
	}
	currentString.append("\n");
	}
	is.close();
	} catch (IOException e) {
	LOG.error("IO Exception : File not found " + e);
	}
	return currentString.toString();
	}
	public long getBytesRead() {
	return bytesRead;
	}
	}

view raw

ExcelParser

hosted with ❤ by GitHub

ExcelInputFormat.java

	package com.sreejithpillai.excel.mapreduce;
	import java.io.IOException;
	import org.apache.hadoop.io.LongWritable;
	import org.apache.hadoop.io.Text;
	import org.apache.hadoop.mapreduce.InputSplit;
	import org.apache.hadoop.mapreduce.RecordReader;
	import org.apache.hadoop.mapreduce.TaskAttemptContext;
	import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
	/**
	* <p>
	* An {@link org.apache.hadoop.mapreduce.InputFormat} for excel spread sheet files.
	* Multiple sheets are supported
	* <p/>
	* Keys are the position in the file, and values are the row containing all columns for the
	* particular row.
	*/
	public class ExcelInputFormat extends FileInputFormat<LongWritable,Text>{
	@Override
	public RecordReader<LongWritable, Text> createRecordReader(InputSplit split,
	TaskAttemptContext context) throws IOException, InterruptedException {
	return new ExcelRecordReader();
	}
	}

view raw

ExcelInputFormat

hosted with ❤ by GitHub

ExcelRecordReader.java

	package com.sreejithpillai.excel.mapreduce;
	import java.io.IOException;
	import java.io.InputStream;
	import org.apache.hadoop.conf.Configuration;
	import org.apache.hadoop.fs.FSDataInputStream;
	import org.apache.hadoop.fs.FileSystem;
	import org.apache.hadoop.fs.Path;
	import org.apache.hadoop.io.LongWritable;
	import org.apache.hadoop.io.Text;
	import org.apache.hadoop.mapreduce.InputSplit;
	import org.apache.hadoop.mapreduce.RecordReader;
	import org.apache.hadoop.mapreduce.TaskAttemptContext;
	import org.apache.hadoop.mapreduce.lib.input.FileSplit;
	import com.sreejithpillai.excel.parser.ExcelParser;
	/**
	* Reads excel spread sheet , where keys are offset in file and value is the row
	* containing all column as a string.
	*/
	public class ExcelRecordReader extends RecordReader<LongWritable, Text> {
	private LongWritable key;
	private Text value;
	private InputStream is;
	private String[] strArrayofLines;
	@Override
	public void initialize(InputSplit genericSplit, TaskAttemptContext context)
	throws IOException, InterruptedException {
	FileSplit split = (FileSplit) genericSplit;
	Configuration job = context.getConfiguration();
	final Path file = split.getPath();
	FileSystem fs = file.getFileSystem(job);
	FSDataInputStream fileIn = fs.open(split.getPath());
	is = fileIn;
	String line = new ExcelParser().parseExcelData(is);
	this.strArrayofLines = line.split("\n");
	}
	@Override
	public boolean nextKeyValue() throws IOException, InterruptedException {
	if (key == null) {
	key = new LongWritable(0);
	value = new Text(strArrayofLines[0]);
	} else {
	if (key.get() < (this.strArrayofLines.length – 1)) {
	long pos = (int) key.get();
	key.set(pos + 1);
	value.set(this.strArrayofLines[(int) (pos + 1)]);
	pos++;
	} else {
	return false;
	}
	}
	if (key == null || value == null) {
	return false;
	} else {
	return true;
	}
	}
	@Override
	public LongWritable getCurrentKey() throws IOException,
	InterruptedException {
	return key;
	}
	@Override
	public Text getCurrentValue() throws IOException, InterruptedException {
	return value;
	}
	@Override
	public float getProgress() throws IOException, InterruptedException {
	return 0;
	}
	@Override
	public void close() throws IOException {
	if (is != null) {
	is.close();
	}
	}
	}

view raw

ExcelRecordReader

hosted with ❤ by GitHub

ExcelMapper.java

	package com.sreejithpillai.excel.mapreduce;
	import java.io.IOException;
	import org.apache.hadoop.io.LongWritable;
	import org.apache.hadoop.io.Text;
	import org.apache.hadoop.mapreduce.Mapper;
	import org.slf4j.Logger;
	import org.slf4j.LoggerFactory;
	public class ExcelMapper extends
	Mapper<LongWritable, Text, Text, Text> {
	private static Logger LOG = LoggerFactory.getLogger(ExcelMapper.class);
	/**
	* Excel Spreadsheet is supplied in string form to the mapper.
	* We are simply emitting them for viewing on HDFS.
	*/
	public void map(LongWritable key, Text value, Context context)
	throws InterruptedException, IOException {
	String line = value.toString();
	context.write(new Text(line), null);
	LOG.info("Map processing finished");
	}
	}

view raw

ExcelMapper

hosted with ❤ by GitHub

ExcelDriver.java

	package com.sreejithpillai.excel.mapreduce;
	import org.apache.hadoop.fs.Path;
	import org.apache.hadoop.mapreduce.Job;
	import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
	import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;
	import org.slf4j.Logger;
	import org.slf4j.LoggerFactory;
	public class ExcelDriver {
	private static Logger logger = LoggerFactory.getLogger(ExcelDriver.class);
	/**
	* Main entry point for the example.
	*
	* @param args arguments
	* @throws Exception when something goes wrong
	*/
	public static void main(String[] args) throws Exception {
	logger.info("Driver started");
	Job job = new Job();
	job.setJarByClass(ExcelDriver.class);
	job.setJobName("Excel Record Reader");
	job.setMapperClass(ExcelMapper.class);
	job.setNumReduceTasks(0);
	FileInputFormat.addInputPath(job, new Path(args[0]));
	FileOutputFormat.setOutputPath(job, new Path(args[1]));
	job.setInputFormatClass(ExcelInputFormat.class);
	job.waitForCompletion(true);
	}
	}

view raw

ExcelDriver

hosted with ❤ by GitHub

I have implemented only Mapper class and no Reducer because we just need to see if Mappers are able to read each line from excel sheet. Folks who want to further process these data can implement those on Reducers.

To use this custom class you need to just specify the following line

job.setInputFormatClass(ExcelInputFormat.class); 

Happy Coding
Cheers !

Tags: BigData, CustomInputFormat, example of Custom InputFormat for MapReduce, ExcelParser, Hadoop RecordReader, InputFormat, MapReduce, Microsoft Excel Reader for hadoop Mapreduce