This is an automated email from the ASF dual-hosted git repository.
zykkk pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 32fce013f7 [feature](docs) add docs dbt-doris adapter (#22067)
32fce013f7 is described below
commit 32fce013f7fbbe709e73650464e33e758ff4f4d5
Author: catpineapple <42031973+catpineap...@users.noreply.github.com>
AuthorDate: Fri Jul 21 23:34:47 2023 +0800
[feature](docs) add docs dbt-doris adapter (#22067)
---
docs/en/docs/ecosystem/dbt-doris-adapter.md | 261 +++++++++++++++++++++
docs/sidebars.json | 1 +
docs/zh-CN/docs/ecosystem/dbt-doris-adapter.md | 260 ++++++++++++++++++++
.../dbt-doris/dbt/adapters/doris/__version__.py | 2 +-
.../dbt-doris/dbt/include/doris/dbt_project.yml | 2 +-
extension/dbt-doris/setup.py | 2 +-
6 files changed, 525 insertions(+), 3 deletions(-)
diff --git a/docs/en/docs/ecosystem/dbt-doris-adapter.md
b/docs/en/docs/ecosystem/dbt-doris-adapter.md
new file mode 100644
index 0000000000..0f15e7d7f4
--- /dev/null
+++ b/docs/en/docs/ecosystem/dbt-doris-adapter.md
@@ -0,0 +1,261 @@
+---
+{
+"title": "DBT Doris Adapter",
+"language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# DBT Doris Adapter
+
+[DBT(Data Build Tool)](https://docs.getdbt.com/docs/introduction) is a
component that focuses on doing T (Transform) in ELT (extraction, loading,
transformation) - the "transformation data" link
+The `dbt-doris` adapter is developed based on `dbt-core` 1.5.0 and relies on
the `mysql-connector-python` driver to convert data to doris.
+
+git：https://github.com/apache/doris/tree/master/extension/dbt-doris
+
+## version
+
+| doris | python | dbt-core |
+|---------|--------------|----------|
+| >=1.2.5 | >=3.8,<=3.10 | >=1.5.0 |
+
+
+## dbt-doris adapter Instructions
+
+### dbt-doris adapter install
+use pip install：
+```shell
+pip install dbt-doris
+```
+check version：
+```shell
+dbt --version
+```
+if command not found: dbt：
+```shell
+ln -s /usr/local/python3/bin/dbt /usr/bin/dbt
+```
+
+### dbt-doris adapter project init
+```shell
+dbt init
+```
+Users need to prepare the following information to init dbt project
+
+| name | default | meaning
|
+|----------|------|-------------------------------------------------------------------------------------------------------------------------------------------|
+| project | | project name
|
+| database | | Enter the corresponding number to select the adapter
（选择doris）
|
+| host | | doris host
|
+| port | 9030 | doris MySQL Protocol Port
|
+| schema | | In dbt-doris, it is equivalent to database, Database name
|
+| username | | doris username
|
+| password | | doris password
|
+| threads | 1 | Parallelism in dbt-doris (setting a parallelism that does
not match the cluster capability will increase the risk of dbt running failure)
|
+
+
+### dbt-doris adapter run
+For dbt run documentation, please refer to
[here](https://docs.getdbt.com/docs/get-started/run-your-dbt-projects).
+Go to the project directory and execute the default dbt model:
+```shell
+dbt run
+```
+model：`my_first_dbt_model`和`my_second_dbt_model`
+
+They are materialized `table` and `view` respectively.
+then login to doris to view the data results and table creation statements of
`my_first_dbt_model` and `my_second_dbt_model`.
+### dbt-doris adapter Materialization
+dbt-doris Materialization support three:
+1. view
+2. table
+3. incremental
+
+#### View
+
+Using `view` as the materialization, Models will be rebuilt as views each time
they are run through the create view as statement. (By default, the
materialization method of dbt is view)
+```
+Advantages: No extra data is stored, and views on top of the source data will
always contain the latest records.
+Disadvantages: View queries that perform large transformations or are nested
on top of other views are slow.
+Recommendation: Usually start with the view of the model and only change to
another materialization if there are performance issues. Views are best suited
for models that do not undergo major transformations, such as renaming, column
changes.
+```
+
+config：
+```yaml
+models:
+ <resource-path>:
+ +materialized: view
+```
+Or write in the model file
+```jinja
+{{ config(materialized = "view") }}
+```
+
+#### Table
+
+When using the `table` materialization mode, your model is rebuilt as a table
at each run with a `create table as select` statement.
+For the tablet materialization of dbt, dbt-doris uses the following steps to
ensure the atomicity of data changes:
+1. first create a temporary table: `create table this_table_temp as {{ model
sql}}`.
+2. Determine whether `this_table` does not exist, that is, it is created for
the first time, execute `rename`, and change the temporary table to the final
table.
+3. if already exists, then `alter table this_table REPLACE WITH TABLE
this_table_temp PROPERTIES('swap' = 'False')`，This operation can exchange the
table name and delete the `this_table_temp` temporary
table，[this](../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-REPLACE.md)
guarantees the atomicity of this operation through the transaction mechanism
of the Doris.
+
+```
+Advantages: table query speed will be faster than view.
+Disadvantages: The table takes a long time to build or rebuild, additional
data will be stored, and incremental data synchronization cannot be performed.
+Recommendation: It is recommended to use the table materialization method for
models queried by BI tools or models with slow operations such as downstream
queries and conversions.
+```
+
+config:
+```yaml
+models:
+ <resource-path>:
+ +materialized: table
+ +duplicate_key: [ <column-name>, ... ],
+ +replication_num: int,
+ +partition_by: [ <column-name>, ... ],
+ +partition_type: <engine-type>,
+ +partition_by_init: [<pertition-init>, ... ]
+ +distributed_by: [ <column-name>, ... ],
+ +buckets: int | 'auto',
+ +properties: {<key>:<value>,...}
+```
+Or write in the model file:
+```jinja
+{{ config(
+ materialized = "table",
+ duplicate_key = [ "<column-name>", ... ],
+ replication_num = "<int>"
+ partition_by = [ "<column-name>", ... ],
+ partition_type = "<engine-type>",
+ partition_by_init = ["<pertition-init>", ... ]
+ distributed_by = [ "<column-name>", ... ],
+ buckets = "<int>" | "auto",
+ properties = {"<key>":"<value>",...}
+ ...
+ ]
+) }}
+```
+
+The details of the above configuration items are as follows:
+
+| item | description
| Required? |
+|---------------------|------------------------------------------------------------|-----------|
+| `materialized` | The materialized form of the table (Doris Duplicate
table) | Required |
+| `duplicate_key` | Doris Duplicate key
| Optional |
+| `replication_num` | Number of table replicas
| Optional |
+| `partition_by` | Table partition column
| Optional |
+| `partition_type` | Table partition type, `range` or `list`.(default:
`RANGE`) | Optional |
+| `partition_by_init` | Initialized table partitions
| Optional |
+| `distributed_by` | Table distributed column
| Optional |
+| `buckets` | Bucket size
| Optional |
+| `properties` | Doris table properties
| Optional |
+
+
+
+
+#### Incremental
+
+Based on the incremental model results of the last run of dbt, records are
incrementally inserted or updated into the table.
+There are two ways to realize the increment of doris. `incremental_strategy`
has two incremental strategies:
+* `insert_overwrite`: Depends on the doris `unique` model. If there is an
incremental requirement, specify the materialization as incremental when
initializing the data of the model, and aggregate by specifying the aggregation
column to achieve incremental data coverage.
+* `append`: Depends on the doris `duplicate` model, it only appends
incremental data and does not involve modifying any historical data. So no need
to specify unique_key.
+```
+Advantages: Significantly reduces build time by only converting new records.
+Disadvantages: incremental mode requires additional configuration, which is an
advanced usage of dbt, and requires the support of complex scenarios and the
adaptation of corresponding components.
+Recommendation: The incremental model is best for event-based scenarios or
when dbt runs become too slow
+```
+
+config:
+```yaml
+models:
+ <resource-path>:
+ +materialized: incremental
+ +incremental_strategy: <strategy>
+ +unique_key: [ <column-name>, ... ],
+ +replication_num: int,
+ +partition_by: [ <column-name>, ... ],
+ +partition_type: <engine-type>,
+ +partition_by_init: [<pertition-init>, ... ]
+ +distributed_by: [ <column-name>, ... ],
+ +buckets: int | 'auto',
+ +properties: {<key>:<value>,...}
+```
+Or write in the model file:
+```jinja
+{{ config(
+ materialized = "incremental",
+ incremental_strategy = "<strategy>"
+ unique_key = [ "<column-name>", ... ],
+ replication_num = "<int>"
+ partition_by = [ "<column-name>", ... ],
+ partition_type = "<engine-type>",
+ partition_by_init = ["<pertition-init>", ... ]
+ distributed_by = [ "<column-name>", ... ],
+ buckets = "<int>" | "auto",
+ properties = {"<key>":"<value>",...}
+ ...
+ ]
+) }}
+```
+
+The details of the above configuration items are as follows:
+
+| item | description
| Required? |
+|----------------------------|-------------------------------------------------------------------|-----------|
+| `materialized` | The materialized form of the table (Doris
Duplicate/Unique table) | Required |
+| `incremental_strategy` | Incremental_strategy
| Optional |
+| `unique_key` | Doris Unique key
| Optional |
+| `replication_num` | Number of table replicas
| Optional |
+| `partition_by` | Table partition column
| Optional |
+| `partition_type` | Table partition type, `range` or
`list`.(default: `RANGE`) | Optional |
+| `partition_by_init` | Initialized table partitions
| Optional |
+| `distributed_by` | Table distributed column
| Optional |
+| `buckets` | Bucket size
| Optional |
+| `properties` | Doris table properties
| Optional |
+
+
+
+### dbt-doris adapter seed
+
+[`seed`](https://docs.getdbt.com/faqs/seeds/build-one-seed) is a functional
module used to load data files such as csv. It is a way to load files into the
library and participate in model building, but there are the following
precautions:
+1. Seeds should not be used to load raw data (for example, large CSV exports
from a production database).
+2. Since seeds are version controlled, they are best suited to files that
contain business-specific logic, for example a list of country codes or user
IDs of employees.
+3. Loading CSVs using dbt's seed functionality is not performant for large
files. Consider using `streamload` to load these CSVs into doris.
+
+Users can see the seeds directory under the dbt project directory, upload the
csv file and seed configuration file in it and run
+```shell
+ dbt seed --select seed_name
+```
+
+Common seed configuration file writing method supports the definition of
column types:
+```yaml
+seeds:
+ seed_name:
+ config:
+ schema: demo_seed
+ full_refresh: true
+ replication_num: 1
+ column_types:
+ id: bigint
+ phone: varchar(32)
+ ip: varchar(15)
+ name: varchar(20)
+ cost: DecimalV3(19,10)
+```
\ No newline at end of file
diff --git a/docs/sidebars.json b/docs/sidebars.json
index 005945a120..350f93755c 100644
--- a/docs/sidebars.json
+++ b/docs/sidebars.json
@@ -236,6 +236,7 @@
"ecosystem/audit-plugin",
"ecosystem/cloudcanal",
"ecosystem/hive-bitmap-udf",
+ "ecosystem/dbt-doris-adapter",
{
"type": "category",
"label": "UDF",
diff --git a/docs/zh-CN/docs/ecosystem/dbt-doris-adapter.md
b/docs/zh-CN/docs/ecosystem/dbt-doris-adapter.md
new file mode 100644
index 0000000000..d173b16d7c
--- /dev/null
+++ b/docs/zh-CN/docs/ecosystem/dbt-doris-adapter.md
@@ -0,0 +1,260 @@
+---
+{
+"title": "DBT Doris Adapter",
+"language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# DBT Doris Adapter
+
+[DBT(Data Build Tool)](https://docs.getdbt.com/docs/introduction)
是专注于做ELT（提取、加载、转换）中的T（Transform）—— “转换数据”环节的组件
+`dbt-doris` adapter 是基于`dbt-core` 1.5.0
开发，依赖于`mysql-connector-python`驱动对doris进行数据转换。
+
+代码仓库：https://github.com/apache/doris/tree/master/extension/dbt-doris
+
+## 版本支持
+
+| doris | python | dbt-core |
+|---------|--------------|----------|
+| >=1.2.5 | >=3.8,<=3.10 | >=1.5.0 |
+
+
+## dbt-doris adapter 使用
+
+### dbt-doris adapter 安装
+使用pip安装：
+```shell
+pip install dbt-doris
+```
+安装行为会默认安装所有dbt运行的依赖，可以使用如下命令查看验证：
+```shell
+dbt --version
+```
+如果系统未识别dbt这个命令，可以创建一条软连接：
+```shell
+ln -s /usr/local/python3/bin/dbt /usr/bin/dbt
+```
+
+### dbt-doris adapter 初始化
+```shell
+dbt init
+```
+会出现询问式命令行，输入相应配置如下即可初始化一个dbt项目：
+
+| 名称 | 默认值 | 含义 |
+|----------|------|------------------------------------------------------|
+| project | | 项目名 |
+| database | | 输入对应编号选择适配器 （选择doris） |
+| host | | doris 的 host |
+| port | 9030 | doris 的 MySQL Protocol Port |
+| schema | | 在dbt-doris中，等同于database，库名 |
+| username | | doris 的 username |
+| password | | doris 的 password |
+| threads | 1 | dbt-doris中并行度 （设置与集群能力不匹配的并行度会增加dbt运行失败风险） |
+
+
+### dbt-doris adapter 运行
+相关dbt运行文档，可参考[此处](https://docs.getdbt.com/docs/get-started/run-your-dbt-projects)。
+进入到刚刚创建的项目目录下面，执行默认的dbt模型：
+```shell
+dbt run
+```
+可以看到运行了两个model：my_first_dbt_model和my_second_dbt_model
+
+他们分别是物化表table和视图view。
+
+可以登陆doris，查看my_first_dbt_model和my_second_dbt_model的数据结果及建表语句。
+
+### dbt-doris adapter 物化方式
+dbt-doris 的 物化方式（Materialization）支持一下三种
+1. view
+2. table
+3. incremental
+
+#### View
+
+使用`view`作为物化模式，在Models每次运行时都会通过 create view as 语句重新构建为视图。(默认情况下，dbt 的物化方式为view)
+```
+优点：没有存储额外的数据，源数据之上的视图将始终包含最新的记录。
+缺点：执行较大转换或嵌套在其他view之上的view查询速度很慢。
+建议：通常从模型的视图开始，只有当存在性能问题时才更改为另一个物化方式。view最适合不进行重大转换的模型，例如重命名，列变更。
+```
+
+配置项：
+```yaml
+models:
+ <resource-path>:
+ +materialized: view
+```
+或者在model文件里面写
+```jinja
+{{ config(materialized = "view") }}
+```
+
+#### Table
+
+使用 `table` 物化模式时，您的模型在每次运行时都会通过 `create table as select` 语句重建为表。
+对于dbt 的tablet物化，dbt-doris 采用以下步骤保证数据更迭时候的原子性：
+1. `create table this_table_temp as {{ model sql}}`，首先创建临时表。
+2. 判断 `this_table` 是否不存在，即是首次创建，执行`rename`，将临时表变更为最终表。
+3. 若已经存在，则 `alter table this_table REPLACE WITH TABLE this_table_temp
PROPERTIES('swap' =
'False')`，此操作可以交换表名并且删除`this_table_temp`临时表，[此过程](../sql-manual/sql-reference/Data-Definition-Statements/Alter/ALTER-TABLE-REPLACE.md)通过Doris内核的事务机制保证本次操作原子性。
+```
+优点：table查询速度会比view快。
+缺点：table需要较长时间才能构建或重建，会额外存储数据，而且不能够做增量数据同步。
+建议：建议对 BI 工具查询的model或下游查询、转换等操作较慢的model使用table物化方式。
+```
+
+配置项：
+```yaml
+models:
+ <resource-path>:
+ +materialized: table
+ +duplicate_key: [ <column-name>, ... ],
+ +replication_num: int,
+ +partition_by: [ <column-name>, ... ],
+ +partition_type: <engine-type>,
+ +partition_by_init: [<pertition-init>, ... ]
+ +distributed_by: [ <column-name>, ... ],
+ +buckets: int | 'auto',
+ +properties: {<key>:<value>,...}
+```
+或者在model文件里面写
+```jinja
+{{ config(
+ materialized = "table",
+ duplicate_key = [ "<column-name>", ... ],
+ replication_num = "<int>"
+ partition_by = [ "<column-name>", ... ],
+ partition_type = "<engine-type>",
+ partition_by_init = ["<pertition-init>", ... ]
+ distributed_by = [ "<column-name>", ... ],
+ buckets = "<int>" | "auto",
+ properties = {"<key>":"<value>",...}
+ ...
+ ]
+) }}
+```
+
+上述配置项详情如下：
+
+| 配置项 | 描述 | Required? |
+|---------------------|--------------------------------------|-----------|
+| `materialized` | 该表的物化形式 （对应创建表模型为明细模型（Duplicate）） | Required |
+| `duplicate_key` | 明细模型的排序列 | Optional |
+| `replication_num` | 表副本数 | Optional |
+| `partition_by` | 表分区列 | Optional |
+| `partition_type` | 表分区类型，range或list .(default: `RANGE`) | Optional |
+| `partition_by_init` | 初始化的表分区 | Optional |
+| `distributed_by` | 表桶区列 | Optional |
+| `buckets` | 分桶数量 | Optional |
+| `properties` | 建表的其他配置 | Optional |
+
+
+
+
+#### Incremental
+
+以上次运行 dbt的 incremental model结果为基准，增量的将记录插入或更新到表中。
+doris的增量实现有两种方式，此项设计两种增量（incremental_strategy设置）的策略：
+*
`insert_overwrite`：依赖于unique模型，如果有增量需求，在初始化该模型的数据时就指定物化为incremental，通过指定聚合列进行聚合，实现增量数据的覆盖。
+* `append`：依赖于`duplicate`模型，仅仅对增量数据做追加，不涉及修改任何历史数据。因此不需要指定unique_key。
+```
+优点：只需转换新记录，可显著减少构建时间。
+缺点：incremental模式需要额外的配置，是 dbt 的高级用法，需要复杂场景的支持和对应组件的适配。
+建议：增量模型最适合基于事件相关的场景或 dbt 运行变得太慢时使用增量模型
+```
+
+配置项：
+```yaml
+models:
+ <resource-path>:
+ +materialized: incremental
+ +incremental_strategy: <strategy>
+ +unique_key: [ <column-name>, ... ],
+ +replication_num: int,
+ +partition_by: [ <column-name>, ... ],
+ +partition_type: <engine-type>,
+ +partition_by_init: [<pertition-init>, ... ]
+ +distributed_by: [ <column-name>, ... ],
+ +buckets: int | 'auto',
+ +properties: {<key>:<value>,...}
+```
+或者在model文件里面写
+```jinja
+{{ config(
+ materialized = "incremental",
+ incremental_strategy = "<strategy>"
+ unique_key = [ "<column-name>", ... ],
+ replication_num = "<int>"
+ partition_by = [ "<column-name>", ... ],
+ partition_type = "<engine-type>",
+ partition_by_init = ["<pertition-init>", ... ]
+ distributed_by = [ "<column-name>", ... ],
+ buckets = "<int>" | "auto",
+ properties = {"<key>":"<value>",...}
+ ...
+ ]
+) }}
+```
+
+上述配置项详情如下：
+
+| 配置项 | 描述 |
Required? |
+|----------------------------|--------------------------------------|-----------|
+| `materialized` | 该表的物化形式 | Required
|
+| `incremental_strategy` | 增量策略 | Optional
|
+| `unique_key` | unique表的key列 | Optional
|
+| `replication_num` | 表副本数 | Optional
|
+| `partition_by` | 表分区列 | Optional
|
+| `partition_type` | 表分区类型，range或list .(default: `RANGE`) | Optional
|
+| `partition_by_init` | 初始化的表分区 | Optional
|
+| `distributed_by` | 表桶区列 | Optional
|
+| `buckets` | 分桶数量 | Optional
|
+| `properties` | 建表的其他配置 | Optional
|
+
+### dbt-doris adapter seed
+
+[`seed`](https://docs.getdbt.com/faqs/seeds/build-one-seed)
是用于加载csv等数据文件时的功能模块，它是一种加载文件入库参与模型构建的一种方式，但有以下注意事项：
+1. seed不应用于加载原始数据（例如，从生产数据库导出大型 CSV文件）。
+2. 由于seed是受版本控制的，因此它们最适合包含特定于业务的逻辑的文件，例如国家/地区代码列表或员工的用户 ID。
+3. 对于大文件，使用 dbt 的seed功能加载 CSV 的性能不佳。应该考虑使用streamload等方式将这些 CSV 加载到doris中。
+
+用户可以在dbt project的目录下面看到 seeds的目录，在里面上传csv 文件和seed配置文件并运行
+```shell
+ dbt seed --select seed_name
+```
+
+常见seed配置文件写法,支持对列类型的定义：
+```yaml
+seeds:
+ seed_name: # 种子名称，在seed 构建后，会作为表名
+ config:
+ schema: demo_seed # 在seed 构建后，会作为database 的一部分
+ full_refresh: true
+ replication_num: 1
+ column_types:
+ id: bigint
+ phone: varchar(32)
+ ip: varchar(15)
+ name: varchar(20)
+ cost: DecimalV3(19,10)
+```
\ No newline at end of file
diff --git a/extension/dbt-doris/dbt/adapters/doris/__version__.py
b/extension/dbt-doris/dbt/adapters/doris/__version__.py
index 182e7d11f2..5aa23e7a71 100644
--- a/extension/dbt-doris/dbt/adapters/doris/__version__.py
+++ b/extension/dbt-doris/dbt/adapters/doris/__version__.py
@@ -22,4 +22,4 @@
# this 'version' must be set !!!
# otherwise the adapters will not be found after the 'dbt init xxx' command
-version = "0.3.0"
+version = "0.3.1"
diff --git a/extension/dbt-doris/dbt/include/doris/dbt_project.yml
b/extension/dbt-doris/dbt/include/doris/dbt_project.yml
index 5712a0e267..6abec6e23f 100644
--- a/extension/dbt-doris/dbt/include/doris/dbt_project.yml
+++ b/extension/dbt-doris/dbt/include/doris/dbt_project.yml
@@ -19,7 +19,7 @@
# under the License.
name: dbt_doris
-version: 0.3.0
+version: 0.3.1
config-version: 2
macro-paths: ["macros"]
diff --git a/extension/dbt-doris/setup.py b/extension/dbt-doris/setup.py
index 62916d1021..ebbc15b2a2 100644
--- a/extension/dbt-doris/setup.py
+++ b/extension/dbt-doris/setup.py
@@ -22,7 +22,7 @@ from setuptools import find_namespace_packages, setup
package_name = "dbt-doris"
# make sure this always matches dbt/adapters/{adapter}/__version__.py
-package_version = "0.3.0"
+package_version = "0.3.1"
dbt_core_version = "1.5.0"
description = """The doris adapter plugin for dbt """
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
