查詢文法說明 - Elasticsearch

本文介紹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類型的欄位，具體使用方式如下。

建立表結構。

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

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文法類似。