博客 / 工程

ClickHouse 和 dbt - 来自社区的礼物

ClickHouse 团队

2023年2月1日 - 11 分钟阅读

作为一家致力于开源精神的公司，接受来自社区的请求以及功能和新的集成至关重要。

上周，我们举办了一场网络研讨会，讨论了这样一个项目 - dbt-clickhouse 插件。

该插件最初由社区贡献者创建，旨在满足他们的需求并以 OSS 精神发布，随着用户将 dbt 的功能与 ClickHouse 结合使用，该插件的采用率不断提高。

在这篇博文中，我们将探讨 dbt，它与 ClickHouse 结合使用时可能带来的价值，以及一个关于不断发展的支持的故事，以利用 ClickHouse 最近发布的新功能。

dbt（数据构建工具）使分析工程师能够通过简单地编写 SELECT 语句来转换他们数据仓库中的数据。dbt 处理将这些 SELECT 语句物化为数据库中的对象，形式为表和视图 - 执行提取、加载和转换 (ELT)的 T。用户可以创建一个由 SELECT 语句定义的模型。

在 dbt 中，这些模型可以交叉引用和分层，以构建更高级别的概念。连接模型所需的样板 SQL 会自动生成。此外，dbt 识别模型之间的依赖关系，并确保使用有向无环图 (DAG) 以适当的顺序创建它们。

总之，用户可以定义一个表示概念的模型，例如，以 SQL 形式表示的演员出场次数摘要。然后，这些模型可以物化*为视图或表。然后，可以将这些模型组合起来以生成更复杂的模型。

在我们最近的网络研讨会中，我们展示了 IMDB 电影数据的以下数据模式。由此，我们利用 dbt 创建了两个模型：一个总结导演，另一个提供每个演员的概述，例如，电影出场次数、他们的平均评分、他们主要合作的导演等。在这种情况下，演员的模型依赖于导演的模型。dbt 允许将这些模型定义为 SQL 语句，并使用 Jinja 模板语言来连接和引用它们。请注意，某些数据源甚至不在 ClickHouse 中，电影类型和电影导演表分别在 S3 和 Postgres 中。然后，最终模型在 ClickHouse 中表示为名为 actor_summary 的视图或表。

dbt 支持适配器插件 API，以允许用户连接到核心产品中未包含的数据平台。在 2021 年初，ClickHouse 社区成员 Dmitriy Sokolov 创建了一个插件，以支持 ClickHouse 以满足他的要求，并慷慨地开源了他的工作。为了响应不断增长的采用率以及稍稍一年多后，该项目被转移到 ClickHouse，并正式成为供应商支持的插件。我们将继续投资于此插件，以支持 dbt 的最新功能，并利用 ClickHouse 的最佳实践来公开此功能。

我们针对 ClickHouse Cloud 严格测试此插件，并确保当前的 dbt 插件测试套件通过。

我们主要看到用户将 dbt 与 ClickHouse 一起用于以下多项任务

数据分析师为业务部门的 BI 目的或需要特定视图来构建最新模型的数据科学家提供数据视图请求。管理这些视图和模型并确保它们被跟踪、最小化、测试、记录和版本化至关重要。
用户在各种系统（如 Postgres、MySQL 和 S3）中拥有数据，并希望将这些数据建模为 ClickHouse 中的一组表或视图。使用 ClickHouse 的表函数和引擎，用户创建 dbt 模型来管理此数据迁移过程。反过来，他们受益于 dbt 将这些数据集成任务表示为代码，然后可以使用 dbt 功能对其进行版本控制、记录和测试。
用户通常需要将数据从其他 OLTP 数据库（如 Postgres）迁移到 ClickHouse，以服务于实时分析工作负载。在简单的情况下，可以使用原生 ClickHouse 表函数来实现。但是，由许多受更新影响的源表表示的 ClickHouse 表通常需要变更数据捕获流程。Dbt 可以协助管理和建模此过程中所需的查询。
用户可能还需要跟踪 ClickHouse 中行的更改 - 一种变更捕获形式 - 以回答有关数据库先前状态的问题。Dbt 提供了诸如增量模型和快照之类的功能，可以帮助满足这些要求。

持久化模型 SQL 查询结果的过程称为“物化模型”。模型可以通过多种方式物化。clickhouse-dbt 插件确保使用最合适的 ClickHouse 功能和最佳实践来实现这一点

视图物化 - 最简单和默认的方法。这里，模型只是使用 CREATE VIEW AS 语法在 ClickHouse 中创建。当运行 dbt 时，对模型的更改会导致视图被重新创建。
表格物化- 来自模型 SQL 查询的结果被流式传输到具有适当模式的新表中，使用 INSERT INTO table SELECT <model_query> 语句。用户为将模型表示为表而支付存储成本，以获得性能提升的好处。
增量物化- 提供了一种将模型物化为表但保持更新的方法。模型的后续运行使用唯一键字段和条件（例如，基于时间戳）识别源数据中的更改，并确保这反映在 ClickHouse 中的目标表中。这对于与 ClickHouse 关联的事件类型数据非常强大。
临时物化 - 将模型表示为 CTE，然后可以在其他模型中引用它。

不同类型的模型可以组合起来以生成更复杂的模型，从而允许对组件部分进行测试和记录。下面显示了在网络研讨会上使用的演员模型的简单表格物化

{{ config(order_by='director_id', engine='MergeTree()', materialized='table') }}


WITH directors AS (
   SELECT directors.id as director_id, movie_id, first_name, last_name FROM {{ source('imdb', 'movie_directors') }} OUTER JOIN {{ source('imdb', 'directors') }} ON {{ source('imdb', 'movie_directors') }}.director_id = {{ source('imdb', 'directors') }}.id
)

select *
from directors

有关更多详细信息，请参阅网络研讨会录像或我们的文档，其中包含类似的示例。

增量物化代表了在 ClickHouse 中实现的最复杂的模型类型。从历史上看，ClickHouse 对更新和删除的支持有限，形式为异步mutation。这些需要谨慎使用，因为它们会重写所有受影响的数据部分，因此可能会消耗大量 IO。

假设我们有一个原始表 X，它表示一个物化模型。为了实现 dbt 所需的增量语义，默认情况下，dbt-clickhouse 会创建一个新的临时表 T，其中包含用户指定的条件（例如 select max(updated_at) from {{this}}）标识的新记录。然后，创建一个与原始表具有相同模式的新表 N。数据从原始表流式传输到表 N 中，使用 INSERT INTO SELECT 并检查以确保记录不在 T 中。最后，我们将记录从 T 流式传输到 N 中，并与 X 和 N 进行原子表交换，以公开新的已实现模型。下图对此进行了可视化

虽然这种方法有效，但它涉及为每次 dbt 运行和捕获更改而创建的数据的完全重复。理想情况下，我们将避免这种开销，而只写入新记录。上述过程很复杂，因为它支持源数据中的重复项和更新。如果用户可以接受自己在 ClickHouse 中处理重复项，或者确信其源数据是不可变的且仅追加的，则可以将增量模型配置为使用 append-only 策略。在这种情况下，只需识别源数据中的更改，并将其直接流式传输到现有表中，如下所示

虽然为部分情况改善了状况，但我们仍然对当前解决方案不满意，尤其是在可能出现重复项的情况下。幸运的是，在 22.8 版本中，ClickHouse 添加了轻量级删除作为一项实验性功能。轻量级删除比 ALTER TABLE ... DELETE 操作快得多，因为它们不需要重写 ClickHouse 数据部分。只需将“墓碑”添加到已删除的行，即可在查询时自动排除记录。然后，这些已删除的记录会在正常的合并过程中合并消失。有了不会消耗大量 IO 并威胁集群稳定性的删除功能，我们可以添加一种称为 delete+insert 的新方法来增量物化。

总而言之，这种方法

创建一个临时表 T。已更改的行被流式传输到此表中。针对当前表 X 发出 DELETE，根据 T 中的值按 id 删除行。
使用 INSERT INTO X SELECT * FROM T 将 T 中的行插入到当前表 X 中。

我们在下面对此进行了可视化

我们的测试表明，这种方法比“传统”策略性能更好。但是，使用此策略有一些重要的注意事项

必须在您的 ClickHouse 服务器上启用设置 allow_experimental_lightweight_delete，或者将其包含在您的 ClickHouse 配置文件的 custom_settings 中。
正如设置名称所暗示的那样，轻量级删除功能仍处于实验阶段，尚未被完全视为生产就绪，因此使用应仅限于易于重新创建的数据集。
此策略直接在受影响的表/关系上操作（不创建任何中间表或临时表），因此如果在操作过程中出现问题，增量模型中的数据很可能处于无效状态

虽然前两个问题很可能很快会随着轻量级删除功能预计很快 GA 而得到解决，但后者需要进一步的开发工作。您说事务怎么样？嗯，那是个好主意……

在这篇文章中，我们介绍了 dbt 和 ClickHouse 插件。我们介绍了 dbt 对 ClickHouse 用户的价值，并讨论了一些关键功能，然后介绍了基于 ClickHouse 新功能的近期发展。有关更多详细信息，我们建议观看近期网络研讨会和我们的文档中的示例。建模愉快。

分享这篇文章