你的位置:首页 > 数据库

[数据库]大量数据快速导出的解决方案


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>

Maven依赖

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

 

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


 由于博客园不支持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: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组件运行稍慢,后面几次再导数据速度就飞快了,更多结果有兴趣的可以去试试。

 

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