本文介绍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。
    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语句转换为ES的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"
        ]
      ]
    }

自定义UDF函数

目前UDF不支持动态添加,只能在插件初始化的时候添加。例如扩展date_format方法,实现方法如下:

  1. 基于UDF,自定义DateFormat类。
    /**
     * 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. DateFormat类添加到插件初始化方法中。
    udfTable.add(KeplerSqlUserDefinedScalarFunction
                    .create("date_format"
                            , DateFormat.class
                            , (JavaTypeFactoryImpl) typeFactory));
  3. 使用UDF查询。
    select date_format(date_f,'yyyy') from date_test

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语法类似。