今回はHadoopおよびHiveを用いて抽出したデータを、Apache POIを使用してExcelシート上に出力します。
HiveはHiveQLというSQLに近い言語で開発が可能であり、JDBCドライバも提供されています。
過去の記事（Apache POI によるエクセルファイルの出力 その1）のソースコードを流用することで、極めて簡単にHadoopとExcelを連携させることができます。
 |
| Figure 1: Overall image |
【バージョン】
HadoopおよびHiveはそれぞれ現時点で入手可能な最新の安定版を使用します。
- Hadoop: Release 1.0.4 (12 October, 2012)
- Hive: Release 0.9.0 (30 April, 2012)
また、HiveのJDBCドライバ（${HIVE_HOME}/lib/hive-jdbc-0.9.0.jar）がHadoop 1.xのAPIと不整合を起こしている様ですので、Hadoop 0.1xのhadoop-core-0.19.1.jarも併せて使用します。
【前準備】
サンプルプログラムのプロジェクトに、$HIVE_HOME/lib 配下のjarファイル、およびhadoop-core-0.19.1.jarを参照設定します。
※HiveのJDBCドライバ（hive-jdbc-0.9.0.jar）の依存するjarを特定していない為、今回は全てのjarを参照する事としています。
【実装】
ソースコードの変更は、DBコネクション生成に関わる部分のみです。
List 1: Main logic
package util;
import java.io.*;
import java.util.Iterator;
import java.sql.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
public class Ora2Excel {
// db objects
private Connection con;
private Statement stm;
// poi objects
private Workbook wbk;
public Ora2Excel() throws SQLException {
DriverManager.registerDriver(new org.apache.hadoop.hive.jdbc.HiveDriver());
}
public void openDb(String userId, String password, String connString) throws SQLException {
con = DriverManager.getConnection(connString, userId, password);
stm = con.createStatement();
}
public void closeDb() throws SQLException {
stm.close();
con.close();
}
public void openBook(String fileName) throws IOException {
wbk = new XSSFWorkbook(fileName);
}
public void saveBook(String fileName) throws IOException {
FileOutputStream out = new FileOutputStream(fileName);
wbk.write(out);
out.close();
}
public void closeBook() {
wbk = null;
}
public void extract(String sheetName, String sql) throws SQLException {
Sheet wsh = wbk.getSheet(sheetName);
ResultSet rst = stm.executeQuery(sql);
int colCount = rst.getMetaData().getColumnCount();
// determine the start position: search "$start"
int rowStart = 0;
int colStart = 0;
Iterator<Row> iRow = wsh.rowIterator();
while (iRow.hasNext() && rowStart + colStart == 0) {
Row currentRow = (Row) iRow.next();
Iterator<Cell> iCol = currentRow.cellIterator();
while (iCol.hasNext()) {
Cell currentCell = (Cell) iCol.next();
if (currentCell.getCellType() == Cell.CELL_TYPE_STRING && currentCell.getStringCellValue().trim().equalsIgnoreCase("$start")) {
rowStart = currentCell.getRowIndex();
colStart = currentCell.getColumnIndex();
break;
}
}
}
// get "template row"
Row templateRow = wsh.getRow(rowStart);
// set cell values
int idxRow = rowStart;
while (rst.next()) {
wsh.shiftRows(idxRow, wsh.getLastRowNum()+1, 1);
Row r = wsh.createRow(idxRow);
for (int idxCol = templateRow.getFirstCellNum(); idxCol < templateRow.getLastCellNum(); idxCol++) {
Cell c = r.createCell(idxCol);
if (idxCol >= colStart && idxCol - colStart < colCount) {
int idxDbCol = idxCol-colStart + 1;
switch(rst.getMetaData().getColumnType(idxDbCol)){
case Types.NUMERIC:
c.setCellValue(rst.getDouble(idxDbCol));
break;
case Types.DATE:
c.setCellValue(rst.getDate(idxDbCol));
break;
case Types.TIMESTAMP:
c.setCellValue(rst.getDate(idxDbCol));
break;
default:
c.setCellValue(rst.getString(idxDbCol));
}
} else if (templateRow.getCell(idxCol).getCellType() == Cell.CELL_TYPE_FORMULA){
c.setCellFormula(templateRow.getCell(idxCol).getCellFormula());
} else if (templateRow.getCell(idxCol).getCellType() == Cell.CELL_TYPE_NUMERIC){
c.setCellValue(templateRow.getCell(idxCol).getNumericCellValue());
} else if (templateRow.getCell(idxCol).getCellType() == Cell.CELL_TYPE_STRING){
c.setCellValue(templateRow.getCell(idxCol).getStringCellValue());
}
c.setCellStyle(templateRow.getCell(idxCol).getCellStyle());
}
idxRow++;
}
rst.close();
// remove the template row.
wsh.removeRow(templateRow);
wsh.shiftRows(idxRow, wsh.getLastRowNum()+1, -1);
// calculate formula cells
XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) wbk);
}
}
【テスト用ソース（呼び出し部）】
上記クラスを呼び出す側の例を以下に示します。
List 2: Calling Main Logic
import util.Ora2Excel;
public class Test1 {
public static void main(String[] args) throws Exception {
Ora2Excel o2x = new Ora2Excel();
o2x.openDb("", "", "jdbc:hive://HiveServer:10000/default");
o2x.openBook("c:\\template.xlsx");
o2x.extract("Sheet1", "select deptno, empno, ename, job, mgr, hiredate, sal, comm from emp order by deptno, empno");
o2x.saveBook("c:\\result.xlsx");
o2x.closeBook();
o2x.closeDb();
}}
HiveのJDBCドライバはまだ開発途上ではありますが、Hadoopから帳票を出力する際には極めて効率的なツールであると言えます。
参考URL:
https://cwiki.apache.org/Hive/hiveclient.html
[Summary]
Hive provides JDBC driver. List 1 shows how to get data from Hadoop via Hive.
The version of Hadoop and Hive are:
- Hadoop: Release 1.0.4 (12 October, 2012)
- Hive: Release 0.9.0 (30 April, 2012)
Since it needs org/apache/hadoop/io/Writable that is not included in Hive 0.9.0, this time I bring hadoop-core-0.19.1.jar from another project.
To run the source code, you have to add these jar files above as External JAR.
Please see Export to Excel file via Apache POI, Part 1 for your reference.
