本文介绍aliyun-sql插件的查询语法，包括基础查询、游标查询、JSON格式查询、translate查询、特殊语法、自定义UDF函数以及Function和表达式。

说明 本文仅介绍aliyun-sql插件的查询语法，了解了查询语法后，您可以在Kibana控制台上进行测试和使用。具体操作方法，请参见aliyun-sql使用方法。

基础查询说明

  • 普通查询 
    POST /_alisql?pretty
{
  "query": "select * from monitor where host='100.80.xx.xx' limit 5"
}
  • 查询时指定返回结果条数 
    POST /_alisql?pretty
{
    "query": "select * from monitor",
    "fetch_size": 3
}
  • 查询时指定参数 
    POST /_alisql?pretty
{
  "query": "select * from monitor where host= ? ",
  "params": [{"type":"STRING","value":"100.80.xx.xx"}],
  "fetch_size": 1
}
    参数类型 参数名称 是否必须 示例 描述
    URL参数 pretty 否 无 将返回结果格式化显示。
    请求体参数 query 是 select * from monitor where host='100.80.xx.xx' limit 5 具体的SQL查询语句。
    fetch_size 否 3 每次查询的数据条数。默认值为1000，最大值为10000。如果超过10000，将使用默认最大值10000。
    说明 limit和fetch_size都可以限制查询范围，但两者的本质不同，区别在于：
    • limit：可以实现全量或范围查询。
    • fetch_size：类似于游标查询。
    params 否 [{"type":"STRING","value":"100.80.xx.xx"}] 主要实现类似PreparedStatement的功能。
  • 查询返回结果
    对于数据量比较大的查询，首次执行SQL查询，返回结果中包含的数据条数为fetch_size设置的值，并且包含了游标cursor。 
    {
  "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****"
}
    参数 说明
    columns 包含name和type字段，表示查询字段的名称和类型。
    rows 查询结果。
    cursor 游标，用于下次查询。
    注意 默认返回结果为1000条，如果返回结果大于1000条，您可以不断地使用游标查询（直到无cursor返回或者返回结果为空），获取剩余的所有数据。

游标查询

  • 查询请求 
    POST /_alisql?pretty
{
    "cursor": "u5HzAgJzY0BEWEYxWlhKNVFXNWtS****"
}
    参数类型 参数 是否必须 说明
    URL参数 pretty 否 将返回结果格式化显示。
    请求体参数 cursor 是 指定游标值，获取对应数据。
  • 返回结果 
    {
  "rows": [
    [
      572547,
      3.327459E7,
      "100.80.xx.xx",
      "china-dd",
      "cpu",
      "2018-08-09T08:19:12.000Z"
    ]
  ],
  "cursor": "u5HzAgJzY0BEWEYxWlhKNVFXNWtS****"
}

    返回结果中的字段与基础查询类似，只是为了减少网络传输延迟去掉了columns字段。

JSON格式查询

  • 查询请求（不支持join查询） 
    POST /_alisql?format=org
{
  "query": "select * from monitor where host= ? ",
  "params": [{"type":"STRING","value":"100.80.xx.xx"}],
  "fetch_size": 1
}

    format=org：表示将返回结果以JSON格式显示，其他查询参数与基础查询相同，详情请参见基础查询说明。

  • 返回结果 
    {
  "_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"
        }
      }
    ]
  }
}

    返回结果和原始DSL（Domain Specific Language）查询的返回结果格式相同，_scroll_id参数用来设置翻页。

translate查询

您可以使用translate查询，将请求的SQL语句转换为Elasticsearch的DSL语句。

  • 查询请求（不支持join查询） 
    POST _alisql/translate
{
  "query": "select * from monitor where host= '100.80.xx.xx' "
}
  • 返回结果 
    {
  "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": []
  }
}

特殊语法

aliyun-sql插件支持查询nested和text类型的字段，具体使用方式如下。

  1. 创建表结构。 
    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. 批量插入数据。 
    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. 根据nested类型的second_name字段查询用户信息。 
    POST _alisql
{
  "query": "select * from user_info where name.second_name='Adams'"
}
    返回结果如下。 
    {
  "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. 根据text类型的addr字段查询用户信息。 
    POST _alisql
{
  "query": "select * from user_info where addr='Bristol'"
}
    返回结果如下。 
    {
  "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"
    ]
  ]
}

Function和表达式

类型 名称 示例 说明
Numeric Function ABS SELECT ABS(num_field) FROM table 返回指定数字的绝对值。
ACOS SELECT ACOS(num_field) FROM table 返回指定数字的反余弦值。
ASIN SELECT ASIN(num_field) FROM table 返回指定数字的反正弦值。
ATAN SELECT ATAN(num_field) FROM table 返回指定数字的反正切值。
ATAN2 SELECT ATAN2(num_field1,num_field2) FROM table 返回两个指定数字的反正切值。
CEIL SELECT CEIL(num_field) FROM table 返回大于等于指定数字的最小整数值。
CBRT SELECT CBRT(num_field) FROM table 返回指定数字的双精度立方根值。
COS SELECT COS(num_field) FROM table 返回指定数字的余弦值。
COT SELECT COT(num_field) FROM table 返回指定数字的余切值。
DEGREES SELECT DEGREES(num_field) FROM table 将弧度值转换为度。
EXP或EXPM1 SELECT EXP(num_field) FROM table 返回e的指定数字的次方幂。
FLOOR SELECT FLOOR(num_field) FROM table 返回小于等于指定数字的最大整数值。
SIN SELECT SIN(num_field) FROM table 返回指定数字的正弦值。
SINH SELECT SINH(num_field) FROM table 返回指定数字的双曲正弦值。
SQRT SELECT SQRT(num_field) FROM table 返回指定数字的正平方根。
TAN SELECT TAN(num_field) FROM table 返回指定数字的三角正切值。
ROUND SELECT ROUND(num_field,2) FROM table 将指定数字四舍五入到指定的小数位。
RADIANS SELECT RADIANS (num_field) FROM table 将以度为单位的角度转换为以弧度为单位的近似等效角度。
RAND SELECT RAND() FROM table 返回一个带正号的双精度值，大于或等于0.0且小于1.0。
LN SELECT LN (num_field) FROM table 返回指定数字的自然对数。
LOG10 SELECT LOG10 (num_field) FROM table 返回指定数字以10为底的自然对数。
PI SELECT PI() FROM table 返回PI的值。
POWER SELECT POWER (num_field,2) FROM table 返回指定数字的乘幂。
TRUNCATE SELECT TRUNCATE (num_field,2) FROM table 返回将指定数字截断到指定小数位的值。
Arithmetic Operate + SELECT (v1 + v2) as v FROM table 返回两个数字之和。
- SELECT(v1 - v2) as v FROM table 返回两个数字之差。
* SELECT(v1 * v2) as v FROM table 返回两个数字相乘的结果。
/ SELECT(v1 / v2) as v FROM table 返回两个数字相除的结果。
% SELECT(v1 % v2) as v FROM table 返回两个数字相除后的余数。
Logic Operate AND SELECT * FROM table WHERE condition AND condition 返回将两种情况并运算后，查询的数据。
OR SELECT * FROM table WHERE condition OR condition 返回将两种情况或运算后，查询的数据。
NOT SELECT * FROM table WHERE NOT condition 返回排除某种情况的查询数据。
IS NULL SELECT * FROM table WHERE field IS NULL 返回当指定字段为空时的查询数据。
IS NOT NULL SELECT * FROM table WHERE field IS NOT NULL 返回当指定字段不为空时的查询数据。
String Function ASCII SELECT ASCII(str_field) FROM table 返回指定字符的ASCII值。
LCASE或LOWER SELECT LCASE(str_field) FROM table 将指定字符串转换为小写。
UCASE或UPPER SELECT UCASE(str_field) FROM table 将指定字符串转换为大写。
CHAR_LENGTH或CHARACTER_LENGTH SELECT CHAR_LENGTH(str_field) FROM table 返回指定字符串的长度（以字节为单位）。
TRIM SELECT TRIM(str_field) FROM table 从指定字符串中删除字首和字尾的空格。
SPACE SELECT SPACE(num_field) FROM table 返回指定数量的空格字符的字符串。
LEFT SELECT LEFT(str_field, 3) FROM table 从字符串中提取多个字符（从左开始）。
RIGHT SELECT RIGHT(str_field, 3) FROM table 从字符串中提取多个字符（从右开始）。
REPEAT SELECT REPEAT(str_field, 3) FROM table 返回一个新字符串，表示将原字符串重复指定次数。
REPLACE SELECT REPLACE(str_field, "SQL", "HTML") FROM table 用新的子字符串替换字符串中所有出现的子字符串。
POSITION SELECT POSITION("test" IN str_field) FROM table 返回子字符串在字符串中首次出现的位置。
REVERSE SELECT REVERSE(str_test) from table 反转字符串并返回结果。
LPAD SELECT LPAD(str_field, 20, "ABC") FROM table 从左边对字符串使用指定的字符进行填充，并指定填充之后字符串的长度。
CONCAT SELECT CONCAT(str_field,'test') FROM table 将两个或多个表达式加在一起。
SUBSTRING SELECT SUBSTRING(str_field, 5, 3) FROM table 从字符串中提取子字符串（从任何位置开始）。
Date Function CURRENT_DATE SELECT CURRENT_DATE() FROM table 返回当前日期。
CURRENT_TIME SELECT CURRENT_TIME() FROM table 返回当前时间。
CURRENT_TIMESTAMP SELECT CURRENT_TIMESTAMP() FROM table 返回当前日期和时间。
DAYNAME SELECT DAYNAME(date_field) FROM table 返回指定日期的工作日名称。
DAYOFMONTH SELECT DAYOFMONTH(date_field) FROM table 返回指定日期的月份。
DAYOFYEAR SELECT DAYOFYEAR(date_field) FROM table 返回指定日期的一年中的一天。
DAYOFWEEK SELECT DAYOFWEEK(date_field) FROM table 返回指定日期的星期的索引。
HOUR SELECT HOUR(date_field) FROM table 返回指定日期的小时部分。
MINUTE SELECT MINUTE(date_field) FROM table 返回指定时间或时间日期的分钟部分。
SECOND SELECT SECOND(date_field) FROM table 返回指定时间或时间日期的秒部分。
YEAR SELECT YEAR(date_field) FROM table 返回指定日期的年份部分。
MONTH SELECT MONTH(date_field) FROM table 返回指定日期的月份部分。
WEEK SELECT WEEK(date_field) FROM table 返回指定日期的星期数（1~54，MySQL的为0~53）。
MONTHNAME SELECT MONTHNAME(date_field) FROM table 返回指定日期的月份名称。
LAST_DAY SELECT LAST_DAY(date_field) FROM table 返回指定日期的所在月份的最后一天。
QUARTER SELECT QUARTER(date_field) FROM table 返回年份日期的季度值。
EXTRACT SELECT EXTRACT(MONTH FROM date_field) FROM table 返回指定日期或时间的单独部分，例如年、月、日、小时、分钟等。
DATE_FORMAT SELECT DATE_FORMAT(date_field,'yyyy') from date_test 将日期或时间数据格式化输出。
Aggregation Function MIN SELECT MIN(num_field) FROM table 返回一组值中的最小值。
MAX SELECT MAX(num_field) FROM table 返回一组值中的最大值。
AVG SELECT AVG(num_field) FROM table 返回一组值的平均值。
SUM SELECT SUM(num_field) FROM table 返回一组值的总和。
COUNT SELECT COUNT(num_field) FROM table 返回指定条件的记录数。
Advanced Function CASE SELECT * FROM table ORDER BY(CASE WHEN exp1 THEN exp2 ELSE exp3 END) 语法为CASE WHEN THEN ELSE END。当满足WHEN条件时返回THEN中的值，否则返回ELSE中的值（该值可以在SELECT、WHERE和ORDER中使用）。与IF THEN ELSE语法类似。