查询Elasticsearch SQL 在6.5.4及之后版本中我们提供Open Distro for Elasticsearch SQL插件允许您使用SQL而不是Elasticsearch查询域特定语言(DSL)编写查询。 如果您已经熟悉SQL并且不想学习DSL查询,那么此功能是一个很好的选择。 基本操作 要使用该功能,需要将请求发送到opendistro/sqlURI。您可以使用请求参数或请求正文(推荐)。 GET from myindex limit 50 POST { "query": "SELECT FROM myindex LIMIT 50" } 您还可以使用curl命令: curl XPOST u username:password k d '{"query": "SELECT FROM kibanasampledataflights LIMIT 10"}' H 'ContentType: application/json' 默认情况下,查询返回JSON,但您也可以选择CSV格式返回数据,需要对format参数进行设置: POST opendistro/sql?formatcsv { "query": "SELECT FROM myindex LIMIT 50" } CSV格式返回数据时,每行对应一个文档,每列对应一个字段。 支持操作 我们支持的SQL操作包括声明、条件、聚合函数、Include和Exclude、常用函数、连接join和展示等操作。 声明statements 声明statements Statement Example Select SELECT FROM myindex Delete DELETE FROM myindex WHERE id1 Where SELECT FROM myindex WHERE ['field']'value' Order by SELECT FROM myindex ORDER BY id asc Group by SELECT FROM myindex GROUP BY range(age, 20,30,39) Limit SELECT FROM myindex LIMIT 50 (default is 200) Union SELECT FROM myindex1 UNION SELECT FROM myindex2 Minus SELECT FROM myindex1 MINUS SELECT FROM myindex2 说明 与任何复杂查询一样,大型UNION和MINUS语句可能会使集群资源紧张甚至崩溃。 条件Conditions 条件Conditions Condition Example Like SELECT FROM myindex WHERE name LIKE 'j%' And SELECT FROM myindex WHERE name LIKE 'j%' AND age > 21 Or SELECT FROM myindex WHERE name LIKE 'j%' OR age > 21 Count distinct SELECT count(distinct age) FROM myindex In SELECT FROM myindex WHERE name IN ('alejandro', 'carolina') Not SELECT FROM myindex WHERE name NOT IN ('jane') Between SELECT FROM myindex WHERE age BETWEEN 20 AND 30 Aliases SELECT avg(age) AS AverageAge FROM myindex Date SELECT FROM myindex WHERE birthday'19901115' Null SELECT FROM myindex WHERE name IS NULL 聚合函数Aggregation 聚合函数Aggregation Aggregation Example avg() SELECT avg(age) FROM myindex count() SELECT count(age) FROM myindex max() SELECT max(age) AS HighestAge FROM myindex min() SELECT min(age) AS LowestAge FROM myindex sum() SELECT sum(age) AS AgeSum FROM myindex Include和Exclude字段 Include和Exclude Pattern Example include() SELECT include('a'), exclude('age') FROM myindex exclude() SELECT exclude('name') FROM myindex 函数Functions 函数Functions Function Example floor SELECT floor(number) AS RoundedDown FROM myindex trim SELECT trim(name) FROM myindex log SELECT log(number) FROM myindex log10 SELECT log10(number) FROM myindex substring SELECT substring(name, 2,5) FROM myindex round SELECT round(number) FROM myindex sqrt SELECT sqrt(number) FROM myindex concatws SELECT concatws(' ', age, height) AS combined FROM myindex / SELECT number / 100 FROM myindex % SELECT number % 100 FROM myindex dateformat SELECT dateformat(date, 'Y') FROM myindex 说明 必须在文档映射中启用fielddata才能使大多数字符串函数正常工作。 连接操作Joins