使用aliyun-sql外掛程式的方法 - Elasticsearch

aliyun-sql外掛程式是基於Apache Calcite開發的部署在服務端的SQL解析外掛程式，使用此外掛程式您可以像使用普通資料庫一樣使用SQL語句查詢Elasticsearch中的資料，從而極大地降低您學習和使用Elasticsearch的成本。

背景資訊

與開源的SQL外掛程式相比，aliyun-sql外掛程式支援更豐富的SQL功能，說明如下。

SQL外掛程式	sql解析器	分頁查詢	Join	Nested	常用Function	Case Function	擴充UDF	執行計畫最佳化
x-pack-sql（6.x版本）	Antlr	支援	不支援	支援（正常文法為a.b）	支援的Function較豐富。	不支援	不支援	執行計畫最佳化規則相對較多。
opendistro-for-elasticsearch	Druid	不支援（最大查詢數量受Elasticsearch的`max_result_window`參數限制）	支援	支援（`nested(message.info)`）	支援的Function較少。	不支援	不支援	有少量的執行計畫最佳化規則。
aliyun-sql	Javacc	支援	支援。具有截斷功能，可動態配置單表查詢數量，詳情請參見文法介紹。	支援（正常文法為a.b）	支援的Function較豐富，詳情請參見Function和運算式。	支援	支援。詳情請參見自訂UDF函數。	執行計畫最佳化規則相對較多，並且使用Calcite最佳化執行計畫。

說明

上表中的Case Function是指CASE WHEN THEN ELSE文法。

前提條件

您已完成以下操作：

建立Elasticsearch執行個體，且版本為6.7.0及以上，7.10.0以下。
具體操作步驟請參見建立Elasticsearch執行個體。
安裝aliyun-sql外掛程式。
您可在外掛程式配置頁面查看外掛程式的安裝情況（預設已安裝），如果還未安裝，請參見安裝或卸載系統預設外掛程式進行安裝。

使用限制

僅6.7.0及以上，7.10.0以下版本的Elasticsearch執行個體支援aliyun-sql外掛程式。

注意事項

在使用aliyun-sql外掛程式前請確保Elasticsearch叢集的aliyun.sql.enabled參數設定為true，可在Kibana控制台上進行設定，具體操作步驟請參見登入Kibana控制台。
您可以手動卸載aliyun-sql外掛程式。卸載時，請先在Kibana控制台中執行以下命令，關閉外掛程式配置（aliyun.sql.enabled）。
```
PUT _cluster/settings
{
  "persistent": {
    "aliyun.sql.enabled": null
  }
}
```
卸載外掛程式會觸發叢集重啟。如果在卸載過程中沒有關閉外掛程式配置，會導致重啟流程卡住。此時可執行以下命令清空歸檔配置，恢複重啟流程。
```
PUT _cluster/settings
{
  "persistent": {
    "archived.aliyun.sql.enabled": null
  }
}
```

文法介紹

aliyun-sql外掛程式使用MySQL 5文法，支援豐富的Function及運算式，詳情請參見Function和運算式。

基本查詢

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] 
```
重要
- 進行Join查詢時，Elasticsearch會限制單表最大查詢數，預設為10000條資料。您可以通過設定叢集動態參數max.join.size修改最大查詢數。例如，在Kibana中執行以下代碼，指定最大查詢數為20000。
```
PUT /_cluster/settings
{
  "transient": {
    "max.join.size": 20000
  }
}
```
- aliyun-sql外掛程式的Join查詢是指Inner Join，底層通過Merge Join實現。使用Join查詢時，需要確保Join欄位隨著Elasticsearch文檔ID遞增或遞減。Join欄位僅支援數實值型別，不支援字串類型。

操作步驟

登入目標Elasticsearch執行個體的Kibana控制台，根據頁面提示進入Kibana首頁。
登入Kibana控制台的具體操作，請參見登入Kibana控制台。
說明本文以Elasticsearch 6.7.0版本為例，其他版本操作可能略有差別，請以實際介面為準。
在左側導覽列，單擊Dev Tools。

在Console中執行如下命令，開啟外掛程式配置。

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

寫入資料。

說明

aliyun-sql只支援查詢類請求，不支援寫入類請求，因此以下樣本通過bulk寫入資料。

學生資訊資料

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

執行SQL查詢語句。

使用Join查詢學生名稱和排名。

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

執行成功後，aliyun-sql將返回table資訊。columns中包含列名和類型，rows中包含行資料。

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