elasticsearch查询opendistrosql支持的操作

基本操作

要使用该功能，需要将请求发送到_opendistro/_sqlURI。您可以使用请求参数或请求正文（推荐）。

GET https://<host>:<port>/_opendistro/_sql?sql=select * from my-index limit 50

POST https://<host>:<port>/_opendistro/_sql
{
  "query": "SELECT * FROM my-index LIMIT 50"
}

您还可以使用curl命令：

curl -XPOST https://localhost:9200/_opendistro/_sql -u username:password -k -d '{"query": "SELECT * FROM kibana_sample_data_flights LIMIT 10"}' -H 'Content-Type: application/json'

默认情况下，查询返回JSON，但您也可以选择CSV格式返回数据，需要对format参数进行设置：

POST _opendistro/_sql?format=csv
{
  "query": "SELECT * FROM my-index LIMIT 50"
}

CSV格式返回数据时，每行对应一个文档，每列对应一个字段。

支持操作

我们支持的SQL操作包括声明、条件、聚合函数、Include和Exclude、常用函数、连接join和展示等操作。

• 声明statements

  Statement	Example
  Select	SELECT * FROM my-index
  Delete	DELETE FROM my-index WHERE _id=1
  Where	SELECT * FROM my-index WHERE [‘field’]=‘value’
  Order by	SELECT * FROM my-index ORDER BY _id asc
  Group by	SELECT * FROM my-index GROUP BY range(age, 20,30,39)
  Limit	SELECT * FROM my-index LIMIT 50 (default is 200)
  Union	SELECT * FROM my-index1 UNION SELECT * FROM my-index2
  Minus	SELECT * FROM my-index1 MINUS SELECT * FROM my-index2

  说明：

  与任何复杂查询一样，大型UNION和MINUS语句可能会使集群资源紧张甚至崩溃。

• 条件Conditions

  Condition	Example
  Like	SELECT * FROM my-index WHERE name LIKE ‘j%’
  And	SELECT * FROM my-index WHERE name LIKE ‘j%’ AND age > 21
  Or	SELECT * FROM my-index WHERE name LIKE ‘j%’ OR age > 21
  Count distinct	SELECT count(distinct age) FROM my-index
  In	SELECT * FROM my-index WHERE name IN (‘alejandro’, ‘carolina’)
  Not	SELECT * FROM my-index WHERE name NOT IN (‘jane’)
  Between	SELECT * FROM my-index WHERE age BETWEEN 20 AND 30
  Aliases	SELECT avg(age) AS Average_Age FROM my-index
  Date	SELECT * FROM my-index WHERE birthday=‘1990-11-15’
  Null	SELECT * FROM my-index WHERE name IS NULL

• 聚合函数Aggregation

  Aggregation	Example
  avg()	SELECT avg(age) FROM my-index
  count()	SELECT count(age) FROM my-index
  max()	SELECT max(age) AS Highest_Age FROM my-index
  min()	SELECT min(age) AS Lowest_Age FROM my-index
  sum()	SELECT sum(age) AS Age_Sum FROM my-index

• Include和Exclude字段

  Pattern	Example
  include()	SELECT include(‘a*’), exclude(‘age’) FROM my-index
  exclude()	SELECT exclude(’*name’) FROM my-index

• 函数Functions

  Function	Example
  floor	SELECT floor(number) AS Rounded_Down FROM my-index
  trim	SELECT trim(name) FROM my-index
  log	SELECT log(number) FROM my-index
  log10	SELECT log10(number) FROM my-index
  substring	SELECT substring(name, 2,5) FROM my-index
  round	SELECT round(number) FROM my-index
  sqrt	SELECT sqrt(number) FROM my-index
  concat_ws	SELECT concat_ws(’ ', age, height) AS combined FROM my-index
  /	SELECT number / 100 FROM my-index
  %	SELECT number % 100 FROM my-index
  date_format	SELECT date_format(date, ‘Y’) FROM my-index说明：

  必须在文档映射中启用fielddata才能使大多数字符串函数正常工作。

• 连接操作Joins

  Join	Example
  Inner join	SELECT p.firstname, p.lastname, p.gender, dogs.name FROM people p JOIN dogs d ON d.holdersName = p.firstname WHERE p.age > 12 AND d.age > 1
  Left outer join	SELECT p.firstname, p.lastname, p.gender, dogs.name FROM people p LEFT JOIN dogs d ON d.holdersName = p.firstname
  Cross join	SELECT p.firstname, p.lastname, p.gender, dogs.name FROM people p CROSS JOIN dogs d

  相关约束和限制，参考“连接操作Joins”

• 展示Show

  展示show操作与索引模式匹配的索引和映射。您可以使用*或%使用通配符。

  Show	Example
  Show tables like	SHOW TABLES LIKE logs-*

连接操作Joins

Open Distro for Elasticsearch SQL支持inner joins, left outer joins,和cross joins。Join操作有许多约束：

• 您只能加入两个参数。

• 您必须为索引使用别名（例如people p）。

• 在ON子句中，您只能使用AND条件。

• 在WHERE语句中，不要将包含多个索引的树组合在一起。例如，以下语句有效：

  WHERE (a.type1 > 3 OR a.type1 < 0) AND (b.type2 > 4 OR b.type2 < -1)
  

  以下声明无效：

  WHERE (a.type1 > 3 OR b.type2 < 0) AND (a.type1 > 4 OR b.type2 < -1)