大批量数据导出处理
场景：最近接到领导的任务，导出近百万的车辆数据，其中要经过三个接口的处理，导致一辆车最少需要1.17秒，按这种情况推算，1000000 * 1.17 / 3600 = 325 小时。。
思路 ：当数据库查询结果集数量过大时，必使用多线程，并且根据手头服务器的配置，规划多线程的线程数。考虑到写入文件数据过多，可以选择分批导出多个文件。
实现：代码里配的线程数为16，线程数 = CPU核数 + 1（其实根据情况可以更多，没敢太多），且 数组切割为10000一批次。如此处理，20小时之内完成百万导出。
注意：一定要注意内存释放，省的full GC OMM
如写入同一个文件加锁；
记得打日志；
代码执行性能优化。（速度多优化 1mm，对于整个执行时间就会优化好几个小时）
代码如下：
package com.foton.m2m.iov.standard.controller.openapi; import java.io.BufferedWriter; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStreamWriter; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.concurrent.Executor; import java.util.concurrent.Semaphore; import javax.servlet.http.HttpServletRequest; import org.apache.commons.collections.MapUtils; import org.apache.commons.lang3.StringUtils; import org.springframework.basic.annotation.DisplayName; import org.springframework.basic.domain.Pair; import org.springframework.basic.domain.map.Address; import org.springframework.basic.orm.DataAccessException; import org.springframework.basic.service.ServiceException; import org.springframework.basic.utils.DateUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import com.foton.m2m.basic.annotation.IgnoreHibernateSession; import com.foton.m2m.basic.domain.vo.ApiResponse; import com.foton.m2m.basic.map.component.MapService; import com.foton.m2m.basic.utils.ContextUtil; import com.foton.m2m.iov.basic.component.CapacityStatsService; import com.foton.m2m.iov.basic.component.FenceRecordService; import com.foton.m2m.iov.basic.controller.openapi.OpenApiBaseController; import com.foton.m2m.iov.basic.domain.SimpleDevice; import com.foton.m2m.iov.basic.model.CapacityStats; import com.foton.m2m.iov.standard.Constants; import com.google.common.collect.Lists; /** * 平台报警信息 */ @DisplayName("平台报警信息") @Controller(Constants.STANDARD_BEAN_NAME_PREFIX + "OpenAPIPlatformAlarmController") @RequestMapping("/openapi/iov/business/platformAlarm") @IgnoreHibernateSession public class PlatformAlarmController extends OpenApiBaseController { @Autowired @Qualifier(com.foton.m2m.cbm.manage.Constants.JDBC_TEMPLATE) private JdbcTemplate jdbcTemplate; @Autowired private MapService mapService; @Autowired private transient CapacityStatsService capacityStatsService; @Autowired private transient FenceRecordService fenceRecordService; @Autowired @Qualifier(com.foton.m2m.iov.basic.Constants.TASK_EXECUTOR) private Executor executor; @RequestMapping(value = "/demo" + Constants.SPRING_MVC_JSON_SUFFIX, method = RequestMethod.GET) public @ResponseBody ApiResponse<Map<String, Object>> demo(@RequestParam(value = "startTime", required = false) String startTime, @RequestParam(value = "endTime", required = false) String endTime, @RequestParam(value = "companyId") Long companyId, HttpServletRequest request) { String sql = "select d.did from iov_biz_car_info c join iov_biz_device d on d.iov_biz_car_info_id = c.id and d.is_primary = 1 where d.cbm_mag_company_id = " + companyId + " order by d.lasttime desc"; List<String> dids = jdbcTemplate.queryForList(sql, String.class); Date start = new Date(); Date end = new Date(); if (StringUtils.isNotEmpty(startTime)) { start = DateUtils.parseDateQuietly(startTime, DateUtils.DEFAULT_DATE_FORMAT); } if (StringUtils.isNotEmpty(endTime)) { end = DateUtils.parseDateQuietly(endTime, DateUtils.DEFAULT_DATE_FORMAT); } Date startDate = DateUtils.truncateDayOfMonth(start); Date endDate = DateUtils.setEndMonth(end); Object[] headers = { "vin", "车牌号", "月份", "行车路线Top1", "行车路线Top2", "行车路线Top3", "停车地点Top1", "停车地点Top2", "停车地点Top3", "车辆足迹Top1", "车辆足迹Top2", "车辆足迹Top3", "加油站Top1", "加油站Top2", "加油站Top3" }; List<Object> headerData = Arrays.asList(headers); String filePath = "/data-nfs/iov/upload/temp/zzz/"; //filePath = "attached/zzz/"; String lineName = "TRAVEL_ROUTE_ANALYSIS_"; write2TempFile(dids, startDate, endDate, headerData, filePath, lineName); return ApiResponse.success("ok"); } private void write2TempFile(List<String> dids,Date startDate,Date endDate, List<Object> headerData, String filePath, String lineName) { try { Semaphore semaphore = new Semaphore(16, true); List<List<String>> list = Lists.partition(dids, 10000);// 10000个数据分组执行 semaphore.acquire(); for (int i = 0; i < list.size(); i++) { List<String> batch = list.get(i); executor.execute(() -> { List<List<Object>> datalist = new ArrayList<>(); batch.stream().forEach(did -> { long start1 = System.currentTimeMillis(); // 按月获取车辆画像数据、故障数据 Iterator<Pair<Date, Date>> iter = DateUtils.getIterator(startDate, endDate, DateUtils.DateType.MONTHLY, false); SimpleDevice deviceInfo = deviceMapService.findOne(did); String vin = deviceInfo.getFramenum(); while (iter.hasNext()) { Pair<Date, Date> current = iter.next(); // 封装运营时间 String run_month = DateUtils.toString(current.getKey(), DateUtils.DEFAULT_DATE_FORMAT); // 车辆画像数据(前五的停车地点、前五的经常行驶路线、前五的车辆足迹） CapacityStats capacityStats = capacityStatsService.findAll(Lists.newArrayList(did), current.getKey(), current.getValue()); if (capacityStats != null) { List<Pair<String, Integer>> stops = capacityStats.getStops();// 停车地点 List<Pair<String, Integer>> cities = capacityStats.getCities();// 车辆足迹 List<Pair<String, Integer>> streets = capacityStats.getStreets();// 经常行驶路线 List<Object> data = new ArrayList<>(); data.add(StringUtils.isBlank(deviceInfo.getFramenum()) ? "-" : deviceInfo.getFramenum());// vin data.add(StringUtils.isBlank(deviceInfo.getLpn()) ? "-" : deviceInfo.getLpn());// lpn data.add(StringUtils.isBlank(run_month) ? "-" : run_month);// 月份 buildMapByTop3(streets, data, false);// 行车路线 buildMapByTop3(stops, data, true);// 停车地点 buildMapByTop3(cities, data, false);// 车辆足迹 buildGasMapByTop3(did, current.getKey(), current.getValue(), data); datalist.add(data); } } logger.info("task:{}=====Completed car vin {} ========= totalTime:{}.",Thread.currentThread().getName(), vin, System.currentTimeMillis() - start1); }); File file = createCSVFile(headerData, datalist, filePath, lineName+"("+datalist.size()+")"+ System.currentTimeMillis()); }); } semaphore.acquire(16); return; } catch (DataAccessException | InterruptedException e) { logger.error(e.getMessage(), e); throw new ServiceException("please check template!",e); } } private void buildGasMapByTop3(String did, Date start, Date end, List<Object> data) { List<Map<String, Object>> mapList = fenceRecordService.findTopFenceByDids(Lists.newArrayList(did), (short) 21, start, end); if (mapList != null) { if (mapList.size() == 0) { data.add("-");// Top1 data.add("-");// Top2 data.add("-");// Top3 } else if (mapList.size() == 1) { data.add(MapUtils.getString(mapList.get(0), "name","-"));// Top1 data.add("-");// Top2 data.add("-");// Top3 } else if (mapList.size() == 2) { data.add(MapUtils.getString(mapList.get(0), "name","-"));// Top1 data.add(MapUtils.getString(mapList.get(1), "name","-"));// Top2 data.add("-");// Top3 } else if (mapList.size() >= 3) { data.add(MapUtils.getString(mapList.get(0), "name","-"));// Top1 data.add(MapUtils.getString(mapList.get(1), "name","-"));// Top2 data.add(MapUtils.getString(mapList.get(2), "name","-"));// Top3 } }else{ data.add("-");// Top1 data.add("-");// Top2 data.add("-");// Top3 } } private void buildMapByTop3(List<Pair<String, Integer>> list, List<Object> data, Boolean isStops) { if (isStops) { for (Pair<String, Integer> pair : list) { String lnglats = pair.getKey(); if (lnglats == null || !lnglats.contains("-")) { continue; } String[] arr = pair.getKey().split("-"); Address addr = mapService.inverseGeocoding(arr[0], arr[1]); String address = "-"; if (addr != null) { address = addr.getFormattedAddress(); } pair.setKey(address); } } if (list.size() == 0) { data.add("-");// Top1 data.add("-");// Top2 data.add("-");// Top3 } else if (list.size() == 1) { data.add(StringUtils.isBlank(list.get(0).getKey()) ? "-" : list.get(0).getKey());// Top1 data.add("-");// Top2 data.add("-");// Top3 } else if (list.size() == 2) { data.add(StringUtils.isBlank(list.get(0).getKey()) ? "-" : list.get(0).getKey());// Top1 data.add(StringUtils.isBlank(list.get(1).getKey()) ? "-" : list.get(1).getKey());// Top2 data.add("-");// Top3 } else if (list.size() >= 3) { data.add(StringUtils.isBlank(list.get(0).getKey()) ? "-" : list.get(0).getKey());// Top1 data.add(StringUtils.isBlank(list.get(1).getKey()) ? "-" : list.get(1).getKey());// Top2 data.add(StringUtils.isBlank(list.get(2).getKey()) ? "-" : list.get(2).getKey());// Top3 } } /** * CSV文件生成方法 * * @param head 文件头 * @param dataList 数据列表 * @param outPutPath 文件输出路径 * @param filename 文件名 * @return */ public static File createCSVFile(List<Object> head, List<List<Object>> dataList, String outPutPath, String filename) { File csvFile = null; BufferedWriter csvWtriter = null; try { //ContextUtil.getWebRootPath() + csvFile = new File(outPutPath + File.separator + filename + ".csv"); File parent = csvFile.getParentFile(); if (parent != null && !parent.exists()) { parent.mkdirs(); } csvFile.createNewFile(); // GB2312使正确读取分隔符"," csvWtriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "GB2312"), 1024); // 写入文件头部 writeRow(head, csvWtriter); // 写入文件内容 for (List<Object> row : dataList) { writeRow(row, csvWtriter); } csvWtriter.flush(); } catch (Exception e) { e.printStackTrace(); } finally { try { csvWtriter.close(); } catch (IOException e) { e.printStackTrace(); } } return csvFile; } /** * 写一行数据方法 * * @param row * @param csvWriter * @throws IOException */ private static void writeRow(List<Object> row, BufferedWriter csvWriter) throws IOException { // 写入文件头部 for (Object data : row) { StringBuffer sb = new StringBuffer(); if (data == null) { continue; } String rowStr = sb.append("\"").append(String.valueOf(data).replaceAll("\"", "'").replaceAll(",", "，")).append("\",").toString(); csvWriter.write(rowStr); } csvWriter.newLine(); } }
