基于SpringDataJest的ES数据查询与统计

• 命令查询职责分离模式(Command Query Responsibility Segregation，CQRS)从业务上分离修改 (Command，增，删，改，会对系统状态进行修改)和查询（Query，查，不会对系统状态进行修改)的行为。从而使得逻辑更加清晰，便于对不同部分进行针对性的优化。

  CQRS有以下几点有点：

  1. 分工明确，可以负责不同的部分；
  2. 将业务上的命令和查询的职责分离能够提高系统的性能、可扩展性和安全性。并且在系统的演化中能够保持高度的灵活性，能够防止出现CRUD模式中，对查询或者修改中的某一方进行改动，导致另一方出现问题的情况；
  3. 逻辑清晰，能够看到系统中的那些行为或者操作导致了系统的状态变化；
  4. 可以从数据驱动(Data-Driven) 转到任务驱动(Task-Driven)以及事件驱动(Event-Driven)。

  因此Command使用普通数据库（关系型数据库或非关系型数据库），Query使用效率查询效率更高的Elasticsearch。

• 1. 项目构建

  1. pom依赖如下:

     <dependency>
      <groupId>com.github.vanroy</groupId>
      <artifactId>spring-boot-starter-data-jest</artifactId>
      <version>3.1.2.RELEASE</version>
     </dependency>
     
     <dependency>
      <groupId>io.searchbox</groupId>
      <artifactId>jest</artifactId>
      <version>5.3.2</version>
     </dependency>
     

  2. 配置文件

     spring:
     data:
      jest:
        uri: http://127.0.0.1:9200
        username: elastic
        password: elastic
     

• 2. 构造查询条件

    package com.hfcsbc.esetl.domain;
  
    import lombok.Data;
    import org.springframework.data.elasticsearch.annotations.Document;
    import org.springframework.data.elasticsearch.annotations.Field;
    import org.springframework.data.elasticsearch.annotations.FieldType;
    import javax.persistence.Entity;
    import javax.persistence.Id;
    import javax.persistence.OneToOne;
    import java.util.Date;
    import java.util.List;
  
    /**
     * Create by zz on 2018/8/30
     */
    @Document(indexName = "person_v1", type = "personV1")
    public class Person {
        @Id
        private Long id;
        private String name;
        @Field(type = FieldType.Nested)
        private List<Address> address;
        private Integer number;
        private Integer status;
        private Date birthDay;
        @Field(type=FieldType.Long)
        private Date createTime;
    }
  
    package com.hfcsbc.esetl.domain;
  
    import lombok.Data;
    import javax.persistence.Entity;
    import javax.persistence.Id;
  
    /**
     * Create by zz on 2018/8/30
     */
    @Entity
    @Data
    public class Address {
        @Id
        private Long id;
        private String name;
        private Integer number;
    }
  

  1. 根据多个状态查询(类似于sql的in)

     BoolQueryBuilder queryBuidler = boolQuery()
          .should(termQuery("status", 1))
          .should(termQuery("status", 2))
          .should(termQuery("status", 3))
          .should(termQuery("status", 4))
          .should(termQuery("status", 5));
     

  2. and链接查询(类似于sql的and)

     BoolQueryBuilder queryBuilder = boolQuery();
     queryBuilder
          .must(queryBuilder1)
          .must(queryBuilder2)
          .must(queryBuilder3);
     

  3. range查询（类似于sql的between .. and ..)

     QueryBuilder rangeQuery1 = rangeQuery("birthDay").from(yesterday).to(today); //任何一种都可以
     QueryBuilder rangeQuery2 = rangeQuery("createTime").lte(qaParam.getRangeEndTime()).gte(qaParam.getRangeStartTime());
     

  4. 嵌套对象查询

     BoolQueryBuilder nested = boolQuery();
     if (CollectionUtils.isNotEmpty(qaParam.getAddress())) {
      for (Address address : qaParam.getTags()) {
          nested.should(nestedQuery("address", termQuery("address.name.keyword", address.getName()), ScoreMode.None));
      }
     }
     

     ScoreMode: 定义other join side中score是如何被使用的。如果不关注scoring，我们只需要设置成ScoreMode.None，此种方式会忽略评分因此会更高效和节约内存

     注: termQuery 中文条件查询,需在属性名后边添加 < .keyword >

• 3. 获取统计数据

  1. 非嵌套获取数据求和

     SumAggregationBuilder sumBuilder = AggregationBuilders.sum("sum").field("number");
     SearchQuery searchQuery = new NativeSearchQueryBuilder()
          .withIndices(QUERY_INDEX)
          .withTypes(QUERY_TYPE)
          .withQuery(boolQueryBuilder)
          .addAggregation(sumBuilder).build();
     
     AggregatedPage<ParkingOrder> account = (AggregatedPage<ParkingOrder>) esParkingOrderRepository.search(EsQueryBuilders.buildYesterdayArrearsSumQuery(employeeId));
     
     int sum = account.getAggregation("sum", SumAggregation.class).getSum().intValue();
     

  2. 嵌套数据求和

     SumAggregationBuilder sumBuilder = AggregationBuilders.sum("sum").field("adress.num");
     AggregationBuilder aggregationBuilder = AggregationBuilders.nested("nested", "adress").subAggregation(sumBuilder);
     SearchQuery searchQuery = new NativeSearchQueryBuilder()
          .withIndices(QUERY_INDEX)
          .withTypes(QUERY_TYPE)
          .withQuery(boolQueryBuilder)
          .addAggregation((AbstractAggregationBuilder) aggregationBuilder).build();
     AggregatedPage<ParkingOrder> account = (AggregatedPage<ParkingOrder>) esParkingOrderRepository.search(EsQueryBuilders.buildYesterdayArrearsSumQuery(employeeId));
     int sum = account.getAggregation("nested", SumAggregation.class).getAggregation("sum", SumAggregation.class).getSum().intValue();