如何使用aliyun-sql插件_检索分析服务 Elasticsearch版-阿里云帮助中心

背景信息

与开源的SQL插件相比，aliyun-sql插件支持更丰富的SQL功能，说明如下。


SQL插件	sql解析器	分页查询	Join	Nested	常用Function	Case Function	扩展UDF	执行计划优化
x-pack-sql（6.x版本）	Antlr	支持	不支持	支持（正常语法为a.b）	支持的Function较丰富。	不支持	不支持	执行计划优化规则相对较多。
opendistro-for-elasticsearch	Druid	不支持（最大查询数量受Elasticsearch的`max_result_window`参数限制）	支持	支持（`nested(message.info)`）	支持的Function较少。	不支持	不支持	有少量的执行计划优化规则。
aliyun-sql	Javacc	支持	支持。具有截断功能，可动态配置单表查询数量，详情请参见语法介绍。	支持（正常语法为a.b）	支持的Function较丰富，详情请参见Function和表达式。	支持	支持。详情请参见自定义UDF函数。	执行计划优化规则相对较多，并且使用Calcite优化执行计划。

说明

上表中的Case Function是指CASE WHEN THEN ELSE语法。
x-pack-sql的使用方法，请参见Elasticsearch的SQL功能。

前提条件

您已完成以下操作：

创建阿里云Elasticsearch实例，且版本为6.7.0及以上，7.10.0以下。
具体操作步骤请参见创建阿里云Elasticsearch实例。
安装aliyun-sql插件。
您可在插件配置页面查看插件的安装情况（默认已安装），如果还未安装，请参见安装或卸载系统默认插件进行安装。

使用限制

仅6.7.0及以上，7.10.0以下版本的阿里云Elasticsearch实例支持aliyun-sql插件。

注意事项

在使用aliyun-sql插件前请确保Elasticsearch集群的aliyun.sql.enabled参数设置为true，可在Kibana控制台上进行设置，具体操作步骤请参见登录Kibana控制台。
您可以手动卸载aliyun-sql插件。卸载时，请先在Kibana控制台中执行以下命令，关闭插件配置（aliyun.sql.enabled）。
```
PUT _cluster/settings
{
  "persistent": {
    "aliyun.sql.enabled": null
  }
}
```
卸载插件会触发集群重启。如果在卸载过程中没有关闭插件配置，会导致重启流程卡住。此时可执行以下命令清空归档配置，恢复重启流程。
```
PUT _cluster/settings
{
  "persistent": {
    "archived.aliyun.sql.enabled": null
  }
}
```

语法介绍

aliyun-sql插件使用MySQL 5语法，支持丰富的Function及表达式，详情请参见Function和表达式。

基本查询

SELECT [DISTINCT] (* | expression) [[AS] alias] [, ...]
FROM table_name
[WHERE condition]
[GROUP BY expression [, ...]
 [HAVING condition]]
[ORDER BY expression [ ASC | DESC ] [, ...]]
[LIMIT [offset, ] size]

Join查询
```
SELECT
  expression
FROM table_name
JOIN table_name 
 ON expression
[WHERE condition] 
```
重要
- 进行Join查询时，阿里云Elasticsearch会限制单表最大查询数，默认为10000条数据。您可以通过设置集群动态参数修改max.join.size，指定最大查询数。
- aliyun-sql插件的Join查询是指Inner Join，底层通过Merge Join实现。使用Join查询时，需要确保Join字段随着Elasticsearch文档ID递增或递减。Join字段仅支持数值类型，不支持字符串类型。

操作步骤

登录目标阿里云Elasticsearch实例的Kibana控制台，根据页面提示进入Kibana主页。
登录Kibana控制台的具体操作，请参见登录Kibana控制台。

说明本文以阿里云Elasticsearch 6.7.0版本为例，其他版本操作可能略有差别，请以实际界面为准。
在左侧导航栏，单击Dev Tools。

在Console中执行如下命令，开启插件配置。

PUT _cluster/settings
{
  "transient": {
    "aliyun.sql.enabled": true
  }
}

写入数据。

说明 aliyun-sql只支持查询类请求，不支持写入类请求，因此以下示例通过bulk写入数据。

学生信息数据

PUT stuinfo/_doc/_bulk?refresh
{"index":{"_id":"1"}}
{"id":572553,"name":"xiaoming","age":"22","addr":"addr1"}
{"index":{"_id":"2"}}
{"id":572554,"name":"xiaowang","age":"23","addr":"addr2"}
{"index":{"_id":"3"}}
{"id":572555,"name":"xiaoliu","age":"21","addr":"addr3"}

学生排名数据

PUT sturank/_doc/_bulk?refresh
{"index":{"_id":"1"}}
{"id":572553,"score":"90","sorder":"5"}
{"index":{"_id":"2"}}
{"id":572554,"score":"92","sorder":"3"}
{"index":{"_id":"3"}}
{"id":572555,"score":"86","sorder":"10"}

执行SQL查询语句。

使用Join查询学生名称和排名。

POST /_alisql
{
  "query":"select stuinfo.name,sturank.sorder from stuinfo join sturank on stuinfo.id=sturank.id"
}

执行成功后，aliyun-sql将返回table信息。columns中包含列名和类型，rows中包含行数据。

{
  "columns" : [
    {
      "name" : "name",
      "type" : "text"
    },
    {
      "name" : "sorder",
      "type" : "text"
    }
  ],
  "rows" : [
    [
      "xiaoming",
      "5"
    ],
    [
      "xiaowang",
      "3"
    ],
    [
      "xiaoliu",
      "10"
    ]
  ]
}