# 使用SQL语言搜索数据
对于熟悉关系型数据库（如MySQL）的开发人员和数据分析师而言，直接使用Elasticsearch原生的DSL（JSON格式）查询语言存在较高的学习门槛，且编写复杂聚合查询时容易出错。为了降低使用难度，CSS服务的Elasticsearch集群集成了Open Distro for Elasticsearch SQL插件，允许用户直接使用标准的SQL语法对Elasticsearch进行查询、聚合与分析，甚至支持JDBC驱动对接BI工具（如Tableau），实现无缝的数据探索体验。
#### 功能介绍
Open Distro for Elasticsearch SQL插件本质上是一个翻译器，核心原理如下：
1. 解析：接收用户的SQL语句。
2. 翻译：将SQL转换为Elasticsearch原生的DSL请求。
3. 执行：将DSL发送给集群执行。
4. 返回：将结果格式化为JSON或CSV返回。
更多SQL语法请参见[SQL Functions](https://opendistro.github.io/for-elasticsearch-docs/docs/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> \
    -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 |
     
  ![](https://support.huaweicloud.com/usermanual-css/public_sys-resources/caution_3.0-zh-cn.png)
  大型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字段
  表4Include和Exclude 
  | Pattern   | Example                                             |
  |:---|:---|
  | include() | SELECT include('a\*'), exclude('age') FROM my-index |
  | exclude() | SELECT exclude('\*name') FROM my-index              |
     
  

- 函数Functions
  ![](https://support.huaweicloud.com/usermanual-css/public_sys-resources/caution_3.0-zh-cn.png)
  在文档映射中必须启用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
  ![](https://support.huaweicloud.com/usermanual-css/public_sys-resources/caution_3.0-zh-cn.png)
  支持类型：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                                        |
     
  

- 展示Show 展示show操作与索引模式匹配的索引和映射。您可以使用\*或%使用通配符。
  表7展示show 
  | Show             | Example                  |
  |:---|:---|
  | Show tables like | SHOW TABLES LIKE logs-\* |
     
  
 
#### JDBC驱动与BI集成
如果您使用Tableau、PowerBI或自研Java应用，可以通过JDBC驱动连接CSS集群。
JDBC驱动下载地址：[GitHub仓库](https://github.com/opendistro-for-elasticsearch/sql-jdbc)