ES中SQL查询详解_斗者_2013_es sql

网络 02-07 5324

一、Elasticsearch SQL简介

Elasticsearch SQL 是一个 X-Pack 组件，它允许对 Elasticsearch 实时执行类似 SQL 的查询。无论是使用 REST 接口、命令行还是 JDBC，任何客户机都可以使用 SQL 在 Elasticsearch 中本地搜索和聚合数据。我们可以把 Elasticsearch SQL 看作一个翻译器，它同时理解 SQL 和 Elasticsearch，并且通过 Elasticsearch 的功能，可以方便地实时读取和处理数据。

官方文档：

根据版本级别的特征支持说明：https://www.elastic.co/cn/subscriptions 免费开源的版本中，已经提供了对Elasticsearch SQL API功能的支持。我们通过对官网链接的版本号修改会发现： 1、6.3版本还能正常访问到sql-overview相关介绍 https://www.elastic.co/guide/en/elasticsearch/reference/6.3/sql-overview.html#sql-introduction

2、切换成6.2后，出现页面不可用的提示。 https://www.elastic.co/guide/en/elasticsearch/reference/6.2/sql-overview.html#sql-introduction 可以初步得出结论，ES6.3之后的版本才提供免费的Elasticsearch SQL的特性。

二、X-Pack 组件说明

2019年5月21日，Elastic官方发布消息： Elastic Stack 新版本6.8.0 和7.1.0的核心安全功能现免费提供。

这意味着用户现在能够对网络流量进行加密、创建和管理用户、定义能够保护索引和集群级别访问权限的角色，并且使用 Spaces 为 Kibana提供全面保护。

免费提供的核心安全功能如下： 1）TLS 功能。可对通信进行加密； 2）文件和原生 Realm。可用于创建和管理用户； 3）基于角色的访问控制。可用于控制用户对集群 API 和索引的访问权限；通过针对 Kibana Spaces 的安全功能，还可允许在Kibana 中实现多租户。

1、X-Pack演变

1）5.X版本之前：没有x-pack，是独立的：security安全,watch查看，alert警告等独立单元。 2）5.X版本：对原本的安全，警告，监视，图形和报告做了一个封装，形成了x-pack。 3）6.3 版本之前：需要额外安装。 4）6.3版本及之后：已经集成在一起发布，无需额外安装，基础安全属于付费黄金版内容。 5）6.8.0和7 .1版本：基础安全免费。

2、X-Pack包含的特性

2018年2月28日X-Pack 特性的所有代码开源，主要包含： Security、Monitoring、Alerting、Graph、Reporting、专门的 APM UI、Canvas、Elasticsearch SQL、Search Profiler、Grok Debugger、Elastic Maps Service zoom levels 以及 Machine Learning。

3、开源!=免费

2019年5月21日免费开放了文章开头的基础安全功能，在这之前的版本都是仅有1个月的适用期限的。如下功能点仍然是收费的。

付费黄金版&白金版提供功能：

审核日志IP 筛选LDAP、PKI*和活动目录身份验证Elasticsearch 令牌服务

付费白金版提供安全功能：

单点登录身份验证（SAML、Kerberos*）基于属性的权限控制字段和文档级别安全性第三方整合（自定义身份验证和授权 Realm）授权 Realm静态数据加密支持三、Elasticsearch SQL入门使用 1、创建索引 PUT /library/book/_bulk?refresh {"index":{"_id": "Leviathan Wakes"}} {"name": "Leviathan Wakes", "author": "James S.A. Corey", "release_date": "2011-06-02", "page_count": 561} {"index":{"_id": "Hyperion"}} {"name": "Hyperion", "author": "Dan Simmons", "release_date": "1989-05-26", "page_count": 482} {"index":{"_id": "Dune"}} {"name": "Dune", "author": "Frank Herbert", "release_date": "1965-06-01", "page_count": 604} 2、使用sql查询索引数据 POST /_sql?format=txt { "query": "SELECT * FROM library WHERE release_date < '2000-01-01'" }

响应结果：

主要有如下格式化类型：其中用的最多的主要是csv、json、text。

JSON：

POST /_sql?format=json { "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 5 }

响应结果：

{ "columns": [ {"name": "author", "type": "text"}, {"name": "name", "type": "text"}, {"name": "page_count", "type": "short"}, {"name": "release_date", "type": "datetime"} ], "rows": [ ["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"], ["Alastair Reynolds", "Revelation Space", 585, "2000-03-15T00:00:00.000Z"], ["James S.A. Corey", "Leviathan Wakes", 561, "2011-06-02T00:00:00.000Z"] ], "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWWWdrRlVfSS1TbDYtcW9lc1FJNmlYdw==:BAFmBmF1dGhvcgFmBG5hbWUBZgpwYWdlX2NvdW50AWYMcmVsZWFzZV9kYXRl+v///w8=" }

CSV：

POST /_sql?format=csv { "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 5 }

响应结果：

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 Alastair Reynolds,Revelation Space,585,2000-03-15T00:00:00.000Z James S.A. Corey,Leviathan Wakes,561,2011-06-02T00:00:00.000Z 4、sql查询中使用filter POST /_sql?format=txt { "query": "SELECT * FROM library ORDER BY page_count DESC", "filter": { "range": { "page_count": { "gte" : 100, "lte" : 200 } } }, "fetch_size": 5 } 5、参数传递

可以直接将参数组装成完整的SQL语句，也可以使用？占位符来传参。

POST /_sql?format=txt { "query": "SELECT YEAR(release_date) AS year FROM library WHERE page_count > ? AND author = ? GROUP BY year HAVING COUNT(*) > ?", "params": [300, "Frank Herbert", 0] } 6、使用运行时字段 POST _sql?format=txt { "runtime_mappings": { "release_day_of_week": { "type": "keyword", "script": """ emit(doc['release_date'].value.dayOfWeekEnum.toString()) """ } }, "query": """ SELECT * FROM library WHERE page_count > 300 AND author = 'Frank Herbert' """ }

响应结果：

author | name | page_count | release_date |release_day_of_week ---------------+---------------+---------------+------------------------+------------------- Frank Herbert |Dune |604 |1965-06-01T00:00:00.000Z|TUESDAY 7、Sql查询语句转为DSL查询语句 POST /_sql/translate { "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 10 }

响应结果：

{ "size": 10, "_source": false, "fields": [ { "field": "author" }, { "field": "name" }, { "field": "page_count" }, { "field": "release_date", "format": "strict_date_optional_time_nanos" } ], "sort": [ { "page_count": { "order": "desc", "missing": "_first", "unmapped_type": "short" } } ] } 8、Sql支持的函数

查看支持的所有函数：

SHOW FUNCTIONS;

查看支持的天数相关函数：

Elasticsearch SQL 提供了一整套内置的操作符和函数: 官网说明：sql-functions

9、子查询支持

使用子选择(SELECT x FROM (SELECT y))在很小程度上是受支持的: Elasticsearch SQL 可以将任何子选择“扁平化”为单个 SELECT。

注意??：如果子查询中包含 GROUP BY 或 HAVING 或封闭的 SELECT语句，这些比 SELECT X FROM (SELECT ...) WHERE [simple_condition]更复杂的查询，目前不支持。

更多ES中SQL查询的限制，可以查看官网说明SQL Limitations

10、SQL分页查询支持

1）使用limit限制返回记录数：

POST /_sql?format=txt { "query": "SELECT * FROM library limit 2" }

2）使用top函数限制返回记录数：

POST /_sql?format=txt { "query": "SELECT top 2 * FROM library" }

3）使用fetch_size参数限制返回记录数：

POST /_sql?format=txt { "query": "SELECT * FROM library", "fetch_size":2 }

4）采用limit结合自查询进行分页查询：

POST /_sql?format=txt { "query": "SELECT * FROM (SELECT * FROM library limit 2) limit 1" }

5）通过游标访问下一页：说明：在采用CSV, TSV 和 TXT 格式化返回时, 会返回一个游标值cursor，通过游标值我们可以继续访问下一页。这种方式非常时候大数据量的分页返回。

POST /_sql?format=json { "query": "SELECT * FROM library ORDER BY page_count DESC", "fetch_size": 5 } 响应结果： ```csharp { "columns": [ {"name": "author", "type": "text"}, {"name": "name", "type": "text"}, {"name": "page_count", "type": "short"}, {"name": "release_date", "type": "datetime"} ], "rows": [ ["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"], ["Alastair Reynolds", "Revelation Space", 585, "2000-03-15T00:00:00.000Z"], ["James S.A. Corey", "Leviathan Wakes", 561, "2011-06-02T00:00:00.000Z"] ], "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWWWdrRlVfSS1TbDYtcW9lc1FJNmlYdw==:BAFmBmF1dGhvcgFmBG5hbWUBZgpwYWdlX2NvdW50AWYMcmVsZWFzZV9kYXRl+v///w8=" }

通过游标访问下一页：

POST /_sql?format=json { "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAEWYUpOYklQMHhRUEtld3RsNnFtYU1hQQ==:BAFmBGRhdGUBZgVsaWtlcwFzB21lc3NhZ2UBZgR1c2Vy9f///w8=" } 四、Elasticsearch和SQL对应关系

虽然 SQL 和 Elasticsearch 对于数据的组织方式(以及不同的语义)有不同的术语，但本质上它们的用途是相同的。

SQLElasticsearch说明columnfield在 Elasticsearch 字段时，SQL 将这样的条目调用为 column。注意，在 Elasticsearch，一个字段可以包含同一类型的多个值(本质上是一个列表) ，而在 SQL 中，一个列可以只包含一个表示类型的值。Elasticsearch SQL 将尽最大努力保留 SQL 语义，并根据查询的不同，拒绝那些返回多个值的字段。rowdocument列和字段本身不存在; 它们是行或文档的一部分。两者的语义略有不同: 行row往往是严格的（并且有更多的强制执行），而文档往往更灵活或更松散（同时仍然具有结构）。tableindex在 SQL 还是 Elasticsearch 中查询针对的目标schema无在关系型数据库中，schema 主要是表的名称空间，通常用作安全边界。Elasticsearch没有为它提供一个等价的概念。

总结

本文主要介绍了Elasticsearch SQL的使用。如果你对DSL查询语句不熟悉，那么采用SQL查询索引数据将是一个非常简单，0门槛入门的好方法。 1、注意ES在6.3版本之后才原生支持SQL查询。 2、可以通过translate API将sql语句转为DSL语句。 3、ES的SQL查询提供对自查询的简单支持。 4、通过SHOW FUNCTIONS可以查看ES的SQL查询支持的函数。 5、ES的SQL查询可以通过游标cursor实现分页查询。