【持续记录】使用elasticSearch支持的sql语句进行查询

爱被打了一巴掌 2022-11-13 10:23 585阅读 0赞

# ElasticSearch使用SQL语句查询 #

本文环境：  
（1）ElasticSearch版本：7.11.2  
（2）es服务所在操作系统CentOS7.6；  
（3）开发语言：Java（jdk8）；  
（4）查询的是单个索引，不存在跨索引查询。  
（5）本文还会涉及到mybatis知识点。  
（6）2021年4月记录，由于es更新较快，本文记录的问题随着es的更新可能不再出现，还望注意。  
为避免歧义，规定：本文中elasticsearch支持的SQL语句简写为“esSQL”，注意不是DSL。MySQL、Oracle支持的SQL为“标准SQL”。  
本文用于记录esSQL和标准SQL的差异，mybatis的SQL语句如何转换为es查询，如mybatis内的SQL语句转换为对应的esSQL可能出现的问题。

## 网址链接 ##

### 官网类 ###

[esSQL支持的函数][esSQL]

### 其它 ###

[bboss][]，支持将DSL请求封装为一个mapper文件。  
[elasticSearch中文文档][elasticSearch]，基于es7.3翻译的中文文档。

## 简单的SQL示例 ##

### 搭建SpringBoot项目 ###

依赖

<dependency>
                <groupId>org.elasticsearch.client</groupId>
                <artifactId>elasticsearch-rest-high-level-client</artifactId>
                <version>7.11.2</version>
            </dependency>
            
            <dependency>
                <groupId>org.elasticsearch.client</groupId>
                <artifactId>elasticsearch-rest-client</artifactId>
                <version>7.11.2</version>
            </dependency>
            <dependency>
                <groupId>org.elasticsearch</groupId>
                <artifactId>elasticsearch</artifactId>
                <version>7.11.2</version>
            </dependency>

或者是SpringBoot集成的es依赖，但是该依赖版本更新不及时。  
需要对结果进行json转换，这里使用Gson：

<dependency>
        <groupId>com.google.code.gson</groupId>
        <artifactId>gson</artifactId>
        <version>2.8.6</version>
    </dependency>

### kibana执行DSL ###

kibana执行的命令示例：

POST /_sql
    {
      "query": "SELECT butcher_name FROM (SELECT butcher_id, butcher_name, butcher_credit_code, DATE_DIFF('dd', MAX(issue_print_date), now()) AS noPrintDays, butcher_butcher_type_name, butcher_legal_person, butcher_legal_mobile, butcher_full_address, MAX(issue_print_date) printDate, butcher_province, butcher_city, butcher_county FROM butcher_issue_index_2 WHERE issue_state = '1' AND issue_print_flag = 1 AND butcher_state = '1' GROUP BY butcher_id, butcher_name, butcher_credit_code, butcher_butcher_type_name, butcher_legal_person, butcher_legal_mobile, butcher_full_address, butcher_province, butcher_city, butcher_county) WHERE noPrintDays >= 7 LIMIT 10",
      "fetch_size": 10
    }

本文重点，之所以记录本文，就是因为esSQL所支持的函数与标准SQL相比有一定的差异，坑多。

## SQL子句 ##

### limit ###

标准SQL支持分页查询，可以指定第几页，每页查询数量，而esSQL只支持每页查询数量，查询下一页，需要根据上一页的查询结果集内的光标作为参数来得到结果。

### group by ###

es严格要求select字段中的非聚合字段必须在group by后面出现。  
例如下面语句在es内执行失败。

select a, sum(b), c from test group by a

如果想成功，必须如下：

select a, sum(b), c from test group by a, c

## SQL函数 ##

### 时间类 ###

#### cast ####

将字符串转换为时间  
（1）日期字符串转换为日期  
esSQL：

CAST('2020-04-05' AS DATE)

（2）时间戳字符串转换为时间戳  
esSQL:

CAST('2020-04-06T00:12:13' AS TIMESTAMP)

或：

'2019-11-10T12:10:00.000Z'::datetime

#### format ####

时间格式化，es支持的年、月、日、时、分、秒分别是YYYY、MM、dd、HH、mm、ss。  
获取时间“2021-04-01 12:13:14”的年月日，期望结果“2021-04-01”，esSQL：

FORMAT(CAST('2020-04-06 00:12:13' AS TIMESTAMP),'YYYY-MM-dd')

标准SQL使用的是，支持的年、月、日、时分秒是%Y、%m、%d、%T。  
获取时间“2021-04-01 12:13:14”的年月日，期望结果“2021-04-01”，标准SQL：

DATE_FORMAT('2020-04-06 00:12:13','%Y-%m-%d %T')

#### datediff ####

计算时间差，esSQL：

DATE_DIFF(string_exp,datetime_exp,datetime_exp)

这里以计算相差天数为例，es支持的datediff函数用第三个表达式减去第二个表达式。  
注意标准SQL的datediff函数是第一个表达式减去第二个表达式。  
标准SQL：

select datediff('2021-02-02 00:00:00','2021-02-01 00:00:00');

结果：1  
esSQL：

select datediff('dd', '2021-02-02T00:00:00'::datetime, '2021-02-01T00:00:00'::datetime)

结果：-1

### 字符串类 ###

#### locate函数和instr函数 ####

标准SQL的instr在esSQL中是locate函数。

### 状态控制 ###

条件选择和分支判断，类似于MyBatis的if和choose。

#### if和iif ####

标准SQL中条件选择使用if关键字，esSQL使用iif。二者语法和结果一致。  
esSQL:

IIF(2>1,12,23)

结果12  
mybatis的SQL：

<if test="1 != areaLevel">
                        AND month = 10
                    </if>

等同的esSQL:

AND IIF(1 != areaLevel,month = 10, 1=1)

#### choose和case ####

MyBatis支持的动态SQL内使用choose…when…when…otherwise…，esSQL相同作用的是case…when…when…else…end…。

[esSQL]: https://www.elastic.co/guide/en/elasticsearch/reference/7.11/sql-functions.html
[bboss]: https://esdoc.bbossgroups.com/
[elasticSearch]: https://learnku.com/docs/elasticsearch73/7.3