This topic describes the query syntax of the aliyun-sql plug-in. This plug-in supports basic queries, queries with cursors, JSON-formatted queries, translate queries, special queries, user-defined functions (UDFs), other functions, and expressions.

Note After you understand the query syntax of the aliyun-sql plug-in, you can test and use the plug-in in the Kibana console. For more information, see Use method.

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.
    params No [{"type":"STRING","value":"100.80.xx.xx"}] This parameter 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.

  • 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

Type Name Example Description
Numeric function ABS SELECT ABS(num_field) FROM table Returns the absolute value of a number.
ACOS SELECT ACOS(num_field) FROM table Returns the arccosine of a number.
ASIN SELECT ASIN(num_field) FROM table Returns the arcsine of a number.
ATAN SELECT ATAN(num_field) FROM table Returns the arctangent of a number.
ATAN2 SELECT ATAN2(num_field1,num_field2) FROM table Returns the arctangent of two numbers.
CEIL SELECT CEIL(num_field) FROM table Returns the smallest integer that is greater than or equal to a number.
CBRT SELECT CBRT(num_field) FROM table Returns the double-precision cube root of a number.
COS SELECT COS(num_field) FROM table Returns the cosine of a number.
COT SELECT COT(num_field) FROM table Returns the cotangent of a number.
DEGREES SELECT DEGREES(num_field) FROM table Converts radians into degrees.
EXP or EXPM1 SELECT EXP(num_field) FROM table Returns the value of e raised to the power of a number.
FLOOR SELECT FLOOR(num_field) FROM table Returns the largest integer that is less than or equal to a number.
SIN SELECT SIN(num_field) FROM table Returns the sine of a number.
SINH SELECT SINH(num_field) FROM table Returns the hyperbolic sine of a number.
SQRT SELECT SQRT(num_field) FROM table Returns the positive square root of a number.
TAN SELECT TAN(num_field) FROM table Returns the tangent of a number.
ROUND SELECT ROUND(num_field,2) FROM table Rounds a number to a specific decimal place.
RADIANS SELECT RADIANS (num_field) FROM table Converts an angle in degrees to its equivalent in radians.
RAND SELECT RAND() FROM table Returns a double-precision number that includes a plus sign. The number must be greater than or equal to 0.0 and less than 1.0.
LN SELECT LN (num_field) FROM table Returns the natural logarithm of a number.
LOG10 SELECT LOG10 (num_field) FROM table Returns the base 10 logarithm of a number.
PI SELECT PI() FROM table Returns the value of PI.
POWER SELECT POWER (num_field,2) FROM table Returns the result of a number raised to a power.
TRUNCATE SELECT TRUNCATE (num_field,2) FROM table Truncates a number to a specific decimal place.
Arithmetic operation + SELECT (v1 + v2) as v FROM table Returns the sum of two numbers.
- SELECT(v1 - v2) as v FROM table Returns the difference of two numbers.
* SELECT(v1 * v2) as v FROM table Returns the product of two numbers.
/ SELECT(v1 / v2) as v FROM table Returns the quotient of a number divided by another.
% SELECT(v1 % v2) as v FROM table Returns the remainder of a number divided by another.
Logical operation AND SELECT * FROM table WHERE condition AND condition Returns data for a query in which the AND operation is performed on two conditions.
OR SELECT * FROM table WHERE condition OR condition Returns data for a query in which the OR operation is performed on two conditions.
NOT SELECT * FROM table WHERE NOT condition Returns data for a query in which a condition is excluded.
IS NULL SELECT * FROM table WHERE field IS NULL Returns data for a query in which the value of a specific field is null.
IS NOT NULL SELECT * FROM table WHERE field IS NOT NULL Returns data for a query in which the value of a specific field is not null.
String function ASCII SELECT ASCII(str_field) FROM table Returns the ASCII value of a character.
LCASE or LOWER SELECT LCASE(str_field) FROM table Converts a string to lowercase.
UCASE or UPPER SELECT UCASE(str_field) FROM table Converts a string to uppercase.
CHAR_LENGTH or CHARACTER_LENGTH SELECT CHAR_LENGTH(str_field) FROM table Returns the length of a string, in bytes.
TRIM SELECT TRIM(str_field) FROM table Trims a string by removing leading and trailing spaces from it.
SPACE SELECT SPACE(num_field) FROM table Returns a string that includes the specific number of spaces.
LEFT SELECT LEFT(str_field, 3) FROM table Returns the specific number of leftmost characters from a string.
RIGHT SELECT RIGHT(str_field, 3) FROM table Returns the specific number of rightmost characters from a string.
REPEAT SELECT REPEAT(str_field, 3) FROM table Repeats a string the specific number of times and returns the result string.
REPLACE SELECT REPLACE(str_field, "SQL", "HTML") FROM table Replaces a substring with a new substring within a string.
POSITION SELECT POSITION("test" IN str_field) FROM table Returns the position where a substring appears within a string for the first time.
REVERSE SELECT REVERSE(str_test) from table Reverses a string and returns the result string.
LPAD SELECT LPAD(str_field, 20, "ABC") FROM table Prepends specified characters to a string based on a specific length.
CONCAT SELECT CONCAT(str_field,'test') FROM table Concatenates two or more strings and returns the result string.
SUBSTRING SELECT SUBSTRING(str_field, 5, 3) FROM table Returns a substring that is extracted from a string based on the specified character position.
Date function CURRENT_DATE SELECT CURRENT_DATE() FROM table Returns the current date.
CURRENT_TIME SELECT CURRENT_TIME() FROM table Returns the current time.
CURRENT_TIMESTAMP SELECT CURRENT_TIMESTAMP() FROM table Returns the current date and time.
DAYNAME SELECT DAYNAME(date_field) FROM table Returns the day of the week for a date.
DAYOFMONTH SELECT DAYOFMONTH(date_field) FROM table Returns the index of the day of the month for a date.
DAYOFYEAR SELECT DAYOFYEAR(date_field) FROM table Returns the index of the day of the year for a date.
DAYOFWEEK SELECT DAYOFWEEK(date_field) FROM table Returns the index of the day of the week for a date.
HOUR SELECT HOUR(date_field) FROM table Returns the hour part of a date.
MINUTE SELECT MINUTE(date_field) FROM table Returns the minute part of a time or datetime.
SECOND SELECT SECOND(date_field) FROM table Returns the seconds part of a time or datetime.
YEAR SELECT YEAR(date_field) FROM table Returns the year part of a date.
MONTH SELECT MONTH(date_field) FROM table Returns the month part of a date.
WEEK SELECT WEEK(date_field) FROM table Returns the index of the week in which a date falls. Valid values for the aliyun-sql plug-in: 1 to 54. Valid values for MySQL: 0 to 53.
MONTHNAME SELECT MONTHNAME(date_field) FROM table Returns the name of the month for a date.
LAST_DAY SELECT LAST_DAY(date_field) FROM table Returns the last day of the month for a date.
QUARTER SELECT QUARTER(date_field) FROM table Returns the quarter of the year for a date.
EXTRACT SELECT EXTRACT(MONTH FROM date_field) FROM table Returns one or more separate parts of a date or time. For example, this function can return the year, month, day, hour, or minute part of a date or time.
DATE_FORMAT SELECT DATE_FORMAT(date_field,'yyyy') from date_test Formats a date or time.
Aggregation function MIN SELECT MIN(num_field) FROM table Returns the minimum value among a set of values.
MAX SELECT MAX(num_field) FROM table Returns the maximum value among a set of values.
AVG SELECT AVG(num_field) FROM table Returns the average of a set of values.
SUM SELECT SUM(num_field) FROM table Returns the sum of a set of values.
COUNT SELECT COUNT(num_field) FROM table Returns the number of records that meet the specified conditions.
Advanced function CASE SELECT * FROM table ORDER BY(CASE WHEN exp1 THEN exp2 ELSE exp3 END) The syntax is CASE WHEN THEN ELSE END. If the condition specified in the WHEN clause is met, the value specified in the THEN clause is returned. If the condition is not met, the value specified in the ELSE clause is returned. The syntax of the CASE statement is similar to that of the IF THEN ELSE statement.