使用Open Distro sql插件
在6.5.4及之后版本中提供Open Distro for Elasticsearch SQL插件允许您使用SQL而不是Elasticsearch查询域特定语言（DSL）编写查询。
如果您已经熟悉SQL并且不想学习DSL查询，那么此功能是一个很好的选择。
基本操作
- Kibana（推荐）
- 登录Kibana，在DevTools中将请求发送到_opendistro/_sqlURI，可以使用请求参数或请求正文。
GET _opendistro/_sql?sql=select * from my-index limit 50
POST _opendistro/_sql { "query": "SELECT * FROM my-index LIMIT 50" } - 默认情况下，查询返回JSON。您也可以选择CSV格式返回数据，选择CSV格式需要对format参数进行如下设置：
POST _opendistro/_sql?format=csv { "query": "SELECT * FROM my-index LIMIT 50" }CSV格式返回数据时，每行对应一个文档，每列对应一个字段。
- 登录Kibana，在DevTools中将请求发送到_opendistro/_sqlURI，可以使用请求参数或请求正文。
- 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'
支持操作
支持的SQL操作包括声明、条件、聚合函数、Include和Exclude、常用函数、连接join和展示等操作。
- 声明statements
表1 声明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
表2 条件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
表3 聚合函数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字段
表4 Include和Exclude Pattern
Example
include()
SELECT include('a*'), exclude('age') FROM my-index
exclude()
SELECT exclude('*name') FROM my-index
- 函数Functions
表5 函数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
表6 连接操作Joins Join
Example
Inner join
SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s JOIN school sc ON sc.name = s.school_name WHERE s.age > 20
Left outer join
SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s LEFT JOIN school sc ON sc.name = s.school_name
Cross join
SELECT s.firstname, s.lastname, s.gender, sc.name FROM student s CROSS JOIN school sc
相关约束和限制，参考连接操作Joins。
连接操作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)
- 您不能使用GROUP BY或ORDER BY来获得结果。
- LIMIT和OFFSET不支持一起使用（例如LIMIT 25 OFFSET 25）。
JDBC驱动
Java数据库连接（JDBC）驱动程序允许您将Open Distro for Elasticsearch与您的商业智能（BI）应用程序集成。
有关下载和使用JAR文件的信息，请参阅GitHub仓库。
