使用aliyun-sql外掛程式執行SQL查詢-檢索分析服務 Elasticsearch版-阿里雲

使用前須知

aliyun-sql外掛程式已停止對新客戶提供服務，僅存量客戶可繼續使用。推薦使用Elastic官方的x-pack-sql外掛程式替代，詳情請參見sql-search-api。

aliyun-sql外掛程式支援使用SQL語句查詢Elasticsearch中的資料，相容MySQL 5文法。該外掛程式僅適用於6.7.0及以上且低於7.10.0版本的Elasticsearch執行個體（即6.7.0、6.8.x、7.4.0、7.7.1等版本，不含7.10.0及以上版本）。

開啟和關閉外掛程式

使用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寫入資料。

登入目標Elasticsearch執行個體的Kibana控制台。
登入Kibana控制台的操作請參見通過Kibana串連叢集。

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

使用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
}

請求參數

參數類型	參數名稱	是否必須	樣本	描述
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類似遊標查詢。
請求體參數	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或返回為空白。

遊標查詢

通過上次查詢返回的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類型的欄位。

建立包含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"
                        }
                    }
                }
            }
        }
    }
}

批量插入資料。

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

根據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"
    ]
  ]
}

根據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方法為例。

基於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);
    }

}

將DateFormat類添加到外掛程式初始化方法中。

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

使用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]

Join查詢文法

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

Function和運算式

類型	名稱	樣本	說明
Numeric Function	ABS	`SELECT ABS(num_field) FROM table`	返回指定數位絕對值。
Numeric Function	ACOS	`SELECT ACOS(num_field) FROM table`	返回指定數位反餘弦值。
Numeric Function	ASIN	`SELECT ASIN(num_field) FROM table`	返回指定數位反正弦值。
Numeric Function	ATAN	`SELECT ATAN(num_field) FROM table`	返回指定數位反正切值。
Numeric Function	ATAN2	`SELECT ATAN2(num_field1,num_field2) FROM table`	返回兩個指定數位反正切值。
Numeric Function	CEIL	`SELECT CEIL(num_field) FROM table`	返回大於等於指定數位最小整數值。
Numeric Function	CBRT	`SELECT CBRT(num_field) FROM table`	返回指定數位雙精確度立方根值。
Numeric Function	COS	`SELECT COS(num_field) FROM table`	返回指定數位餘弦值。
Numeric Function	COT	`SELECT COT(num_field) FROM table`	返回指定數位餘切值。
Numeric Function	DEGREES	`SELECT DEGREES(num_field) FROM table`	將弧度值轉換為度。
Numeric Function	EXP或EXPM1	`SELECT EXP(num_field) FROM table`	返回e的指定數位次方冪。
Numeric Function	FLOOR	`SELECT FLOOR(num_field) FROM table`	返回小於等於指定數位最大整數值。
Numeric Function	SIN	`SELECT SIN(num_field) FROM table`	返回指定數位正弦值。
Numeric Function	SINH	`SELECT SINH(num_field) FROM table`	返回指定數位雙曲正弦值。
Numeric Function	SQRT	`SELECT SQRT(num_field) FROM table`	返回指定數位正平方根。
Numeric Function	TAN	`SELECT TAN(num_field) FROM table`	返回指定數位三角正切值。
Numeric Function	ROUND	`SELECT ROUND(num_field,2) FROM table`	將指定數字四捨五入到指定的小數位。
Numeric Function	RADIANS	`SELECT RADIANS (num_field) FROM table`	將以度為單位的角度轉換為以弧度為單位的近似等效角度。
Numeric Function	RAND	`SELECT RAND() FROM table`	返回一個帶正號的雙精確度值，大於或等於0.0且小於1.0。
Numeric Function	LN	`SELECT LN (num_field) FROM table`	返回指定數位自然對數。
Numeric Function	LOG10	`SELECT LOG10 (num_field) FROM table`	返回指定數字以10為底的自然對數。
Numeric Function	PI	`SELECT PI() FROM table`	返回PI的值。
Numeric Function	POWER	`SELECT POWER (num_field,2) FROM table`	返回指定數位乘冪。
Numeric Function	TRUNCATE	`SELECT TRUNCATE (num_field,2) FROM table`	返回將指定數字截斷到指定小數位的值。
Arithmetic Operate	+	`SELECT (v1 + v2) as v FROM table`	返回兩個數字之和。
Arithmetic Operate	-	`SELECT(v1 - v2) as v FROM table`	返回兩個數字之差。
Arithmetic Operate	*	`SELECT(v1 * v2) as v FROM table`	返回兩個數字相乘的結果。
Arithmetic Operate	/	`SELECT(v1 / v2) as v FROM table`	返回兩個數字相除的結果。
Arithmetic Operate	%	`SELECT(v1 % v2) as v FROM table`	返回兩個數字相除後的餘數。
Logic Operate	AND	`SELECT * FROM table WHERE condition AND condition`	返回將兩種情況並運算後，查詢的資料。
Logic Operate	OR	`SELECT * FROM table WHERE condition OR condition`	返回將兩種情況或運算後，查詢的資料。
Logic Operate	NOT	`SELECT * FROM table WHERE NOT condition`	返回排除某種情況的查詢資料。
Logic Operate	IS NULL	`SELECT * FROM table WHERE field IS NULL`	返回當指定欄位為空白時的查詢資料。
Logic Operate	IS NOT NULL	`SELECT * FROM table WHERE field IS NOT NULL`	返回當指定欄位不為空白時的查詢資料。
String Function	ASCII	`SELECT ASCII(str_field) FROM table`	返回指定字元的ASCII值。
String Function	LCASE或LOWER	`SELECT LCASE(str_field) FROM table`	將指定字串轉換為小寫。
String Function	UCASE或UPPER	`SELECT UCASE(str_field) FROM table`	將指定字串轉換為大寫。
String Function	CHAR_LENGTH或CHARACTER_LENGTH	`SELECT CHAR_LENGTH(str_field) FROM table`	返回指定字串的長度（以位元組為單位）。
String Function	TRIM	`SELECT TRIM(str_field) FROM table`	從指定字串中刪除字首和字尾的空格。
String Function	SPACE	`SELECT SPACE(num_field) FROM table`	返回指定數量的空白字元的字串。
String Function	LEFT	`SELECT LEFT(str_field, 3) FROM table`	從字串中提取多個字元（從左開始）。
String Function	RIGHT	`SELECT RIGHT(str_field, 3) FROM table`	從字串中提取多個字元（從右開始）。
String Function	REPEAT	`SELECT REPEAT(str_field, 3) FROM table`	返回一個新字串，表示將原字串重複指定次數。
String Function	REPLACE	`SELECT REPLACE(str_field, "SQL", "HTML") FROM table`	用新的子字串替換字串中所有出現的子字串。
String Function	POSITION	`SELECT POSITION("test" IN str_field) FROM table`	返回子字串在字串中首次出現的位置。
String Function	REVERSE	`SELECT REVERSE(str_test) from table`	反轉字串並返回結果。
String Function	LPAD	`SELECT LPAD(str_field, 20, "ABC") FROM table`	從左邊對字串使用指定的字元進行填充，並指定填充之後字串的長度。
String Function	CONCAT	`SELECT CONCAT(str_field,'test') FROM table`	將兩個或多個運算式加在一起。
String Function	SUBSTRING	`SELECT SUBSTRING(str_field, 5, 3) FROM table`	從字串中提取子字串（從任何位置開始）。
Date Function	CURRENT_DATE	`SELECT CURRENT_DATE() FROM table`	返回當前日期。
Date Function	CURRENT_TIME	`SELECT CURRENT_TIME() FROM table`	返回目前時間。
Date Function	CURRENT_TIMESTAMP	`SELECT CURRENT_TIMESTAMP() FROM table`	返回當前日期和時間。
Date Function	DAYNAME	`SELECT DAYNAME(date_field) FROM table`	返回指定日期的工作日名稱。
Date Function	DAYOFMONTH	`SELECT DAYOFMONTH(date_field) FROM table`	返回指定日期的月份。
Date Function	DAYOFYEAR	`SELECT DAYOFYEAR(date_field) FROM table`	返回指定日期的一年中的一天。
Date Function	DAYOFWEEK	`SELECT DAYOFWEEK(date_field) FROM table`	返回指定日期的星期的索引。
Date Function	HOUR	`SELECT HOUR(date_field) FROM table`	返回指定日期的小時部分。
Date Function	MINUTE	`SELECT MINUTE(date_field) FROM table`	返回指定時間或時間日期的分鐘部分。
Date Function	SECOND	`SELECT SECOND(date_field) FROM table`	返回指定時間或時間日期的秒部分。
Date Function	YEAR	`SELECT YEAR(date_field) FROM table`	返回指定日期的年份部分。
Date Function	MONTH	`SELECT MONTH(date_field) FROM table`	返回指定日期的月份部分。
Date Function	WEEK	`SELECT WEEK(date_field) FROM table`	返回指定日期的星期數（1~54，MySQL的為0~53）。
Date Function	MONTHNAME	`SELECT MONTHNAME(date_field) FROM table`	返回指定日期的月份名稱。
Date Function	LAST_DAY	`SELECT LAST_DAY(date_field) FROM table`	返回指定日期的所在月份的最後一天。
Date Function	QUARTER	`SELECT QUARTER(date_field) FROM table`	返回年份日期的季度值。
Date Function	EXTRACT	`SELECT EXTRACT(MONTH FROM date_field) FROM table`	返回指定日期或時間的單獨部分，例如年、月、日、小時、分鐘等。
Date Function	DATE_FORMAT	`SELECT DATE_FORMAT(date_field,'yyyy') from date_test`	將日期或時間資料格式化輸出。
Aggregation Function	MIN	`SELECT MIN(num_field) FROM table`	返回一組值中的最小值。
Aggregation Function	MAX	`SELECT MAX(num_field) FROM table`	返回一組值中的最大值。
Aggregation Function	AVG	`SELECT AVG(num_field) FROM table`	返回一組值的平均值。
Aggregation Function	SUM	`SELECT SUM(num_field) FROM table`	返回一組值的總和。
Aggregation Function	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文法類似。