如何用 SQL 代替 DSL 查询 ElasticSearch？

233酱工作中使用了一点ELK，偶尔使用Kibana拼接ES DSL简直要命。如果你和我一样「熟悉SQL，但不咋会写DSL」 or 「想要用SQL简化查询」，本文会介绍一下官方对ES SQL的支持，希望对你有所帮助～

ES7.x版本的x-pack自带ElasticSearch SQL，我们可以直接通过SQL REST API、SQL CLI等方式使用SQL查询。

SQL REST API

在Kibana Console中输入：

POST/_sql?format=txt

{

"query":"SELECT*FROMlibraryORDERBYpage_countDESCLIMIT5"

}

将上述SQL替换为你自己的SQL语句，即可。返回格式如下：

author|name|page_count|release_date

-----------------+--------------------+---------------+------------------------

PeterF.Hamilton|Pandora'sStar|768|2004-03-02T00:00:00.000Z

VernorVinge|AFireUpontheDeep|613|1992-06-01T00:00:00.000Z

FrankHerbert|Dune|604|1965-06-01T00:00:00.000Z

SQL CLI

elasticsearch-sql-cli是安装ES时bin目录的一个脚本文件，也可单独下载。我们在ES目录运行

./bin/elasticsearch-sql-clihttps://some.server:9200

输入sql即可查询

sql>SELECT*FROMlibraryWHEREpage_count>500ORDERBYpage_countDESC;

author|name|page_count|release_date

-----------------+--------------------+---------------+---------------

PeterF.Hamilton|Pandora'sStar|768|1078185600000

VernorVinge|AFireUpontheDeep|613|707356800000

FrankHerbert|Dune|604|-144720000000

SQL To DSL

在Kibana输入：

POST/_sql/translate

{

"query":"SELECT*FROMlibraryORDERBYpage_countDESC",

"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[,...]

[FROMtable_name]

[WHEREcondition]

[GROUPBYgrouping_element[,...]]

[HAVINGcondition]

[ORDERBYexpression[ASC|DESC][,...]]

[LIMIT[count]]

[PIVOT(aggregation_exprFORcolumnIN(value[[AS]alias][,...]))]

表示从0-N个表中获取行数据。SQL的执行顺序为：

获取所有 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 可列举出支持的函数名称和所属类型。

SHOWFUNCTIONS;

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])--可选项

使用举例：

SELECTauthor,nameFROMlibraryWHEREMATCH(author,'frank');

author|name

---------------+-------------------

FrankHerbert|Dune

FrankHerbert|DuneMessiah

SELECTauthor,name,SCORE()FROMlibraryWHEREMATCH('author^2,name^5','frankdune');

author|name|SCORE()

---------------+-------------------+---------------

FrankHerbert|Dune|11.443176

FrankHerbert|DuneMessiah|9.446629

QUERY：相当于DSL中的 query_string 查询。

QUERY(

constant_exp--匹配值表达式

[,options])--可选项

使用举例：

SELECTauthor,name,page_count,SCORE()FROMlibraryWHEREQUERY('_exists_:"author"ANDpage_count:>200AND(name:/star.*/ORname:duna~)');

author|name|page_count|SCORE()

------------------+-------------------+---------------+---------------

FrankHerbert|Dune|604|3.7164764

FrankHerbert|DuneMessiah|331|3.4169943

SCORE()：返回输入数据和返回数据的相关度relevance.

使用举例：

SELECTSCORE(),*FROMlibraryWHEREMATCH(name,'dune')ORDERBYSCORE()DESC;

SCORE()|author|name|page_count|release_date

---------------+---------------+-------------------+---------------+--------------------

2.2886353|FrankHerbert|Dune|604|1965-06-01T00:00:00Z

1.8893257|FrankHerbert|DuneMessiah|331|1969-10-15T00:00:00Z

聚合函数

AVG(numeric_field) ：计算数字类型的字段的平均值。

SELECTAVG(salary)ASavgFROMemp;

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号凌晨出生的数据:

ELECTHISTOGRAM(birth_date,INTERVAL1YEAR)ASh,COUNT(*)AScFROMempGROUPBYh;

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|1

ES SQL局限性

因为ES SQL和ES DSL在功能上并非完全匹配，官方文档提到的SQL局限性有：

大的查询可能抛ParsingException

在解析阶段，极大的查询会占用过多的内存，在这种情况下，Elasticsearch SQL引擎将中止解析并抛出错误。

nested类型字段的表示方法

SQL中不支持nested类型的字段，只能使用

[nested_field_name].[sub_field_name]

这种形式来引用内嵌子字段。

使用举例：

SELECTdep.dep_name.keywordFROMtest_empGROUPBYlanguages;

nested类型字段不能用在where 和 order by 的Scalar函数上

如以下SQL都是错误的

SELECT*FROMtest_empWHERELENGTH(dep.dep_name.keyword)>5;

SELECT*FROMtest_empORDERBYYEAR(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*FROMtestGROUPBYageORDERBYCOUNT(*)LIMIT100;

聚合排序的排序条件不支持Scalar函数或者简单的操作符运算。聚合后的复杂字段(比如包含聚合函数)也是不能用在排序条件上的。

以下是错误例子：

SELECTage,ROUND(AVG(salary))ASavgFROMtestGROUPBYageORDERBYavg;

SELECTage,MAX(salary)-MIN(salary)ASdiffFROMtestGROUPBYageORDERBYdiff;

子查询的限制

子查询中包含GROUP BY or HAVING 或者比SELECT X FROM (SELECT ...) WHERE [simple_condition]这种结构复杂，都是可能执行不成功的。

TIME 数据类型的字段不支持GROUP BY条件和HISTOGRAM函数

如以下查询是错误的：

SELECTcount(*)FROMtestGROUPBYCAST(date_createdASTIME);

SELECTHISTOGRAM(CAST(birth_dateASTIME),INTERVAL'10'MINUTES)ash,COUNT(*)FROMtGROUPBYh

但是将TIME类型的字段包装为Scalar函数返回是支持GROUP BY的，如：

SELECTcount(*)FROMtestGROUPBYMINUTE((CAST(date_createdASTIME));

返回字段的限制

如果一个字段不在source中存储，是无法查询到的。keyword, date, scaled_float, geo_point, geo_shape这些类型的字段不受这种限制，因为他们不是从_source中返回，而是从docvalue_fields中返回。