使用aliyun-sql插件的方法 - 检索分析服务Elasticsearch版

aliyun-sql插件是一个SQL解析插件，借助该插件可以使用SQL语句查询Elasticsearch中的数据。后续aliyun-sql插件将不再对外提供服务，您可以使用官方提供的x-pack-sql插件使用SQL语句查询Elasticsearch中的数据。

说明

aliyun-sql插件已不对新客户提供服务，目前仅存量客户可以使用，后续aliyun-sql插件将不再对外提供服务。如需使用SQL语句查询Elasticsearch中的数据，可以使用Elastic官方提供的x-pack-sql插件，具体信息请参见sql-search-api。

前提条件

阿里云Elasticsearch实例版本为6.7.0及以上，7.10.0以下。
已安装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修改最大查询数。例如，在Kibana中执行以下代码，指定最大查询数为20000。
```
PUT /_cluster/settings
{
  "transient": {
    "max.join.size": 20000
  }
}
```
- 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"
    ]
  ]
}