大量数据快速导出的解决方案-Kettle-白红宇

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

阅读量：7273 次

发布时间：2019-06-29

本文共 6335 字，大约阅读时间需要 21 分钟。

1.开发背景

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

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

2.kettle相关maven依赖如下

1 
       
         2     
        
         org.apache.commons
         3     
        
         commons-vfs2
         4     
        
         2.0
         5 
        6 
       
         7     
        
         org.scannotation
         8     
        
         scannotation
         9     
        
         1.0.3
        10 
       11 
       
        12     
        
         dom4j
        13     
        
         dom4j
        14     
        
         1.6.1
        15 
       16 
       
        17     
        
         pentaho-kettle
        18     
        
         kettle-vfs
        19     
        
         5.2.0.0
        20     
        
         pentaho
        21 
       22 
       
        23     
        
         pentaho-kettle
        24     
        
         kettle-engine
        25     
        
         5.2.0.0
        26 
       27 
       
        28     
        
         pentaho-kettle
        29     
        
         kettle-core
        30     
        
         5.2.0.0
        31

Maven依赖

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

3.ktr文件：如以下附件

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

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

查询语句： ${exec_select_sql}、

文件名称：${filepath}、

sheet名称：${sheetname}

4.调用ktr

1 /**  2      * @功能描述: java调用Kettle导出的KTR，方法调用成功后，通过filepath参数获取文件
该程序已经指定数据源 3      * @创建作者: *** 4      * @创建日期: 2016年11月1日 下午7:50:57 5      * @param exec_select_sql:可执行的SELECT语句（案例：SELECT username '名称',userName '员工名称',ID 'ID' FROM `User`;） 6      * @param filepath:保存的文件名称，不含后缀,后缀统一xlsx（案例：C:\\test） 7      * @param sheetname:文件中的sheet名称（默认:下载） 8      * @return 9      */10     public static boolean exportXlsx(String exec_select_sql, String filepath, String sheetname) {11         if(StringUtils.isEmpty(exec_select_sql)||StringUtils.isEmpty(filepath))12             return false;13         Trans trans = null;14         if(StringUtils.isEmpty(sheetname)) sheetname = "下载";15         String uuid = UUID.randomUUID().toString();16         logger_info.info("KettleUtil@exportXlsx:"+uuid+" {exec_select_sql:"+exec_select_sql+",filepath:"+filepath+",sheetname:"+sheetname+"}");17         try {18             String root_path = getPathMethod();19             // 初始化20             String fName = root_path+"export_xlsx.ktr";21             // 转换元对象22             KettleEnvironment.init();// 初始化23             EnvUtil.environmentInit();24             TransMeta transMeta = new TransMeta(fName);25             // 转换26             trans = new Trans(transMeta);27             // 执行转换28             trans.setVariable("exec_select_sql", exec_select_sql);29             trans.setVariable("filepath", filepath);30             trans.setVariable("sheetname", sheetname);31             trans.execute(null);32             // 等待转换执行结束33             trans.waitUntilFinished();34             // 抛出异常35             if (trans.getErrors() > 0) {36                 logger_info.info("KettleUtil@exportXlsx:"+uuid+" 执行失败");37             }else{38                 logger_info.info("KettleUtil@exportXlsx:"+uuid+" 执行成功");39             }40             return true;41         } catch (Exception e) {42             logger_error.error("KettleUtil@exportXlsx:"+uuid, e);43             return false;44         }45     }46      47     /** 48      * @功能描述: 获取编译目录49      * @创建作者: ***50      * @创建日期: 2016年11月1日 下午7:59:1351      * @return52      */53     private static String getPathMethod(){ 54         URL url= KettleUtil.class.getClassLoader().getResource(""); 55         String p = url.getPath(); 56         try { 57             p=URLDecoder.decode(p, "UTF-8");58         } catch (UnsupportedEncodingException e) {59             logger_error.error("KettleUtil@getPathMethod:", e);60         } 61         return p; 62     }

java调用kettle转换

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@Aspectpublic 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;    }}

View Code

7.执行结果

* 导出10w行记录

执行时间: 1133ms

执行时间: 1082ms

执行时间: 1096ms

* 导出100w行记录

执行时间: 39784ms

执行时间: 8566ms

执行时间: 8622ms

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

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