The aliyun-sql plug-in is used to parse SQL queries. It is developed based on Apache Calcite and deployed on a server. After you install this plug-in on your Alibaba Cloud Elasticsearch cluster, you can execute SQL statements to query data in the cluster like in common databases. This greatly reduces the costs of learning and using Elasticsearch.

Background information

The aliyun-sql plug-in offers more features than open-source SQL plug-ins. The following table compares the aliyun-sql plug-in with open-source SQL plug-ins.
SQL plug-in SQL parser Paged query JOIN query Nested query Common function CASE statement Extended UDF Optimization of execution plans
x-pack-sql (6.x) antlr Supported. Not supported. Supported. The syntax is a.b. Supports abundant functions. Not supported. Not supported. Provides a large number of rules for the optimization of execution plans.
opendistro-for-elasticsearch druid Not supported. The maximum number of data entries that can be queried is determined by the max_result_window parameter. Supported. Supported. The syntax is nested(message.info)). Supports a few functions. Not supported. Not supported. Provides a few rules for the optimization of execution plans.
aliyun-sql javacc Supported. Supported. The plug-in provides the truncation feature. This feature allows you to dynamically configure the number of data entries that you can query from a single table. For more information, see Syntax overview. Supported. The syntax is a.b. Supports abundant functions. For more information, see Other functions and expressions. Supported. Supported. For more information, see UDFs. Provides a large number of rules for the optimization of execution plans and uses Calcite to optimize execution plans.
Note The CASE statement in the preceding table refers to the CASE WHEN THEN ELSE syntax.

Precautions

  • The aliyun-sql plug-in can be installed only on Alibaba Cloud Elasticsearch clusters of V6.7.0 or later. If you purchase a cluster after the plug-in is launched, the plug-in is already installed on the cluster. If your cluster is purchased before the plug-in is launched, you need to manually install it on your cluster. For more information, see Install and remove a built-in plug-in.
  • Before you use the plug-in, ensure that the aliyun.sql.enabled parameter is set to true for your cluster. You can set the parameter in the Kibana console. For more information, see Log on to the Kibana console.
  • You can manually remove the plug-in. Before you remove it, run the following command in the Kibana console to disable it:
    PUT _cluster/settings
{
  "persistent": {
    "aliyun.sql.enabled": null
  }
}
    Removing the plug-in restarts your cluster. If you do not disable the plug-in before you remove it, your cluster remains stuck in the restart. In this case, you can run the following command to clear archiving configurations and resume the restart: 
    PUT _cluster/settings
{
  "persistent": {
    "archived.aliyun.sql.enabled": null
  }
}

Syntax overview

The aliyun-sql plug-in uses the syntax of MySQL 5.0 and supports a wide range of functions and expressions. For more information, see Other functions and expressions.
  • Basic queries 
    SELECT [DISTINCT] (* | expression) [[AS] alias] [, ...]
FROM table_name
[WHERE condition]
[GROUP BY expression [, ...]
 [HAVING condition]]
[ORDER BY  expression [ ASC | DESC ] [, ...]]
[LIMIT [offset, ] size]
  • JOIN queries 
    SELECT
  expression
FROM table_name
JOIN table_name 
 ON expression
[WHERE condition]
    Notice When you perform a JOIN query, Alibaba Cloud Elasticsearch limits the maximum number of data entries that you can query from a single table. The default number is 10,000. You can use the max.join.size parameter to change the maximum number.

Procedure

  1. Log on to the Kibana console.
    For more information, see Log on to the Kibana console.
  2. Enable the aliyun-sql plug-in.
    Notice The plug-in is already installed on a cluster that is purchased after the plug-in is launched. If your cluster is purchased before the plug-in is launched, you must manually install it. For more information, see Install and remove a built-in plug-in. 
    PUT _cluster/settings
{
  "transient": {
    "aliyun.sql.enabled": true
  }
}
  3. Initiate a write request.
    Note The aliyun-sql plug-in supports only query requests. Therefore, the following code uses a bulk request to write data. 
    PUT monitor/_doc/_bulk?refresh
{"index":{"_id":"1"}}
{"times":572553,"value2":67274280,"host":"10.15.xx.xx","region":"us-east","measurement":"cpu","timestamp":"2018-08-09T16:18:42+0800"}
{"index":{"_id":"2"}}
{"times":572575,"value2":4649800,"host":"100.80.xx.xx","region":"china-dd","measurement":"cpu","timestamp":"2018-08-09T16:18:42+0800"}
{"index":{"_id":"3"}}
{"times":572547,"value2":33274590,"host":"100.80.xx.xx","region":"china-dd","measurement":"cpu","timestamp":"2018-08-09T16:19:12+0800"}
{"index":{"_id":"4"}}
{"times":572541,"value2":29572064,"host":"10.15.xx.xx","region":"us-east","measurement":"cpu","timestamp":"2018-08-09T16:19:12+0800"}
{"index":{"_id":"5"}}
{"times":571703,"value2":50019892,"host":"10.15.xx.xx","region":"us-east","measurement":"disk","timestamp":"2018-08-09T16:20:20+0800"}
  4. Execute an SQL statement.
    POST /_alisql
{"query":"select * from monitor where host='100.80.xx.xx' limit 5 "}
    If the statement is successfully executed, the aliyun-sql plug-in returns table information. The columns field contains column names and data types. The rows field contains row data. 
    {
  "columns" : [
    {
      "name" : "times",
      "type" : "long"
    },
    {
      "name" : "value2",
      "type" : "long"
    },
    {
      "name" : "host",
      "type" : "text"
    },
    {
      "name" : "region",
      "type" : "text"
    },
    {
      "name" : "measurement",
      "type" : "text"
    },
    {
      "name" : "timestamp",
      "type" : "date"
    }
  ],
  "rows" : [
    [
      572575,
      4649800,
      "100.80.xx.xx",
      "china-dd",
      "cpu",
      "2018-08-09T08:18:42.000Z"
    ],
    [
      572547,
      33274590,
      "100.80.xx.xx",
      "china-dd",
      "cpu",
      "2018-08-09T08:19:12.000Z"
    ]
  ]
}