一、前言
平时使用Elasticsearch的时候，偶尔会在Kibana中使用Query DSL来查询数据。每次要用到Query DSL时都基本忘光了，需要重新在回顾一遍，现在Elasticsearch6.3版本以后已经支持SQL查询了，我们可以使用SQL来查询ES数据。
二、Elasticsearch SQL简介
Elasticsearch SQL官网地址，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来实现全文搜索，以简洁的方式实时运行全文搜索。
三、Query DSL与SQL对比
说明：在ES里，我们有一个qmmember文档
3.1 Query DSL写法
我们平时在Kibana的Dev Tools中运行的Query DSL命令如下
GET qmmember/_search
1
3.2 SQL写法
我们使用SQL来查询下前10条记录，可以通过format参数控制返回结果的格式，txt表示文本格式，看起来更直观点，默认为json格式。在Kibana的Console中输入如下命令：
GET /_sql?format=txt
{
"query":"select markId,nickName,fansNums,sex from qmmember where fansNums>=0 and fansNums<=100 limit 10"
}
查询结果显示如下:
2
四、将SQL转化为DSL
当我们需要使用Query DSL时，也可以先使用SQL来查询，然后通过Translate API转换即可。例如我们翻译以下查询语句：
GET /_sql/translate
{
"query":"select markId,nickName,fansNums,sex from qmmember where fansNums>=0 and fansNums<=100 limit 10"
}
最终获取到Query DSL结果如下:
3
五、SQL和DSL混合使用
我们还可以将SQL和Query DSL混合使用，比如使用Query DSL来设置过滤条件。例如查询fansNums在0-100之间的记录，可以使用如下查询语句：
GET /_sql?format=txt
{
"query":"select markId,nickName,sex from qmmember",
"filter": {
"range": {
"fansNums": {
"gte" : 0,
"lte" : 100
}
}
},
"fetch_size": 10
}
4
六、SQL和ES对应关系
5
七、常用SQL操作
7.1 语法
在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 ] [, ...] ) ) ]
7.2 WHERE
可以使用WHERE语句设置查询条件，比如查询state字段为VA的记录，查询语句如下：
POST /_sql?format=txt
{
"query": "SELECT account_number,address,age,balance,state FROM account WHERE state='VA' LIMIT 10 "
}
7.3 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"
}
7.4 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"
}
7.5 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 "
}
7.6 DESCRIBE
我们可以使用DESCRIBE语句查看表（ES中为索引）中有哪些字段，比如查看account表的字段，查询语句如下:
POST /_sql?format=txt
{
"query": "DESCRIBE account"
}
7.7 SHOW TABLES
我们可以使用SHOW TABLES查看所有的表（ES中为索引）
POST /_sql?format=txt
{
"query": "SHOW TABLES"
}
八、支持的函数
使用SQL查询ES中的数据，不仅可以使用一些SQL中的函数，还可以使用一些ES中特有的函数。
8.1 查询支持的函数
我们可以使用SHOW FUNCTIONS语句查看所有支持的函数，比如搜索所有带有DATE字段的函数可以使用如下语句。
POST /_sql?format=txt
{
"query": "SHOW FUNCTIONS LIKE '%DATE%'"
}
8.2 全文搜索函数
全文搜索函数是ES中特有的，当使用MATCH或QUERY函数时，会启用全文搜索功能，SCORE函数可以用来统计搜索评分。
8.2.1 MATCH
使用MATCH函数查询address中包含Street的记录
POST /_sql?format=txt
{
"query": "SELECT account_number,address,age,balance,SCORE() FROM account WHERE MATCH(address,'Street') LIMIT 10"
}
8.2.2 QUERY
使用QUERY函数查询address中包含Street的记录
POST /_sql?format=txt
{
"query": "SELECT account_number,address,age,balance,SCORE() FROM account WHERE QUERY('address:Street') LIMIT 10"
}
九、Elasticsearch SQL目前的一些限制
目前Elasticsearch SQL还存在一些限制。例如：不支持JOIN、不支持较复杂的子查询。所以，有一些相对复杂一些的功能，还得借助于DSL方式来实现。
资料来源：
网友评论