ClickHouse 和 dbt - 社区馈赠

ClickHouse 团队

2023年2月1日

作为一家致力于开源理念的公司，我们不仅要接受来自社区的请求，还要接受功能和新集成的请求。

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

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

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

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

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

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

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

dbt 支持一个适配器插件 API，允许用户连接到未包含在核心产品中的数据平台。在2021 年初，ClickHouse 社区成员Dmitriy Sokolov为了满足自己的需求创建了一个插件，并慷慨地开源了他的工作。为了应对日益增长的采用率和一年多后，该项目被转移到 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，其模式与原始表相同。使用带有检查以确保记录不在 T 中的 INSERT INTO SELECT 将数据流式传输到表 N 中。最后，我们将 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 中新功能的最新进展。有关更多详细信息，我们建议您参阅最近的网络研讨会和我们的文档中的示例。建模愉快。

分享此文章