Run MySQL-Compatible SQL Queries via aliyun-sql - Elasticsearch

Prerequisites

Before you begin, ensure that you have:

An Alibaba Cloud Elasticsearch cluster running version V6.7.0 or later, but earlier than V7.10.0
The aliyun-sql plug-in installed on the cluster (verify on the plug-in configuration page)

SQL to Elasticsearch concept mapping

The aliyun-sql plug-in uses MySQL 5.0 syntax. The following table maps SQL terms to their Elasticsearch equivalents.

SQL	Elasticsearch	Description
`table`	`index`	The target against which SQL queries execute
`row`	`document`	A single data record
`column`	`field`	A named attribute within a document

Note

The CASE statement in the preceding table refers to the CASE WHEN THEN ELSE syntax.

Constraints and limitations

The plug-in supports query requests only. Use the bulk API to load data before running SQL queries.
Before removing the plug-in, disable it first (see Remove the plug-in). Removing without disabling leaves the cluster stuck in a restart loop.
Join queries are inner joins implemented as merge joins. Join fields must be of a numeric data type, and field values must change with the document IDs of Elasticsearch.
The default maximum number of entries fetched from a single table during a join query is 10,000. Configure max.join.size to change this limit.

SQL syntax

Basic query

SELECT [DISTINCT] (* | expression) [[AS] alias] [, ...]
FROM table_name
[WHERE condition]
[GROUP BY expression [, ...]
 [HAVING condition]]
[ORDER BY expression [ ASC | DESC ] [, ...]]
[LIMIT [offset, ] size]

Join query

SELECT
  expression
FROM table_name
JOIN table_name
 ON expression
[WHERE condition]

Important

Join queries are inner joins. Only numeric fields whose values change with the document IDs of Elasticsearch can be used as join keys. To raise the per-table row limit, set max.join.size in your cluster settings (see Run SQL queries).

Run SQL queries

Step 1: Enable the plug-in

Log in to the Kibana console of your cluster. For details, see Log on to the Kibana console.

The following steps use an Elasticsearch V6.7.0 cluster as an example. Steps may vary for other versions.
In the left-side navigation pane, click Dev Tools.

On the Console tab, run the following command to enable the plug-in:

PUT _cluster/settings
{
  "transient": {
    "aliyun.sql.enabled": true
  }
}

Step 2: Load data

The plug-in supports query requests only. Use a bulk request to load your data first.

Student information (stuinfo index):

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"}

Student rankings (sturank index):

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"}

Step 3: Run an SQL query

Submit SQL statements to the /_alisql endpoint. The following example runs a join query to retrieve each student's name and ranking:

POST /_alisql
{
  "query":"select stuinfo.name,sturank.sorder from stuinfo join sturank on stuinfo.id=sturank.id"
}

A successful response includes a columns field (column names and types) and a rows field (row data):

{
  "columns" : [
    {
      "name" : "name",
      "type" : "text"
    },
    {
      "name" : "sorder",
      "type" : "text"
    }
  ],
  "rows" : [
    [
      "xiaoming",
      "5"
    ],
    [
      "xiaowang",
      "3"
    ],
    [
      "xiaoliu",
      "10"
    ]
  ]
}

(Optional) Increase the join query row limit

To raise the per-table row limit for join queries, run the following command in the Kibana console:

PUT /_cluster/settings
{
  "transient": {
    "max.join.size": 20000
  }
}

Remove the plug-in

Important

Disable the plug-in before removing it. If you remove it without disabling it first, the cluster gets stuck in a restart loop.

Step 1: Disable the plug-in

Run the following command in the Kibana console to set aliyun.sql.enabled to null:

PUT _cluster/settings
{
  "persistent": {
    "aliyun.sql.enabled": null
  }
}

Step 2: Remove the plug-in

Remove the plug-in from the plug-in configuration page of your cluster. Removal restarts the cluster.

If your cluster is stuck in a restart loop

If the plug-in was removed without being disabled first, run the following command to clear the archived configuration and resume the restart:

PUT _cluster/settings
{
  "persistent": {
    "archived.aliyun.sql.enabled": null
  }
}

Elasticsearch:Use method