SQL插件
更新时间：2025-08-20
7.4.2、7.10.2版本中我们提供Open Distro for Elasticsearch SQL插件，允许用户使用SQL而不是Elasticsearch查询域特定语言（DSL）编写查询语句。
基本操作:
要使用该功能，需要将请求发送到_opendistro/_sqlURI。用户可以使用请求参数或请求正文（推荐）。
Text
1GET https://<host>:<port>/_opendistro/_sql?sql=select * from my-index limit 50
Text
1POST https://<host>:<port>/_opendistro/_sql
2{
3 "query": "SELECT * FROM my-index LIMIT 50"
4}还可以使用curl命令：
Text
1curl -XPOST -u username:password -k -d '{"query": "SELECT * FROM my-index LIMIT 10"}' -H 'Content-Type: application/json'默认情况下，查询返回JSON，但您也可以选择CSV格式返回数据，需要对format参数进行设置：
Text
1POST _opendistro/_sql?format=csv
2{
3 "query": "SELECT * FROM my-index LIMIT 50"
4}CSV格式返回数据时，每行对应一个文档，每列对应一个字段。
Text
1data
22
31
43format也支持json参数（格式与默认方式不同），如下所示：
Text
1POST _opendistro/_sql?format=json
2{
3 "query":"select * from my-index limit 10"
4}
5响应：
6{
7 "total": 3,
8 "size": 3,
9 "columns": [
10 {
11 "name": "data",
12 "type": "long"
13 }
14 ],
15 "rows": [
16 [
17 2
18 ],
19 [
20 1
21 ],
22 [
23 3
24 ]
25 ],
26 "status": 200
27}支持操作
支持的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-in |
说明：
1.字符串函数使用：
必须在文档映射中启用fielddata，字符串函数（例如：substring、concat_ws、trim）才能正常工作。
2.date_format使用说明：
date_format函数只能针对date类型的字段使用，下面是一个完整的示例：
Text
11.创建包含date类型字段的索引
2
3PUT my_index
4{
5 "mappings": {
6 "properties": {
7 "date": {
8 "type": "date"
9 }
10 }
11 }
12}
Text
12.写入数据
2PUT my_index/_doc/2
3{ "date": "2022-10-26T10:46:30Z" }
4
53.函数使用
6POST /_opendistro/_sql?format=json
7{
8 "query": "select DATE_FORMAT(date, 'Y-M-d') from my_index"
9}- 连接操作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 | joinSELECT 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 |
- 展示Show
展示show操作与索引模式匹配的索引和映射。您可以使用*或%使用通配符。
| Show | Example |
|---|---|
| Show tables like | SHOW TABLES LIKE logs-* |
| Show tables | SHOW TABLES |
- 连接操作Joins
支持inner joins, left outer joins,和cross joins。Join操作有许多约束：
您只能加入两个参数。
您必须为索引使用别名（例如people p）。
在ON子句中，您只能使用AND条件。
在WHERE语句中，不要将包含多个索引的树组合在一起。例如，以下语句有效
Text
1WHERE (a.type1 > 3 OR a.type1 < 0) AND (b.type2 > 4 OR b.type2 < -1)以下声明无效：
Text
1WHERE (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仓库。
- 注意：
Sql插件在使用时，需要对线上使用的语句，需进行充分完整的测试。