首页 > 代码库 > SQL to Elasticsearch java code
SQL to Elasticsearch java code
把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 =http://www.mamicode.com/ 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 =http://www.mamicode.com/ 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 =http://www.mamicode.com/ filedABucket.getKey(); Terms fieldCTerms = filedABucket.getAggregations().get("my_fieldC"); for (Terms.Bucket filedCBucket : fieldCTerms.getBuckets()) { //fieldC String fieldCValue =http://www.mamicode.com/ 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 =http://www.mamicode.com/ 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
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。