Writing Hive Tables from MapReduce

This article is by Stephen Mouring Jr, appearing courtesy of Scott Leberknight.

This is part one of a two part blog series on how to read/write Apache Hive data from MapReduce jobs.

Hive is an awesome tool that integrates with HDFS and MapReduce by providing a SQL syntax and schema layer on top of HDFS files. It allows you to treat HDFS files like SQL tables with a row / column format. You can run SQL queries against HDFS files or create new files by running SQL insert statements. 

Hive translates the SQL queries into a series of MapReduce jobs that emulate that query's behavior. While Hive is very useful, it is not always efficient (or even possible) to represent your business logic as a Hive query. Like SQL, Hive is sometimes limited in what it can do. 

So this leaves you with the option of writing your own MapReduce job to perform the business logic you want in an efficient manner. But what if you want to have your data in a form that Hive can still understand so you can query it in Hive in the future? 

Thankfully you can easily emulate Hive's expected file format and output files so that Hive will recognize them. 

MapReduce operates on keys and values. When you write output from a MapReduce job, you have to emit a key and a value. If you want to emit multiple values you have to emit a value (or a key in some cases) that is delimited. Hive adds a layer on top of this paradigm by defining standard delimiters (which you can choose to override) and treating each delimited value in a file as a value of a column. 

Hive by default use the "char 1" as the field delimiter, since it is a nonprintable character not likely to occur in text data. Hive also supports "array fields" which are multivalued fields that are still considered a single column value. Array fields have a separate delimiter for their individual values. Here is a sample Hive table definition where I have, for the sake of clarity, explicitly defined the delimiters: 

create table test (
foo string,
bar string,
baz array<string>
)
row format delimited
fields terminated by '\001'
collections terminated by '\002'

public static final String SEPARATOR_FIELD = new String(new char[] {1});
public static final String SEPARATOR_ARRAY_VALUE = new String(new char[] {2});




public static final BytesWritable NULL_KEY = new BytesWritable(null);

// Build up the array values as a delimited string.
StringBuilder bazValueBuilder = new StringBuilder();
int i = 0;
for (String bazValue : bazValues) {
bazValueBuilder.append(bazValue);
++i;
if (i < bazValues.size()) {
bazValueBuilder.append(SEPARATOR_ARRAY_VALUE);
}
}




// Build up the column values / fields as a delimited string.
StringBuilder hiveRow = new StringBuilder();
hiveRow.append("fooValue");
hiveRow.append(SEPARATOR_FIELD);
hiveRow.append("barValue");
hiveRow.append(SEPARATOR_FIELD);
hiveRow.append(bazValueBuilder.toString());




// Emit a null key and a Text object containing the delimited fields
context.write(NULL_KEY, new Text(hiveRow))

LOAD DATA LOCAL INPATH your_jobs_output_path OVERWRITE INTO TABLE yourtable;

alter table yourtable add partition (yourpartition='yourvalue');