Elasticsearch:Query syntax

Basic queries

• Common query

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

• Query in which the number of data entries to return is specified

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

• Query in which parameters are specified

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

  Type	Parameter	Required	Example	Description
  URL parameter	pretty	No	None.	Formats query results.
  Request body parameter	query	Yes	select * from monitor where host='100.80.xx.xx' limit 5	Specifies the SQL statement that you want to execute.
  	fetch_size	No	3	Specifies the number of data entries to return. The default value is 1000. The maximum value is 10000. If you set this parameter to a value greater than 10000, the system still regards the value as 10000. Note Both limit and fetch_size can limit the query range, but they have the following differences: limit: supports full query or range query. fetch_size: resembles Scroll.
  	params	No	[{"type":"STRING","value":"100.80.xx.xx"}]	Implements the features of the PreparedStatement interface.

• Query results
  When you execute an SQL statement to query large amounts of data for the first time, the number of data entries to return is determined by the fetch_size parameter. The query results also include cursors.

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

  Parameter	Description
  columns	The names and data types of the fields that you queried.
  rows	The query results.
  cursor	The cursor that is used for the next query.

  Notice A maximum number of 1,000 data entries are returned by default. If the number of data entries that you want to query is greater than 1,000, you can continually use cursors to query additional data entries until no cursors or data entries are returned.

Queries with cursors

• Query request

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

  Type	Parameter	Required	Description
  URL parameter	pretty	No	Formats query results.
  Request body parameter	cursor	Yes	The cursor that is used to query specific data.

• Query results

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

  The query results do not include the columns field. This reduces network transmission latency. Other fields in the query results are similar to those in the query results of a basic query.

JSON-formatted queries

• Query request (JOIN statements are not supported.)

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

  format=org indicates that query results are in the JSON format. Other parameters are the same as those in a basic query. For more information, see Basic queries.

• Query results

  {
    "_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 query results are in the same format as those of domain-specific language (DSL) statements. The _scroll_id parameter in the query results is used for paged queries.

Translate queries

You can use translate queries to convert requested SQL statements to DSL statements that Elasticsearch supports.

• Query request (JOIN statements are not supported.)

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

• Query results

  {
    "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": []
    }
  }

Special queries

The aliyun-sql plug-in allows you to query data based on fields of the nested and text types.

1. Create a table schema.

   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. Insert large amounts of data at a time.

   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 user information based on the second_name field of the nested type.

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

   The following results are returned:

   {
     "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 user information based on the addr field of the text type.

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

   The following results are returned:

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

UDFs

UDFs can only be added during the initialization of the aliyun-sql plug-in. The following operations demonstrate how to add the date_format UDF:

1. Define the DateFormat class.

   /**
    * 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 initialization method of the plug-in.

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

3. Use the date_format UDF to query data.

   select date_format(date_f,'yyyy') from date_test

Other functions and expressions