aliyun-sql插件是基于Apache Calcite开发的部署在服务端的SQL解析插件,使用此插件您可以像使用普通数据库一样使用SQL语句查询Elasticsearch中的数据,从而极大地降低您学习和使用Elasticsearch的成本。

前提条件

您已完成以下操作:
  • 创建阿里云Elasticsearch实例,且版本为6.7.0及以上版本。

    具体操作步骤请参见创建阿里云Elasticsearch实例

    注意 aliyun-sql插件仅支持6.7.0及以上版本的阿里云Elasticsearch实例。
  • 安装aliyun-sql插件。

    您可在插件配置页面查看插件的安装情况(默认已安装),如果还未安装,请参见安装或卸载系统默认插件进行安装。

背景信息

与开源的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语法。

注意事项

  • 在使用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字段仅支持数值类型,不支持字符串类型。

操作步骤

  1. 登录Kibana控制台。
    具体操作步骤请参见登录Kibana控制台
  2. 开启插件配置。
    PUT _cluster/settings
    {
      "transient": {
        "aliyun.sql.enabled": true
      }
    }
  3. 写入数据。
    说明 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"}
  4. 执行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"
        ]
      ]
    }