通过aliyun-sql插件查询数据（已停止新客户服务）

开启和关闭插件

使用aliyun-sql插件前，需要在Kibana控制台的Dev Tools中开启插件配置。登录Kibana控制台的操作请参见通过Kibana连接集群。

• 开启插件

  在Kibana的Dev Tools中执行以下命令，开启插件：

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

• 关闭并卸载插件

  卸载aliyun-sql插件前，必须先关闭插件配置，否则卸载触发的集群重启会卡住。

  关闭插件配置：

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

  如果未关闭插件配置就卸载了插件，导致重启卡住，执行以下命令清空归档配置恢复重启流程：

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

快速入门

以下示例通过写入测试数据并执行Join查询，演示aliyun-sql插件的基本用法。

aliyun-sql只支持查询类请求，不支持写入类请求，以下通过bulk API写入数据。

1. 登录目标阿里云Elasticsearch实例的Kibana控制台。

   登录Kibana控制台的操作请参见通过Kibana连接集群。

2. 在Kibana的 Dev Tools > Console 中，写入学生信息数据和排名数据。

   学生信息数据：

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

   学生排名数据：

   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. 使用Join查询学生名称和排名。

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

   返回结果中，columns包含列名和类型，rows包含对应的行数据：

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

基础查询

所有SQL查询通过POST /_alisql端点执行。

• 普通查询

  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
  }

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

游标查询

通过上次查询返回的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格式查询

通过format=org参数以原始Elasticsearch JSON格式返回查询结果。此模式不支持Join查询。

• 查询请求

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

  其他查询参数与基础查询相同。

• 返回结果

  {
    "_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查询相同，通过_scroll_id参数翻页。

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

Join查询

aliyun-sql插件的Join查询为Inner Join，底层通过Merge Join实现。使用时需注意以下限制：

• Join字段必须随着Elasticsearch文档ID递增或递减。

• Join字段仅支持数值类型，不支持字符串类型。

• 单表最大查询数默认为10000条数据。

语法格式：

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

通过集群动态参数max.join.size修改单表最大查询数，例如设置为20000：

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

nested和text类型字段查询

aliyun-sql插件支持查询nested和text类型的字段。

1. 创建包含nested和text字段的索引。

   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

SQL语法概览

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]

Function和表达式