使用SQL替代DSL操作ES

最新推荐文章于 2024-07-25 18:15:00 发布

强哥叨逼叨

最新推荐文章于 2024-07-25 18:15:00 发布

阅读量1.5k

点赞数 3

分类专栏： es 文章标签： es sql

本文链接：https://blog.csdn.net/seanxwq/article/details/114357966

版权

es 专栏收录该内容

8 篇文章 2 订阅

订阅专栏

有接触过大数据BI框架的同学应该都有类似的需求：项目需要对接不同类型的数据源（如：MYSQL、ES、HIVE等），并提供一个数据视图（View）用于用户编写 SQL，并将SQL执行结果转为对应报表项。类似如下：

需要实现对不同数据源都能使用基础的SQL进行操作的功能，以此来减少用户的使用和学习成本。

但是我们都知道，ES操作数据库都是使用其自己的DSL语句，而不是标准的SQL语句。因此就需要找一种解决方案，来实现：使用SQL替代DSL操作ES。

一个人的力量总是有限的，为了不重复造轮子，最好的办法就是去调研一下当前已有的开源解决方案。

经过强哥不懈的查找，有如下几个开源方案：

NLPchina提供的elasticsearch-sql
X-Pack-Sql
OpenDistro for Elasticsearch SQL
iamazy.elasticsearch

它们对应的github地址如下：

https://github.com/NLPchina/elasticsearch-sql
https://github.com/elastic/elasticsearch/tree/master/x-pack/plugin/sql
https://github.com/opendistro-for-elasticsearch/sql
https://github.com/iamazy/elasticsearch-sql

那么怎么找出最适合的呢？

NLPchina提供的elasticsearch-sql

NLPchina（中国自然语言处理开源组织）提供的elasticsearch-sql应该是建项目比较早的，也实现了许多sql操作ES的功能，不过项目目前已经废弃不再更新了：

虽然是废弃了，不过在它兴盛之初，还是有很多开源BI框架使用它来实现SQL操作ES的，例如Davinci。从上面描述中可以看出，NLPchina推荐我们可参考X-Pack-Sql或OpenDistro for Elasticsearch SQL。这两个是相对来说更官方的实现。

X-Pack-Sql

官方ES默认自带X-Pack安装，GitHub获Start也是这四个中最多的（55.9k），功能也比较齐全，不过如果需要使用X-Pack的所有功能，需要付费，否则只有30天的试用期，其GitHub项目上也没有过多的介绍这一项目内容：

OpenDistro for Elasticsearch SQL

OpenDistro for Elasticsearch是AWS公司领头从官方ES开源项目搞出来的一个符合Apache 2.0-licensed协议的开源ES。AWS为什么要出这么一个ES以及为此导致的和Elastic公司的冲突可以见今天的另一篇推文。

OpenDistro for Elasticsearch SQL虽然是后起之秀，于NLPchina提供的elasticsearch-sql的基础上开发。不过，由于其符合Apache 2.0-licensed协议，所以绝对开源。在开源力量的贡献下，目前已经实现了很多基本的SQl功能。且可通过插件的方式直接作用在Elastic公司的官方标准ES上。

iamazy.elasticsearch

非官方实现，由国内大神使用antlr4开发的一个SQL解释器。原理就是将基础SQL通过antlr4转换成标准的ES DSL语句，之后就可以继续使用常规的方式（如rest-high-level）操作ES，而开发人员只需要编写SQL即可。这种实现方式不需要为ES安装插件，最简单方便，且不受ES版本的限制。

经过上面的简单介绍，强哥在选型的时候，最后选择了OpenDistro for Elasticsearch SQL。

首先：白嫖最香，其次OpenDistro for Elasticsearch SQL功能齐全，且更官方开源。而iamazy.elasticsearch毕竟还是个人开源项目，社区不够活跃且不稳定，虽然不受ES版本限制，但是iamazy.elasticsearch目前还不支持Having等语句，且部分标准SQL的功能iamazy.elasticsearch需要以不标准的SQL来实现。比如它的聚合语句是这样的：

select * from apple aggregate by cardinality(productName)

这就让人有点别扭了，难不成写SQL还要再学一套写SQL的语法？

所以，终上所述，强哥最后选择了OpenDistro for Elasticsearch SQL。而对于iamazy.elasticsearch这里就暂时不过多说明了，有兴趣的可以去对应的GitHub地址看看。

接下来就介绍OpenDistro for Elasticsearch SQL的使用吧。

首先，从ES官网下载ES并安装，从下面的地址可以下载到想要的ES版本：

https://www.elastic.co/cn/downloads/past-releases#elasticsearch

下载后直接安装即可。

然后是下载Kibana，当然也可不用Kibana，强哥这里就是多介绍下，地址如下：

https://www.elastic.co/cn/downloads/past-releases#kibana

版本需要和ES版本一致。

注意，上面的ES和Kibana都是Elastic公司官网下的，而不是AWS开源的OpenDistro for Elasticsearch。毕竟，当前业内使用Elastic公司的官方ES还是占多数。所以，强哥这里就只介绍OpenDistro for Elasticsearch SQL以插件的方式结合正统的ES。而如果你用的就是OpenDistro for Elasticsearch，那么不需要再下载插件，便可以直接使用OpenDistro for Elasticsearch SQL。

那么OpenDistro for Elasticsearch SQL 插件的下载地址在哪呢？

当然是在OpenDistro for Elasticsearch的官网啦：

https://opendistro.github.io/for-elasticsearch-docs/docs/install/plugins/#sql

注意：OpenDistro for Elasticsearch SQL 插件的版本必须和ES的版本保持一致，否则在使用的时候，会报版本不一致的错误。

下载下来后，直接在ES的安装目录下执行如下命令安装插件：

bin/elasticsearch-plugin install file:///data/pack/opendistro_sql-x.x.x.zip

执行完后，便成功安装上插件了，是不是很简单，同时对应的Kibana也便支持_opendistro/sql命令啦。

效果如下：

哈哈，关联语句都可以查，是不是很香。当前OpenDistro for Elasticsearch SQL 已经支持的基础SQL语句如下：

SQL Select
SQL Delete
SQL Where
SQL Order By
SQL Group By
SQL Having
SQL Inner Join
SQL Left Join
SQL Show
SQL Describe
SQL AND & OR
SQL Like
SQL COUNT distinct
SQL In
SQL Between
SQL Aliases
SQL Not Null
SQL(ES) Date
SQL avg()
SQL count()
SQL max()
SQL min()
SQL sum()
SQL Nulls
SQL isnull()
SQL floor
SQL trim
SQL log
SQL log10
SQL substring
SQL round
SQL sqrt
SQL concat_ws
SQL union and minus

基本上是够用了。

当然，光是安了插件肯定还是不够的，我们怎么在代码中直接使用SQL来操作ES的数据勒？

那就需要OpenDistro for Elasticsearch SQL-JDBC登场啦~

相关GitHub地址如下：

https://github.com/opendistro-for-elasticsearch/sql-jdbc

项目中导入MAVEN包：

<dependency>
  <groupId>com.amazon.opendistroforelasticsearch.client</groupId>  
  <artifactId>opendistro-sql-jdbc</artifactId>
  <version>1.13.0.0</version>
</dependency>

opendistro-sql-jdbc的版本强哥试过，可以和ES版本不一致。

接着，直接敲一个JDBC的单测试试吧：

public static void main(String[] args) throws Exception{
    String url = "jdbc:elasticsearch://http://localhost:9200";
    Connection con = DriverManager.getConnection(url);
    Statement stmt = con.createStatement();
    String sql = "SELECT COUNT(*) m, sum(time_create) as t FROM ad GROUP BY level  HAVING m >= 3  ORDER BY time_create DESC";
    // 执行查询
    ResultSet rs = stmt.executeQuery(sql);
      while (rs.next()){
        System.out.println(rs.getString("t"));
    }
    con.close();
}

代码中：url="jdbc:elasticsearch:……"为固定写法，JDBC以此识别出需要使用opendistro-sql-jdbc连接数据源。上述执行结果如下：

4
6.458618234064E12
3
4.843963675548E12

哈哈，也是比较简单的。

既然JDBC可以的话，那么结合Spring-Data，使用jdbcTemplate操作ES当然一样可以啦，代码如下:


@Autowired
private JdbcTemplate jdbcTemplate;

@GetMapping("/query")
public List<Map<String, Object>> queryAll() {
    String sql = "select * from ad";
    //执行sql语句
    List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
    return maps;
}

结果如下：