大量数据快速导出的解决方案-Kettle
1.开发背景
在web项目中，经常会需要查询数据导出excel，以前比较常见的就是用poi。使用poi的时候也有两种方式，一种就是直接将集合一次性导出为excel，还有一种是分批次追加的方式适合数据量较大的情况。poi支持xls和xlsx，使用2003版本的只支持6万多行以下的数据量，使用2007版本的支持百万行。但是呢，当数据量大了之后这种方式却非常耗内存和时间。
接触了etl之后就想着用kettle来做导数据，经过测试是完全可行的。几十万行，一百万行都能快速导出来，代码也非常简单。
2.kettle相关maven依赖如下
1 <dependency> 2 <groupId>org.apache.commons</groupId> 3 <artifactId>commons-vfs2</artifactId> 4 <version>2.0</version> 5 </dependency> 6 <dependency> 7 <groupId>org.scannotation</groupId> 8 <artifactId>scannotation</artifactId> 9 <version>1.0.3</version> 10 </dependency> 11 <dependency> 12 <groupId>dom4j</groupId> 13 <artifactId>dom4j</artifactId> 14 <version>1.6.1</version> 15 </dependency> 16 <dependency> 17 <groupId>pentaho-kettle</groupId> 18 <artifactId>kettle-vfs</artifactId> 19 <version>5.2.0.0</version> 20 <classifier>pentaho</classifier> 21 </dependency> 22 <dependency> 23 <groupId>pentaho-kettle</groupId> 24 <artifactId>kettle-engine</artifactId> 25 <version>5.2.0.0</version> 26 </dependency> 27 <dependency> 28 <groupId>pentaho-kettle</groupId> 29 <artifactId>kettle-core</artifactId> 30 <version>5.2.0.0</version> 31 </dependency>
仓库如果没有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参数获取文件<br><font color="red">该程序已经指定数据源</font> 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:13 51 * @return 52 */ 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 }
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
