Elasticsearch:Query data by using the aliyun-sql plug-in (new customer onboarding discontinued)

Enable and disable the plug-in

Before you use the aliyun-sql plug-in, you must enable it in the Dev Tools of the Kibana console. To log on to the Kibana console, see Connect to a cluster using Kibana.

• Enable the plug-in

  In Kibana's Dev Tools, run the following command:

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

• Disable and uninstall the plug-in

  You must disable the configuration of the aliyun-sql plug-in before you uninstall it. Otherwise, the cluster restart triggered by the uninstallation will hang.

  Disable the plugin configuration.

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

  If you uninstall the plug-in before you disable its configuration and the restart hangs, run the following command to clear the archived configuration and resume the restart:

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

Quick Start

The following example shows how to use the aliyun-sql plug-in to write test data and run a JOIN query.

The aliyun-sql plug-in supports only query requests, not write requests. You can use the bulk API to write data.

1. Log on to the Kibana console of your Alibaba Cloud Elasticsearch instance.

   For more information about how to log on to the Kibana console, see Connect to a cluster using Kibana.

2. In Kibana, go to Dev Tools > Console, and enter the student information and ranking data.

   Student information data:

   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 ranking data:

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

3. Use a JOIN query to retrieve the names and rankings of the students.

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

   In the response, the columns field contains the column names and types, and the rows field contains the row data:

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

Basic queries

You can run all SQL queries using the POST /_alisql endpoint.

• Simple query

  POST /_alisql?pretty
  {
    "query": "select * from monitor where host='100.80.xx.xx' limit 5"
  }

• Set the number of results to return

  POST /_alisql?pretty
  {
      "query": "select * from monitor",
      "fetch_size": 3
  }

• Parameterized query

  POST /_alisql?pretty
  {
    "query": "select * from monitor where host= ? ",
    "params": [{"type":"STRING","value":"100.80.xx.xx"}],
    "fetch_size": 1
  }

{
  "columns": [
    {
      "name": "times",
      "type": "integer"
    },
    {
      "name": "value2",
      "type": "float"
    },
    {
      "name": "host",
      "type": "keyword"
    },
    {
      "name": "region",
      "type": "keyword"
    },
    {
      "name": "measurement",
      "type": "keyword"
    },
    {
      "name": "timestamp",
      "type": "date"
    }
  ],
  "rows": [
    [
      572575,
      4649800.0,
      "100.80.xx.xx",
      "china-dd",
      "cpu",
      "2018-08-09T08:18:42.000Z"
    ]
  ],
  "cursor": "u5HzAgJzY0BEWEYxWlhKNVFXNWtS****"
}

Scroll queries

Use the cursor from the previous query to retrieve the next page of data.

• Query request

  POST /_alisql?pretty
  {
      "cursor": "u5HzAgJzY0BEWEYxWlhKNVFXNWtS****"
  }

  Parameter type	Parameter	Is it required?	Description
  URL parameter	pretty	No	Format the response for readability.
  Request body parameter	cursor	Yes	The cursor value to fetch the corresponding data.

• Response

  {
    "rows": [
      [
        572547,
        3.327459E7,
        "100.80.xx.xx",
        "china-dd",
        "cpu",
        "2018-08-09T08:19:12.000Z"
      ]
    ],
    "cursor": "u5HzAgJzY0BEWEYxWlhKNVFXNWtS****"
  }

  The response is similar to that of a basic query, but the columns field is omitted to reduce network traffic.

JSON format queries

You can use the format=org parameter to return results in the raw Elasticsearch JSON format. This mode does not support JOIN queries.

• Query request

  POST /_alisql?format=org
  {
    "query": "select * from monitor where host= ? ",
    "params": [{"type":"STRING","value":"100.80.xx.xx"}],
    "fetch_size": 1
  }

  The other query parameters are the same as those for basic queries.

• Response

  {
    "_scroll_id": "DXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAAAsWYXNEdlVJZzJTSXFfOGluOVB4Q3Z****",
    "took": 18,
    "timed_out": false,
    "_shards": {
      "total": 1,
      "successful": 1,
      "skipped": 0,
      "failed": 0
    },
    "hits": {
      "total": 2,
      "max_score": 1.0,
      "hits": [
        {
          "_index": "monitor",
          "_type": "_doc",
          "_id": "2",
          "_score": 1.0,
          "_source": {
            "times": 572575,
            "value2": 4649800,
            "host": "100.80.xx.xx",
            "region": "china-dd",
            "measurement": "cpu",
            "timestamp": "2018-08-09T16:18:42+0800"
          }
        }
      ]
    }
  }

  The response format matches that of the original DSL query. You can use the _scroll_id parameter for pagination.

Translate queries

You can convert an SQL statement into an Elasticsearch Domain-Specific Language (DSL) statement. This feature does not support JOIN queries.

• Query request

  POST _alisql/translate
  {
    "query": "select * from monitor where host= '100.80.xx.xx' "
  }

• Response

  {
    "size": 1000,
    "query": {
      "constant_score": {
        "filter": {
          "term": {
            "host": {
              "value": "100.80.xx.xx",
              "boost": 1.0
            }
          }
        },
        "boost": 1.0
      }
    },
    "_source": {
      "includes": [
        "times",
        "value2",
        "host",
        "region",
        "measurement",
        "timestamp"
      ],
  
      "excludes": [ ]
  
    }
  }

JOIN queries

The aliyun-sql plug-in supports only inner joins, which are implemented using merge joins. Note the following limits:

• The JOIN field must be strictly increasing or decreasing in relation to the Elasticsearch document ID.

• The JOIN field must be numeric. String fields are not supported.

• By default, the maximum number of rows that can be returned per table is 10,000.

Syntax:

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

You can change the maximum number of rows per table using the dynamic cluster parameter max.join.size. For example, to set the maximum number of rows to 20,000, run the following command:

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

Queries for nested and text fields

The aliyun-sql plug-in supports queries for nested and text fields.

1. Create an index with nested and text fields.

   PUT user_info/
   {
       "mappings":{
           "_doc":{
               "properties":{
                   "addr":{
                       "type":"text"
                   },
                   "age":{
                       "type":"integer"
                   },
                   "id":{
                       "type":"integer"
                   },
                   "name":{
                        "type":"nested",
                        "properties":{
                           "first_name":{
                               "type":"keyword"
                           },
                           "second_name":{
                               "type":"keyword"
                           }
                       }
                   }
               }
           }
       }
   }

2. Perform a bulk insert.

   PUT user_info/_doc/_bulk?refresh
   {"index":{"_id":"1"}}
   {"addr":"467 Hutchinson Court","age":80,"id":"1","name":[{"first_name":"lesi","second_name" : "Adams"},{"first_name":"chaochaosi","second_name" : "Aams"}]}
   {"index":{"_id":"2"}}
   {"addr":"671 Bristol Street","age":21,"id":"2","name":{"first_name":"Hattie","second_name" : "Bond"}}
   {"index":{"_id":"3"}}
   {"addr":"554 Bristol Street","age":23,"id":"3","name":{"first_name":"Hattie","second_name" : "Bond"}}

3. Query the second_name field of the nested type.

   POST _alisql
   {
     "query": "select * from user_info where name.second_name='Adams'"
   }

   Response:

   {
     "columns" : [
       {
         "name" : "id",
         "type" : "integer"
       },
       {
         "name" : "addr",
         "type" : "text"
       },
       {
         "name" : "name.first_name",
         "type" : "keyword"
       },
       {
         "name" : "age",
         "type" : "integer"
       },
       {
         "name" : "name.second_name",
         "type" : "keyword"
       }
     ],
     "rows" : [
       [
         1,
         "467 Hutchinson Court",
         "lesi",
         80,
         "Adams"
       ]
     ]
   }

4. Query the addr field of the text type.

   POST _alisql
   {
     "query": "select * from user_info where addr='Bristol'"
   }

   Response:

   {
     "columns" : [
       {
         "name" : "id",
         "type" : "integer"
       },
       {
         "name" : "addr",
         "type" : "text"
       },
       {
         "name" : "name.first_name",
         "type" : "keyword"
       },
       {
         "name" : "age",
         "type" : "integer"
       },
       {
         "name" : "name.second_name",
         "type" : "keyword"
       }
     ],
     "rows" : [
       [
         2,
         "671 Bristol Street",
         "Hattie",
         21,
         "Bond"
       ],
       [
         3,
         "554 Bristol Street",
         "Hattie",
         23,
         "Bond"
       ]
     ]
   }

Custom UDF functions

You can register User-Defined Functions (UDFs) only during plug-in initialization. You cannot add UDFs dynamically. The following example shows how to extend the date_format method.

1. Define a DateFormat class that is based on the UDF.

   /**
    * DateFormat.
    */
   public class DateFormat extends UDF {
   
       public String eval(DateTime time, String toFormat) {
           if (time == null || toFormat == null) {
               return null;
           }
           Date date = time.toDate();
           SimpleDateFormat format =  new SimpleDateFormat(toFormat);
           return format.format(date);
       }
   
   }

2. Add the DateFormat class to the plug-in initialization method.

   udfTable.add(KeplerSqlUserDefinedScalarFunction
                   .create("date_format"
                           , DateFormat.class
                           , (JavaTypeFactoryImpl) typeFactory));

3. Use the UDF in a query.

   select date_format(date_f,'yyyy') from date_test

SQL syntax overview

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

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

Functions and expressions