在SelectDB中使用Elasticsearch数据源 - 云数据库 SelectDB 版

本文介绍云数据库 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计算完结果后，返回给您。

概述