开发者社区> 橘子红了呐> 正文

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 > 10
AND 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 并查询另一个filed的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并查询第三个filed的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();
}
复制代码

 

5. 按某个field group by 并按另一个filed的Sum排序,获取前10

复制代码
SELECT  
fieldA, SUM(fieldB)
from table
WHERE fieldC = "hoge" 
group by fieldA
order by SUM(fieldB) DESC
limit 10;
复制代码

对应的java code:

复制代码
QueryBuilder termsc = QueryBuilders.termQuery("fieldC","hoge");
QueryBuilder queryBuilder = QueryBuilders.boolQuery().must(termsc);
TermsAggregationBuilder aggregationBuilder = AggregationBuilders.terms("my_fieldA").field("fieldA").size(10);
aggregationBuilder.subAggregation(AggregationBuilders.sum("my_sum_fieldB").field("fieldB"));
aggregationBuilder.order(Order.aggregation("my_sum_fieldB", false));
SearchResponse searchResponse = client.prepareSearch("sample_index").setQuery(queryBuilder).addAggregation(aggregationBuilder).execute().actionGet();
Terms terms = searchResponse.getAggregations().get("my_fieldA");
for (Terms.Bucket entry : terms.getBuckets()) {
    String fieldAValue = entry.getKey().toString();

    Sum sumagg = entry.getAggregations().get("my_sum_fieldB");
    double fieldValue = sumagg.getValue();
}
复制代码

 

代码在GitHub上:https://github.com/luxiaoxun/Code4Java

 


    本文转自阿凡卢博客园博客,原文链接:http://www.cnblogs.com/luxiaoxun/p/6826211.html,如需转载请自行联系原作者


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
来聊一聊 ElasticSearch 最新版的 Java 客户端
来聊一聊 ElasticSearch 最新版的 Java 客户端
15 0
实践教程之采集PolarDB-X SQL日志到ElasticSearch
PolarDB-X 为了方便用户体验,提供了免费的实验环境,您可以在实验环境里体验 PolarDB-X 的安装部署和各种内核特性。除了免费的实验,PolarDB-X 也提供免费的视频课程,手把手教你玩转 PolarDB-X 分布式数据库。本期实验将指导您如何采集PolarDB-X SQL日志到ElasticSearch。
7830 0
ElasticSearch进阶:一文全览各种ES查询在Java中的实现(下)
ElasticSearch进阶:一文全览各种ES查询在Java中的实现(下)
37 0
ElasticSearch进阶:一文全览各种ES查询在Java中的实现(上)
ElasticSearch进阶:一文全览各种ES查询在Java中的实现(上)
75 0
十四、.net core(.NET 6)搭建ElasticSearch(ES)系列之给ElasticSearch添加SQL插件和浏览器插件
给ES添加SQL插件的方法:下载SQL插件地址:https://github.com/NLPchina/elasticsearch-sql当前最新的是7.12版本,我的ES是7.13版本,暂且将就用一下,也许能用呢?
73 0
玩转 Elasticsearch 的 SQL 功能
  最近发布的 Elasticsearch 6.3 包含了大家期待已久的 SQL 特性,今天给大家介绍一下具体的使用方法。   首先看看接口的支持情况   目前支持的 SQL 只能进行数据的查询只读操作,不能进行数据的修改,所以我们的数据插入还是要走之前的常规索引接口。
253 0
且慢!听说你线上环境准备选型 Elasticsearch SQL 了?
1、由两个线上问题说开去 问题1:用 Elasticsearch 做宽表的报表查询,我们打算用 jdbc 的方式访问,方便开发(使用sql),这种方式还是用JAVA封装的方法好。有没有坑? 问题2:Elasticsearch 可以用脚本写sql ,像执行hive脚本一样吗? 2、问题解读 问题 1 涉及选型问题,我们会条分缕析解读。 问题 2 涉及 Elasticsearch 是否支持 SQL 功能。 3、Elasticsearch SQL 到底指什么? 一句话:能像关系型数据库如 Mysql 中使用 SQL 方式一样方便的实现 Elasticsearch 增、删、改、查(尤其是检索、
493 0
DSL的诞生 | 复杂sql转成Elasticsearch DSL深入详解
源自死磕ElasticsearchQQ群(626036393)中的一个问题: 问题如下: where (position=ES or work=ES or content=ES) and academic=本科 and (city=北京 or city=深圳) 1 怎么构建ES的查询条件? 我的问题拆解与实现如下:
1238 0
Elasticsearch 如何实现 SQL 语句中 Group By 和 Limit 的功能
给大家讲一条常见的 SQL 语句如何用 Elasticsearch 的查询语言实现。
2696 0
【最佳实践】使用 Elasticsearch SQL 实现数据查询
如何使用 Elasticsearch SQL 来对我们的数据进行查询。
4131 0
+关注
橘子红了呐
文章
问答
视频
文章排行榜
最热
最新
相关电子书
更多
Elasticsearch全观测技术解析与应用(构建日志、指标、APM统一观测平台)
立即下载
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
相关实验场景
更多