233酱在工作中用了一点ELK，偶尔用Kibana把ESDSL拼死。如果你和我一样“熟悉SQL，但不擅长写DSL”或者“想用SQL简化查询”，本文将介绍官方对ESSQL的支持，希望对你有所帮助~ES7.x版本x-pack自带ElasticSearchSQL，我们可以直接通过SQLRESTAPI、SQLCLI等方式使用SQL查询。在Kibana控制台输入SQLRESTAPI：POST/_sql?format=txt{"query":"SELECT*FROMlibraryORDERBYpage_countDESCLIMIT5"}，把上面的SQL换成自己的SQL语句，就可以了。返回格式如下：author|name|page_count|release_date----------------+-----------------+--------------+------------------------彼得·汉密尔顿|潘多拉之星|768|2004-03-02T00:00:00.000ZVernorVinge|AFireUpontheDeep|613|1992-06-01T00:00:00.000ZFrankHerbert|Dune|604|1965-06-01T00:00:00.000ZSQLCLIelasticsearch-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|1078185600000VernorVinge|AFireUpontheDeep|613|707356800000FrankHerbert|Dune|604|-144720000000SQL到Kibana中的DSL输入：POST/_sql/translate{"query":"SELECT*FROMlibraryORDERBYpage_countDESC","fetch_size":10}获取转换后的DSLquery:{"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即可。下面详细介绍一下ESSQL支持的SQL语句以及如何避免误用。首先需要了解ESSQL支持的SQL语句中SQL术语和ES术语的对应关系：ESSQL的语法支持大部分遵循ANSISQL标准，支持的SQL语句包括DML查询和部分DDL查询。DDL查询如：DESCRIBE表、SHOWCOLUMNSIN表略显鸡肋，我们主要看DML查询对SELECT和Function的支持。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条件，则过滤掉所有不符合要求的行。如果有GROUPBY条件，分组聚合；如果有HAVING条件，过滤聚合的结果。上一步得到的结果通过select_expr计算，确定具体返回的数据。如果有ORDERBY条件，返回的数据将被排序。如果存在LIMIT或TOP条件，将返回上一步结果的子集。与常用的SQL有两点不同。ESSQL支持TOP[count]和PIVOT(aggregation_exprFORcolumnIN(value[[AS]alias][,...]))子句。TOP[count]：比如SELECTTOP2first_nameFROMemp表示最多返回两条数据，不能和LIMIT条件一起使用。PIVOT子句将对其聚合条件的结果进行行到列的转换，以便进一步操作。这个我没用过，就不介绍了。FUNCTION基于上面的SQL，我们已经可以拥有过滤、聚合、排序、分页功能的SQL了。但是，我们需要进一步了解ESSQL中对FUNCTION的支持，才能编写出具有全文搜索、聚合、分组功能的富SQL。使用SHOWFUNCTIONS列出支持的函数名称及其类型。SHOWFUNCTIONS;名称|类型-------------+------------AVG|AGGREGATECOUNT|AGGREGATEFIRST|AGGREGATEFIRST_VALUE|AGGREGATELAST|AGGREGATELAST_VALUE|AGGREGATEMAX|AGGREGATEMIN|AGGREGATESUM|AGGREGATE......我们主要看聚合、分组和全文搜索相关的常用函数。全文匹配函数MATCH：相当于DSL中的match和multi_match查询。MATCH(field_exp,--字段名constant_exp,--字段的匹配值[,options])--选项使用示例：SELECTauthor,nameFROMlibraryWHEREMATCH(author,'frank');author|name-------------+----------------FrankHerbert|DuneFrankHerbert|DuneMessiahSELECTauthor,name,SCORE()FROMlibraryWHEREMATCH('author^2,name^5','frankdune');author|name|SCORE()----------------+----------------+-------------FrankHerbert|Dune|11.443176FrankHerbert|DuneMessiah|9.446629QUERY：相当于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.7164764FrankHerbert|DuneMessiah|331|3.4169943SCORE()：返回输入数据与返回数据的相关性。示例：SELECTSCORE(),*FROMlibraryWHEREMATCH(name,'dune')ORDERBYSCORE()DESC;SCORE()|author|name|page_count|release_date------------+-------------+------------------+--------------+-------------------2.2886353|FrankHerbert|Dune|604|1965-06-01T00:00:00Z1.8893257|FrankHerbert|DuneMessiah|331|1969-10-15T00:00:00Z聚合函数AVG(numeric_field)：计算数值型字段的平均值。SELECTAVG(salary)ASavgFROMemp;COUNT(expression)：返回输入数据总数，包括COUNT()时field_name对应值为null的数据。COUNT(ALLfield_name)：返回输入数据的总数，不包括值为null的field_name对应的数据。COUNT(DISTINCTfield_name)：返回输入数据中field_name对应的值不为null的总数。SUM(field_name)：返回输入数据中数值字段field_name对应的值的总和。MIN(field_name)：返回输入数据中数值字段field_name对应的value的最小值。MAX(field_name)：返回输入数据中数值型字段field_name对应的value的最大值。分组函数这里的分组函数对应DSL中的bucket分组。HISTOGRAM:语法如下：HISTOGRAM(numeric_exp,--数值表达式，通常是field_namenumeric_interval--number的区间值)HISTOGRAM(date_exp,--日期/时间表达式，通常是field_namedate_time_interval--date/time的区间值)返回每年1月1日凌晨出生的数据如下：ELECTHISTOGRAM(birth_date,INTERVAL1YEAR)ASh,COUNT(*)AScFROMempGROUPBYh;h|c----------------------+----------------null|101952-01-01T00:00:00.000Z|81953-01-01T00:00:00.000Z|111954-01-01T00：00：00.000Z|81955-01-01T00：00：00.000Z|41956-01-01T00：00：00.000Z|51957-01-01T00：00：00.000Z|41958-01-01T00：00：00.000Z|71959-01-01T00:00:00.000Z|91960-01-01T00:00:00.000Z|81961-01-01T00:00:00.000Z|81962-01-01T00:00:00.000Z|61963-01-01T00:00:00.000Z|71964-01-01T00:00:00.000Z|41965-01-01T00:00:00.000Z|1ESSQLlimitations因为ESSQL和ESDSL在功能上不完全匹配，所以在官方文档特点是：查询量大解析阶段可能会抛出ParsingException，非常大的查询会占用太多内存。在这种情况下，ElasticsearchSQL引擎将停止解析并抛出错误。嵌套类型字段的表示方法SQL不支持嵌套类型字段，只能用[nested_field_name].[sub_field_name]的形式来引用嵌套子字段。使用示例：SELECTdep.dep_name.keywordFROMtest_empGROUPBYlanguages；where和orderbyScalar函数不能使用嵌套类型的字段，比如下面的SQL都是错误的SELECT*FROMtest_empWHERELENGTH(dep.dep_name.keyword)>5;SELECT*FROMtest_empORDERBYYEAR(dep.start_date);不支持同时查询多个嵌套字段如nested_A和nested_B不能同时使用。嵌套内字段分页限制当分页查询有嵌套字段时，分页结果可能不正确。这是因为：ES中的分页查询发生在Root嵌套文档上，而不是在其内部字段上。keyword类型字段不支持normalizer字段，不支持array类型字段。这是因为SQL中的一个字段只对应一个值。这种情况下，我们可以使用上面介绍的SQLToDSL的API，将其转换成DSL语句，用DSL查询就可以了。聚合排序的限制排序字段必须是聚合桶中的字段。ESSQLCLI突破了这个限制，但是上限不能超过512行，否则在排序阶段会抛出异常。建议配合Limit子句使用，如：SELECT*FROMtestGROUPBYageORDERBYCOUNT(*)LIMIT100;聚合排序的排序条件不支持标量函数或简单的运算符操作。聚合的复杂字段（比如包含聚合函数）不能用于排序条件。下面是错误示例：SELECTage,ROUND(AVG(salary))ASavgFROMtestGROUPBYageORDERBYavg;SELECTage,MAX(salary)-MIN(salary)ASdiffFROMtestGROUPBYageORDERBYdiff;限制子查询包含GROUPBY或HAVING或大于SELECTXFROM(SELECT...)WHERE[simple_condition]的结构比较复杂，可能会执行失败。TIME数据类型的字段不支持GROUPBY条件和HISTOGRAM函数，例如下面的查询是错误的：SELECTcount(*)FROMtestGROUPBYCAST(date_createdASTIME);SELECTHISTOGRAM(CAST(birth_dateASTIME),INTERVAL'10'MINUTES)ash,COUNT(*)FROMtGROUPBYh但是把TIME类型的字段包装成标量函数返回GROUPBY，如：SELECTcount(*)FROMtestGROUPBYMINUTE((CAST(date_createdASTIME));返回字段的限制如果一个字段没有存储在源中，则无法查询。keyword,date,scaled_float,geo_point,geo_shape这些类型的字段不受此限制，因为它们不是从_source返回的，而是从docvalue_fields返回的。关注下方二维码，转载本文请联系码农知乎点公众号。
