You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by GitBox <gi...@apache.org> on 2020/06/15 08:09:02 UTC
[GitHub] [incubator-doris] wuyunfeng opened a new pull request #3867: [Doris On ES] refator documentation for doe
wuyunfeng opened a new pull request #3867:
URL: https://github.com/apache/incubator-doris/pull/3867
The past few months Doris On ES make a lots of performance optimization and bug-fix, we should supplement and reorganize Doris On ES's documentation
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wutiangan commented on a change in pull request #3867: [Doris On ES][Docs] refator documentation for doe
Posted by GitBox <gi...@apache.org>.
wutiangan commented on a change in pull request #3867:
URL: https://github.com/apache/incubator-doris/pull/3867#discussion_r440105076
##########
File path: docs/en/extending-doris/doris-on-es.md
##########
@@ -26,62 +26,314 @@ under the License.
# Doris On ES
-Doris-On-ES combines Doris's distributed query planning capability with ES (Elastic search)'s full-text search capability to provide a more complete OLAP scenario solution:
+Doris-On-ES not only take advantage of Doris's distributed query planning capability but also ES (Elastic search)'s full-text search capability, provide a more complete OLAP scenario solution:
1. Multi-index Distributed Join Query in ES
2. Joint Query of Tables in Doris and ES, More Complex Full-Text Retrieval and Filtering
-3. Aggregated queries for fields of ES keyword type: suitable for frequent changes in index, tens of millions or more of single fragmented documents, and the cardinality of the field is very large
This document mainly introduces the realization principle and usage of this function.
-## Noun Interpretation
+## Glossary
+
+### Noun in Doris
* FE: Frontend, the front-end node of Doris. Responsible for metadata management and request access.
* BE: Backend, Doris's back-end node. Responsible for query execution and data storage.
-* Elastic search (ES): The most popular open source distributed search engine.
+
+### Noun in ES
+
* DataNode: The data storage and computing node of ES.
* MasterNode: The Master node of ES, which manages metadata, nodes, data distribution, etc.
* scroll: The built-in data set cursor feature of ES for streaming scanning and filtering of data.
+* _source: contains the original JSON document body that was passed at index time
+* doc_values: store the same values as the _source but in a column-oriented fashion
+* keyword: string datatype in ES, but the content not analyzed by analyzer
+* text: string datatype in ES, the content analyzed by analyzer
-## How to use it
+## How To Use
-### Create appearance
+### Create ES Index
```
-CREATE EXTERNAL TABLE `es_table` (
- `id` bigint(20) COMMENT "",
+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"
+ }
+ }
+ }
+ }
+}
+```
+
+### Add JSON documents to ES index
+
+```
+POST /_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}
+```
+
+### Create external ES table
+
+```
+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"
+);
+```
+
+The following parameters are accepted by ES table:
+
+参数 | 说明
Review comment:
change it to english
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] kangkaisen merged pull request #3867: [Doris On ES][Docs] refator documentation for doe
Posted by GitBox <gi...@apache.org>.
kangkaisen merged pull request #3867:
URL: https://github.com/apache/incubator-doris/pull/3867
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wutiangan commented on a change in pull request #3867: [Doris On ES][Docs] refator documentation for doe
Posted by GitBox <gi...@apache.org>.
wutiangan commented on a change in pull request #3867:
URL: https://github.com/apache/incubator-doris/pull/3867#discussion_r440109428
##########
File path: docs/zh-CN/extending-doris/doris-on-es.md
##########
@@ -30,72 +30,320 @@ Doris-On-ES将Doris的分布式查询规划能力和ES(Elasticsearch)的全文
1. ES中的多index分布式Join查询
2. Doris和ES中的表联合查询,更复杂的全文检索过滤
- 3. ES keyword类型字段的聚合查询:适用于index 频繁发生变化、单个分片文档数量千万级以上且该字段基数(cardinality)非常大
本文档主要介绍该功能的实现原理、使用方式等。
## 名词解释
-* FE:Frontend,Doris 的前端节点。负责元数据管理和请求接入。
-* BE:Backend,Doris 的后端节点。负责查询执行和数据存储。
-* Elasticsearch(ES):目前最流行的开源分布式搜索引擎。
-* DataNode:ES的数据存储与计算节点。
-* MasterNode:ES的Master节点,管理元数据、节点、数据分布等。
-* scroll:ES内置的数据集游标特性,用来对数据进行流式扫描和过滤。
+### Doirs相关
+* 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索引
```
-CREATE EXTERNAL TABLE `es_table` (
- `id` bigint(20) COMMENT "",
+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索引导入数据
+
+```
+POST /_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 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名字
+**type** | index的type,不指定的情况会使用_doc
+**user** | ES集群用户名
+**password** | 对应用户的密码信息
+
+* ES 7.x之前的集群请注意在建表的时候选择正确的**索引类型type**
+* 认证方式仅支持Http Bastic认证,完全开源的ES集群用户和密码不需要指定,需要确保该用户有访问: /\_cluster/state/、\_nodes/http等路径权限和对index的读权限
+* Doris表中的列名需要和ES中的字段名完全匹配,字段类型应该保持一致
+* **ENGINE**必须是 **Elasticsearch**
+
+##### 过滤条件下推
+`Doris On ES`一个重要的功能就是过滤条件的下推: 过滤条件下推给ES,这样只有真正满足条件的数据才会被返回,能够显著的提高查询性能和降低Doris和Elasticsearch的CPU、memory、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 |
+
+##### 数据类型映射
+
+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
-PARTITION BY RANGE(`id`)
-()
PROPERTIES (
"hosts" = "http://192.168.0.1:8200,http://192.168.0.2:8200",
+"index" = "test”,
+"type" = "doc",
"user" = "root",
"password" = "root",
-"index" = "tindex”,
-"type" = "doc"
+
+"enable_docvalue_scan" = "true"
);
```
参数说明:
参数 | 说明
---|---
-hosts | ES集群连接地址,可指定一个或多个,Doris通过这个地址获取到ES版本号、index的shard分布信息
-user | 开启basic认证的ES集群的用户名,需要确保该用户有访问: /\_cluster/state/\_nodes/http等路径权限和对index的读权限
-password | 对应用户的密码信息
-index | Doris中的表对应的ES的index名字,可以是alias
-type | 指定index的type,默认是_doc
-transport | 内部保留,默认为http
+**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”,
+"type" = "doc",
+"user" = "root",
+"password" = "root",
+
+"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进行条件过滤时比如=,Doris On ES会将查询转换为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,所以可以匹配
+
+### 查询用法
+
+完成在Doris中建立ES外表后,除了无法使用Doris中的数据模型(rollup、预聚合、物化视图等)外并无区别
+
+#### 基本查询
```
select * from es_table where k1 > 1000 and k3 ='term' or k4 like 'fu*z_'
```
-#### 扩展的esquery sql语法
+#### 扩展的esquery
通过`esquery`函数将一些无法用sql表述的ES query如match、geoshape等下推给ES进行过滤处理,`esquery`的第一个列名参数用于关联`index`,第二个参数是ES的基本`Query DSL`的json表述,使用花括号`{}`包含,json的`root key`有且只能有一个,如match、geo_shape、bool等
Review comment:
query后面需要加个括号
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wuyunfeng commented on a change in pull request #3867: [Doris On ES][Docs] refator documentation for doe
Posted by GitBox <gi...@apache.org>.
wuyunfeng commented on a change in pull request #3867:
URL: https://github.com/apache/incubator-doris/pull/3867#discussion_r440531919
##########
File path: docs/zh-CN/extending-doris/doris-on-es.md
##########
@@ -30,72 +30,320 @@ Doris-On-ES将Doris的分布式查询规划能力和ES(Elasticsearch)的全文
1. ES中的多index分布式Join查询
2. Doris和ES中的表联合查询,更复杂的全文检索过滤
- 3. ES keyword类型字段的聚合查询:适用于index 频繁发生变化、单个分片文档数量千万级以上且该字段基数(cardinality)非常大
本文档主要介绍该功能的实现原理、使用方式等。
## 名词解释
-* FE:Frontend,Doris 的前端节点。负责元数据管理和请求接入。
-* BE:Backend,Doris 的后端节点。负责查询执行和数据存储。
-* Elasticsearch(ES):目前最流行的开源分布式搜索引擎。
-* DataNode:ES的数据存储与计算节点。
-* MasterNode:ES的Master节点,管理元数据、节点、数据分布等。
-* scroll:ES内置的数据集游标特性,用来对数据进行流式扫描和过滤。
+### Doirs相关
+* 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索引
```
-CREATE EXTERNAL TABLE `es_table` (
- `id` bigint(20) COMMENT "",
+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索引导入数据
+
+```
+POST /_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 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名字
+**type** | index的type,不指定的情况会使用_doc
+**user** | ES集群用户名
+**password** | 对应用户的密码信息
+
+* ES 7.x之前的集群请注意在建表的时候选择正确的**索引类型type**
+* 认证方式仅支持Http Bastic认证,完全开源的ES集群用户和密码不需要指定,需要确保该用户有访问: /\_cluster/state/、\_nodes/http等路径权限和对index的读权限
+* Doris表中的列名需要和ES中的字段名完全匹配,字段类型应该保持一致
+* **ENGINE**必须是 **Elasticsearch**
+
+##### 过滤条件下推
+`Doris On ES`一个重要的功能就是过滤条件的下推: 过滤条件下推给ES,这样只有真正满足条件的数据才会被返回,能够显著的提高查询性能和降低Doris和Elasticsearch的CPU、memory、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 |
+
+##### 数据类型映射
+
+Doris\ES | byte | short | integer | long | float | double| keyword | text | date
+------------- | ------------- | ------ | ---- | ----- | ---- | ------ | ----| --- | --- |
+tinyint | √ | | | | | | | |
+smallint | √ | √ | | | | | | |
+int | √ | √ | √ | | | | | |
+bigint | √ | √ | √ | √ | | | | |
+float | | | | | √ | | | |
+double | | | | | | √ | | |
+char | | | | | | | √ | √ |
+varchar | | | | | | | √ | √ |
+date | | | | | | | | | √|
+datetime | | | | | | | | | √|
+
Review comment:
emm, not support decimal
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wutiangan commented on a change in pull request #3867: [Doris On ES][Docs] refator documentation for doe
Posted by GitBox <gi...@apache.org>.
wutiangan commented on a change in pull request #3867:
URL: https://github.com/apache/incubator-doris/pull/3867#discussion_r440107816
##########
File path: docs/zh-CN/extending-doris/doris-on-es.md
##########
@@ -30,72 +30,320 @@ Doris-On-ES将Doris的分布式查询规划能力和ES(Elasticsearch)的全文
1. ES中的多index分布式Join查询
2. Doris和ES中的表联合查询,更复杂的全文检索过滤
- 3. ES keyword类型字段的聚合查询:适用于index 频繁发生变化、单个分片文档数量千万级以上且该字段基数(cardinality)非常大
本文档主要介绍该功能的实现原理、使用方式等。
## 名词解释
-* FE:Frontend,Doris 的前端节点。负责元数据管理和请求接入。
-* BE:Backend,Doris 的后端节点。负责查询执行和数据存储。
-* Elasticsearch(ES):目前最流行的开源分布式搜索引擎。
-* DataNode:ES的数据存储与计算节点。
-* MasterNode:ES的Master节点,管理元数据、节点、数据分布等。
-* scroll:ES内置的数据集游标特性,用来对数据进行流式扫描和过滤。
+### Doirs相关
+* 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索引
```
-CREATE EXTERNAL TABLE `es_table` (
- `id` bigint(20) COMMENT "",
+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索引导入数据
+
+```
+POST /_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 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名字
+**type** | index的type,不指定的情况会使用_doc
+**user** | ES集群用户名
+**password** | 对应用户的密码信息
+
+* ES 7.x之前的集群请注意在建表的时候选择正确的**索引类型type**
+* 认证方式仅支持Http Bastic认证,完全开源的ES集群用户和密码不需要指定,需要确保该用户有访问: /\_cluster/state/、\_nodes/http等路径权限和对index的读权限
+* Doris表中的列名需要和ES中的字段名完全匹配,字段类型应该保持一致
+* **ENGINE**必须是 **Elasticsearch**
+
+##### 过滤条件下推
+`Doris On ES`一个重要的功能就是过滤条件的下推: 过滤条件下推给ES,这样只有真正满足条件的数据才会被返回,能够显著的提高查询性能和降低Doris和Elasticsearch的CPU、memory、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 |
+
+##### 数据类型映射
+
+Doris\ES | byte | short | integer | long | float | double| keyword | text | date
+------------- | ------------- | ------ | ---- | ----- | ---- | ------ | ----| --- | --- |
+tinyint | √ | | | | | | | |
+smallint | √ | √ | | | | | | |
+int | √ | √ | √ | | | | | |
+bigint | √ | √ | √ | √ | | | | |
+float | | | | | √ | | | |
+double | | | | | | √ | | |
+char | | | | | | | √ | √ |
+varchar | | | | | | | √ | √ |
+date | | | | | | | | | √|
+datetime | | | | | | | | | √|
+
Review comment:
es support decimal?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] EmmyMiao87 commented on a change in pull request #3867: [Doris On ES][Docs] refator documentation for doe
Posted by GitBox <gi...@apache.org>.
EmmyMiao87 commented on a change in pull request #3867:
URL: https://github.com/apache/incubator-doris/pull/3867#discussion_r440052521
##########
File path: docs/zh-CN/extending-doris/doris-on-es.md
##########
@@ -30,72 +30,320 @@ Doris-On-ES将Doris的分布式查询规划能力和ES(Elasticsearch)的全文
1. ES中的多index分布式Join查询
2. Doris和ES中的表联合查询,更复杂的全文检索过滤
- 3. ES keyword类型字段的聚合查询:适用于index 频繁发生变化、单个分片文档数量千万级以上且该字段基数(cardinality)非常大
本文档主要介绍该功能的实现原理、使用方式等。
## 名词解释
-* FE:Frontend,Doris 的前端节点。负责元数据管理和请求接入。
-* BE:Backend,Doris 的后端节点。负责查询执行和数据存储。
-* Elasticsearch(ES):目前最流行的开源分布式搜索引擎。
-* DataNode:ES的数据存储与计算节点。
-* MasterNode:ES的Master节点,管理元数据、节点、数据分布等。
-* scroll:ES内置的数据集游标特性,用来对数据进行流式扫描和过滤。
+### Doirs相关
+* FE:Frontend,Doris 的前端节点,负责元数据管理和请求接入
+* BE:Backend,Doris 的后端节点,负责查询执行和数据存储
+### ES相关
+* DataNode:ES的数据存储与计算节点
+* MasterNode:ES的Master节点,管理元数据、节点、数据分布等
+* scroll:ES内置的数据集游标特性,用来对数据进行流式扫描和过滤
+* _source: 导入时传入的原始JSON格式文档内容
Review comment:
用``将变量名括起来,可以避免下划线造成的斜体影响
##########
File path: docs/zh-CN/extending-doris/doris-on-es.md
##########
@@ -30,72 +30,320 @@ Doris-On-ES将Doris的分布式查询规划能力和ES(Elasticsearch)的全文
1. ES中的多index分布式Join查询
2. Doris和ES中的表联合查询,更复杂的全文检索过滤
- 3. ES keyword类型字段的聚合查询:适用于index 频繁发生变化、单个分片文档数量千万级以上且该字段基数(cardinality)非常大
本文档主要介绍该功能的实现原理、使用方式等。
## 名词解释
-* FE:Frontend,Doris 的前端节点。负责元数据管理和请求接入。
-* BE:Backend,Doris 的后端节点。负责查询执行和数据存储。
-* Elasticsearch(ES):目前最流行的开源分布式搜索引擎。
-* DataNode:ES的数据存储与计算节点。
-* MasterNode:ES的Master节点,管理元数据、节点、数据分布等。
-* scroll:ES内置的数据集游标特性,用来对数据进行流式扫描和过滤。
+### Doirs相关
+* FE:Frontend,Doris 的前端节点,负责元数据管理和请求接入
+* BE:Backend,Doris 的后端节点,负责查询执行和数据存储
+### ES相关
+* DataNode:ES的数据存储与计算节点
+* MasterNode:ES的Master节点,管理元数据、节点、数据分布等
+* scroll:ES内置的数据集游标特性,用来对数据进行流式扫描和过滤
+* _source: 导入时传入的原始JSON格式文档内容
+* doc_values: ES/Lucene 中字段的列式存储定义
Review comment:
同上
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wutiangan commented on a change in pull request #3867: [Doris On ES][Docs] refator documentation for doe
Posted by GitBox <gi...@apache.org>.
wutiangan commented on a change in pull request #3867:
URL: https://github.com/apache/incubator-doris/pull/3867#discussion_r440107314
##########
File path: docs/zh-CN/extending-doris/doris-on-es.md
##########
@@ -30,72 +30,320 @@ Doris-On-ES将Doris的分布式查询规划能力和ES(Elasticsearch)的全文
1. ES中的多index分布式Join查询
2. Doris和ES中的表联合查询,更复杂的全文检索过滤
- 3. ES keyword类型字段的聚合查询:适用于index 频繁发生变化、单个分片文档数量千万级以上且该字段基数(cardinality)非常大
本文档主要介绍该功能的实现原理、使用方式等。
## 名词解释
-* FE:Frontend,Doris 的前端节点。负责元数据管理和请求接入。
-* BE:Backend,Doris 的后端节点。负责查询执行和数据存储。
-* Elasticsearch(ES):目前最流行的开源分布式搜索引擎。
-* DataNode:ES的数据存储与计算节点。
-* MasterNode:ES的Master节点,管理元数据、节点、数据分布等。
-* scroll:ES内置的数据集游标特性,用来对数据进行流式扫描和过滤。
+### Doirs相关
+* 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索引
```
-CREATE EXTERNAL TABLE `es_table` (
- `id` bigint(20) COMMENT "",
+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索引导入数据
+
+```
+POST /_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 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名字
+**type** | index的type,不指定的情况会使用_doc
+**user** | ES集群用户名
+**password** | 对应用户的密码信息
+
+* ES 7.x之前的集群请注意在建表的时候选择正确的**索引类型type**
+* 认证方式仅支持Http Bastic认证,完全开源的ES集群用户和密码不需要指定,需要确保该用户有访问: /\_cluster/state/、\_nodes/http等路径权限和对index的读权限
Review comment:
"完全开源的ES集群用户和密码不需要指定"
This sentence doesn't read smoothly
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wutiangan commented on a change in pull request #3867: [Doris On ES][Docs] refator documentation for doe
Posted by GitBox <gi...@apache.org>.
wutiangan commented on a change in pull request #3867:
URL: https://github.com/apache/incubator-doris/pull/3867#discussion_r440110249
##########
File path: docs/zh-CN/extending-doris/doris-on-es.md
##########
@@ -195,37 +443,30 @@ select * from es_table where esquery(k4, ' {
1. 创建ES外表后,FE会请求建表指定的主机,获取所有节点的HTTP端口信息以及index的shard分布信息等,如果请求失败会顺序遍历host列表直至成功或完全失败
-2. 查询时,会根据FE得到的一些节点信息和index的元数据信息,生成查询计划并发给对应的BE节点
+2. 查询时会根据FE得到的一些节点信息和index的元数据信息,生成查询计划并发给对应的BE节点
-3. BE节点会根据`就近原则`即优先请求本地部署的ES节点,BE通过`HTTP Scroll`方式流式的从ES index的每个分片中并发的获取数据
+3. BE节点会根据`就近原则`即优先请求本地部署的ES节点,BE通过`HTTP Scroll`方式流式的从ES index的每个分片中并发的从`_source`或`docvalue`中获取数据
-4. 计算完结果后,返回给client端
-
-## Push-Down operations
-`Doris On Elasticsearch`一个重要的功能就是过滤条件的下推: 过滤条件下推给ES,这样只有真正满足条件的数据才会被返回,能够显著的提高查询性能和降低Doris和Elasticsearch的CPU、memory、IO利用率
-
-下面的操作符(Operators)会被优化成如下下推filters:
-
-| SQL syntax | ES 5.x+ syntax |
-|-------|:---:|
-| = | term query|
-| in | terms query |
-| > , < , >= , ⇐ | range |
-| and | bool.filter |
-| or | bool.should |
-| not | bool.must_not |
-| not in | bool.must_not + terms |
-| esquery | ES Query DSL |
+4. Doris计算完结果后,返回给用户
-## 其他说明
+## Q&A
-1. ES的版本要求
+1. Doris On ES对ES的版本要求
- ES主版本大于5,ES在2.x之前和5.x之后数据的扫描方式不同,目前支持5.x之后的
+ 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中进行计算
+
+5. 日期类型字段的过滤条件无法下推
+
+ 日期类型的字段因为时间格式的问题,大多数情况下都不不会下推;对于日期类型的过滤可以采用字符串形式,日期格式需要和ES中保持完全一致
Review comment:
不不=不
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wutiangan commented on a change in pull request #3867: [Doris On ES][Docs] refator documentation for doe
Posted by GitBox <gi...@apache.org>.
wutiangan commented on a change in pull request #3867:
URL: https://github.com/apache/incubator-doris/pull/3867#discussion_r440109428
##########
File path: docs/zh-CN/extending-doris/doris-on-es.md
##########
@@ -30,72 +30,320 @@ Doris-On-ES将Doris的分布式查询规划能力和ES(Elasticsearch)的全文
1. ES中的多index分布式Join查询
2. Doris和ES中的表联合查询,更复杂的全文检索过滤
- 3. ES keyword类型字段的聚合查询:适用于index 频繁发生变化、单个分片文档数量千万级以上且该字段基数(cardinality)非常大
本文档主要介绍该功能的实现原理、使用方式等。
## 名词解释
-* FE:Frontend,Doris 的前端节点。负责元数据管理和请求接入。
-* BE:Backend,Doris 的后端节点。负责查询执行和数据存储。
-* Elasticsearch(ES):目前最流行的开源分布式搜索引擎。
-* DataNode:ES的数据存储与计算节点。
-* MasterNode:ES的Master节点,管理元数据、节点、数据分布等。
-* scroll:ES内置的数据集游标特性,用来对数据进行流式扫描和过滤。
+### Doirs相关
+* 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索引
```
-CREATE EXTERNAL TABLE `es_table` (
- `id` bigint(20) COMMENT "",
+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索引导入数据
+
+```
+POST /_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 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名字
+**type** | index的type,不指定的情况会使用_doc
+**user** | ES集群用户名
+**password** | 对应用户的密码信息
+
+* ES 7.x之前的集群请注意在建表的时候选择正确的**索引类型type**
+* 认证方式仅支持Http Bastic认证,完全开源的ES集群用户和密码不需要指定,需要确保该用户有访问: /\_cluster/state/、\_nodes/http等路径权限和对index的读权限
+* Doris表中的列名需要和ES中的字段名完全匹配,字段类型应该保持一致
+* **ENGINE**必须是 **Elasticsearch**
+
+##### 过滤条件下推
+`Doris On ES`一个重要的功能就是过滤条件的下推: 过滤条件下推给ES,这样只有真正满足条件的数据才会被返回,能够显著的提高查询性能和降低Doris和Elasticsearch的CPU、memory、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 |
+
+##### 数据类型映射
+
+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
-PARTITION BY RANGE(`id`)
-()
PROPERTIES (
"hosts" = "http://192.168.0.1:8200,http://192.168.0.2:8200",
+"index" = "test”,
+"type" = "doc",
"user" = "root",
"password" = "root",
-"index" = "tindex”,
-"type" = "doc"
+
+"enable_docvalue_scan" = "true"
);
```
参数说明:
参数 | 说明
---|---
-hosts | ES集群连接地址,可指定一个或多个,Doris通过这个地址获取到ES版本号、index的shard分布信息
-user | 开启basic认证的ES集群的用户名,需要确保该用户有访问: /\_cluster/state/\_nodes/http等路径权限和对index的读权限
-password | 对应用户的密码信息
-index | Doris中的表对应的ES的index名字,可以是alias
-type | 指定index的type,默认是_doc
-transport | 内部保留,默认为http
+**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”,
+"type" = "doc",
+"user" = "root",
+"password" = "root",
+
+"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进行条件过滤时比如=,Doris On ES会将查询转换为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,所以可以匹配
+
+### 查询用法
+
+完成在Doris中建立ES外表后,除了无法使用Doris中的数据模型(rollup、预聚合、物化视图等)外并无区别
+
+#### 基本查询
```
select * from es_table where k1 > 1000 and k3 ='term' or k4 like 'fu*z_'
```
-#### 扩展的esquery sql语法
+#### 扩展的esquery
通过`esquery`函数将一些无法用sql表述的ES query如match、geoshape等下推给ES进行过滤处理,`esquery`的第一个列名参数用于关联`index`,第二个参数是ES的基本`Query DSL`的json表述,使用花括号`{}`包含,json的`root key`有且只能有一个,如match、geo_shape、bool等
Review comment:
query后面需要加个括号,将如match、geoshape括起来
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wuyunfeng commented on a change in pull request #3867: [Doris On ES][Docs] refator documentation for doe
Posted by GitBox <gi...@apache.org>.
wuyunfeng commented on a change in pull request #3867:
URL: https://github.com/apache/incubator-doris/pull/3867#discussion_r440088348
##########
File path: docs/zh-CN/extending-doris/doris-on-es.md
##########
@@ -30,72 +30,320 @@ Doris-On-ES将Doris的分布式查询规划能力和ES(Elasticsearch)的全文
1. ES中的多index分布式Join查询
2. Doris和ES中的表联合查询,更复杂的全文检索过滤
- 3. ES keyword类型字段的聚合查询:适用于index 频繁发生变化、单个分片文档数量千万级以上且该字段基数(cardinality)非常大
本文档主要介绍该功能的实现原理、使用方式等。
## 名词解释
-* FE:Frontend,Doris 的前端节点。负责元数据管理和请求接入。
-* BE:Backend,Doris 的后端节点。负责查询执行和数据存储。
-* Elasticsearch(ES):目前最流行的开源分布式搜索引擎。
-* DataNode:ES的数据存储与计算节点。
-* MasterNode:ES的Master节点,管理元数据、节点、数据分布等。
-* scroll:ES内置的数据集游标特性,用来对数据进行流式扫描和过滤。
+### Doirs相关
+* FE:Frontend,Doris 的前端节点,负责元数据管理和请求接入
+* BE:Backend,Doris 的后端节点,负责查询执行和数据存储
+### ES相关
+* DataNode:ES的数据存储与计算节点
+* MasterNode:ES的Master节点,管理元数据、节点、数据分布等
+* scroll:ES内置的数据集游标特性,用来对数据进行流式扫描和过滤
+* _source: 导入时传入的原始JSON格式文档内容
Review comment:
这应该显示问题吧,表格里面的加\就可以了,类别的好像不用
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] wutiangan commented on a change in pull request #3867: [Doris On ES][Docs] refator documentation for doe
Posted by GitBox <gi...@apache.org>.
wutiangan commented on a change in pull request #3867:
URL: https://github.com/apache/incubator-doris/pull/3867#discussion_r440105196
##########
File path: docs/en/extending-doris/doris-on-es.md
##########
@@ -26,62 +26,314 @@ under the License.
# Doris On ES
-Doris-On-ES combines Doris's distributed query planning capability with ES (Elastic search)'s full-text search capability to provide a more complete OLAP scenario solution:
+Doris-On-ES not only take advantage of Doris's distributed query planning capability but also ES (Elastic search)'s full-text search capability, provide a more complete OLAP scenario solution:
1. Multi-index Distributed Join Query in ES
2. Joint Query of Tables in Doris and ES, More Complex Full-Text Retrieval and Filtering
-3. Aggregated queries for fields of ES keyword type: suitable for frequent changes in index, tens of millions or more of single fragmented documents, and the cardinality of the field is very large
This document mainly introduces the realization principle and usage of this function.
-## Noun Interpretation
+## Glossary
+
+### Noun in Doris
* FE: Frontend, the front-end node of Doris. Responsible for metadata management and request access.
* BE: Backend, Doris's back-end node. Responsible for query execution and data storage.
-* Elastic search (ES): The most popular open source distributed search engine.
+
+### Noun in ES
+
* DataNode: The data storage and computing node of ES.
* MasterNode: The Master node of ES, which manages metadata, nodes, data distribution, etc.
* scroll: The built-in data set cursor feature of ES for streaming scanning and filtering of data.
+* _source: contains the original JSON document body that was passed at index time
+* doc_values: store the same values as the _source but in a column-oriented fashion
+* keyword: string datatype in ES, but the content not analyzed by analyzer
+* text: string datatype in ES, the content analyzed by analyzer
-## How to use it
+## How To Use
-### Create appearance
+### Create ES Index
```
-CREATE EXTERNAL TABLE `es_table` (
- `id` bigint(20) COMMENT "",
+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"
+ }
+ }
+ }
+ }
+}
+```
+
+### Add JSON documents to ES index
+
+```
+POST /_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}
+```
+
+### Create external ES table
+
+```
+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"
+);
+```
+
+The following parameters are accepted by ES table:
+
+参数 | 说明
+---|---
+**hosts** | ES Cluster Connection Address, maybe one or more node, load-balance is also accepted
+**index** | the related ES index name
+**type** | the type for this index,If not specified, `_doc` will be used
+**user** | username for ES
+**password** | password for the user
+
+* For clusters before 7.x, please pay attention to choosing the correct type when building the table
+* The authentication method only supports Http Bastic authentication, and the fully open source ES cluster user and password do not need to be specified,Need to ensure that the user has access to /_cluster/state and other path permissions and read permissions to index
+* The column names in the Doris table need to exactly match the field names in the ES, and the field types should be as consistent as possible
+* **ENGINE** must be: **Elasticsearch**
+
+##### Filter to push down
+
+An important ability of `Doris On ES` is the push-down of filter conditions: The filtering conditions are pushed to ES, so that only the data that really meets the conditions will be returned, which can significantly improve query performance and reduce CPU, memory, and IO utilization of Doris and ES
+
+The following operators (Operators) will be optimized to the following 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 | QueryDSL in ES native json form |
+
+##### Data type mapping
+
+Doris\ES | byte | short | integer | long | float | double| keyword | text | date
+------------- | ------------- | ------ | ---- | ----- | ---- | ------ | ----| --- | --- |
+tinyint | √ | | | | | | | |
+smallint | √ | √ | | | | | | |
+int | √ | √ | √ | | | | | |
+bigint | √ | √ | √ | √ | | | | |
+float | | | | | √ | | | |
+double | | | | | | √ | | |
+char | | | | | | | √ | √ |
+varchar | | | | | | | √ | √ |
+date | | | | | | | | | √|
+datetime | | | | | | | | | √|
+
+
+### Enable column scan to optimize query speed(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
-PARTITION BY RANGE(`id`)
-()
PROPERTIES (
"hosts" = "http://192.168.0.1:8200,http://192.168.0.2:8200",
+"index" = "test”,
+"type" = "doc",
"user" = "root",
"password" = "root",
-"index" = "tindex”,
-"type" = "doc"
+
+"enable_docvalue_scan" = "true"
);
```
-Description of parameters:
+Parameter Description:
-Parameter | description
+Parameter | Description
---|---
-Hosts | ES Cluster Connection Address, which can specify one or more, through which Doris obtains the share distribution information of ES version number and index
-User | Open the user name of the ES cluster authenticated by basic, you need to ensure that the user has access to: / cluster / state / nodes / HTTP and other path permissions and read permissions for index
-Password | corresponding user's password information
-The index name of the ES corresponding to the table in index | Doris can be alias
-Type | Specifies the type of index, defaulting to _doc
-Transport | Internal reservation, default to http
+**enable\_docvalue\_scan** | whether to enable ES/Lucene column storage to get the value of the query field, the default is false
+
+Doris obtains data from ES following the following two principles:
+
+* **Best effort**: Automatically detect whether the column to be read has column storage enabled (doc_value: true).If all the fields obtained have column storage, Doris will obtain the values of all fields from the column storage(doc_values)
+* **Automatic downgrade**: If the field to be obtained has one or more field that is not have doc_value, the values of all fields will be parsed from the line store `_source`
+
+##### Advantage:
+
+By default, Doris On ES will get all the required columns from the row storage, which is `_source`, and the storage of `_source` is the origin json format document,Inferior to column storage in batch read performance,Especially obvious when only a few columns are needed,When only a few columns are obtained, the performance of docvalue is about ten times that of _source
-### Query
+##### Tip
+1. Fields of type `text` are not column-stored in ES, so if the value of the field to be obtained has a field of type `text`, it will be automatically downgraded to get from `_source`
+2. In the case of too many fields obtained (`>= 25`), the performance of getting field values from `docvalue` will be basically the same as getting field values from `_source`
-#### Basic Conditions Filtration
+
+### Detect keyword type field(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”,
+"type" = "doc",
+"user" = "root",
+"password" = "root",
+
+"enable_keyword_sniff" = "true"
+);
+```
+
+参数说明:
+
Review comment:
change it to english
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org