Elasticsearch获取ES查询的所有结果,并批量导出Excel2

工作环境是内网所以不能截图。搭建了ELK环境。3500W个dic中查询数据，并要求导出excel。从es中查询 status=500，返回为空，查询时间超过2000ms的数据head插件查询出索引的数据sql更方便查询支持标准sqlselect param from logstash-sql---3p where numfounds=0一、kibana画图1.首先是用kibana画条状图，create index 之后 在discover中可以设置查询条件。右上角是时间设置，默认是15min。 2.discover中点击param，点击下面的add，然后返回的结果中就只剩param3.visualize画图，filter作为x，count作为y轴二、获取所有es的查询数据，并导出excel1、es的size默认是100002、sql插件默认的size是200所以用到了分页查询之后又用了scroll和mapreduce，有个es对应的api很方便没有条数的限制首先呢，需要在java中引入elasticsearch-jar，比如使用maven：<dependency><groupId>org.elasticsearch</groupId><artifactId>elasticsearch</artifactId><version>1.4.4</version></dependency>然后初始化一个client对象：private static TransportClient client; private static String INDEX = "index_name"; private static String TYPE = "type_name";public static TransportClient init(){ Settings settings = ImmutableSettings.settingsBuilder() .put("client.transport.sniff", true) .put("cluster.name", "cluster_name") .build(); client = new TransportClient(settings).addTransportAddress(new InetSocketTransportAddress("localhost",9300)); return client; } public static void main(String[] args) { TransportClient client = init(); //这样就可以使用client执行查询了 }然后就是创建两个查询过程了 ，下面是from-size分页的执行代码：System.out.println("from size 模式启动！");Date begin = new Date();long count = client.prepareCount(INDEX).setTypes(TYPE).execute().actionGet().getCount();SearchRequestBuilder requestBuilder = client.prepareSearch(INDEX).setTypes(TYPE).setQuery(QueryBuilders.matchAllQuery());for(int i=0,sum=0; sum<count; i++){ SearchResponse response = requestBuilder.setFrom(i).setSize(50000).execute().actionGet(); sum += response.getHits().hits().length; System.out.println("总量"+count+" 已经查到"+sum);}Date end = new Date();System.out.println("耗时: "+(end.getTime()-begin.getTime()));下面是scroll分页的执行代码，注意啊！scroll里面的size是相对于每个分片来说的，所以实际返回的数量是：分片的数量*sizeSystem.out.println("scroll 模式启动！");begin = new Date();SearchResponse scrollResponse = client.prepareSearch(INDEX) .setSearchType(SearchType.SCAN).setSize(10000).setScroll(TimeValue.timeValueMinutes(1)).execute().actionGet();count = scrollResponse.getHits().getTotalHits();//第一次不返回数据for(int i=0,sum=0; sum<count; i++){ scrollResponse = client.prepareSearchScroll(scrollResponse.getScrollId()) .setScroll(TimeValue.timeValueMinutes(8)).execute().actionGet(); sum += scrollResponse.getHits().hits().length; System.out.println("总量"+count+" 已经查到"+sum);}end = new Date();System.out.println("耗时: "+(end.getTime()-begin.getTime()));2.导出excel 主要用到jxl包 一：史上最简单的方法 对于简单的表格（纯文本），其实可以不借助java Excel API而有更简单的方法！用制表符/t隔开每个域，用换行符/n隔开每一行，将文件后缀名改为".xls"搞定！只是这样弄出来的Excel表无法指定格式（如颜色，边框，对齐方式等等）。 二：Java Excel API Java Excel 是一个开源项目，通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件等，在项目中需要导入名为jxl.jar的包。在这里只是示例它的基本用法，其他高级的功能（图片、公式、格式等）请参考Java Excel的帮助文档，这里是关于它的资料：http://jexcelapi.sourceforge.net/。 如有一个用户资料的Excel表，包含ID、用户名、性别、邮件等信息，定义一个用户JavaBean：[java] view plain copypackage com.monitor1394.excel; /****用户 **@author monitor *Created on 2010-12-22, 9:57:58 */ public class User {/** ID */ private int id; /** 用户名 */ private String name; /** 性别 1:男 2:女*/private int sex; /** 邮件 */ private String email; public User(){ } public User(int id,String name,int sex,String email){this.id=id; this.name=name; this.sex=sex;this.email=email; } public String getEmail() {return email; } public void setEmail(String email) {this.email = email; } public int getId() {return id; } public void setId(int id) {this.id = id; } public String getName() {return name; } public void setName(String name) {this.name = name; } public int getSex() {return sex; } public void setSex(int sex) {this.sex = sex; } @Overridepublic String toString(){return id+":"+name; } }提供的Excel表操作类如下，某些单元格的格式可按自己意愿指定：[java] view plain copypackage com.monitor1394.excel; import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.List; import jxl.Sheet; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.Border; import jxl.format.BorderLineStyle; import jxl.format.Colour; import jxl.format.VerticalAlignment; import jxl.read.biff.BiffException; import jxl.write.Label; import jxl.write.Number; import jxl.write.NumberFormats; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; /****Excel表操作 **@author monitor *Created on 2010-12-22, 9:50:28 */ public class Excel {/** 标题单元格格式 */ private static WritableCellFormat titleFormat=null; /** 主题内容单元格格式 */ private static WritableCellFormat bodyFormat=null; /** 注释单元格格式 */ private static WritableCellFormat noteFormat=null; /** 浮点型数据的单元格格式 */ private static WritableCellFormat floatFormat=null; /** 整型数据的单元格格式 */ private static WritableCellFormat intFormat=null; /** 初始化数据 */ private static boolean init=false; /** 私有构造方法，防止错误使用Excel类 */ private Excel(){} /** *初始化各单元格格式 *@throws WriteException 初始化失败 */ private static void init() throws WriteException{WritableFont font1,font2,font3,font4;//Arial字体，9号，粗体，单元格黄色，田字边框，居中对齐font1 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD, false);titleFormat = new WritableCellFormat (font1);titleFormat.setBackground(Colour.YELLOW);titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN);titleFormat.setAlignment(Alignment.CENTRE);//Arial字体，9号，粗体，单元格黄色，田字边框，左右居中对齐，垂直居中对齐，自动换行font2 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD, false);noteFormat = new WritableCellFormat (font2);noteFormat.setBackground(Colour.YELLOW);noteFormat.setBorder(Border.ALL, BorderLineStyle.THIN);noteFormat.setAlignment(Alignment.CENTRE);noteFormat.setVerticalAlignment(VerticalAlignment.CENTRE);noteFormat.setWrap(true);//Arial字体，9号，非粗体，单元格淡绿色，田字边框font3 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD, false);bodyFormat = new WritableCellFormat (font3);bodyFormat.setBackground(Colour.LIGHT_GREEN);bodyFormat.setBorder(Border.ALL, BorderLineStyle.THIN);//Arial字体，9号，非粗体，单元格淡绿色，田字边框font4 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD, false);floatFormat = new WritableCellFormat (font4,NumberFormats.FLOAT);floatFormat.setBackground(Colour.LIGHT_GREEN);floatFormat.setBorder(Border.ALL, BorderLineStyle.THIN);//Arial字体，9号，非粗体，单元格淡绿色，田字边框font4 = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD, false);intFormat = new WritableCellFormat (font4,NumberFormats.INTEGER);intFormat.setBackground(Colour.LIGHT_GREEN);intFormat.setBorder(Border.ALL, BorderLineStyle.THIN);init=true;} public static void createUserExcelFile(List<User> userList,File destFile) throws WriteException, IOException{ if(init==false) init(); int index,row; WritableSheet sheet=null;WritableWorkbook book=null;book = Workbook.createWorkbook(destFile);sheet = book.createSheet("用户表", 0);sheet.setColumnView(0, 15);sheet.setColumnView(1, 15);sheet.setColumnView(2, 15);sheet.setColumnView(3, 40);//字段变量名 index=0; sheet.addCell(new Label(index++,0,"id",titleFormat));sheet.addCell(new Label(index++,0,"name",titleFormat));sheet.addCell(new Label(index++,0,"sex",titleFormat));sheet.addCell(new Label(index++,0,"email",titleFormat));//字段名 index=0; sheet.addCell(new Label(index++,1,"ID",titleFormat));sheet.addCell(new Label(index++,1,"用户名",titleFormat));sheet.addCell(new Label(index++,1,"性别",titleFormat));sheet.addCell(new Label(index++,1,"邮件",titleFormat));//字段注释 index=0; sheet.addCell(new Label(index++,2,null,noteFormat));sheet.addCell(new Label(index++,2,null,noteFormat));sheet.addCell(new Label(index++,2,"1:男/n2:女",noteFormat));sheet.addCell(new Label(index++,2,null,noteFormat));row=3; for(User user:userList){ if(user==null) continue; index=0; sheet.addCell(new Number(index++,row,user.getId(),bodyFormat));sheet.addCell(new Label(index++,row,user.getName(),bodyFormat));sheet.addCell(new Number(index++,row,user.getSex(),bodyFormat));sheet.addCell(new Label(index++,row,user.getEmail(),bodyFormat));row++; } book.write();if(book!=null) book.close();} public static List<User> readUserExcelFile(File file) throws IOException, BiffException{ if(file==null) return null; int row,column; String temp=null;Workbook book =null;Sheet sheet=null; List<User> userList=new ArrayList<User>();book = Workbook.getWorkbook(file);sheet = book.getSheet(0);row=3; while(row<sheet.getRows()){ column=0; User user=new User();//id temp=sheet.getCell(column++,row).getContents().trim(); if(temp!=null && !temp.equals("") && temp.matches("//d+")) user.setId(Integer.parseInt(temp));else break; //名称 temp=sheet.getCell(column++,row).getContents().trim();if(temp!=null && !temp.equals("")) user.setName(temp);//性别 temp=sheet.getCell(column++,row).getContents().trim(); if(temp!=null && !temp.equals("") && temp.matches("//d+")) user.setSex(Integer.parseInt(temp));//邮件 temp=sheet.getCell(column++,row).getContents().trim(); if(temp!=null && !temp.equals("")) user.setEmail(temp); userList.add(user);row++; } if(book!=null) book.close();return userList; } }