使用SQL语言搜索数据

对于熟悉关系型数据库（如MySQL）的开发人员和数据分析师而言，直接使用Elasticsearch原生的DSL（JSON格式）查询语言存在较高的学习门槛，且编写复杂聚合查询时容易出错。为了降低使用难度，CSS服务的Elasticsearch集群集成了Open Distro for Elasticsearch SQL插件，允许用户直接使用标准的SQL语法对Elasticsearch进行查询、聚合与分析，甚至支持JDBC驱动对接BI工具（如Tableau），实现无缝的数据探索体验。

功能介绍

Open Distro for Elasticsearch SQL插件本质上是一个翻译器，核心原理如下：

解析：接收用户的SQL语句。
翻译：将SQL转换为Elasticsearch原生的DSL请求。
执行：将DSL发送给集群执行。
返回：将结果格式化为JSON或CSV返回。

更多SQL语法请参见SQL Functions。

约束限制

Elasticsearch集群需要版本大于或等于6.5.4才支持Open Distro for Elasticsearch SQL插件。

SQL查询指导

方式一：在Kibana中执行SQL（推荐）
适用于开发调试和临时查询。
- 基础查询。
  例如，执行如下命令，从索引my-index中查询age大于20的10条数据。
```
POST _opendistro/_sql
{
  "query": "SELECT * FROM my-index WHERE age > 20 LIMIT 10"
}
```
- 导出CSV数据。当需要导出数据到Excel分析，可指定“format=csv”。
  例如，执行如下命令，从索引my-index中根据name和age字段检索10条数据，并以CSV格式输出。
```
POST _opendistro/_sql?format=csv
{
  "query": "SELECT name, age FROM my-index LIMIT 10"
}
```
- SQL转DSL。当需要学习如何写DSL或分析SQL的性能时，可以使用“_explain”接口查看翻译后的DSL。
  例如，执行如下命令，将SQL语句转换为相应的DSL语句。
```
POST _opendistro/_sql/_explain
{
  "query": "SELECT * FROM my-index WHERE age > 20"
}
```
方式二：在服务器使用Curl命令行执行SQL
适用于脚本化任务。

例如，执行如下命令，从“my-index”索引中搜索10条数据。以下是接入安全集群（HTTP协议）的示例代码。
```
curl -XPOST http://<集群IP地址>:9200/_opendistro/_sql \
  -u <username>:<password> -k \
  -H 'Content-Type: application/json' \
  -d '{"query": "SELECT * FROM my-index LIMIT 10"}'
```

支持的SQL语法

支持的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 ('Bob', 'David')
Not	SELECT * FROM my-index WHERE name NOT IN ('Bob')
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

表4 Include和Exclude
Pattern	Example
include()	SELECT include('a*'), exclude('age') FROM my-index
exclude()	SELECT exclude('*name') FROM my-index

函数Functions

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

表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

连接操作Joins

支持类型：INNER JOIN、LEFT JOIN、CROSS JOIN。

硬性限制：

只能连接2个表（索引）。
必须为每个表指定别名，例如“FROM people p JOIN index b”。
ON条件仅支持AND组合，不支持OR或嵌套条件。
不支持在JOIN查询中同时使用GROUP BY或ORDER BY。
不支持LIMIT和OFFSET的组合使用。反例：“LIMIT 25 OFFSET 25”。

不支持在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)

表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