tencent cloud

文档反馈

ES 外表

最后更新时间:2024-07-04 15:35:48
    说明:
    本文档展示的内容仅适用于腾讯云数据仓库 TCHouse-D 1.1及以下版本,后续版本建议使用 Multi-Catalog 功能对接外部数据目录。
    Doris-On-ES 将 Doris 的分布式查询规划能力和 ES(Elasticsearch)的全文检索能力相结合,提供更完善的 OLAP 分析场景解决方案:
    1. ES 中的多 index 分布式 Join 查询。
    2. Doris 和 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://192.168.0.1:8200,http://192.168.0.2:8200",
    "index" = "test",
    "type" = "doc",
    "user" = "root",
    "password" = "root"
    );
    
    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必须是Elasticsearch
    PROPERTIES (
    "hosts" = "http://192.168.0.1:8200,http://192.168.0.2:8200",
    "index" = "test",
    "type" = "doc",
    "user" = "root",
    "password" = "root"
    );
    参数说明:
    参数
    说明
    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=ELASTICSEARCH
    PROPERTIES (
    "hosts" = "http://192.168.0.1:8200,http://192.168.0.2:8200",
    "index" = "test",
    "user" = "root",
    "password" = "root",
    "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)

    CREATE EXTERNAL TABLE `test` (
    `k1` bigint(20) COMMENT "",
    `k2` datetime COMMENT "",
    `k3` varchar(20) COMMENT "",
    `k4` varchar(100) COMMENT "",
    `k5` float COMMENT ""
    ) ENGINE=ELASTICSEARCH
    PROPERTIES (
    "hosts" = "http://192.168.0.1:8200,http://192.168.0.2:8200",
    "index" = "test",
    "user" = "root",
    "password" = "root",
    "enable_keyword_sniff" = "true"
    );
    参数说明:
    参数
    说明
    enable_keyword_sniff
    是否对 ES 中字符串类型分词类型(textfields 进行探测,获取额外的未分词(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)。
    CREATE EXTERNAL TABLE `test` (
    `k1` bigint(20) COMMENT "",
    `k2` datetime COMMENT "",
    `k3` varchar(20) COMMENT "",
    `k4` varchar(100) COMMENT "",
    `k5` float COMMENT ""
    ) ENGINE=ELASTICSEARCH
    PROPERTIES (
    "hosts" = "http://192.168.0.1:8200,http://192.168.0.2:8200",
    "index" = "test",
    "user" = "root",
    "password" = "root",
    "nodes_discovery" = "true"
    );
    参数说明:
    参数
    说明
    nodes_discovery
    是否开启 EST 节点发现,默认为 true
    当配置为 true 时,Doris 将从 ES 找到所有可用的相关数据节点(在上面分配的分片)。如果 ES 数据节点的地址没有被 Doris BE 访问,则设置为 false。ES 群部署在与公共 Internet 隔离的内网,用户通过代理访问。

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

    如果开启应设置为true,默认为 false(http_ssl_enabled=true) 。
    CREATE EXTERNAL TABLE `test` (
    `k1` bigint(20) COMMENT "",
    `k2` datetime COMMENT "",
    `k3` varchar(20) COMMENT "",
    `k4` varchar(100) COMMENT "",
    `k5` float COMMENT ""
    ) ENGINE=ELASTICSEARCH
    PROPERTIES (
    "hosts" = "http://192.168.0.1:8200,http://192.168.0.2:8200",
    "index" = "test",
    "user" = "root",
    "password" = "root",
    "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 的每个分片中并发的从_sourcedocvalue 中获取数据。
    4. Doris 计算完结果后,返回给用户。

    最佳实践

    时间类型字段使用建议

    在 ES 中,时间类型的字段使用十分灵活,但是在 Doris On ES 中如果对时间类型字段的类型设置不当,则会造成过滤条件无法下推。 创建索引时对时间类型格式的设置做最大程度的格式兼容:
    "dt": {
    "type": "date",
    "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
    }
    在 Doris 中建立该字段时建议设置为 datedatetime,也可以设置为 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 会出现显示错误。

    获取 ES 元数据字段_id

    导入文档在不指定_id的情况下 ES 会给每个文档分配一个全局唯一的_id即主键, 用户也可以在导入时为文档指定一个含有特殊业务意义的_id;如果需要在 Doris On 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"
    }
    注意
    _id 字段的过滤条件仅支持 =in 两种。
    _id 字段只能是 varchar 类型。

    常见问题

    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 中进行计算。
    联系我们

    联系我们,为您的业务提供专属服务。

    技术支持

    如果你想寻求进一步的帮助,通过工单与我们进行联络。我们提供7x24的工单服务。

    7x24 电话支持