腾讯云数据仓库 TCHouse-D ES 外表

二、Doris 相关

·FE：Frontend，Doris 的前端节点，负责元数据管理和请求接入。

·BE：Backend，Doris 的后端节点，负责查询执行和数据存储。

三、ES 相关

·DataNode：ES 的数据存储与计算节点。

·MasterNode：ES 的 Master 节点，管理元数据、节点、数据分布等。

·scroll：ES内置的数据集游标特性，用来对数据进行流式扫描和过滤。

·_source：导入时传入的原始 JSON 格式文档内容。

·doc_values：ES/Lucene 中字段的列式存储定义。

·keyword：字符串类型字段，ES/Lucene 不会对文本内容进行分词处理。

·text：字符串类型字段，ES/Lucene 会对文本内容进行分词处理，分词器需要用户指定，默认为 standard 英文分词器。

五、创建 ES 索引

PUT test{ "settings": { "index": { "number_of_shards": "1", "number_of_replicas": "0" } }, "mappings": { "doc": { // ES 7.x版本之后创建索引时不需要指定type，会有一个默认且唯一的`_doc` type "properties": { "k1": { "type": "long" }, "k2": { "type": "date" }, "k3": { "type": "keyword" }, "k4": { "type": "text", "analyzer": "standard" }, "k5": { "type": "float" } } } }}

六、ES 索引导入数据

OST /_bulk{"index":{"_index":"test","_type":"doc"}}{ "k1" : 100, "k2": "2020-01-01", "k3": "Trying out Elasticsearch", "k4": "Trying out Elasticsearch", "k5": 10.0}{"index":{"_index":"test","_type":"doc"}}{ "k1" : 100, "k2": "2020-01-01", "k3": "Trying out Doris", "k4": "Trying out Doris", "k5": 10.0}{"index":{"_index":"test","_type":"doc"}}{ "k1" : 100, "k2": "2020-01-01", "k3": "Doris On ES", "k4": "Doris On ES", "k5": 10.0}{"index":{"_index":"test","_type":"doc"}}{ "k1" : 100, "k2": "2020-01-01", "k3": "Doris", "k4": "Doris", "k5": 10.0}{"index":{"_index":"test","_type":"doc"}}{ "k1" : 100, "k2": "2020-01-01", "k3": "ES", "k4": "ES", "k5": 10.0}

七、Doris 中创建 ES 外表

具体建表语法参照 CREATE TABLE。

CREATE EXTERNAL TABLE `test` // 不指定schema，自动拉取es mapping进行建表 ENGINE=ELASTICSEARCH PROPERTIES ("hosts" = "http://172.**.**.**:8200,http://172.**.**.**:8200","index" = "test","type" = "doc","user" = "root","password" = "*******");CREATE EXTERNAL TABLE `test` ( `k1` bigint(20) COMMENT "", `k2` datetime COMMENT "", `k3` varchar(20) COMMENT "", `k4` varchar(100) COMMENT "", `k5` float COMMENT "") ENGINE=ELASTICSEARCH // ENGINE必须是ElasticsearchPROPERTIES ("hosts" = "http://172.**.**.**:8200,http://172.**.**.**:8200","index" = "test","type" = "doc","user" = "root","password" = "********");

参数说明：

参数	说明
hosts	ES 集群地址，可以是一个或多个，也可以是 ES 前端的负载均衡地址
index	对应的 ES 的 index 名字，支持 alias，如果使用 doc_value，需要使用真实的名称
type	index 的 type，ES 7.x 及以后的版本不传此参数
user	ES 集群用户名
password	对应用户的密码信息

·ES 7.x 之前的集群请注意在建表的时候选择正确的索引类型 type。

·认证方式目前仅支持 HTTP Basic 认证，并且需要确保该用户有访问：/_cluster/state/、_nodes/http 等路径和 index 的读权限；集群未开启安全认证，用户名和密码不需要设置。

·Doris 表中的列名需要和 ES 中的字段名完全匹配，字段类型应该保持一致。

·ENGINE 必须是 Elasticsearch。

八、过滤条件下推

Doris On ES一个重要的功能就是过滤条件的下推：过滤条件下推给 ES，这样只有真正满足条件的数据才会被返回，能够显著的提高查询性能和降低 Doris和 Elasticsearch 的 CPU、memory、IO 使用量。

enable_new_es_dsl代表是否使用新版 DSL 生成逻辑，后续 bug 修复和迭代都在新版 DSL 开发, 默认为true，可在fe.conf中进行修改。

下面的操作符（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

九、数据类型映射

Doris/ES	byte	short	integer	long	float	double	keyword	text	date
tinyint	✓	–	–	–	–	–	–	–	–
smallint	✓	✓	–	–	–	–	–	–	–
int	✓	✓	✓	–	–	–	–	–	–
bigint	✓	✓	✓	✓	–	–	–	–	–
float	–	–	–	–	✓	–	–	–	–
double	–	–	–	–	–	✓	–	–	–
char	–	–	–	–	–	–	✓	✓	–
varchar	–	–	–	–	–	–	✓	✓	–
date	–	–	–	–	–	–	–	–	✓
datetime	–	–	–	–	–	–	–	–	✓

十、启用列式扫描优化查询速度（enable_docvalue_scan=true）

CREATE EXTERNAL TABLE `test` ( `k1` bigint(20) COMMENT "", `k2` datetime COMMENT "", `k3` varchar(20) COMMENT "", `k4` varchar(100) COMMENT "", `k5` float COMMENT "") ENGINE=ELASTICSEARCHPROPERTIES ("hosts" = "http://172.**.**.**:8200,http://172.**.**.**:8200","index" = "test","user" = "root","password" = "********","enable_docvalue_scan" = "true");

参数说明：

参数	说明
enable_docvalue_scan	是否开启通过 ES/Lucene 列式存储获取查询字段的值，默认为 false

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

·尽力而为：自动探测要读取的字段是否开启列式存储（doc_value: true），如果获取的字段全部有列存，Doris 会从列式存储中获取所有字段的值。

·自动降级：如果要获取的字段只要有一个字段没有列存，所有字段的值都会从行存_source中解析获取。

十一、优势

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

十二、注意

1. text类型的字段在 ES 中是没有列式存储，因此如果要获取的字段值有text类型字段会自动降级为从_source中获取。

2. 在获取的字段数量过多的情况下(>= 25)，从docvalue中获取字段值的性能会和从_source中获取字段值基本一样。

十三、探测 keyword 类型字段（enable_keyword_sniff=true）

参数说明：

参数	说明
enable_keyword_sniff	是否对 ES 中字符串类型分词类型（text） fields 进行探测，获取额外的未分词（keyword）字段名（multi-fields 机制）

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

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

对 k4进行条件过滤时会将查询转换为 ES 的 TermQuery。
SQL 过滤条件：

k4 = "Doris On ES"

转换成 ES 的 query DSL 为：

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

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

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

分词的结果是：

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

查询时使用的是：

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

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

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

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

十四、开启节点自动发现

默认为 true（nodes_discovery=true）。

参数说明：

参数	说明
nodes_discovery	是否开启 EST 节点发现，默认为 true

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

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

如果开启应设置为true，默认为 false(http_ssl_enabled=true) 。

参数说明：

参数	说明
http_ssl_enabled	ES 集群是否开启 HTTPS 访问模式

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

十六、查询用法

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

十七、基本查询

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

select * from es_table where esquery(k4, '{ "match_phrase": { "k4": "doris on es" } }');

geo相关查询：

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 ] } } ] } }');

十九、原理

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

1. 创建 ES 外表后，FE 会请求建表指定的主机，获取所有节点的 HTTP 端口信息以及 index 的 shard 分布信息等，如果请求失败会顺序遍历 host 列表直至成功或完全失败。

2. 查询时会根据 FE 得到的一些节点信息和 index 的元数据信息，生成查询计划并发给对应的 BE 节点。

3. BE 节点会根据就近原则即优先请求本地部署的 ES 节点，BE 通过HTTP Scroll方式流式的从 ES index 的每个分片中并发的从_source 或 docvalue 中获取数据。

4. Doris 计算完结果后，返回给用户。

21、时间类型字段使用建议

在 ES 中，时间类型的字段使用十分灵活，但是在 Doris On ES 中如果对时间类型字段的类型设置不当，则会造成过滤条件无法下推。
创建索引时对时间类型格式的设置做最大程度的格式兼容：

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

在 Doris 中建立该字段时建议设置为 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进行处理的，否则 Doris On ES 会出现显示错误。

22、获取 ES 元数据字段_id

导入文档在不指定_id的情况下 ES 会给每个文档分配一个全局唯一的_id即主键, 用户也可以在导入时为文档指定一个含有特殊业务意义的_id；如果需要在 Doris On ES 中获取该字段值，建表时可以增加类型为varchar的_id字段：

CREATE EXTERNAL TABLE `doe` ( `_id` varchar COMMENT "", `city` varchar COMMENT "") ENGINE=ELASTICSEARCHPROPERTIES ("hosts" = "http://172.**.**.**:8200","user" = "root","password" = "*******","index" = "doe"}

23、常见问题

1. Doris On ES 对 ES 的版本要求。
ES 主版本大于5，ES 在2.x 之前和5.x 之后数据的扫描方式不同，目前仅支持5.x 之后的。

2. 是否支持 X-Pack 认证的 ES 集群？
支持所有使用 HTTP Basic 认证方式的 ES 集群。

3. 一些查询比请求 ES 慢很多？
是，例如_count 相关的 query 等，ES 内部会直接读取满足条件的文档个数相关的元数据，不需要对真实的数据进行过滤。

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

腾讯云数据仓库 TCHouse-D ES 外表

二、Doris 相关

三、ES 相关

五、创建 ES 索引

六、ES 索引导入数据

七、Doris 中创建 ES 外表

八、过滤条件下推

九、数据类型映射

十、启用列式扫描优化查询速度（enable_docvalue_scan=true）

十一、优势

十二、注意

十三、探测 keyword 类型字段（enable_keyword_sniff=true）

十四、开启节点自动发现

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

十六、查询用法

十七、基本查询

十八、扩展的 esquery(field, QueryDSL)

十九、原理

21、时间类型字段使用建议

22、获取 ES 元数据字段_id

23、常见问题

相关内容：