elasticsearch aggregation example

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