在6.5.4及之后版本中我们提供Open Distro for Elasticsearch SQL插件允许您使用SQL而不是Elasticsearch查询域特定语言(DSL)编写查询。
如果您已经熟悉SQL并且不想学习DSL查询,那么此功能是一个很好的选择。
基本操作
要使用该功能,需要将请求发送到_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
声明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
条件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
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字段
Include和Exclude
Pattern | Example |
---|---|
include() | SELECT include('a*'), exclude('age') FROM my-index |
exclude() | SELECT exclude('*name') FROM my-index |
- 函数Functions
函数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
连接操作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
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)
- 您不能使用GROUP BY或ORDER BY来获得结果。
- LIMIT和OFFSET不支持一起使用(例如LIMIT 25 OFFSET 25)。
JDBC驱动
Java数据库连接(JDBC)驱动程序允许您将Open Distro for Elasticsearch与您的商业智能(BI)应用程序集成。