Elasticsearch Catalog 除了支持自动映射 ES 元数据外，也可以利用 Doris 的分布式查询规划能力和 ES(Elasticsearch) 的全文检索能力相结合，提供更完善的 OLAP 分析场景解决方案：

1. ES 中的多 index 分布式 Join 查询。
2. Doris 和 ES 中的表联合查询，更复杂的全文检索过滤。

使用限制

支持 Elasticsearch 5.x 及以上版本。

创建 Catalog

1. CREATE CATALOG es PROPERTIES (
2. "type"="es",
3. "hosts"="http://127.0.0.1:9200"
4. );

因为 Elasticsearch 没有 Database 的概念，所以连接 ES 后，会自动生成一个唯一的 Database：default_db。

并且在通过 SWITCH 命令切换到 ES Catalog 后，会自动切换到 default_db。无需再执行 USE default_db 命令。

参数说明

提示

1. 认证方式目前仅支持 Http Basic 认证，并且需要确保该用户有访问：/_cluster/state/、_nodes/http 等路径和 Index 的读权限; 集群未开启安全认证，用户名和密码不需要设置。
2. 5.x 和 6.x 中一个 Index 中的多个 type 默认取第一个。

列类型映射

Array 类型

Elasticsearch 没有明确的数组类型，但是它的某个字段可以含有 0 个或多个值。

为了表示一个字段是数组类型，可以在索引映射的 _meta 部分添加特定的doris结构注释。

对于 Elasticsearch 6.x 及之前版本，请参考 _meta。

举例说明，假设有一个索引 doc 包含以下的数据结构：

1. {
2. "array_int_field": [1, 2, 3, 4],
3. "array_string_field": ["doris", "is", "the", "best"],
4. "id_field": "id-xxx-xxx",
5. "timestamp_field": "2022-11-12T12:08:56Z",
6. "array_object_field": [
7. {
8. "name": "xxx",
9. "age": 18
10. }
11. ]
12. }

该结构的数组字段可以通过使用以下命令将字段属性定义添加到目标索引映射的_meta.doris属性来定义。

1. # ES 7.x and above
2. curl -X PUT "localhost:9200/doc/_mapping?pretty" -H 'Content-Type:application/json' -d '
3. {
4. "_meta": {
5. "doris":{
6. "array_fields":[
7. "array_int_field",
8. "array_string_field",
9. "array_object_field"
10. ]
11. }
12. }
13. }'

15. # ES 6.x and before
16. curl -X PUT "localhost:9200/doc/_mapping?pretty" -H 'Content-Type: application/json' -d '
17. {
18. "_doc": {
19. "_meta": {
20. "doris":{
21. "array_fields":[
22. "array_int_field",
23. "array_string_field",
24. "array_object_field"
25. ]
26. }
27. }
28. }
29. }

array_fields：用来表示是数组类型的字段。

最佳实践

过滤条件下推

ES Catalog 支持过滤条件的下推：过滤条件下推给 ES，这样只有真正满足条件的数据才会被返回，能够显著的提高查询性能和降低 Doris 和 Elasticsearch 的 CPU、memory、IO 使用量

下面的操作符 (Operators) 会被优化成如下 ES Query:

启用列式扫描优化查询速度 (enable_docvalue_scan=true)

设置 "enable_docvalue_scan" = "true"

开启后 Doris 从 ES 中获取数据会遵循以下两个原则：

• 尽力而为: 自动探测要读取的字段是否开启列式存储 (doc_value: true)，如果获取的字段全部有列存，Doris 会从列式存储中获取所有字段的值
• 自动降级: 如果要获取的字段只要有一个字段没有列存，所有字段的值都会从行存_source中解析获取

优势

默认情况下，Doris On ES 会从行存也就是_source中获取所需的所有列，_source的存储采用的行式+json 的形式存储，在批量读取性能上要劣于列式存储，尤其在只需要少数列的情况下尤为明显，只获取少数列的情况下，docvalue 的性能大约是_source 性能的十几倍

注意

1. text类型的字段在 ES 中是没有列式存储，因此如果要获取的字段值有text类型字段会自动降级为从_source中获取
2. 在获取的字段数量过多的情况下 (>= 25)，从docvalue中获取字段值的性能会和从_source中获取字段值基本一样
3. keyword类型字段由于ignore_above参数的限制，对于超过该限制的长文本字段会忽略，所以可能会出现结果为空的情况。此时需要关闭enable_docvalue_scan，从_source中获取结果。

探测 Keyword 类型字段

设置 "enable_keyword_sniff" = "true"

在 ES 中可以不建立 index 直接进行数据导入，这时候 ES 会自动创建一个新的索引，针对字符串类型的字段 ES 会创建一个既有text类型的字段又有keyword类型的字段，这就是 ES 的 multi fields 特性，mapping 如下：

1. "k4": {
2. "type": "text",
3. "fields": {
4. "keyword": {
5. "type": "keyword",
6. "ignore_above": 256
7. }
8. }
9. }

对 k4 进行条件过滤时比如=，Doris On ES 会将查询转换为 ES 的 TermQuery

SQL 过滤条件：

1. k4 = "Doris On ES"

转换成 ES 的 query DSL 为：

1. "term" : {
2. "k4": "Doris On ES"

4. }

因为 k4 的第一字段类型为text，在数据导入的时候就会根据 k4 设置的分词器 (如果没有设置，就是 standard 分词器) 进行分词处理得到 doris、on、es 三个 Term，如下 ES analyze API 分析：

1. POST /_analyze
2. {
3. "analyzer": "standard",
4. "text": "Doris On ES"
5. }

分词的结果是：

1. {
2. "tokens": [
3. {
4. "token": "doris",
5. "start_offset": 0,
6. "end_offset": 5,
7. "type": "<ALPHANUM>",
8. "position": 0
9. },
10. {
11. "token": "on",
12. "start_offset": 6,
13. "end_offset": 8,
14. "type": "<ALPHANUM>",
15. "position": 1
16. },
17. {
18. "token": "es",
19. "start_offset": 9,
20. "end_offset": 11,
21. "type": "<ALPHANUM>",
22. "position": 2
23. }
24. ]
25. }

查询时使用的是：

1. "term" : {
2. "k4": "Doris On ES"
3. }

Doris On ES这个 term 匹配不到词典中的任何 term，不会返回任何结果，而启用enable_keyword_sniff: true会自动将k4 = "Doris On ES"转换成k4.keyword = "Doris On ES"来完全匹配 SQL 语义，转换后的 ES query DSL 为：

1. "term" : {
2. "k4.keyword": "Doris On ES"
3. }

k4.keyword 的类型是keyword，数据写入 ES 中是一个完整的 term，所以可以匹配

开启节点自动发现，默认为 true(nodes_discovery=true)

设置 "nodes_discovery" = "true"

当配置为 true 时，Doris 将从 ES 找到所有可用的相关数据节点 (在上面分配的分片)。如果 ES 数据节点的地址没有被 Doris BE 访问，则设置为 false。ES 集群部署在与公共 Internet 隔离的内网，用户通过代理访问

ES 集群是否开启 HTTPS 访问模式

设置 "ssl" = "true"

目前会 FE/BE 实现方式为信任所有，这是临时解决方案，后续会使用真实的用户配置证书

查询用法

完成在 Doris 中建立 ES 外表后，除了无法使用 Doris 中的数据模型 (Rollup、预聚合、物化视图等) 外并无区别

基本查询

1. select * from es_table where k1 > 1000 and k3 ='term' or k4 like 'fu*z_'

扩展的 esquery(field, QueryDSL)

通过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 查询：

1. select * from es_table where esquery(k4, '{
2. "match_phrase": {
3. "k4": "doris on es"
4. }
5. }');

geo 相关查询：

1. select * from es_table where esquery(k4, '{
2. "geo_shape": {
3. "location": {
4. "shape": {
5. "type": "envelope",
6. "coordinates": [
7. [
8. 13,
9. 53
10. ],
11. [
12. 14,
13. 52
14. ]
15. ]
16. },
17. "relation": "within"
18. }
19. }
20. }');

bool 查询：

1. select * from es_table where esquery(k4, ' {
2. "bool": {
3. "must": [
4. {
5. "terms": {
6. "k1": [
7. 11,
8. 12
9. ]
10. }
11. },
12. {
13. "terms": {
14. "k2": [
15. 100
16. ]
17. }
18. }
19. ]
20. }
21. }');

时间类型字段使用建议

提示

仅 ES 外表适用，ES Catalog 中自动映射日期类型为 Date 或 Datetime

在 ES 中，时间类型的字段使用十分灵活，但是在 ES 外表中如果对时间类型字段的类型设置不当，则会造成过滤条件无法下推

创建索引时对时间类型格式的设置做最大程度的格式兼容：

1. "dt": {
2. "type": "date",
3. "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
4. }

在 Doris 中建立该字段时建议设置为date或datetime,也可以设置为varchar类型，使用如下 SQL 语句都可以直接将过滤条件下推至 ES：

1. select * from doe where k2 > '2020-06-21';

3. select * from doe where k2 < '2020-06-21 12:00:00';

5. select * from doe where k2 < 1593497011;

7. select * from doe where k2 < now();

9. select * from doe where k2 < date_format(now(), '%Y-%m-%d');

注意：

• 在 ES 中如果不对时间类型的字段设置format, 默认的时间类型字段格式为

1. strict_date_optional_time||epoch_millis - 导入到 ES 的日期字段如果是时间戳需要转换成ms, ES 内部处理时间戳都是按照ms进行处理的，否则 ES 外表会出现显示错误

获取 ES 元数据字段 ID

导入文档在不指定 _id 的情况下，ES 会给每个文档分配一个全局唯一的 _id 即主键，用户也可以在导入时为文档指定一个含有特殊业务意义的 _id;

如果需要在 ES 外表中获取该字段值，建表时可以增加类型为varchar的_id字段：

1. CREATE EXTERNAL TABLE `doe` (
2. `_id` varchar COMMENT "",
3. `city`  varchar COMMENT ""
4. ) ENGINE=ELASTICSEARCH
5. PROPERTIES (
6. "hosts" = "http://127.0.0.1:8200",
7. "user" = "root",
8. "password" = "root",
9. "index" = "doe"
10. }

如果需要在 ES Catalog 中获取该字段值，请设置 "mapping_es_id" = "true"

注意：

1. _id 字段的过滤条件仅支持=和in两种
2. _id 字段必须为 varchar 类型

获取全局有序的查询结果

在相关性排序、优先展示重要内容等场景中 ES 查询结果按照 score 来排序非常有用。Doris 查询 ES 为了充分利用 MPP 的架构优势，是按照 ES 索引的 shard 的分布情况来拉取数据。 为了得到全局有序的排序结果，需要对 ES 进行单点查询。可以通过 session 变量 enable_es_parallel_scroll （默认为 true）来控制。 当设置 enable_es_parallel_scroll=false 时，Doris 将会向 ES 集群发送不带 shard_preference 和 sort 信息的 scroll 查询，从而得到全局有序的结果。 注意： 在查询结果集较大时，谨慎使用。

修改 scroll 请求的 batch 大小

scroll 请求的 batch 默认为 4064。可以通过 session 变量 batch_size 来修改。

常见问题

1. 是否支持 X-Pack 认证的 ES 集群

支持所有使用 HTTP Basic 认证方式的 ES 集群 2. 一些查询比请求 ES 慢很多

是，比如_count 相关的 query 等，ES 内部会直接读取满足条件的文档个数相关的元数据，不需要对真实的数据进行过滤 3. 聚合操作是否可以下推

目前 Doris On ES 不支持聚合操作如 sum, avg, min/max 等下推，计算方式是批量流式的从 ES 获取所有满足条件的文档，然后在 Doris 中进行计算

附录

Doris 查询 ES 原理

1. +----------------------------------------------+
2. |                                              |
3. | Doris      +------------------+              |
4. |            |       FE         +--------------+-------+
5. |            |                  |  Request Shard Location
6. |            +--+-------------+-+              |       |
7. |               ^             ^                |       |
8. |               |             |                |       |
9. |  +-------------------+ +------------------+  |       |
10. |  |            |      | |    |             |  |       |
11. |  | +----------+----+ | | +--+-----------+ |  |       |
12. |  | |      BE       | | | |      BE      | |  |       |
13. |  | +---------------+ | | +--------------+ |  |       |
14. +----------------------------------------------+       |
15. |        |          | |        |         |          |
16. |        |          | |        |         |          |
17. |    HTTP SCROLL    | |    HTTP SCROLL   |          |
18. +-----------+---------------------+------------+       |
19. |  |        v          | |        v         |  |       |
20. |  | +------+--------+ | | +------+-------+ |  |       |
21. |  | |               | | | |              | |  |       |
22. |  | |   DataNode    | | | |   DataNode   +<-----------+
23. |  | |               | | | |              | |  |       |
24. |  | |               +<--------------------------------+
25. |  | +---------------+ | | |--------------| |  |       |
26. |  +-------------------+ +------------------+  |       |
27. |   Same Physical Node                         |       |
28. |                                              |       |
29. |           +-----------------------+          |       |
30. |           |                       |          |       |
31. |           |      MasterNode       +<-----------------+
32. | ES        |                       |          |
33. |           +-----------------------+          |
34. +----------------------------------------------+

1. FE 会请求建表指定的主机，获取所有节点的 HTTP 端口信息以及 index 的 shard 分布信息等，如果请求失败会顺序遍历 host 列表直至成功或完全失败
2. 查询时会根据 FE 得到的一些节点信息和 index 的元数据信息，生成查询计划并发给对应的 BE 节点
3. BE 节点会根据就近原则即优先请求本地部署的 ES 节点，BE 通过HTTP Scroll方式流式的从 ES index 的每个分片中并发的从_source或docvalue中获取数据
4. Doris 计算完结果后，返回给用户