在SelectDB中使用Elasticsearch資料來源 - ApsaraDB for SelectDB

本文介紹ApsaraDB for SelectDB與Elasticsearch（簡稱ES）資料來源進行對接使用的流程，協助您對Elasticsearch資料來源進行聯邦分析。

概述

Elasticsearch Catalog除了支援自動對應ES中繼資料外，也可以結合SelectDB的分散式查詢規劃能力和ES的全文檢索索引能力，提供更完善的OLAP分析情境解決方案。可以體現在：

ES中多Index分布式Join查詢。
SelectDB和ES中的表聯集查詢，實現更複雜的全文檢索索引過濾。

當前SelectDB支援Elasticsearch 5.x及以上版本。

建立Catalog

CREATE CATALOG test_es PROPERTIES (
    "type"="es",
    "hosts"="http://127.0.0.1:9200",
    "user"="test_user",
    "password"="test_passwd",
    "nodes_discovery"="false"
);

因為Elasticsearch沒有庫（Database）的概念，所以串連ES後，會自動產生一個唯一的庫：default_db，並且在通過SWITCH命令切換到ES Catalog後，會自動切換到default_db庫，無需再執行USE default_db命令。

參數說明：

參數	是否必選	預設值	說明
hosts	是	無	ES地址，可以是一個或多個，也可以是ES的負載平衡地址。
user	否	空	ES的帳號。
password	否	空	對應帳號的密碼。
doc_value_scan	否	true	是否開啟通過ES或Lucene列式儲存擷取查詢欄位的值。
keyword_sniff	否	true	是否對ES中字串分詞類型text.fields進行探測，通過keyword進行查詢。設定為false會按照分詞後的內容匹配。
nodes_discovery	否	true	是否開啟ES節點發現，預設為true。說明阿里雲ES服務採用負載平衡服務作為ES請求入口，無法直接存取叢集節點，這裡需設定為false。
ssl	否	false	ES是否開啟HTTPS訪問模式，目前在fe/be實現方式為信任所有。
mapping_es_id	否	false	是否映射ES索引中的`_id`欄位。
like_push_down	否	true	是否將like轉化為wildchard下推到ES，會增加ES的CPU消耗。
include_hidden_index	否	false	是否包含隱藏的索引，預設為false。

說明

認證方式目前僅支援HTTP Basic認證，並且需要確保該帳號具有訪問/_cluster/state/、_nodes/http等路徑和讀取index的許可權；若叢集未開啟安全認證，則不需要設定帳號和密碼。
當ES 5.x和 6.x中一個index中存在多個type時，SelectDB預設讀取第一個。

查詢用法

在SelectDB中建立ES Catalog後，除了無法使用SelectDB中的資料模型（ROLLUP、預彙總、物化視圖等）外，與在SelectDB查詢普通表並無區別。

基本查詢

SELECT * FROM es_table WHERE k1 > 1000 AND k3 ='term' OR k4 LIKE 'fu*z_';

擴充的esquery

通過esquery(field, QueryDSL)函數，可以將一些無法用SQL表述的Query如match_phrase、geoshape等下推給ES進行過濾處理。esquery的第一個列名參數用於關聯index，第二個參數是ES的基本Query DSL的JSON表述，使用花括弧{}將參數包含在其中。JSON的root key有且只能有一個，如match_phrase、geo_shape、bool等。樣本如下：

match_phrase查詢：

SELECT * FROM es_table WHERE esquery(k4, '{ "match_phrase": { "k4": "selectdb on es" } }');

geo_shape查詢：

SELECT * FROM es_table WHERE esquery(k4, '{ "geo_shape": { "location": { "shape": { "type": "envelope", "coordinates": [ [ 13, 53 ], [ 14, 52 ] ] }, "relation": "within" } } }');

bool查詢：

SELECT * FROM es_table WHERE esquery(k4, ' { "bool": { "must": [ { "terms": { "k1": [ 11, 12 ] } }, { "terms": { "k2": [ 100 ] } } ] } }');

列類型映射

ES Type	SelectDB Type	備忘
null	null	無
boolean	boolean	無
byte	tinyint	無
short	smallint	無
integer	int	無
long	bigint	無
unsigned_long	largeint	無
float	float	無
half_float	float	無
double	double	無
scaled_float	double	無
date	date	僅支援default/yyyy-MM-dd HH:mm:ss/yyyy-MM-dd/epoch_millis格式。
keyword	string	無
text	string	無
ip	string	無
nested	string	無
object	string	無
other	unsupported	無

Array類型

Elasticsearch沒有明確的數群組類型，但是它的某個欄位可以含有0個或多個值。為了表示一個欄位是數群組類型，可以在索引映射的_meta部分添加特定的selectdb結構注釋。對於Elasticsearch 6.x及之前版本，請參考_meta。

舉例說明，假設有一個索引doc包含以下的資料結構：

{
  "array_int_field": [1, 2, 3, 4],
  "array_string_field": ["selectdb", "is", "the", "best"],
  "id_field": "id-xxx-xxx",
  "timestamp_field": "2022-11-12T12:08:56Z",
  "array_object_field": [
    {
      "name": "xxx",
      "age": 18
    }
  ]
}

該結構的數組欄位，可通過如下命令將欄位屬性定義添加到目標索引映射的_meta.selectdb屬性來定義。

# ES 7.x and above
curl -X PUT "localhost:9200/doc/_mapping?pretty" -H 'Content-Type:application/json' -d '
{
    "_meta": {
        "selectdb":{
            "array_fields":[
                "array_int_field",
                "array_string_field",
                "array_object_field"
            ]
        }
    }
}'

# ES 6.x and before
curl -X PUT "localhost:9200/doc/_mapping?pretty" -H 'Content-Type: application/json' -d '
{
    "_doc": {
        "_meta": {
            "selectdb":{
                "array_fields":[
                    "array_int_field",
                    "array_string_field",
                    "array_object_field"
                ]
            }
    }
    }
}

array_fields：用來表示是數群組類型的欄位。

最佳實務

過濾條件下推

ES Catalog支援過濾條件的下推，即將過濾條件下推給ES，僅返回真正滿足條件的資料，顯著地提高查詢效能，降低SelectDB和Elasticsearch的CPU、記憶體及IO使用量。

下面的操作符（Operators）會被最佳化成如下ES Query:

SQL syntax	ES 5.x+ syntax
=	term query
in	terms query
> , < , >= , ⇐	range query
and	bool.filter
or	bool.should
not	bool.must_not
not in	bool.must_not + terms query
is_not_null	exists query
is_null	bool.must_not + exists query
esquery	ES原生JSON形式的QueryDSL

啟用列式掃描最佳化查詢速度

通過設定"enable_docvalue_scan" = "true"，可以啟用列式掃描，以最佳化查詢速度。

開啟後，SelectDB在通過ES查詢資料的過程中，會遵循以下兩個原則：

儘力而為：自動探測待查詢的欄位是否開啟列式儲存（doc_value: true），如果待查詢欄位全部已開啟列式儲存，SelectDB會從列式儲存中擷取所有欄位的值。
自動降級：如果待查詢的欄位中有一個欄位沒有列存，所有欄位都會從行存（_source）中解析擷取。

預設情況下，SelectDB On ES會從行存（_source）中擷取所需的所有列，_source的儲存採用的行式+JSON的形式儲存，在批量讀取效能上要劣於列式儲存，尤其是在只需要查詢少數列的情況下尤為明顯。在只查詢少數列的情況下，docvalue的效能大約是_source效能的十幾倍。

重要

text類型的欄位在ES中沒有列式儲存，因此如果要擷取的欄位值有text類型欄位，SelectDB會自動降級為從_source中擷取。
當擷取的欄位數量過多（大於等於25）時，從docvalue中擷取欄位值的效能與從_source中擷取欄位值基本一樣。

探測keyword類型欄位

通過設定"enable_keyword_sniff" = "true"，可以啟用keyword類型欄位探測。

在ES中可以不建立index直接進行資料匯入，此時ES會自動建立一個新的索引。針對字串類型的欄位，ES會建立一個既有text類型的欄位，又有keyword類型的欄位，這是ES的multi fields特性。

例如如下的mapping：

"k4": {
   "type": "text",
   "fields": {
      "keyword": {   
         "type": "keyword",
         "ignore_above": 256
      }
   }
}

對k4進行條件過濾時例如=，SelectDB On ES會將查詢轉換為ES的TermQuery。SQL過濾條件：

k4 = "SelectDB On ES"

轉換成ES的query DSL為：

"term" : { "k4": "SelectDB On ES"}

因為k4的第一欄位類型為text，在資料匯入的時候就會根據k4設定的分詞器（如果沒有設定，預設為standard分詞器）進行分詞處理得到selectdb、on、es三個Term，如下ES analyze API分析：

POST /_analyze{ "analyzer": "standard", "text": "SelectDB On ES"}

分詞的結果是：

{
   "tokens": [
      {
         "token": "selectdb",
         "start_offset": 0,
         "end_offset": 8,
         "type": "<ALPHANUM>",
         "position": 0
      },
      {
         "token": "on",
         "start_offset": 9,
         "end_offset": 11,
         "type": "<ALPHANUM>",
         "position": 1
      },
      {
         "token": "es",
         "start_offset": 12,
         "end_offset": 15,
         "type": "<ALPHANUM>",
         "position": 2
      }
   ]
}

查詢時使用的是：

"term" : { "k4": "SelectDB On ES"}

SelectDB On ES這個term匹配不到詞典中的任何term，不會返回任何結果。而在修改配置enable_keyword_sniff: true後，會自動將k4 = "SelectDB On ES"轉換成k4.keyword = "SelectDB On ES"來完全符合SQL語義。轉換後的ES query DSL為：

"term" : { "k4.keyword": "SelectDB On ES"}

k4.keyword的類型是keyword，資料寫入ES中是一個完整的term，所以可以匹配。

開啟自動探索節點

通過設定"nodes_discovery" = "true"，可以啟用自動探索節點功能。

當配置為true時，SelectDB將從ES找到所有可用的相關資料節點（在上面分配的分區）。如果ES資料節點的地址不能被SelectDB BE訪問，則設定為false。

說明

公用雲端ES服務通常採用負載平衡服務作為ES請求入口，無法直接存取叢集節點，需要將nodes_discovery設定為false。

ES叢集是否開啟HTTPS訪問模式

通過設定"ssl" = "true"，可以開啟HTTPS訪問方式。

目前FE、BE實現方式為信任所有HTTPS請求。

時間類型欄位使用建議

說明

僅適用ES外表，ES Catalog中自動對應日期類型為Date或Datetime。

在ES中，時間類型的欄位是十分靈活，但是在ES外表中如果對時間類型欄位的類型設定不當，則會造成過濾條件無法下推。

建立索引時，對時間類型格式的設定做最大程度的格式相容：

 "dt": {
     "type": "date",
     "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
 }

在SelectDB中建立該欄位時，一般建議設定為date或datetime，也可以設定為varchar類型，可使用如下SQL樣本語句將過濾條件下推至ES：

SELECT * FROM doe WHERE k2 > '2020-06-21';

SELECT * FROM doe WHERE k2 < '2020-06-21 12:00:00'; 

SELECT * FROM doe WHERE k2 < 1593497011; 

SELECT * FROM doe WHERE k2 < now();

SELECT * FROM doe WHERE k2 < date_format(now(), '%Y-%m-%d');

重要

在ES中如果不對時間類型的欄位設定format, 預設的時間類型欄位格式為：strict_date_optional_time||epoch_millis。
匯入到ES的日期欄位如果是時間戳記，則需要轉換成ms單位，ES內部處理時間戳都是按照ms進行處理，否則ES外表會報錯。

擷取ES中繼資料欄位`_id`

在ES中，在不指定_id的情況下匯入文件，ES會給每個文檔分配一個全域唯一的_id，即主鍵。您也可以在匯入時為文檔指定一個含有特殊業務意義的_id。如果需要在ES外表中擷取該欄位值，建表時可以增加類型為varchar的_id欄位：

CREATE EXTERNAL TABLE `doe` (
  `_id` varchar COMMENT "",
  `city`  varchar COMMENT ""
) ENGINE=ELASTICSEARCH
PROPERTIES (
"hosts" = "http://127.0.0.1:8200",
"user" = "root",
"password" = "root",
"index" = "doe"
}

如果需要在ES Catalog中擷取該欄位值，請設定"mapping_es_id" = "true"。

重要

_id欄位的過濾條件僅支援=和in兩種。
_id欄位必須為varchar類型。

附錄

SelectDB查詢ES原理如下。

+----------------------------------------------+
|                                              |
| SelectDB   +------------------+              |
|            |       FE         +--------------+-------+
|            |                  |  Request Shard Location
|            +--+-------------+-+              |       |
|               ^             ^                |       |
|               |             |                |       |
|  +-------------------+ +------------------+  |       |
|  |            |      | |    |             |  |       |
|  | +----------+----+ | | +--+-----------+ |  |       |
|  | |      BE       | | | |      BE      | |  |       |
|  | +---------------+ | | +--------------+ |  |       |
+----------------------------------------------+       |
   |        |          | |        |         |          |
   |        |          | |        |         |          |
   |    HTTP SCROLL    | |    HTTP SCROLL   |          |
+-----------+---------------------+------------+       |
|  |        v          | |        v         |  |       |
|  | +------+--------+ | | +------+-------+ |  |       |
|  | |               | | | |              | |  |       |
|  | |   DataNode    | | | |   DataNode   +<-----------+
|  | |               | | | |              | |  |       |
|  | |               +<--------------------------------+
|  | +---------------+ | | |--------------| |  |       |
|  +-------------------+ +------------------+  |       |
|   Same Physical Node                         |       |
|                                              |       |
|           +-----------------------+          |       |
|           |                       |          |       |
|           |      MasterNode       +<-----------------+
| ES        |                       |          |
|           +-----------------------+          |
+----------------------------------------------+

FE會請求建表指定的主機，擷取所有節點的HTTP連接埠資訊以及index的shard分布資訊等，如果請求失敗會順序遍曆host列表直至成功或完全失敗。
查詢時，FE會根據FE得到的一些節點資訊和index的中繼資料資訊產生查詢計劃，並發給對應的BE節點。
BE節點通過HTTP Scroll方式，流式地從ES index的每個分區中並發擷取_source或docvalue中的資料。
SelectDB計算完結果後，返回給您。

概述