The aliyun-sql plug-in is developed based on Apache Calcite and deployed on a server. It is used to parse SQL queries. After you install this plug-in on your Alibaba Cloud Elasticsearch cluster, you can execute SQL statements to query data in the cluster the same way as in common databases. This greatly reduces the training and usage costs of Elasticsearch.
Prerequisites
- An Alibaba Cloud Elasticsearch cluster is created. The cluster version is 6.7.0 or
later.
For more information, see Create an Alibaba Cloud Elasticsearch cluster.
Notice The aliyun-sql plug-in is available only for Alibaba Cloud Elasticsearch clusters of V6.7.0 or later. - The aliyun-sql plug-in is installed.
By default, the aliyun-sql plug-in is installed on the Elasticsearch cluster. You can check whether the plug-in is installed on the plug-in configuration page. If the plug-in is not installed, follow the instructions provided in Install and remove a built-in plug-in to install the plug-in.
Background information
SQL plug-in | SQL parser | Paged query | Join | Nested | Common function | Case Function | 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. |
Precautions
- Before you use the plug-in, make sure that the
aliyun.sql.enabled
parameter is set to true for your Elasticsearch 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 the plug-in, run the following
command in the Kibana console to disable it. To disable the plug-in, set
aliyun.sql.enabled
to null. The following example shows the configuration:PUT _cluster/settings { "persistent": { "aliyun.sql.enabled": null } }
Plug-in removal 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 must run the following command to clear the archiving configurations and resume the restart:PUT _cluster/settings { "persistent": { "archived.aliyun.sql.enabled": null } }
Syntax overview
- 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 specify the maximum number of queries by setting
max.join.size
. - The JOIN query you performed is an INNER JOIN query. Actually, the aliyun-sql plug-in performs a merge join for the query. When you perform a JOIN query, make sure that the field values in the tables you want to join change with the document IDs of Elasticsearch. JOIN queries can be performed only for fields of a numeric data type.
- 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 specify the maximum number of queries by setting