频道栏目
首页 > 网络 > 云计算 > 正文

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

2017-09-12 10:33:54      个评论    来源:qq_32674727的博客  
收藏   我要投稿

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,然后返回的结果中就只剩param

3.visualize画图,filter作为x,count作为y轴二、获取所有es的查询数据,并导出excel

1、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; 
} @Override
public 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; 
} }

 

上一篇:流量汇总mapreduce
下一篇:ApplicationScenariosofEdgeComputing——边缘计算的应用场景
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训

版权所有: 红黑联盟--致力于做实用的IT技术学习网站