为什么查询ElasticSearch用SQL代替DSL
这篇文章主要讲解了“为什么查询ElasticSearch用SQL代替DSL”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“为什么查询ElasticSearch用SQL代替DSL”吧!
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.000ZVernorVinge|AFireUpontheDeep|613|1992-06-01T00:00:00.000ZFrankHerbert|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|1078185600000VernorVinge|AFireUpontheDeep|613|707356800000FrankHerbert|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的执行顺序为:
鸿蒙官方战略合作共建——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 可列举出支持的函数名称和所属类型。
SHOWFUNCTIONS;name|type-----------------+---------------AVG|AGGREGATECOUNT|AGGREGATEFIRST|AGGREGATEFIRST_VALUE|AGGREGATELAST|AGGREGATELAST_VALUE|AGGREGATEMAX|AGGREGATEMIN|AGGREGATESUM|AGGREGATE........
我们主要看下聚合,分组,全文搜索相关的常用函数。
全文匹配函数
MATCH:相当于DSL中的match and 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.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.7164764FrankHerbert|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:00Z1.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_namenumeric_interval--数字的区间值)HISTOGRAM(date_exp,--date/time表达式,通常是一个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|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中返回。
感谢各位的阅读,以上就是“为什么查询ElasticSearch用SQL代替DSL”的内容了,经过本文的学习后,相信大家对为什么查询ElasticSearch用SQL代替DSL这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。