导出HBase数据到Excel（Java代码） 博客分类： 大数据 hbaseexcel

 

一、主要代码

package com.sgcc.mcsas.bigdata.tool;

import com.sgcc.mcsas.bigdata.service.HBaseServiceImpl;
import com.sgcc.mcsas.bigdata.service.IHBaseService;
import org.apache.hadoop.hbase.Cell;
import org.apache.hadoop.hbase.client.Result;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by dwxx-120 on 2016/7/12.
 */
public class Export {

    private static IHBaseService service = new HBaseServiceImpl();
    private static final int count = 5;//记录数
    private static final String fileName = "D:/hbase_export.xls";

    public static void exportExcel(List<String> tableNames) throws Exception {
        //创建excel文件
        File file = new File(fileName);
        if (!file.exists()) {
            file.createNewFile();
        }

        //设定输出流
        FileOutputStream fos = new FileOutputStream(file);
        HSSFWorkbook book = new HSSFWorkbook();

        for (int k = 0; k < tableNames.size(); k++) {
            String tableName = tableNames.get(k);
            //取部分数据
            List<Result> results = service.getTopSomeRecords(tableName, "05M", count);

            //创建sheet
            HSSFSheet sheet = book.createSheet();
            book.setSheetName(k, tableName.replace(":", "_"));

            //为空返回
            if (results.size() == 0) {
                System.out.println(tableName + " has no data!");
                continue;
            }

            //生成表头
            HSSFRow header = sheet.createRow(0);
            HSSFCell header_rowkey = header.createCell(0);

            //rowkey表头
            header_rowkey.setCellValue("ROWKEY");
            //其他表头
            List<Cell> cells0 = results.get(0).listCells();
            for (int i = 0; i < cells0.size(); i++) {
                HSSFCell header_other = header.createCell(i + 1);
                String name = new String(cells0.get(i).getQualifier());
                header_other.setCellValue(name);
            }

            //遍历查询的数据
            for (int i = 0; i < results.size(); i++) {
                //一个Result创建一行
                HSSFRow data_row = sheet.createRow(i + 1);
                Result r = results.get(i);

                //设置rowkey的值
                String rowkey = new String(r.getRow());
                HSSFCell data_rowkey = data_row.createCell(0);
                data_rowkey.setCellValue(rowkey);

                //设置其他值
                List<Cell> cellList = r.listCells();
                for (int j = 0; j < cellList.size(); j++) {
                    HSSFCell data_other = data_row.createCell(j + 1);
                    data_other.setCellValue(new String(cellList.get(j).getValue()));
                }
            }
        }
        //写入
        book.write(fos);
    }

    public static void main(String args[]) throws Exception {
        //HBase表名
        List<String> list = new ArrayList<String>();
        list.add("mcsas:zxjc_aeolianvibration");
        list.add("mcsas:zxjc_airmoisture");
        list.add("mcsas:zxjc_airpresure");
        list.add("mcsas:zxjc_brakecable");
        exportExcel(list);
    }
}

 

二、HBase取数据主要代码（getTopSomeRecords）

/**
     * 获取指定位置向下N条记录
     */
    public List<Result> getTopSomeRecords(String tableName, String startRow,
                                           Integer count) throws HBaseException {
        Connection connection = null;
        Table hTable = null;
        try {
            connection = ConnectionFactory.createConnection(conf);
            hTable = connection.getTable(TableName.valueOf(tableName));
            Scan scan = new Scan();
            List<Result> results = new ArrayList<Result>();

            scan.setCacheBlocks(true);
            scan.setCaching(10000);
            scan.setStartRow(Bytes.toBytes(startRow));

            PageFilter filter = new PageFilter(count);
            scan.setFilter(filter);

            ResultScanner scanner = hTable.getScanner(scan);

            for (Result r : scanner) {
                results.add(r);
            }

            scanner.close();
            return results;
        } catch (IOException e) {
            e.printStackTrace();
            logger.error("获取数据出错: ", e);
            throw new HBaseException(HBaseException.UNKNOW_ERROR, "获取数据出错: ", e);
        } finally {
            closeHTable(hTable);
            closeConn(connection);
        }
    }

 

三、备注

1、本文中一个表对应一个sheet，最终只有一个Excel文件，里面含有多个sheet

 

2、本文只是取部分数据，将数据先存入List中，如果数据量太大，会导致内存溢出，另外Excel也不可能存放太多的数据，建议只取部分数据