elasticsearch aggregation
l 环境
l 数据准备
l springboot集成并实现jestClient客户端
l 聚合实现代码
l 聚合结果展示
l 参考
一.elasticsearch安装略
1. elasticsearch-5.6.4.tar.gz
2. jdk1.8.0_144.tar.gz
二.测试数据
2.1: 新建索引
curl -XPUT 'hadoop21:9200/school?pretty' -d '{"mappings":{"student":{"_all":{"enabled":false},"properties":{"time":{"type":"date","format":"yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"},"name":{"type":"keyword","index":"true"},"job":{"type":"keyword","index":"true"},"gender":{"type":"integer"},"age":{"type":"integer"}}}}}'
2.2: 新增文档
curl -XPUT 'hadoop21:9200/school/student/1?pretty' -d '{"time":"2018-05-21 05:05:05","name":"zhangsan","job":"teacher","gender":1,"age":36}'
curl -XPUT 'hadoop21:9200/school/student/2?pretty' -d '{"time":"2018-05-21 05:05:05","name":"lisi","job":"teacher","gender":1,"age":36}'
curl -XPUT 'hadoop21:9200/school/student/3?pretty' -d '{"time":"2018-05-21 05:05:05","name":"wangwu","job":"teacher","gender":1,"age":37}'
curl -XPUT 'hadoop21:9200/school/student/4?pretty' -d '{"time":"2018-05-21 05:05:05","name":"zhangsan","job":"student","gender":1,"age":16}'
curl -XPUT 'hadoop21:9200/school/student/5?pretty' -d '{"time":"2018-05-21 05:05:05","name":"lisi","job":"student","gender":1,"age":17}'
curl -XPUT 'hadoop21:9200/school/student/6?pretty' -d '{"time":"2018-05-21 05:05:05","name":"wangwu","job":"student","gender":1,"age":18}'
三.springboot集成elasticsearch;
3.1: pom.xml添加配置
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-elasticsearch</artifactId>
</dependency>
<dependency>
<groupId>io.searchbox</groupId>
<artifactId>jest</artifactId>
<version>2.0.3</version>
</dependency>
3.2: 实例化jestClient客户端
JestClientFactory factory = new JestClientFactory();
factory.setHttpClientConfig(new HttpClientConfig.Builder(esServiceUrl)
.gson(new GsonBuilder().setDateFormat("yyyy-MM-dd HH:mm:ss").create())
.multiThreaded(true) //多线程模式
.connTimeout(60000) //连接超时
.readTimeout(10000) //由于是基于http,所以超时时间必不可少,不然经常会遇到socket异常:read time out
.build());
this.jestClient = factory.getObject();
四:样例
4.1: sql---(mysql)
select name AS 'name',count(1) AS 'total_num',job AS 'job',round(avg(age),0) AS'avg_age',time AS 'time'
from employee
WHERE gender = '1' AND time = '2018-05-21 05:05:05'
GROUP BY name, job;
注解:1.根据name首先按照name分组
2.紧接着在name分组的基础上按job分组,属于nameAgg聚合的子聚合,后面的都属于前面的子聚合
3.分组完,紧接着统计各组平均年龄
4.当前分组下有多少doc_count
4.2:jestClient操作语句
public void testall(){
try{
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
QueryBuilder queryBuilder = QueryBuilders.boolQuery()
.must(QueryBuilders.rangeQuery("time").gte("2018-05-21 05:05:05").lte("2018-05-21 05:05:05"))
.must(QueryBuilders.termQuery("gender",1));
searchSourceBuilder.query(queryBuilder);
AggregationBuilder aggregationBuilder = AggregationBuilders.terms("group_name").field("name").size(Integer.MAX_VALUE)
.subAggregation(AggregationBuilders.terms("group_job").field("job").size(Integer.MAX_VALUE))
.subAggregation(AggregationBuilders.avg("avg_age").field("age"))
.subAggregation(AggregationBuilders.count("total_num").field("name"));
searchSourceBuilder.aggregation(aggregationBuilder);
String query = searchSourceBuilder.toString();
Search search = new Search.Builder(query).addIndex("school").addType("student").build();
SearchResult result = jestClient.execute(search);
//首先取最外层的聚合,拿到桶
List<TermsAggregation.Entry> name =
result.getAggregations().getTermsAggregation("group_name").getBuckets();
//循环每一个桶,拿到里面的聚合,再拿桶
for(TermsAggregation.Entry entry :name){
List<TermsAggregation.Entry> jobAgg = entry.getTermsAggregation("group_job").getBuckets();
//取到每个分组里的平均年龄
double avgAge = entry.getAvgAggregation("avg_age").getAvg();
//取到每个分组里的文档数
double total = entry.getValueCountAggregation("total_num").getValueCount();
for (TermsAggregation.Entry jobEntry : jobAgg) {
System.out.println(jobEntry.getName()+"..."+jobEntry.getKey()+"..."+jobEntry.getCount());
}
System.out.println(entry.getName()+"=="+entry.getKey()+"=="+entry.getCount()+"=="+avgAge+"=="+total);
}
}catch(Exception e){
log.error("exception: "+e);
}
}
五 数据结果如图:
六 参考:
https://stackoverflow.com/questions/10441499/java-http-client-for-elasticsearch
http://blog.csdn.net/xr568897472/article/details/73826255 (关键)
http://blog.csdn.net/it_lihongmin/article/details/78447001
https://stackoverflow.com/questions/38602135/bucket-script-java-api-in-elasticsearch