大量数据快速导出的解决方案Kettle

1.开发背景

在web项目中，经常会需要查询数据导出excel，以前比较常见的就是用poi。使用poi的时候也有两种方式，一种就是直接将集合一次性导出为excel，还有一种是分批次追加的方式适合数据量较大的情况。poi支持xls和xlsx，使用2003版本的只支持6万多行以下的数据量，使用2007版本的支持百万行。但是呢，当数据量大了之后这种方式却非常耗内存和时间。

接触了etl之后就想着用kettle来做导数据，经过测试是完全可行的。几十万行，一百万行都能快速导出来，代码也非常简单。

 

2.kettle相关maven依赖如下

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-vfs2</artifactId>
    <version>2.0</version>
</dependency>
<dependency>
    <groupId>org.scannotation</groupId>
    <artifactId>scannotation</artifactId>
    <version>1.0.3</version>
</dependency>
<dependency>
    <groupId>dom4j</groupId>
    <artifactId>dom4j</artifactId>
    <version>1.6.1</version>
</dependency>
<dependency>
    <groupId>pentaho-kettle</groupId>
    <artifactId>kettle-vfs</artifactId>
    <version>5.2.0.0</version>
    <classifier>pentaho</classifier>
</dependency>
<dependency>
    <groupId>pentaho-kettle</groupId>
    <artifactId>kettle-engine</artifactId>
    <version>5.2.0.0</version>
</dependency>
<dependency>
    <groupId>pentaho-kettle</groupId>
    <artifactId>kettle-core</artifactId>
    <version>5.2.0.0</version>
</dependency>

仓库如果没有kettle的jar包，可以先现在下来再上传到maven仓库

3.ktr文件：如以下附件下载链接

 由于博客园不支持ktr路径的文件上传，所以我将它保存为xml文件，使用时将xml后缀去掉用ktr后缀就可以 了，该转换就是查询，导出为excel两个组件，如图所示：

这里用到一个输入和excel输出，里面配置的参数：

    查询语句： ${exec_select_sql}、

    文件名称：${filepath}、

    sheet名称：${sheetname}

 

4.调用ktr

/** 
     * @功能描述: java调用Kettle导出的KTR，方法调用成功后，通过filepath参数获取文件<br><font color="red">该程序已经指定数据源</font>
     * @创建作者: ***
     * @创建日期: 2016年11月1日 下午7:50:57
     * @param exec_select_sql:可执行的SELECT语句（案例：SELECT username '名称',userName '员工名称',ID 'ID' FROM `User`;）
     * @param filepath:保存的文件名称，不含后缀,后缀统一xlsx（案例：C:\\test）
     * @param sheetname:文件中的sheet名称（默认:下载）
     * @return
     */
    public static boolean exportXlsx(String exec_select_sql, String filepath, String sheetname) {
        if(StringUtils.isEmpty(exec_select_sql)||StringUtils.isEmpty(filepath))
            return false;
        Trans trans = null;
        if(StringUtils.isEmpty(sheetname)) sheetname = "下载";
        String uuid = UUID.randomUUID().toString();
        logger_info.info("KettleUtil@exportXlsx:"+uuid+" {exec_select_sql:"+exec_select_sql+",filepath:"+filepath+",sheetname:"+sheetname+"}");
        try {
            String root_path = getPathMethod();
            // 初始化
            String fName = root_path+"export_xlsx.ktr";
            // 转换元对象
            KettleEnvironment.init();// 初始化
            EnvUtil.environmentInit();
            TransMeta transMeta = new TransMeta(fName);
            // 转换
            trans = new Trans(transMeta);
            // 执行转换
            trans.setVariable("exec_select_sql", exec_select_sql);
            trans.setVariable("filepath", filepath);
            trans.setVariable("sheetname", sheetname);
            trans.execute(null);
            // 等待转换执行结束
            trans.waitUntilFinished();
            // 抛出异常
            if (trans.getErrors() > 0) {
                logger_info.info("KettleUtil@exportXlsx:"+uuid+" 执行失败");
            }else{
                logger_info.info("KettleUtil@exportXlsx:"+uuid+" 执行成功");
            }
            return true;
        } catch (Exception e) {
            logger_error.error("KettleUtil@exportXlsx:"+uuid, e);
            return false;
        }
    }
     
    /** 
     * @功能描述: 获取编译目录
     * @创建作者: ***
     * @创建日期: 2016年11月1日 下午7:59:13
     * @return
     */
    private static String getPathMethod(){ 
        URL url= KettleUtil.class.getClassLoader().getResource(""); 
        String p = url.getPath(); 
        try { 
            p=URLDecoder.decode(p, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            logger_error.error("KettleUtil@getPathMethod:", e);
        } 
        return p; 
    }

5.测试导出方法

web项目中的测试

@RequestMapping("/kettle")

public Object kettle(int rows, String sql) {

    String sqlLimit = sql + "LIMIT "+rows;

    String fullName = "/home/admin/DataPlatform/temp"+ "/kettle"+uuid;

    this.kettleExportExcel(sqlLimit, fullName, "kettle");

    return null;

}

也可以用main函数或junit测试

 

6.打印执行信息,也可以直接在程序里面加

@Component
@Aspect
public class ControllerAspect {
    private static Logger logger_info = Logger.getLogger("api-info");
    private static Logger logger_error = Logger.getLogger("api-error");
    /**
     * 切面
     */
    private final String POINT_CUT = "execution(* com.demo.controller.*.*(..))";
    @Pointcut(POINT_CUT)
    private void pointcut() {
    }
    @AfterThrowing(value = POINT_CUT, throwing = "e")
    public void afterThrowing(Throwable e) {
        logger_error.error("afterThrowing: " + e.getMessage(), e);
    }
    /**
     * @功能描述: 打印Controller方法的执行时间
     * @创建日期: 2016年11月2日 上午11:44:11
     * @param proceedingJoinPoint
     * @return
     * @throws Throwable
     */
    @Around(value = POINT_CUT)
    public Object around(ProceedingJoinPoint proceedingJoinPoint)
            throws Throwable {
        String className = proceedingJoinPoint.getTarget().getClass().getName();
        String methodName = proceedingJoinPoint.getSignature().getName();
        Long begin = System.currentTimeMillis();
        Long beginMemory = Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory();
        StringBuilder log = new StringBuilder(className+"@"+methodName);
        Object result = null;
        try {
            result = proceedingJoinPoint.proceed();
        } catch (Exception e) {
            logger_error.error(log + e.getMessage(), e);
        }
        Long end = System.currentTimeMillis();
        Long endMemory = Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory();
        log.append(" 执行时间: ").append(end - begin).append("ms");
        log.append(" 消耗内存: ").append(endMemory - beginMemory).append("Byte");
        logger_info.info(log);
        return result;
    }
}

7.执行结果

* 导出10w行记录 

        执行时间: 1133ms

        执行时间: 1082ms 

        执行时间: 1096ms

* 导出100w行记录  

            执行时间: 39784ms

            执行时间: 8566ms 

            执行时间: 8622ms 
* Excel 2007行数极限 1048575 执行时间: 9686ms 

第一次导数据要加载kettle组件运行稍慢，后面几次再导数据速度就飞快了，更多结果有兴趣的可以去试试。

仅供参考，不足之处还请见谅，欢迎指正！转载请标明出处。如有疑问，欢迎评论或者联系我邮箱1034570286@qq.com