本文介紹ApsaraDB for SelectDB與Elasticsearch（簡稱ES）資料來源進行對接使用的流程，協助您對Elasticsearch資料來源進行聯邦分析。
概述
Elasticsearch Catalog除了支援自動對應ES中繼資料外，也可以結合SelectDB的分散式查詢規劃能力和ES的全文檢索索引能力，提供更完善的OLAP分析情境解決方案。可以體現在：
ES中多Index分布式Join查詢。
SelectDB和ES中的表聯集查詢，實現更複雜的全文檢索索引過濾。
當前SelectDB支援Elasticsearch 5.x及以上版本。
前提條件
確保Elasticsearch叢集和SelectDB執行個體的網路處於互連狀態。
Elasticsearch叢集和SelectDB執行個體處於同一VPC下。如果不在同一VPC下，請先解決網路互連問題。如何操作，請參見如何解決SelectDB執行個體與資料來源網路互連問題？
已將Elasticsearch叢集訪問地址IP添加至SelectDB執行個體的白名單。具體操作，請參見設定白名單。
若Elasticsearch叢集存在白名單機制，已將SelectDB執行個體所在網段IP添加至Elasticsearch叢集的白名單中。
擷取SelectDB執行個體VPC地址的IP，請參見如何查看雲資料庫 SelectDB 版執行個體所屬VPC的IP網段？
擷取SelectDB執行個體公網的IP地址，通過ping命令pingSelectDB執行個體公網地址擷取。
瞭解什麼是Catalog，以及Catalog的基本操作。具體資訊，請參見湖倉一體。
建立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索引中的 |
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計算完結果後，返回給您。