SQL to Elasticsearch java code

標籤：代碼   set   需要   dex   add   long   histogram   size   class   

把Elasticsearch當成Database用，因為Elasticsearch不支援SQL，就需要把SQL轉換成代碼實現。

1.按某個field group by查詢count

SELECT  fieldA, COUNT(fieldA)from table   WHERE fieldC = "hoge" AND fieldD = "huga" AND fieldB > 10AND fieldB < 100 group by fieldA;

對應的java code:

SearchRequestBuilder searchReq = client.prepareSearch("sample_index");searchReq.setTypes("sample_types");TermsBuilder termsb = AggregationBuilders.terms("my_fieldA").field("fieldA").size(100);BoolFilterBuilder bf = FilterBuilders.boolFilter();TermFilterBuilder tf_fieldC = FilterBuilders.termFilter("fieldC","hoge");TermFilterBuilder tf_fieldD = FilterBuilders.termFilter("fieldD","huga");bf.must(tf_fieldC);bf.must(tf_fieldD);RangeFilterBuilder rangefieldBFilter = FilterBuilders.rangeFilter("fieldB")                .gt(10)                .lt(100);searchReq.setQuery(QueryBuilders.filteredQuery(QueryBuilders.matchAllQuery(),                    FilterBuilders.andFilter(bf, rangefieldBFilter))).addAggregation(                    termsb);SearchResponse searchRes = searchReq.execute().actionGet();Terms fieldATerms = searchRes.getAggregations().get("my_fieldA");for (Terms.Bucket filedABucket : fieldATerms.getBuckets()) {    //fieldA    String fieldAValue = filedABucket.getKey();        //COUNT(fieldA)    long fieldACount = filedABucket.getDocCount();}

 

2. 按某個field 和 date group by 並查詢sum，時間統計圖，時間間隔是1天。

SELECT  DATE(create_at), fieldA, SUM(fieldB) from table   group by DATE(create_at), fieldA;

對應的java code:

SearchRequestBuilder searchReq = client.prepareSearch("sample_index");searchReq.setTypes("sample_types");DateHistogramBuilder dhb = AggregationBuilders.dateHistogram("my_datehistogram").field("create_at").interval(DateHistogram.Interval.days(1));TermsBuilder termsb_fa = AggregationBuilders.terms("my_fieldA").field("fieldA").size(100);termsb_fa.subAggregation(AggregationBuilders.sum("my_sum_fieldB").field("fieldB"));dhb.subAggregation(termsb_fa)searchReq.setQuery(QueryBuilders.matchAllQuery()).addAggregation(dhb);SearchResponse searchRes = searchReq.execute().actionGet();DateHistogram dateHist = searchRes.getAggregations().get("my_datehistogram");for (DateHistogram.Bucket dateBucket : dateHist.getBuckets()) {    //DATE(create_at)    String create_at = dateentry.getKey();    Terms fieldATerms = dateBucket.getAggregations().get("my_fieldA");    for (Terms.Bucket filedABucket : fieldATerms.getBuckets()) {        //fieldA        String fieldAValue = filedABucket.getKey();                //SUM(fieldB)        Sum sumagg = filedABucket.getAggregations().get("my_sum_fieldB");        long sumFieldB = (long)sumagg.getValues();    }}

 

3. 按兩個field group by並查詢sum

SELECT  fieldA, fieldC, SUM(fieldB)from table   group by fieldA, fieldC;

對應的java code:

SearchRequestBuilder searchReq = client.prepareSearch("sample_index");searchReq.setTypes("sample_types");TermsBuilder termsb_fa = AggregationBuilders.terms("my_fieldA").field("fieldA").size(100);TermsBuilder termsb_fc = AggregationBuilders.terms("my_fieldC").field("fieldC").size(50);termsb_fc.subAggregation(AggregationBuilders.sum("my_sum_fieldB").field("fieldB"));termsb_fa.subAggregation(termsb_fc)searchReq.setQuery(QueryBuilders.matchAllQuery()).addAggregation(termsb_fa);SearchResponse searchRes = searchReq.execute().actionGet();Terms fieldATerms = searchRes.getAggregations().get("my_fieldA");for (Terms.Bucket filedABucket : fieldATerms.getBuckets()) {    //fieldA    String fieldAValue = filedABucket.getKey();    Terms fieldCTerms = filedABucket.getAggregations().get("my_fieldC");    for (Terms.Bucket filedCBucket : fieldCTerms.getBuckets()) {        //fieldC        String fieldCValue = filedCBucket.getKey();                //SUM(fieldB)        Sum sumagg = filedCBucket.getAggregations().get("my_sum_fieldB");        long sumFieldB = (long)sumagg.getValues();    }}

 

4. 按某個filed group by 並查詢count sum 和 average

SELECT  fieldA, COUNT(fieldA), SUM(fieldB), AVG(fieldB) from table   group by fieldA;

對應的java code:

SearchRequestBuilder searchReq = client.prepareSearch("sample_index");searchReq.setTypes("sample_types");TermsBuilder termsb = AggregationBuilders.terms("my_fieldA").field("fieldA").size(100);termsb.subAggregation(AggregationBuilders.sum("my_sum_fieldB").field("fieldB"));termsb.subAggregation(AggregationBuilders.avg("my_avg_fieldB").field("fieldB"));searchReq.setQuery(QueryBuilders.matchAllQuery()).addAggregation(termsb);SearchResponse searchRes = searchReq.execute().actionGet();Terms fieldATerms = searchRes.getAggregations().get("my_fieldA");for (Terms.Bucket filedABucket : fieldATerms.getBuckets()) {    //fieldA    String fieldAValue = filedABucket.getKey();        //COUNT(fieldA)    long fieldACount = filedABucket.getDocCount();        //SUM(fieldB)    Sum sumagg = filedABucket.getAggregations().get("my_sum_fieldB");    long sumFieldB = (long)sumagg.getValues();        //AVG(fieldB)    Avg avgagg = filedABucket.getAggregations().get("my_avg_fieldB");    double avgFieldB = avgagg.getValues();}

 

SQL to Elasticsearch java code