共计 6867 个字符，预计需要花费 18 分钟才能阅读完成。
这篇文章主要讲解了“为什么查询 ElasticSearch 用 SQL 代替 DSL”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“为什么查询 ElasticSearch 用 SQL 代替 DSL”吧！
SQL REST API
在 Kibana Console 中输入：
POST /_sql?format=txt { query : SELECT * FROM library ORDER BY page_count DESC LIMIT 5 }将上述 SQL 替换为你自己的 SQL 语句，即可。返回格式如下：
author | name | page_count | release_date -----------------+--------------------+---------------+------------------------ Peter F. Hamilton|Pandora s Star |768 |2004-03-02T00:00:00.000Z Vernor Vinge |A Fire Upon the Deep|613 |1992-06-01T00:00:00.000Z Frank Herbert |Dune |604 |1965-06-01T00:00:00.000ZSQL CLI
elasticsearch-sql-cli 是安装 ES 时 bin 目录的一个脚本文件，也可单独下载。我们在 ES 目录运行
./bin/elasticsearch-sql-cli https://some.server:9200输入 sql 即可查询
sql SELECT * FROM library WHERE page_count 500 ORDER BY page_count DESC; author | name | page_count | release_date -----------------+--------------------+---------------+--------------- Peter F. Hamilton|Pandora s Star |768 |1078185600000 Vernor Vinge |A Fire Upon the Deep|613 |707356800000 Frank Herbert |Dune |604 |-144720000000SQL To DSL
在 Kibana 输入：
POST /_sql/translate { query : SELECT * FROM library ORDER BY page_count DESC , fetch_size : 10 }即可得到转化后的 DSL query:
{ size : 10, docvalue_fields : [ { field : release_date , format : epoch_millis } ], _source : { includes : [ author , name , page_count ], excludes : [] }, sort : [ { page_count : { order : desc , missing : _first , unmapped_type : short } } ] }因为查询相关的语句已经生成，我们只需要在这个基础上适当修改或不修改就可以愉快使用 DSL 了。
下面我们详细介绍下 ES SQL 支持的 SQL 语句 和 如何避免错误使用。
首先需要了解下 ES SQL 支持的 SQL 语句中，SQL 术语和 ES 术语的对应关系：
ES SQL 的语法支持大多遵循 ANSI SQL 标准，支持的 SQL 语句有 DML 查询和部分 DDL 查询。
DDL 查询如：DESCRIBE table,SHOW COLUMNS IN table 略显鸡肋，我们主要看下对 SELECT,Function 的 DML 查询支持。
SELECT
语法结构如下：
SELECT [TOP [ count ] ] 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 ] [, ...] ) ) ]表示从 0 - N 个表中获取行数据。SQL 的执行顺序为：
鸿蒙官方战略合作共建——HarmonyOS 技术社区
获取所有 FROM 中的关键词，确定表名。
如果有 WHERE 条件，过滤掉所有不符合的行。
如果有 GROUP BY 条件，则分组聚合; 如果有 HAVING 条件，则过滤聚合的结果。
上一步得到的结果经过 select_expr 运算，确定具体返回的数据。
如果有 ORDER BY 条件，会对返回的数据排序。
如果有 LIMIT or TOP 条件，会返回上一步结果的子集。
与常用的 SQL 有两点不同，ES SQL 支持 TOP [count]和 PIVOT (aggregation_expr FOR column IN ( value [ [ AS] alias ] [, …] ) )子句。
TOP [count]：如 SELECT TOP 2 first_name FROM emp 表示最多返回两条数据，不可与 LIMIT 条件共用。
PIVOT 子句会对其聚合条件得到的结果进行行转列，进一步运算。这个我是没用过，不做介绍。
FUNCTION
基于上面的 SQL 我们其实已经能有过滤，聚合，排序，分页功能的 SQL 了。但是我们需要进一步了解 ES SQL 中 FUNCTION 的支持，才能写出丰富的具有全文搜索，聚合，分组功能的 SQL。
使用 SHOW FUNCTIONS 可列举出支持的函数名称和所属类型。
SHOW FUNCTIONS; name | type -----------------+--------------- AVG |AGGREGATE COUNT |AGGREGATE FIRST |AGGREGATE FIRST_VALUE |AGGREGATE LAST |AGGREGATE LAST_VALUE |AGGREGATE MAX |AGGREGATE MIN |AGGREGATE SUM |AGGREGATE ........我们主要看下聚合，分组，全文搜索相关的常用函数。
全文匹配函数
MATCH：相当于 DSL 中的 match and multi_match 查询。
MATCH( field_exp, -- 字段名称 constant_exp, -- 字段的匹配值 [, options]) -- 可选项使用举例：
SELECT author, name FROM library WHERE MATCH(author, frank author | name ---------------+------------------- Frank Herbert |Dune Frank Herbert |Dune Messiah SELECT author, name, SCORE() FROM library WHERE MATCH(author^2,name^5 , frank dune author | name | SCORE() ---------------+-------------------+--------------- Frank Herbert |Dune |11.443176 Frank Herbert |Dune Messiah |9.446629QUERY：相当于 DSL 中的 query_string 查询。
QUERY( constant_exp -- 匹配值表达式 [, options]) -- 可选项使用举例：
SELECT author, name, page_count, SCORE() FROM library WHERE QUERY( _exists_: author AND page_count: 200 AND (name:/star.*/ OR name:duna~) author | name | page_count | SCORE() ------------------+-------------------+---------------+--------------- Frank Herbert |Dune |604 |3.7164764 Frank Herbert |Dune Messiah |331 |3.4169943SCORE()：返回输入数据和返回数据的相关度 relevance.
使用举例：
SELECT SCORE(), * FROM library WHERE MATCH(name, dune) ORDER BY SCORE() DESC; SCORE() | author | name | page_count | release_date ---------------+---------------+-------------------+---------------+-------------------- 2.2886353 |Frank Herbert |Dune |604 |1965-06-01T00:00:00Z 1.8893257 |Frank Herbert |Dune Messiah |331 |1969-10-15T00:00:00Z聚合函数
AVG(numeric_field)：计算数字类型的字段的平均值。
SELECT AVG(salary) AS avg FROM emp;COUNT(expression)：返回输入数据的总数，包括 COUNT()时 field_name 对应的值为 null 的数据。
COUNT(ALL field_name)：返回输入数据的总数，不包括 field_name 对应的值为 null 的数据。
COUNT(DISTINCT field_name)：返回输入数据中 field_name 对应的值不为 null 的总数。
SUM(field_name)：返回输入数据中数字字段 field_name 对应的值的总和。
MIN(field_name)：返回输入数据中数字字段 field_name 对应的值的最小值。
MAX(field_name)：返回输入数据中数字字段 field_name 对应的值的最大值。
分组函数
这里的分组函数是对应 DSL 中的 bucket 分组。
HISTOGRAM：语法如下：
HISTOGRAM( numeric_exp, -- 数字表达式，通常是一个 field_name numeric_interval -- 数字的区间值 ) HISTOGRAM( date_exp, --date/time 表达式，通常是一个 field_name date_time_interval --date/time 的区间值 )如下返回每年 1 月 1 号凌晨出生的数据:
ELECT HISTOGRAM(birth_date, INTERVAL 1 YEAR) AS h, COUNT(*) AS c FROM emp GROUP BY h; h | c ------------------------+--------------- null |10 1952-01-01T00:00:00.000Z|8 1953-01-01T00:00:00.000Z|11 1954-01-01T00:00:00.000Z|8 1955-01-01T00:00:00.000Z|4 1956-01-01T00:00:00.000Z|5 1957-01-01T00:00:00.000Z|4 1958-01-01T00:00:00.000Z|7 1959-01-01T00:00:00.000Z|9 1960-01-01T00:00:00.000Z|8 1961-01-01T00:00:00.000Z|8 1962-01-01T00:00:00.000Z|6 1963-01-01T00:00:00.000Z|7 1964-01-01T00:00:00.000Z|4 1965-01-01T00:00:00.000Z|1ES SQL 局限性
因为 ES SQL 和 ES DSL 在功能上并非完全匹配，官方文档提到的 SQL 局限性有：
大的查询可能抛 ParsingException
在解析阶段，极大的查询会占用过多的内存，在这种情况下，Elasticsearch SQL 引擎将中止解析并抛出错误。
nested 类型字段的表示方法
SQL 中不支持 nested 类型的字段，只能使用
[nested_field_name].[sub_field_name]这种形式来引用内嵌子字段。
使用举例：
SELECT dep.dep_name.keyword FROM test_emp GROUP BY languages;nested 类型字段不能用在 where 和 order by 的 Scalar 函数上
如以下 SQL 都是错误的
SELECT * FROM test_emp WHERE LENGTH(dep.dep_name.keyword) 5; SELECT * FROM test_emp ORDER BY YEAR(dep.start_date);不支持多个 nested 字段的同时查询
如嵌套字段 nested_A 和 nested_B 无法同时使用。
nested 内层字段分页限制
当分页查询有 nested 字段时，分页结果可能不正确。这是因为：ES 中的分页查询发生在 Root nested document 上，而不是它的内层字段上。
keyword 类型的字段不支持 normalizer
不支持数组类型的字段
这是因为在 SQL 中一个 field 只对应一个值，这种情况下我们可以使用上面介绍的 SQL To DSL 的 API 转化为 DSL 语句，用 DSL 查询就好了。
聚合排序的限制
排序字段必须是聚合桶中的字段，ES SQL CLI 突破了这种限制，但上限不能超过 512 行，否则在 sorting 阶段会抛异常。推荐搭配 Limit 子句使用，如：
SELECT * FROM test GROUP BY age ORDER BY COUNT(*) LIMIT 100;聚合排序的排序条件不支持 Scalar 函数或者简单的操作符运算。聚合后的复杂字段 (比如包含聚合函数) 也是不能用在排序条件上的。
以下是错误例子：
SELECT age, ROUND(AVG(salary)) AS avg FROM test GROUP BY age ORDER BY avg; SELECT age, MAX(salary) - MIN(salary) AS diff FROM test GROUP BY age ORDER BY diff;子查询的限制
子查询中包含 GROUP BY or HAVING 或者比 SELECT X FROM (SELECT …) WHERE [simple_condition]这种结构复杂，都是可能执行不成功的。
TIME 数据类型的字段不支持 GROUP BY 条件和 HISTOGRAM 函数
如以下查询是错误的：
SELECT count(*) FROM test GROUP BY CAST(date_created AS TIME); SELECT HISTOGRAM(CAST(birth_date AS TIME), INTERVAL 10 MINUTES) as h, COUNT(*) FROM t GROUP BY h但是将 TIME 类型的字段包装为 Scalar 函数返回是支持 GROUP BY 的，如：
SELECT count(*) FROM test GROUP BY MINUTE((CAST(date_created AS TIME));返回字段的限制
如果一个字段不在 source 中存储，是无法查询到的。keyword, date, scaled_float, geo_point, geo_shape 这些类型的字段不受这种限制，因为他们不是从_source 中返回，而是从 docvalue_fields 中返回。
感谢各位的阅读，以上就是“为什么查询 ElasticSearch 用 SQL 代替 DSL”的内容了，经过本文的学习后，相信大家对为什么查询 ElasticSearch 用 SQL 代替 DSL 这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
