使用SQL查询ES：SpringBoot+Jdbc+Mybatis+Elasticsearch整合方案

1. 前言

ES作为一个搜索工具，寄托于Lucene之上，提供了方便的数据存储和搜索服务，一般的用它来作为网页数据索引以及存储用户画像（即用户标签）数据，可以提供复具有复杂的查询条件的服务。例如在网页索引中，通过倒排的方式索引的方式，对文档进行分词存储，可以很快的定位关键字所在的文档，从而达到毫秒级的搜索效率；而在用户画像存储中，ES既可以作为标签宽表，提供类似HIVE宽表的特性，又可以达到传统关系型数据库或者HBase的实时查询的要求，所以在一般的用户画像存储中也是不二之选。

ES是一个搜索工具，它提供的搜索查询方式是ES特有的查询语法：

Elasticsearch provides a full Query DSL (Domain Specific Language) based on JSON to define queries

语法参考：https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl.html

使用过的ES的同学都知道，ES提供的查询方式还是比较麻烦的，它可以通过两种方式来查询：

• 通过transport api的方式，在Java代码里组装查询条件；
• 通过构建出一个JSON，通过rest api的方式进行查询

无论哪一种方法都需要熟悉ES DSL语法，学习成本还是比较高的。当然这种方式很灵活，有很高的自由度，可以最大限度的利用ES自身的功能和性能。

除了上面的方式外，还有一种更方便的方式来查询：使用SQL来查询ES数据，就像查询Mysql或者Oracle一样简单。

2.使用SQL查询ES数据的方案选择

在数据查询和分析方面，最合适的语言就是SQL了（没有之一），像Hive有hql的支持，HBase有Phoenix的支持，所以ES应该也慢慢开始往SQL方向靠拢了，高版本的ES中已经内置了x-pack，x-pack中就有对ES sql的支持。

虽说x-pack是官方内置的，但是它要钱啊，虽然不知道具体价格，虽然有一个月的试用期，虽然可以破解，但是，不是免费的总是用着不舒服，也不能拿到源码研究研究。所以本次使用的不是x-pack来实现sql查询，而是用另一个国产的开源工具：

https://github.com/NLPchina/elasticsearch-sql

和x-pack一样，elasticsearch-sql也是作为ES的一个插件，安装即可使用，需要注意的是不同版本的url路径是有所区别的。

以ES 7.6.0为例：

• 安装插件

  ./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/7.6.0.0/elasticsearch-sql-7.6.0.0.zip
  1

• 使用rest api接口查询

  	GET /_nlpcn/sql
  	select * from test_index where name = 'wxg'
  1
  2

• 返回结果

   	{
      "took": 9,
      "timed_out": false,
      "_shards": {
          "total": 1,
          "successful": 1,
          "skipped": 0,
          "failed": 0
      },
      "hits": {
          "total": {
              "value": 1,
              "relation": "eq"
          },
          "max_score": 0.0,
          "hits": [
              {
                  "_index": "test_index",
                  "_type": "test_index",
                  "_id": "Rn7_QnMBQTA572lIBs2E",
                  "_score": 0.0,
                  "_source": {
                      "name": "wxg",
                      "age": 23,
                      "sex": "男"
                  }
              }
          ]
      }
  }
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30

可以看到，使用起来很方便，只需要将sql作为参数传给接口即可拿到返回值。其实本质还是将sql翻译成ES的json查询语法实现的。

以上是使用rest api调用的方式实现的查询，其实还可以再简便一点。既然使用到了SQL，那么本质上就可以使用Jdbc来查询查询，elasticsearch-sql就实现了JDBC的查询方式，只是似乎还未正式发布出稳定版本，不过也没关系，既然是开源的，能拿到源码，遇到有什么bug的话，自己修复就是了。

既然都能使用JDBC了，那么就一定可以整合到mybatis里了，像查询关系型数据库一样，使用mybatis来查询ES数据，其优点可想而知了，借助mybatis的拼接sql的能力，以及可以其扩展性，能方便的完成很多事情。

除了x-pack和elasticsearch-sql这两个sql支持外，Spring也提供了多种方式来操作ES，并且更接近原生的方式：

https://docs.spring.io/spring-data/elasticsearch/docs/4.0.1.RELEASE/reference/html/#reference

下面主要就来将ES整合到Mybatis里边使用SQL来查询数据。

3.SpringBoot、Mybatis、Elasticsearch的整合

3.1 版本清单

SpringBoot 2.1.5.RELEASE

Elasticsearch 7.6.0

Elasticsearch-Sql 7.6.0

Mybatis Spring Boot Starter 1.3.2

3.2 整合方式

3.2.1 引入依赖(SpringBoot依赖省略)：

 		
	 	
	        org.mybatis.spring.boot
	        mybatis-spring-boot-starter
	        1.3.2
	    
	    
	    
	        org.springframework.boot
	        spring-boot-starter-data-elasticsearch
	        2.1.5.RELEASE
	        
	            
	                org.elasticsearch
	                elasticsearch
	            
	            
	                transport-netty4-client
	                org.elasticsearch.plugin
	            
	        
	    
	    
	        org.elasticsearch.plugin
	        transport-netty4-client
	        7.6.0
	    
	    
	        org.elasticsearch.client
	        transport
	        7.6.0
	    
	
	    
	        org.elasticsearch
	        elasticsearch
	        7.6.0
	    
	
	    
	    
	        org.nlpcn
	        elasticsearch-sql
	        7.6.0.0
	    
	    
	        org.elasticsearch.client
	        x-pack-transport
	        7.6.0
	        
	            
	                log4j-1.2-api
	                org.apache.logging.log4j
	            
	        
	    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

3.2.1 配置ES数据源及Mybatis配置

yml配置：

spring:
  datasource:
    es:
      url: jdbc:elasticsearch://192.168.1.102:9300/
1
2
3
4

数据源配置：

  	@EqualsAndHashCode(callSuper = true)
	@Configuration
	@ConfigurationProperties("spring.datasource.es")
	public class EsConfig extends Properties {
	    private String url;
	
	    public String getUrl() {
	        return this.url;
	    }
	
	    public void setUrl(String url) {
	        this.url = url;
	        this.put("url", url);
	    }
	}

 	@Configuration
	public class DataSourceConfig {
	    @Bean("esDataSource")
	    public DataSource esDataSource(EsConfig esConfig) throws Exception {
	        return ElasticSearchDruidDataSourceFactory.createDataSource(esConfig);
	    }
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

mybatis配置：

@Configuration
public class MybatisConfig {
    @Value("${mybatis.mapper-locations}")
    private String mapperLocations;

    @Bean("sqlSessionFactoryForEs")
    public SqlSessionFactory sqlSessionFactoryForEs(@Qualifier("esDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        return sessionFactoryBean.getObject();
    }
    @Bean("sqlSessionTemplateForEs")
    public SqlSessionTemplate sqlSessionTemplateForEs(@Qualifier("sqlSessionFactoryForEs") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

以上配置完成后就可以正常使用Mybatis查询了。

    
        select * from test_index
    


1
2
3
4
5
6
7
8
9
10
11
12

查询结果：

需要注意的是，ES集群名称，需要使用默认的：elasticsearch，因为使用SpringDataElasticSearch提供的配置方式似乎在7.6版本是无效的，并未读取到配置的集群名称：

  data:
    elasticsearch:
      #这个配置无效
      cluster-name: "my-es"
1
2
3
4

这个问题后面有时间再找找原因和解决方案。

其次，再引入依赖时可能会遇到各种版本不匹配或者冲突问题而包类或者方法不存在，需要一步步调试，找到合适的版本来解决。

总结

不论是x-pack提供的sql查询方式还是elasticsearch-sql提供的sql查询支持，或者是Spring提供的类似于template的方式，都是为了简化ES DSL查询的复杂度，具体使用那种还是需要更具实际情况（性能，已有框架的支持性、版本、学习成本等）进行测试和选择，另外x-pack和elasticsearch-sql的sql好像都是不支持insert和update的，所以新增和插入操作还是需要用rest的方式或者transport的方式来执行。所以sql方式还不是很成熟或者说支持度不是很好，能用原生方式就用原生，一来可控性好，二来版本兼容性也好。

---

隐秘的角落：运动打卡第1天，去锦城湖快走了两圈(5公里)。突然想起来之前学校附近的那个湖，还有些怀念，真是此情可待成追忆， 只是当时已惘然啊