aliyun-sql插件是基于Apache Calcite开发的部署在服务端的SQL解析插件,使用此插件您可以像使用普通数据库一样使用SQL语句查询Elasticsearch中的数据,从而极大地降低您学习和使用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的 | 支持 | 支持( | 支持的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修改最大查询数。例如,在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" ] ] }
- 本页导读 (1)