查询ElasticSearch：用SQL代替DSL-51CTO.COM
51CTO首页 内容精选 博客 学堂
精培 企业培训 CTO训练营 开源基础软件社区 LeaTech全球CTO领导力峰会 公众号矩阵 移动端 注册/登录 博客 论坛 免费课程 课程排行 直播课 软考学堂 精品班 厂商认证 IT技术 2022年软考 PMP项目管理 在线学习 企业服务 CTO训练营 技术经理研习营 LeaTech峰会 文章 资源 问答 开源课堂 专栏 直播 51CTO 开源基础软件社区 51CTO技术栈 51CTO官微 51CTO学堂 51CTO博客 CTO训练营 开源基础软件社区订阅号 51CTO学堂APP 51CTO学堂企业版APP 开源基础软件社区视频号 内容精选视频话题技术期刊技术大会 社区编辑申请 我关注的话题 我收藏的文章 账号设置 退出
注册/登录 查询ElasticSearch：用SQL代替DSL 作者： Monica2333 2020-09-28 11:09:54 运维 数据库运维 233酱工作中使用了一点ELK，偶尔使用Kibana拼接ES DSL简直要命。如果你和我一样「熟悉SQL，但不咋会写DSL」 or 「想要用SQL简化查询」，本文会介绍一下官方对ES SQL的支持，希望对你有所帮助～
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 * 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.000Z
SQL 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 |-144720000000
SQL 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的执行顺序为：
获取所有 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.446629
QUERY：相当于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.4169943
SCORE()：返回输入数据和返回数据的相关度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|1
ES 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中返回。
本文转载自微信公众号「 码农知识点」，可以通过以下二维码关注。转载本文请联系 码农知识点公众号。
责任编辑：武晓燕
来源：
码农知识点 SQLDSL 分享到微信 微信扫码分享 分享到微博 相关推荐 Elasticsearch不会DSL没事，来试试SQL吧 Elasticsearch是一个分布式、RESTful风格的搜索和数据分析引擎，能够解决不断涌现出的各种用例，最常见的是作为搜索引擎，提供项目中全文搜索。 2020-12-02 08:31:47 Elasticsear SQL Server使用UNION代替OR提升查询性能的实例 本文我们首先介绍了一个查询的SQL代码段，然后以此为基础对代码进行各种优化，以提高查询的性能，希望本次的介绍能够对您有所帮助。 2011-08-24 11:22:38 SQL ServerUNION代替OR 用SQL查询日 月 周 季 怎么用SQL查询昨天、今天、明天和本周的记录？又怎么用SQL查询一天，三天，一周，一个月，更长一些——一个季度的记录呢？本文中给出了一些方法。 2011-03-18 13:44:44 SQL 微软将用SQL Azure代替SQL Server Services 据Technet报道,微软已经将SQLServices和SQLDataServices(数据服务)品牌更名。此次更名也与微软的云计算规划有关。 2009-07-14 09:37:06 SQL Azure 用并行查询让SQL Server加速运行 在通常情况下，SQLServer数据库在执行查询作业的时候，都是单线程操作的。不够数据库为了应对比较大流量的查询作业，故SQLServer提供了并行查询的功能。 2009-03-24 11:11:19 加速并行查询SQL 教您用SQL语句进行模糊查询 如果需要在数据库中进行模糊查询，用SQL语句能否实现呢？答案是肯定的。下面就将为您介绍用SQL语句进行模糊查询的方法，供您参考。 2010-09-07 09:45:48 SQL语句 在 Linux 上用 dust 代替 du 如果你在Linux命令行上工作，你会熟悉du命令。了解像du这样的命令，可以快速返回磁盘使用情况，是命令行使程序员更有效率的方法之一。然而，如果你正在寻找一种方法来节省更多的时间，使你的生活更加容易，看看dust，它是用Rust重写的du，具有更多的直观性。 2021-06-29 06:39:21 Linuxdust命令du命令 战场物联网：用技术代替士兵 随着物联网的快速发展，军方也有能力开发自己的武器装备，以达到前所未有的科幻般先进程度。那么，战场物联网(IoBT)是如何形成的呢？ 2019-08-16 10:47:19 战场物联网IOT SQL中用函数代替游标的方法 如果要根据更新表的某些字段还要进行计算，经常会采用游标的方法，下面将为您介绍在SQL中如何使用函数代替游标，供您参考。 2010-09-10 16:20:10 SQL函数 使用SQL Server CASE表达式代替动态SQL SQLServer中的CASE语句功能很强大，它可以轻松帮助我们解决SQLServer数据库在进行大量更新查询操作时的效率问题。 2010-11-12 13:20:31 SQL Server 后SQL时代，DBA们不用查询语句用什么？ 数据库技术中应用得最多的是查询语句，那么在后SQL时代，DBA们是不是该放弃查询语句？不用查询语句我们用什么呢？ 2010-07-19 16:54:21 SQL 用Select * 进行SQL查询的七宗罪 本文通过在应用编程中的实际经验，向大家证明使用Selectfromtable进行SQL查询的“七宗罪”。 2019-04-15 09:00:00 SQLOracle数据库 如何在 Linux 上用 fd 代替 find 许多Linux程序员在其工作中每天都在使用find命令。但是find给出的文件系统条目是有限的，如果你要进行大量的find操作，它甚至不是很快速。因此，我更喜欢使用Rust编写的fd命令，因为它提供了合理的默认值，适用于大多数使用情况。 2021-06-24 16:07:36 Linuxfind命令fd命令 Node Sass 弃用，以 Dart Sass 代替 就在今天，Sass官方团队正式宣布Libsass将弃用，以及基于它的NodeSass和SassC，并且建议用户使用DartSass。 2020-10-28 07:03:11 NodeSassDart Sass 用Msmtp代替系统自身Sendmail的技巧 sendmail是一个漏洞奇多、配置超级麻烦的东西，所以很多系统管理员都把它禁用了。但是如此一来，如果crontab脚本执行出错，就只有天知地知了。 2012-09-26 14:03:09 sendmailmsmtp API设计新思维：用流畅接口构造内部DSL 不过普通的API设计方法存在一种天然的陷阱，那就是不管怎样封装，大过程虽然比小过程抽象层次更高，但本质上还是过程，受到过程语义的制约。本文将介绍一种被称为流畅接口(FluentInterface)的内部DSL设计方法。 2011-10-31 13:58:32 API 对存储过程代替SQL语句的讨论 存储过程究竟能否代替SQL语句？有顶存储过程的，也有顶SQL语句的，下文就对其中的一些观点进行了剖析，供您学习参考之用。 2010-09-10 14:09:23 sql查询分页 下文将对sql查询分页问题进行详细的分析研究，供您参考，如果您对sql查询分页方面感兴趣的话，不妨一看。 2010-09-26 15:29:13 sql查询分页 如何在 Linux 上用 SQL 语句来查询 Apache 日志 我们将在这篇文章中着重讲解ApacheHTTPwebserver生成的Apacheaccess日志。通过另类的途径来分析Apacheaccess日志，我们使用的工具是asql。它能够允许使用者使用SQL语句来查询日志，从而通过更加友好的格式展现相同的信息。 2014-12-15 09:59:28 LinuxApache DMZ安装：用PaaS代替虚拟机方式 用平台即服务(PaaS)替代把DMZ应用装到本地虚拟机上的方式，可以提供更为简单和灵活的管理。 2011-08-01 09:52:12 DMZPaaS虚拟机 相似话题 系统运维 7140内容 服务器运维 1397内容 网络运维 2139内容 自动化 1111内容 全部话题 编辑推荐 云原生时代数据库运维体系演进 相关专题 更多 勒索攻击离我们远吗？ Cortex 赋能自动化安全运营 2022-09-29 10:56:57 智算智存 全能担当!中小企业共享存储一站式解决方案 2022-12-22 16:54:11 我收藏的内容 微博 QQ 微信 复制链接 微信扫码分享 51CTO业务 媒体 51CTOCIOAgeHC3iTechplur社区 51CTO博客开源基础软件社区汽车开发者社区教育 51CTO学堂精培企业培训CTO训练营 51CTO学堂 51CTO学堂企业版 51CTO官微 51CTO 关于我们&条款 关于我们 站点地图 网站大事 意见反馈 English 用户协议 隐私协议
北京市海淀区中关村南1条甲1号ECO中科爱克大厦6-7层
北京市公安局海淀分局备案编号：110108002980号
营业执照
京ICP备09067568号
Copyright © 2005-2022 51CTO.COM
京ICP证060544 版权所有 未经许可 请勿转载
友情链接 新浪科技腾讯科技网易科技凤凰科技驱动科技科技行者TechWeb艾瑞网站长之家速途网中国经济新闻网IT之家工联网极客公园236视频会议中国IDC圈企业网D1Net投资界 51CTO技术栈公众号 51CTO技术栈公众号
业务
速览
在线客服 媒体 51CTO CIOAge HC3i 社区 51CTO博客 开源基础软件社区 教育 51CTO学堂 精培 企业培训 CTO训练营