Elasticsearch官方已支持SQL查询，用起来贼方便！

谁借莪１个温暖的怀抱￠ 2022-12-27 09:11 38阅读 0赞

![format_png][]

> 平时使用Elasticsearch的时候，偶尔会在Kibana中使用Query DSL来查询数据。每次要用到Query DSL时都基本忘光了，需要重新在回顾一遍，最近发现Elasticsearch已经支持SQL查询了（6.3版本以后），整理了下其用法，希望对大家有所帮助！

## 简介 ##

Elasticsearch SQL是一个X-Pack组件，它允许针对Elasticsearch实时执行类似SQL的查询。无论使用REST接口，命令行还是JDBC，任何客户端都可以使用SQL对Elasticsearch中的数据进行原生搜索和聚合数据。可以将Elasticsearch SQL看作是一种翻译器，它可以将SQL翻译成Query DSL。

Elasticsearch SQL具有如下特性：

*  原生支持：Elasticsearch SQL是专门为Elasticsearch打造的。
 *  没有额外的零件：无需其他硬件，处理器，运行环境或依赖库即可查询Elasticsearch，Elasticsearch SQL直接在Elasticsearch内部运行。
 *  轻巧高效：Elasticsearch SQL并未抽象化其搜索功能，相反的它拥抱并接受了SQL来实现全文搜索，以简洁的方式实时运行全文搜索。

## 学前准备 ##

学习之前我们需要先对Elasticsearch有所了解，并安装好Elasticsearch和Kibana，这里安装的是7.6.2版本。

安装完成后在Kibana中导入测试数据，数据地址：https://github.com/macrozheng/mall-learning/blob/master/document/json/accounts.json

直接在Kibana的Dev Tools中运行如下命令即可：

![format_png 1][]

## 第一个SQL查询 ##

我们使用SQL来查询下前10条记录，可以通过`format`参数控制返回结果的格式，txt表示文本格式，看起来更直观点，默认为json格式。

在Kibana的Console中输入如下命令：

POST /_sql?format=txt
    {
      "query": "SELECT account_number,address,age,balance FROM account LIMIT 10"
    }

查询结果显示如下。

![format_png 2][]

## 将SQL转化为DSL ##

当我们需要使用Query DSL时，也可以先使用SQL来查询，然后通过Translate API转换即可。

例如我们翻译以下查询语句：

POST /_sql/translate
    {
      "query": "SELECT account_number,address,age,balance FROM account WHERE age>32 LIMIT 10"
    }

最终获取到Query DSL结果如下。

![format_png 3][]

## SQL和DSL混合使用 ##

我们还可以将SQL和Query DSL混合使用，比如使用Query DSL来设置过滤条件。

例如查询age在30-35之间的记录，可以使用如下查询语句：

POST /_sql?format=txt
    {
      "query": "SELECT account_number,address,age,balance FROM account",
          "filter": {
            "range": {
                "age": {
                    "gte" : 30,
                    "lte" : 35
                }
            }
        },
        "fetch_size": 10
    }

查询结果展示如下：

![format_png 4][]

## SQL和ES对应关系 ##

<table> 
 <thead> 
  <tr> 
   <th>SQL</th> 
   <th>ES</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>column</td> 
   <td>field</td> 
   <td>数据库中表的字段与ES中的属性对应</td> 
  </tr> 
  <tr> 
   <td>row</td> 
   <td>document</td> 
   <td>数据库表中的行记录与ES中的文档对应</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>index</td> 
   <td>数据库中的表与ES中的索引对应</td> 
  </tr> 
 </tbody> 
</table>

## 常用SQL操作 ##

### 语法 ###

在ES中使用SQL查询的语法与在数据库中使用基本一致，具体格式如下：

SELECT select_expr [, ...]
    [ FROM table_name ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition]
    [ ORDER BY expression [ ASC | DESC ] [, ...] ]
    [ LIMIT [ count ] ]
    [ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ]

### WHERE ###

可以使用`WHERE`语句设置查询条件，比如查询state字段为VA的记录，查询语句如下。

POST /_sql?format=txt
    {
      "query": "SELECT account_number,address,age,balance,state FROM account WHERE state='VA' LIMIT 10 "
    }

查询结果展示如下：

![format_png 5][]

### GROUP BY ###

我们可以使用`GROUP BY`语句对数据进行分组，统计出分组记录数量，最大age和平均balance等信息，查询语句如下。

POST /_sql?format=txt
    {
      "query": "SELECT state,COUNT(*),MAX(age),AVG(balance) FROM account GROUP BY state LIMIT 10"
    }

![format_png 6][]

### HAVING ###

我们可以使用`HAVING`语句对分组数据进行二次筛选，比如筛选分组记录数量大于15的信息，查询语句如下。

POST /_sql?format=txt
    {
      "query": "SELECT state,COUNT(*),MAX(age),AVG(balance) FROM account GROUP BY state HAVING COUNT(*)>15 LIMIT 10"
    }

![format_png 7][]

### ORDER BY ###

我们可以使用`ORDER BY`语句对数据进行排序，比如按照balance字段从高到低排序，查询语句如下。

POST /_sql?format=txt
    {
      "query": "SELECT account_number,address,age,balance,state FROM account ORDER BY balance DESC LIMIT 10 "
    }

![format_png 8][]

### DESCRIBE ###

我们可以使用`DESCRIBE`语句查看表（ES中为索引）中有哪些字段，比如查看account表的字段，查询语句如下。

POST /_sql?format=txt
    {
      "query": "DESCRIBE account"
    }

![format_png 9][]

### SHOW TABLES ###

我们可以使用`SHOW TABLES`查看所有的表（ES中为索引）。

POST /_sql?format=txt
    {
      "query": "SHOW TABLES"
    }

![format_png 10][]

## 支持的函数 ##

使用SQL查询ES中的数据，不仅可以使用一些SQL中的函数，还可以使用一些ES中特有的函数。

### 查询支持的函数 ###

我们可以使用`SHOW FUNCTIONS`语句查看所有支持的函数，比如搜索所有带有`DATE`字段的函数可以使用如下语句。

POST /_sql?format=txt
    {
      "query": "SHOW FUNCTIONS LIKE '%DATE%'"
    }

![format_png 11][]

### 全文搜索函数 ###

全文搜索函数是ES中特有的，当使用`MATCH`或`QUERY`函数时，会启用全文搜索功能，`SCORE`函数可以用来统计搜索评分。

#### MATCH() ####

使用`MATCH`函数查询address中包含Street的记录。

POST /_sql?format=txt
    {
      "query": "SELECT account_number,address,age,balance,SCORE() FROM account WHERE MATCH(address,'Street') LIMIT 10"
    }

![format_png 12][]

#### QUERY() ####

使用`QUERY`函数查询address中包含Street的记录。

POST /_sql?format=txt
    {
      "query": "SELECT account_number,address,age,balance,SCORE() FROM account WHERE QUERY('address:Street') LIMIT 10"
    }

![format_png 13][]

## SQL CLI ##

如果你不想使用Kibana来使用ES SQL的话，也可以使用ES自带的SQL CLI来查询，该命令位于ES的bin目录下。

使用如下命令启动SQL CLI：

elasticsearch-sql-cli http://localhost:9200

![format_png 14][]

然后直接输入SQL命令即可查询了，注意要加分号。

SELECT account_number,address,age,balance FROM account LIMIT 10;

![format_png 15][]

## 局限性 ##

使用SQL查询ES有一定的局限性，没有原生的Query DSL那么强大，对于嵌套属性和某些函数的支持并不怎么好，但是平时用来查询下数据基本够用了。

## 参考资料 ##

官方文档：https://www.elastic.co/guide/en/elasticsearch/reference/7.6/xpack-sql.html

--------------------

推荐阅读：

*  [写代码有这16个好习惯，可以减少80%非业务的bug][16_80_bug]  
    
 *  [年轻人！不讲武德！][Link 1]  
    
 *  [这几款 IDEA 主题也太好看了吧！！][IDEA]  
    
 *  [15 张图， 把TCP/IP 讲得一清二楚！][15 _ _TCP_IP]

**![format_png 16][]喜欢我可以给我设为星标哦![format_png 16][]**

![format_png 17][]

![format_png 18][]

**好文章，我****“在看”**

![format_png 19][]

[format_png]: /images/20221120/208dcdf239614a9c9573a03ca3b7325a.png
[format_png 1]: /images/20221120/bfb324e0279e42d39223cb9f9190c39f.png
[format_png 2]: /images/20221120/b124eb1987a442ed8fe612cd715a009a.png
[format_png 3]: /images/20221120/120ebece72aa49adb1b15465a848ba48.png
[format_png 4]: /images/20221120/78e6b0be1c9c4be1853e34dc6d73a1b4.png
[format_png 5]: /images/20221120/9a962f6952e742839f700d4be7c32b66.png
[format_png 6]: /images/20221120/5d654ffbaa2343049b3c9cd2a2022ccb.png
[format_png 7]: /images/20221120/fa2ccac250374c7e925b12a82e3f9a16.png
[format_png 8]: /images/20221120/ef6f308932ce41bf912d51a06a1d4f1f.png
[format_png 9]: /images/20221120/48327b62231e44a78895f2a34da62735.png
[format_png 10]: /images/20221120/ca489b0d9e4d45dc8f95ce25303703c5.png
[format_png 11]: /images/20221120/93095eac424d4e78921f25e619a1565b.png
[format_png 12]: /images/20221120/521c16cc33094bd68dec16b0f6ce4fd4.png
[format_png 13]: /images/20221120/5b40ad8592db417d8648267d93673f2a.png
[format_png 14]: /images/20221120/853a5e10778f4ebe9755c3b159450561.png
[format_png 15]: /images/20221120/2b826946502146df93324f43db882a71.png
[16_80_bug]: http://mp.weixin.qq.com/s?__biz=Mzg3MjA4MTExMw%3D%3D&chksm=cef604ccf9818dda8885bb8889f3b84cc3363b97f69bcf065a8959f623f3ffc2f8d5fff0e16f&idx=2&mid=2247497594&scene=21&sn=f601bee0a2516b248c48e4e2513e93ce#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=Mzg3MjA4MTExMw%3D%3D&chksm=cef60487f9818d916c2aafee371e1f936524492e68a4ca07ecdbd0ec68ff0faeebacd145fc2d&idx=1&mid=2247497521&scene=21&sn=004e83d2136f6dbc43de4a1f39a08f07#wechat_redirect
[IDEA]: http://mp.weixin.qq.com/s?__biz=Mzg3MjA4MTExMw%3D%3D&chksm=cef60487f9818d9163ac86eec7fe7cbff360111936b8b5ed7db549f2c0c292ac3f3e80ee186d&idx=2&mid=2247497521&scene=21&sn=ac33c275174671d9fd27b167e63ab00b#wechat_redirect
[15 _ _TCP_IP]: http://mp.weixin.qq.com/s?__biz=Mzg3MjA4MTExMw%3D%3D&chksm=cef604a5f9818db3ee48b1e5d1d10e6ecc3604fc796e5d819a0ab2d7c571d968c1f858b82fe2&idx=2&mid=2247497491&scene=21&sn=2d798add1ee4f6ab9f13ff84e843deac#wechat_redirect
[format_png 16]: /images/20221120/8d155e21e3104f6785e54d7aa57a7d99.png
[format_png 17]: /images/20221120/a34fb569917b40f9846a81204df1acea.png
[format_png 18]: /images/20221120/45e7785a295e4fb3a359c743bdd4dfac.png
[format_png 19]: /images/20221120/8c3be7111aed47f0977ff44c16d95d6d.png