DBT hook 实战教程

本文将介绍dbt中在模型和seed级别使用post-hook的几个具体示例。dbt中的Post-hooks是一个强大而简单的特性，它在构建模型之后(如果是pre-hook，甚至在此之前)执行SQL语句。这些语句实际上(几乎)可以是任何东西，从将表复制到另一个数据库/模式，或限制记录的数量，或重新格式化seed。我们将讨论的示例是指DuckDB，但是也可以适用于其他数据库。

认识 dbt hook

将原始数据转换为可供下游消费者使用的模型，直接使用SQL非常实用，但dbt默认不支持，仅支持SELECT 语句。如果遇到下列场景，我们可能需要SQL实现：

• 管理计算层的大小或容量
• 应用屏蔽策略或访问策略
• 管理数据库参数

dbt hook可以实现这些特殊任务。与dbt项目中的许多资源不同，钩子可以使用简单的SELECT语句之外的SQL命令，这打开了充满可能性的新世界。dbt hook 主要分为：

• On-run-start/end: 用于在执行某些dbt命令之前/之后运行SQL查询

在下列命令的开始或结束处运行SQL语句(或SQL语句列表): dbt build, dbt compile, dbt docs generate, dbt run, dbt seed, dbt snapshot, dbt test`. on-run-start和on-run-end hook也可以宏。语法如下：

-- dbt_project.yml
on-run-start: sql-statement | [sql-statement]
on-run-end: sql-statement | [sql-statement]

• Pre-/post-hooks: 用于在执行某些dbt节点之前/之后运行SQL查询

在model, seed, snapshot 被构建之前/后运行SQL语句(或SQL语句列表), Pre-/post-hooks子也可以调用宏。dbt旨在通过开箱即用的功能提供SQL模版代码 (DDL、DML和DCL)，从而快速而简洁地配置模型转换业务。当需要执行dbt尚未提供的特性时，我们可以使用dbt的编译上下文编写所需的SQL，并将其传递到Pre-/post-hooks，以便在model, seed 或 snapshot之前或之后运行。语法如下：

-- dbt_project.ymlmodels:<resource-path>:+pre-hook: SQL-statement | [SQL-statement]+post-hook: SQL-statement | [SQL-statement]

也可以在模型中配置：

-- models/<model_name>.sql{{ config(pre_hook="SQL-statement" | ["SQL-statement"],post_hook="SQL-statement" | ["SQL-statement"],
) }}select ...

对于seed 和 snapshot 语法类似。

dbt模型使用post-hook

想象有业务需求如是：我们dbt项目中模型需存储在特定schema中，其访问权限是不公开的。这意味着组织中的任何人都无法从该模式中读取数据。这样做的原因可能与包含个人敏感数据有关，或者仅仅是数据治理规范，团队希望控制创建表的访问和使用权限。

我们可以使用post-hook实现对数据的访问，同时不影响安全管理规范。在本例中，这些数据需要被其他团队使用，我们将表的内容复制到另一个公共模式/表中:

{{config(materialized='table',post_hook=["CREATE OR REPLACE TABLE {{ env_var('DBT_DATABASE') }}.public_schema.mart_orders AS SELECT * FROM {{this}};"]
)
}}SELECT *
FROM {{ ref('ref_orders') }}

当这个模型运行时，用于构建模型的SQL首先运行，紧接着post-hook语句被执行。在这个示例中，我们使用:

• {{this}}函数，它引用了在此文件中构建的模型，特别是它在数据库中的表示
• env_var函数获取存储在profiles文件中的数据库变量。从技术上讲，如果您已经为本地使用设置了DEV环境，则不需要添加它，因为dbt将自动指向它。然而，这似乎是一个很好的做法，声明它，也使读者清楚。

请注意，建议这里使用{{this}}，特别是如果在两个独立的环境，一个用于本地开发，一个用于生产。如前所述，如果在本地运行该模型，dbt将把FROM {{this}}和CREATE TABLE语句转换为在配置文件中声明的本地开发数据库。然而，很可能实际不需要在开发数据库中使用公开表，而只需要在生产中使用。在这种情况下，可以简单地在post-hook中进行显式设置，只需从生产环境中获取数据并将其构建到生产环境中。代码示例如下：

{{config(materialized='table',post_hook=["CREATE OR REPLACE TABLE prod_database.public_schema.mart_orders AS SELECT * FROM prod_database.team_private_schema.mart_orders;"]
)
}}SELECT *
FROM {{ ref('ref_orders') }}

现在，让我们给post-hook逻辑增加两个业务用例，基于以下现实场景:

• 我们要公开的表包含一些个人隐私数据，如customer_email或customer_phone_number
• 公共模式仅用于“展示”模型的内容，而实际的访问是在其他地方(例如按需)提供的，并且直接访问存储在私有schema中的表。

对于第一个用例，我们仅需排除不想暴露的字段以返回其部分内容:

{{config(materialized='table',post_hook=["CREATE OR REPLACE TABLE prod_database.public_schema.mart_orders AS SELECT * EXCLUDE (customer_email, customer_phone_number), split(customer_email,'@')[2] AS customer_email FROM prod_database.team_private_schema.mart_orders;"]
)
}}SELECT *
FROM {{ ref('ref_orders') }}

对于第二个用例，我们只是限制表中返回的记录数量。并添加一列，其中包含友好的提醒信息，以便与团队联系以访问表格：

{{config(materialized='table',post_hook=["CREATE OR REPLACE TABLE prod_database.public_schema.mart_orders AS SELECT 'request access to the table at [e-mail]' AS readme, * EXCLUDE (customer_email, customer_phone_number), split(customer_email,'@')[2] AS customer_email FROM prod_database.team_private_schema.mart_orders
LIMIT 5;"]
)
}}SELECT *
FROM {{ ref('ref_orders') }}

dbt seed使用post-hook

Dbt seed可以直接加载CSV文件到数据仓库中，通常拥有处理不经常更改的静态数据。有时这些csv是由多个业务团体提供的，格式上可能缺乏标准化。让我们看一个简单的例子，理解post-hook是如何派上用场的。

我们有一个seed文件，包含新旧产品的名称映射，在中间层模型连接引用。我们希望确保列中的值都转换为小写，因为这是编码格式约定，要做的是在seed的.yml配置文件中添加post-hook和相关的SQL语句:

version: 2seeds:- name: seed_product_namesconfig:post-hook: "CREATE OR REPLACE TABLE prod_database.team_private_schema.seed_product_names AS SELECT LOWER(old_product_name) AS old_product_name, LOWER(new_product_name) as new_product_name FROM prod_database.team_private_schema.seed_product_names;"description: >This seed contains all product names (old and new versions)columns:- name: old_product_namedescription: The old product name used in previous platformtype: string- name: new_product_namedescription: The new product name used in current platformtype: string

dbt seed命令运行后，执行post-hook中的SQL，将dbt seed刚刚构建的模型替换为遵循约定的重新格式化版本。

很明显,我们可以在添加至seed之前直接格式化CSV文件：然而添加格式规则在SQL不仅能给你更多的质量控制能力，也会提高文档描述，因为格式化内容在SQL和描述中已清晰地说明。

或许有人认为这种重新格式化SQL操作，可以通过构建从seed获取数据的staging模型表来实现。虽然这显然是可行的，但它违背了将这些“规范”映射CSV文件存储和记录为dbt seed的约定。

同样与dbt模型一样，post-hook可以用于各种数据操作常见(例如，在seed中连接字符串创建新列，截取或替换字符串等)，这些都可以通过SQL语句实现。

总结

本文通过提供在模型和种子级别上使用post-hook的具体示例，展示了dbt中post-hook的多种应用场景。这些示例演示了post-hook如何用于复制表、限制返回记录和重新格式化数据。总之，通过使用post-hooks，用户可以更好地控制数据质量并提升模型文档。期待您的真诚反馈，更多内容请阅读数据分析工程专栏。